Using the same variable multiple times in a SQL query. Python code?

Question

I am new to python. I have the following code where I need to call the same variable multiple times. How can I do this?

import psycopg2 as pg

conn = pg.connect("dbname='db' user='user' host='localhost'")    

q1 = """SELECT value FROM t1;"""
cur1 = conn.cursor()
cur1.execute(q1)

cur2 = conn.cursor()

for row in cur1:
     q2 = """SELECT * FROM t2 WHERE c1 = '%s' or c2 ='%s';""" 
     #what comes here? %s is the 'value' from q1  
     cur2.execute(q2)
     for row in cur2: 
        print(row)

How can I tell python to use 'value' for all appearances of '%s'? %(row) works when %s appears only once:

q2 = """SELECT * FROM t2 WHERE c1 = '%s' or c2 ='%s';"""(%row)

I searched the stackoverflow but could not find my answer. I hope it is not a duplicate question. Thank you.

cco · Accepted Answer · 2016-11-20 04:11:57Z

7

psycopg2 supports named parameters using %(name)s style parameter markers. If you use the same named parameter multiple times in your query, the same parameter value wil be passed each time.

Here is an example from the documentation:

cur.execute(
    """INSERT INTO some_table (an_int, a_date, another_date, a_string)
        VALUES (%(int)s, %(date)s, %(date)s, %(str)s);""",
    {'int': 10, 'str': "O'Reilly", 'date': datetime.date(2005, 11, 18)})

While this looks like string % formatting, it's actually not, so it's safe from SQL injection attacks, while string formatting (using either % or .format() is not.

answered Nov 20, 2016 at 4:11

cco

6,3712 gold badges20 silver badges22 bronze badges

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

4 Comments

ar-siddiqui Over a year ago

In psycopg3 I am getting this error TypeError: not enough arguments for format string when I use one the arguments twice.

cco Over a year ago

Are you using %s format specifiers or %(varname) style? %s style needs one parameter passed per marker, but the other does not. Look at my answer for an example.

ar-siddiqui Over a year ago

I am using %(varname)s marker but it is only working if I have one parameter per marker otherwise raising TypeError: not enough arguments for format string. I used your answer to develop my query.

cco Over a year ago

I thonk it is because psycopg3 uses server-side parameter binding rather than client-side (see psycopg.org/psycopg3/docs/basic/… for more info), so this technique may not work anymore. I haven't used psycopg3, so I can't offer a workaround.

Collectives™ on Stack Overflow

Using the same variable multiple times in a SQL query. Python code?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related