squid_a squid_a - 5 months ago 12
MySQL Question

Using execute()/executemany() for SQL statements in python

I am using the MySQLdb module in python to write SQL statements. I am having a difficult time using variables in the way I'd like to. Here's my work:

stmt = "\
INSERT INTO Table1\
(name, status)\
SELECT (:name1, :status1)\
FROM dual\
WHERE NOT EXISTS (\
SELECT 1 FROM Table1\
WHERE name =(:name1))"
dic = {"name1":"Bob", "status1":"Active"}

dbcursor.executemany(stmt, dic)
dbconnection.commit()
print("Insertion to Table1 committed\n\n")


This does not work, I eventually rollback with the error message
not all arguments converted during string formatting
. If I hardcode the dictionary values in then the insertion works fine. Can you point me to the proper way of using variables in place of hardcoded values?

Answer

executemany() takes a sequence of parameter objects (dictionaries), but you are passing in just one. Either use dbcursor.execute(), or pass in a sequence of dictionaries. Since you only have one dictionary, just use dbcursor.execute():

dbcursor.execute(stmt, dic)

Because executemany() treats the second argument as a series of parameters, each of which is used in a separate execute() call, you are effectively trying to run the statements with the keys of the dictionary as the parameters. In effect, passing in your dic as the parameters produces:

for key in dic:
    dbcursor.execute(stmt, key)

where the key is also an iterable, producing separate characters as the parameters used. You are trying to run the statement with ('n', 'a', 'm', 'e', '1') and ('s', 't', 'a', 't', 'u', 's', '1'). That doesn't work for your statements.