Unsupported operand types in if/else list comprehension

[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


On Sat, Apr 11, 2009 at 11:53 AM, Mike H <cmh.python at gmail.com> wrote:
> 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....
>
> On Sat, Apr 11, 2009 at 11:28 AM, Mike H <cmh.python at gmail.com> wrote:
>> 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
>>
>>
>> On Sat, Apr 11, 2009 at 7:38 AM, Diez B. Roggisch <deets at nospam.web.de> wrote:
>>> 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
>>> --
>>> http://mail.python.org/mailman/listinfo/python-list
>>>
>>
>