Use a variable in SQL queries in python

Question

My python script looks like this:

 #!/usr/bin/python
 import MySQLdb
 u='ABNCSDS7612'
 db = MySQLdb.connect(host="localhost",user="root",passwd="abc",db="mydb")
 cur = db.cursor()
 sql=("insert into t1(p_id,job_id) Values ((select p_id from t2 where name='OUTPUT'),
(select job_id from job where job_uuid= "%s"))",(u))
 cur.execute(*sql)
 db.commit()

I am firing an insert statement where I am trying to insert 2 column values, and I want value of 2nd column from a variable in my python script u='ABNCSDS7612' . I took help from a lot of similar posts but no luck. I am still facing the error as below:

SyntaxError: invalid syntax

It is showing error after the brackets which occur after %s in sql. Can someone please help?

Answer 1

Invalid syntax error is because of the way you've put the quotes.

You actually don't need the quotes around the placeholder. And, you need to put your query parameter into a tuple. Also, I would write the query on multiple lines to improve readability:

sql = """
 insert into 
 t1(p_id,job_id) 
 Values 
 ((select p_id from t2 where name='OUTPUT'),
 (select job_id from job where job_uuid = %s))"""
cur.execute(sql, (u, ))

• Brad K.: Might also be good to make the point that a tuple in Python with one value is (u,) (like you have) not (u) (like the OP had).
• alecxe: @BradK. yup, the note is there in the answer..thanks!

Answer 2

#!/usr/bin/python
import MySQLdb
u='ABNCSDS7612'
db = MySQLdb.connect(
host="localhost",
user="root",
passwd="abc",
db="mydb")
cur = db.cursor()
sql="""insert into t1(p_id,job_id) Values 
((select p_id from t2 where name='OUTPUT'),
(select job_id from job where job_uuid= '%s'))""" % (u)
cur.execute(*sql)
db.commit()

• alecxe: What would happen if u would have the following value: O'Brien?
• CodeMouse92: While this code may answer the question, providing additional context regarding why and/or how it answers the question would significantly improve its long-term value. Please edit your answer to add some explanation.