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?

alecxe · Accepted Answer · 2016-04-12 14:38:12Z

4

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, ))

answered Apr 12, 2016 at 14:38

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brad K. Over a year ago

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 Over a year ago

@BradK. yup, the note is there in the answer..thanks!

Malik · Accepted Answer · 2016-04-12 14:43:11Z

-1

#!/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()

answered Apr 12, 2016 at 14:43

Malik

944 bronze badges

2 Comments

alecxe Over a year ago

What would happen if u would have the following value: O'Brien?

CodeMouse92 Over a year ago

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.

Collectives™ on Stack Overflow

Use a variable in SQL queries in python

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related