Python: inserting double or single quotes around a string

Question

Im using python to access a MySQL database and im getting a unknown column in field due to quotes not being around the variable.

code below:

cur = x.cnx.cursor()
cur.execute('insert into tempPDBcode (PDBcode) values (%s);' % (s)) 
rows = cur.fetchall()

How do i manually insert double or single quotes around the value of s? I've trying using str() and manually concatenating quotes around s but it still doesn't work. The sql statement works fine iv double and triple check my sql query.

Answer 1

If this were purely a string-handling question, the answer would be tojust put them in the string:

cur.execute('insert into tempPDBcode (PDBcode) values ("%s");' % (s)) 

That's the classic use case for why Python supports both kinds of quotes.

However as other answers & comments have pointed out, there are SQL-specific concerns that are relevant in this case.

Answer 2

You shouldn't use Python's string functions to build the SQL statement. You run the risk of leaving an SQL injection vulnerability. You should do this instead:

cur.execute('insert into tempPDBcode (PDBcode) values (%s);', s) 

Note the comma.

Answer 3

Python will do this for you automatically, if you use the database API:

cur = x.cnx.cursor()
cur.execute('insert into tempPDBcode (PDBcode) values (%s)',s) 

Using the DB API means that python will figure out whether to use quotes or not, and also means that you don't have to worry about SQL-injection attacks, in case your s variable happens to contain, say,

value'); drop database; '