0

I'm trying to create a MySQL insert from a python dict using the tips found in here. I'm getting a MySQL error, becouse the syntax is not right. I've tracked down the problem being that in my query, the field names are surrounded by single quotes. My code is:

d=dict(a='pepe',b=4,c='potamo')
qmark = ", ".join(['%s'] * len(d))
sql = "INSERT INTO db.table ({}) VALUES ({})".format(qmark,qmark)
c.execute(sql,(d.keys() + d.values())

Looking at mysql log, this produces:

INSERT INTO db.table ('a', 'b', 'c') VALUES ('pepe', 4, 'potamo')

which raises a mysql exception (syntax error). Using the sentence in the MySQL command line, gives same error, and manually removing the single quotes from the fields fixes it:

INSERT INTO db.table (a,b,c) VALUES ('pepe',4,'potamo')

So I wonder what is the best way to remove those single quotes, or if there is a way to make cursor.execute() (part of Mysqldb) fix it for me. So far I can only think of something like:

str(d.keys()).replace("'","") + str(d.values())

and then split the resultant str back to a list to feed it to cursor.execute(). Or create a 

l=d.keys()

and then remove the ' from each element of the list prior to an

execute(sql,(l + d.values())

all of wich looks ugly.

Community
  • 1
  • 1
Nicolás Victorero
  • 1
  • 1

1 Answers1

0

Try something like this:

d=dict(a='pepe',b=4,c='potamo')
sql = "INSERT INTO db.table (a, b, c) VALUES (%(a)s,%(b)s,%(c)s)"
c.execute(sql, d)
farcepest
  • 145
  • 6