string replacement problems in mysqldb.exeute

Question

I have a small problem. And i think I am f=doing something silly but can not figure it out

first : i want to fire a dynamically constructed sql on mysql from python. Its simple insert statement but the values can have double quotes and other special characters in them. So, i was not sure how to do this. Then i came accross this thread - Stackoverflow

according to this i tried the following code

def fire_statement(stmt, value_tuple=None):

    try:
        if value_tuple == None:
            cur.execute(stmt)
            return True
        else:
            v = tuple(value_tuple)                
            cur.execute(stmt % v)
            return True
    except Exception, ex:
        print ex
        print "Query: '"+stmt+"'"
        return False

I have to pass the dynamically created stmt and value_tuple in this function i have no other way as the column names and values both will depend on external conditions. qand one more piece of information is the passed value_tuple is originally a list.

Two conditions are occurring in this code, and alas none of the approaches work

condition-1: when value_tuple = ('269', '1', '69', '1096', '1', '3070801', 'BE', '1963', '6') and stmt = INSERT INTO XXX (NCLASS, CRECEIVE, SUBCAT, GDATE, ASSCODE, PATENT, COUNTRY, GYEAR, CAT) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) i am getting this error - not all arguments converted during string formatting

(obviously the line - cur.execute(stmt % v) is changed to cur.execute(stmt , v)) for this case.

condition-2: when value_tuple = ('269', '1', '69', '1096', '1', '3070801', 'BE', '1963', '6') and stmt = INSERT INTO XXX (NCLASS, CRECEIVE, SUBCAT, GDATE, ASSCODE, PATENT, COUNTRY, GYEAR, CAT) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s) it is throwing this error - (1054, "Unknown column 'BE' in 'field list'") Query: 'INSERT INTO XXX (NCLASS, CRECEIVE, SUBCAT, GDATE, ASSCODE, PATENT, COUNTRY, GYEAR, CAT) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'

it seems that it is not replacing at all. but if i do a print (stmt % v) just before the cur.execute then the result shows as - INSERT INTO XXX (NCLASS, CRECEIVE, SUBCAT, GDATE, ASSCODE, PATENT, COUNTRY, GYEAR, CAT) VALUES (269, 1, 69, 1096, 1, 3070801, BE, 1963, 6)

Which is again obviously not right because it is not enclosing the string value within quotes. And i believe that this may be causing the error. for this i am badly stopped at the development cycle for a few hours now. Please help

Thanks in advance

score 4 · Accepted Answer · answered Nov 20 '12 at 07:44

You want to pass the arguments using

cur.execute(stmt, v)

and not

cur.execute(stmt % v)

If you have

"INSERT INTO tab (col1, col2) VALUES (%s, %s)"

and you pass two strings ('val1', 'val2') using %, it will become:

"INSERT INTO tab (col1, col2) VALUES (val1, val2)"

which is interpreted as column names, which obviously do not exist.

If you do

cur.execute(stmt, v)

it will be interpreted correctly as

"INSERT INTO tab (col1, col2) VALUES ('val1', 'val2')"

You can also use

print cur.mogrify(stmt, v)

to see the correctly formatted query as it could be executed in your DB.

string replacement problems in mysqldb.exeute

1 Answers1

Linked