Unsupported operand types in if/else list comprehension

[Mike H]

Hello all, I have a question about the if/else aspect of list comprehension:

I would like to go through a list and place quotes around an item if
it is a string, and keep the item the same if it's anything else:

e.g.['a',9,'8b'] --> ['"a"', 9, '"8b"']

I understand that if/else list comprehension should be generally:

b=[(F,T)[boolean test] for val in X]

so, I tried the following code:

a=['test',1,'two']
b=[(inst, '"'+inst+'"')[isinstance(inst, str)] for inst in a]

I end up getting the error: unsupported operand type(s) for +: 'int' and 'str'

From playing around with other examples, I get the feeling that Python

is calculating both values (inst and '"'+inst+'"') before selecting
which one to pass to the new list. Am I right? Is there any way I can
do this using list comprehension?

Thanks in advance,

Michael

 

[John Yeung]

From playing around with other examples, I get the feeling
that Python is calculating both values (inst and '"'+inst+'"')
before selecting which one to pass to the new list. Am I right?

I believe so. (I'm sure the experts here will tell you more
definitively.)

Is there any way I can do this using list comprehension?

Yes. If you are using 2.5 or later, you can do this:

Python 2.6.1 (r261:67517, Dec 4 2008, 16:51:00) [MSC v.1500 32 bit
(Intel)] on
win32
Type "help", "copyright", "credits" or "license" for more information.

a=['test',1,'two']
b=[(x if not isinstance(x, str) else '"'+x+'"') for x in a]
b ['"test"', 1, '"two"']

If you are trying to make a CSV file, then even better may be to use
the csv module.

John

 

[Mike H]

Thanks to all of you.

FYI, I'm doing this because I'm working on creating some insert
statements in SQL, where string values need to be quoted, and integer
values need to be unquoted.

I wanted to be sure that I could pass these values to the list in a
normal way e.g. ['test', 1, 'two'] and have a function correct the
list for me, rather than calling the function with a strangely quoted
list e.g. ['"'test'"', 1, '"'two'"'].

Again, thanks.

From playing around with other examples, I get the feeling
that Python is calculating both values (inst and '"'+inst+'"')
before selecting which one to pass to the new list. Am I right?

I believe so.  (I'm sure the experts here will tell you more
definitively.)

Is there any way I can do this using list comprehension?

Yes.  If you are using 2.5 or later, you can do this:

Python 2.6.1 (r261:67517, Dec  4 2008, 16:51:00) [MSC v.1500 32 bit
(Intel)] on
win32
Type "help", "copyright", "credits" or "license" for more information.

a=['test',1,'two']
b=[(x if not isinstance(x, str) else '"'+x+'"') for x in a]
b ['"test"', 1, '"two"']

If you are trying to make a CSV file, then even better may be to use
the csv module.

John

 

[Miles]

Thanks to all of you.

FYI, I'm doing this because I'm working on creating some insert
statements in SQL, where string values need to be quoted, and integer
values need to be unquoted.

This is what you should have posted in the first place. Your solution
is entirely the wrong one, because it will break if your input strings
contain the quote character (and suffers from other issues as
well)--this is where SQL injection vulnerabilities come from. The
safe and correct way is to allow your database driver to insert the
parameters into the SQL query for you; it will look something like
this (though the exact details will vary depending on what module
you're using):

cursor.execute('INSERT INTO my_table VALUES (?, ?, ?)', ['test',1,'two'])

-Miles

 

[Steven D'Aprano]

Hello all, I have a question about the if/else aspect of list
comprehension:

I would like to go through a list and place quotes around an item if it
is a string, and keep the item the same if it's anything else:

e.g.['a',9,'8b'] --> ['"a"', 9, '"8b"']

I understand that if/else list comprehension should be generally:

b=[(F,T)[boolean test] for val in X]

That's *one* specific form of a list comprehension. A more general form
is:

alist = [ some-expression for val in sequence if condition ]

In your case, (F, T)[boolean test] counts as one such possible
expression. It's not a "magic" list comprehension syntax, you can use it
anywhere:

t = ("not true", "correct")
t[45 > 15] 'correct'
("incorrect", "true")[15 > 99]

'incorrect'

The disadvantage of the (F, T)[bool] expression is that both F and T are
evaluated *before* the boolean test. Think about it: you have to create
the tuple (F, T) before you can index into it!


In your case, there are three obvious solutions (untested). In no
particular order:

(1) Use a helper function.

def quote_obj(obj):
"""Return strings quoted, and all other objects unchanged."""
if isinstance(obj, basestring):
return '"%s"' % obj
else:
return obj

b = [quote_obj(x) for x in alist]


(2) Use the ternary if expression:

b = ['"%s"' % obj if isinstance(obj, basestring) else obj for obj in
alist]


(3) Use a regular for loop with an accumulator:

b = []
for obj in alist:
if isinstance(obj, basestring):
obj = '"%s"' % obj
b.append(obj)



The less obvious solution is to rethink your program design. Having
multiple types of object in the one list is often (but not always) a sign
that your design is too complicated and is trying to do to much in too
little code. It is a mild code-smell:

http://www.joelonsoftware.com/articles/Wrong.html

 

[Diez B. Roggisch]

Thanks to all of you.

FYI, I'm doing this because I'm working on creating some insert
statements in SQL, where string values need to be quoted, and integer
values need to be unquoted.

I wanted to be sure that I could pass these values to the list in a
normal way e.g. ['test', 1, 'two'] and have a function correct the
list for me, rather than calling the function with a strangely quoted
list e.g. ['"'test'"', 1, '"'two'"'].>

Don't do that yourself. This is error-prone. Instead, use the
parametrized verison of the cursor.execute-method. It will perform the
necessary escaping, and depending on the database and database adapter
you use better performance.

Diez

 

[Mike H]

Ok, thanks again to everyone for their suggestions, even if it appears
I was going down the wrong path at the start. I'm a grad student
creating this database to hold some of my own research on an isolated
server, so security, etc. isn't my biggest concern -- but I would like
to do this right. Here's the code that I've come up with now. Although
it's gotten away from the original question, those that have commented
on this seem to have some SQL knowledge, so I'd like to run it by them
to see if this is better in theory. (I've tried it and it works in
practice!)

FYI, I'm using MySQLdb to connect with the Database.

Also, I realize I should probably add in some try/catch statements and
other error handling... but this is what I have at the moment.



def insert_cmd(myTable, myFields, myValues, myReturnKey):
"""Imports given fields and values into a given table, returns an
SQL variable holding the Autoincrement key"""

#tests to see if myParentKey is valid in mySQL.
if not myReturnKey.startswith("@"): print "Error, myReturnKey must
start with '@'"; sys.exit()

SQLcmd="INSERT INTO " + myTable + " (%s) " % ", ".join(myFields)
SQLcmd=SQLcmd + "VALUES (%s,%s,%s);"
cursor.execute(SQLcmd, (myValues))

#sets and returns SQL variable.
SQLcmd="select " + myReturnKey + ":=last_insert_id();"
cursor.execute(SQLcmd)
return myReturnKey

Thanks to all of you.

FYI, I'm doing this because I'm working on creating some insert
statements in SQL, where string values need to be quoted, and integer
values need to be unquoted.

I wanted to be sure that I could pass these values to the list in a
normal way e.g. ['test', 1, 'two'] and have a function correct the
list for me, rather than calling the function with a strangely quoted
list e.g. ['"'test'"', 1, '"'two'"'].>

Don't do that yourself. This is error-prone. Instead, use the parametrized
verison of the cursor.execute-method. It will perform the necessary
escaping, and depending on the database and database adapter you use better
performance.

Diez

 

[Mike H]

Well, I'm an idiot. Obviously, the line "VALUES (%s, %s, %s);" needs
to be modified to adapt for the number of arguments in the list. But
otherwise....

Ok, thanks again to everyone for their suggestions, even if it appears
I was going down the wrong path at the start. I'm a grad student
creating this database to hold some of my own research on an isolated
server, so security, etc. isn't my biggest concern -- but I would like
to do this right. Here's the code that I've come up with now. Although
it's gotten away from the original question, those that have commented
on this seem to have some SQL knowledge, so I'd like to run it by them
to see if this is better in theory. (I've tried it and it works in
practice!)

FYI, I'm using MySQLdb to connect with the Database.

Also, I realize I should probably add in some try/catch statements and
other error handling... but this is what I have at the moment.



def insert_cmd(myTable, myFields, myValues, myReturnKey):
   """Imports given fields and values into a given table, returns an
SQL variable holding the Autoincrement key"""

   #tests to see if myParentKey is valid in mySQL.
   if not myReturnKey.startswith("@"): print "Error, myReturnKey must
start with '@'"; sys.exit()

   SQLcmd="INSERT INTO " + myTable + " (%s) " % ", ".join(myFields)
   SQLcmd=SQLcmd + "VALUES (%s,%s,%s);"
   cursor.execute(SQLcmd, (myValues))

   #sets and returns SQL variable.
   SQLcmd="select " + myReturnKey + ":=last_insert_id();"
   cursor.execute(SQLcmd)
   return myReturnKey

Thanks to all of you.

FYI, I'm doing this because I'm working on creating some insert
statements in SQL, where string values need to be quoted, and integer
values need to be unquoted.

I wanted to be sure that I could pass these values to the list in a
normal way e.g. ['test', 1, 'two'] and have a function correct the
list for me, rather than calling the function with a strangely quoted
list e.g. ['"'test'"', 1, '"'two'"'].>

Don't do that yourself. This is error-prone. Instead, use the parametrized
verison of the cursor.execute-method. It will perform the necessary
escaping, and depending on the database and database adapter you use better
performance.

Diez

 

[Mike H]

Sigh. One more. And again, thank you for all of the help.

I realized that the last version that I posted took care of an SQL
injection problem for the values, but not for the fields. So, I went
ahead and modified the code:

def new_insert_cmd(myTable, myFields, myValues):
"""Imports given fields and values into a given table, returns the
Autoincrement value."""
SQLcmd="INSERT INTO " + myTable + " ( " +
create_input_string(myFields) + " ) VALUES ( " \
+ create_input_string(myValues) +" );"
allArguments=myFields+myValues
cursor.execute(SQLcmd, (allArguments))

create_input_strings() is just a function that creates the necessary
number of %s's for a given list (and which I'm sure there's a faster
way to code):

def create_input_string(myList):
sOut=""
for var in myList:
sOut=sOut+"%s, "
return sOut[:-2]

However, now the cursor.execute statement won't work. I've looked at
the content of SQLcmd and the values of allArguments and they seem
fine.

I've even tried running this at the IDLE command line:

cursor.execute("INSERT INTO plan (%s, %s, %s) VALUES (%s, %s, %s);",
(["name", "fileno", "size", "Test", "AAA-000", 7])) and I get this
error:

File "C:\Python25\lib\site-packages\MySQLdb\cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "C:\Python25\lib\site-packages\MySQLdb\connections.py", line
35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near ''name', 'fileno', 'size') VALUES ('Test',
'AAA-000', 7)' at line 1")

Can I not use the cursor.execute command to pass variables that aren't
immediately next to each other? If so, is there a better way to go
about solving this problem?

Again, thanks for your patience and help with a newbie.

Michael

Well, I'm an idiot. Obviously, the line "VALUES (%s, %s, %s);" needs
to be modified to adapt for the number of arguments in the list. But
otherwise....

Ok, thanks again to everyone for their suggestions, even if it appears
I was going down the wrong path at the start. I'm a grad student
creating this database to hold some of my own research on an isolated
server, so security, etc. isn't my biggest concern -- but I would like
to do this right. Here's the code that I've come up with now. Although
it's gotten away from the original question, those that have commented
on this seem to have some SQL knowledge, so I'd like to run it by them
to see if this is better in theory. (I've tried it and it works in
practice!)

FYI, I'm using MySQLdb to connect with the Database.

Also, I realize I should probably add in some try/catch statements and
other error handling... but this is what I have at the moment.



def insert_cmd(myTable, myFields, myValues, myReturnKey):
   """Imports given fields and values into a given table, returns an
SQL variable holding the Autoincrement key"""

   #tests to see if myParentKey is valid in mySQL.
   if not myReturnKey.startswith("@"): print "Error, myReturnKey must
start with '@'"; sys.exit()

   SQLcmd="INSERT INTO " + myTable + " (%s) " % ", ".join(myFields)
   SQLcmd=SQLcmd + "VALUES (%s,%s,%s);"
   cursor.execute(SQLcmd, (myValues))

   #sets and returns SQL variable.
   SQLcmd="select " + myReturnKey + ":=last_insert_id();"
   cursor.execute(SQLcmd)
   return myReturnKey

Mike H schrieb:

Thanks to all of you.

FYI, I'm doing this because I'm working on creating some insert
statements in SQL, where string values need to be quoted, and integer
values need to be unquoted.

I wanted to be sure that I could pass these values to the list in a
normal way e.g. ['test', 1, 'two'] and have a function correct the
list for me, rather than calling the function with a strangely quoted
list e.g. ['"'test'"', 1, '"'two'"'].>

Don't do that yourself. This is error-prone. Instead, use the parametrized
verison of the cursor.execute-method. It will perform the necessary
escaping, and depending on the database and database adapter you use better
performance.

Diez

 

[Dennis Lee Bieber]

SQLcmd="INSERT INTO " + myTable + " (%s) " % ", ".join(myFields)
SQLcmd=SQLcmd + "VALUES (%s,%s,%s);"

For some reason I have never liked using + to combine strings. It
breaks up the flow when reading the code. Compare to

SQLcmd = ("insert into %s (%s) values (%s)" % # open ( allows
(myTable, # multiline
", ".join(myFields), #statement
", ".join(["%s"] * len(myFields) ) ) )


.... one set of quotes, an obvious string with three interpolation
fields; the fields are "myTable", a comma-separated list made from
"myFields", and a comma-separated list of %s markers whose length
matches that of the length of myFields.

myTable = "ThatTable"
myFields = [ "This",

.... "That",
.... "and",
.... "the",
.... "Other" ].... (myTable,
.... ", ".join(myFields),
.... ", ".join(["%s"] * len(myFields) ) ) )'insert into ThatTable (This, That, and, the, Other) values (%s, %s, %s,
%s, %s)'
If you use triple-quotes, you can even do a multiline formatting of
the SQL...
.... """insert into %s
.... (%s)
.... values (%s)""" %
.... (myTable,
.... ", ".join(myFields),
.... ", ".join(["%s"] * len(myFields))))'insert into ThatTable\n\t\t(This, That, and, the, Other)\n\tvalues (%s,
%s, %s, %s, %s)'insert into ThatTable
(This, That, and, the, Other)
values (%s, %s, %s, %s, %s)

cursor.execute(SQLcmd, (myValues))

Good as is...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[George Sakkis]

Can I not use the cursor.execute command to pass variables that aren't
immediately next to each other? If so, is there a better way to go
about solving this problem?

Yes, there is. Use one of the several production quality python SQL
toolkits built for exactly this purpose instead of putting together an
ad-hoc, informally-specified bug-ridden slow implementation of half of
their feature set.

George

 

[Mike H]

George,

I'd love to. Can you give me an idea of where to start looking? I've
gone through a couple of books, and Googled a ton of websites. Maybe
I'm just not using the right terms. My background is definitely not
CompSci. But if you'd give me a suggestion of where to look, I'd
appreciate it.

Thanks,

 

[George Sakkis]

George,

I'd love to. Can you give me an idea of where to start looking? I've
gone through a couple of books, and Googled a ton of websites. Maybe
I'm just not using the right terms. My background is definitely not
CompSci. But if you'd give me a suggestion of where to look, I'd
appreciate it.

Sure; off the top of my head, check out SQLAlchemy [1], SQLObject [2]
or Storm [3]. SQLAlchemy is probably the most powerful but it has
arguably the steepest learning curve, so you might find it less
appealing initially. In any case, something as simple as an insert
should be trivial in all frameworks, e.g. in SQLAlchemy (if you don't
use the object-relational mapper) you can create an insert statement
as:

# create the statement (my_table: an sqlalchemy.Table)
# you can print to see the actual generated SQL'INSERT INTO my_table (field1, field2, ... fieldN) VALUES
field1, :field2, ... :fieldN)'

# execute it (conn: an active sqlachemy.engine.Connection)
HTH,
George


[1] http://www.sqlalchemy.org/
[2] http://www.sqlobject.org/
[3] https://storm.canonical.com/

 

[Diez B. Roggisch]

Sigh. One more. And again, thank you for all of the help.

I realized that the last version that I posted took care of an SQL
injection problem for the values, but not for the fields. So, I went
ahead and modified the code:

def new_insert_cmd(myTable, myFields, myValues):
"""Imports given fields and values into a given table, returns the
Autoincrement value."""
SQLcmd="INSERT INTO " + myTable + " ( " +
create_input_string(myFields) + " ) VALUES ( " \
+ create_input_string(myValues) +" );"
allArguments=myFields+myValues
cursor.execute(SQLcmd, (allArguments))

create_input_strings() is just a function that creates the necessary
number of %s's for a given list (and which I'm sure there's a faster
way to code):

def create_input_string(myList):
sOut=""
for var in myList:
sOut=sOut+"%s, "
return sOut[:-2]

However, now the cursor.execute statement won't work. I've looked at
the content of SQLcmd and the values of allArguments and they seem
fine.

I've even tried running this at the IDLE command line:

cursor.execute("INSERT INTO plan (%s, %s, %s) VALUES (%s, %s, %s);",
(["name", "fileno", "size", "Test", "AAA-000", 7])) and I get this
error:

File "C:\Python25\lib\site-packages\MySQLdb\cursors.py", line 166, in execute
self.errorhandler(self, exc, value)
File "C:\Python25\lib\site-packages\MySQLdb\connections.py", line
35, in defaulterrorhandler
raise errorclass, errorvalue
ProgrammingError: (1064, "You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near ''name', 'fileno', 'size') VALUES ('Test',
'AAA-000', 7)' at line 1")

Can I not use the cursor.execute command to pass variables that aren't
immediately next to each other? If so, is there a better way to go
about solving this problem?

The escaping mechanism is only working (and supposedly so) for *values*,
not for creating SQL. To prevent SQL-injection don't allow dynamic
content in there (there shouldn't be any need anyway, because your
datamodel isn't changing due to user-input)

Diez