Psycopg2 parameterized execute query

Question

Trying to use parameters in my query for Postgres, using Psycopg2. When I google search, I see people using %s but I want to give more descriptive names like you see me doing below, so that later I can potentially add more columns into the WHERE clause and it be more easily readable. Is that possible? What am I doing wrong?

q = ""
q += "SELECT"
q += " id"
q += " FROM tbl_users "
q += " WHERE "
q += "("
q += " b_enabled = (%enabled)"
q += ")"
enabled = "True"
db_cursor.execute(q,enabled)

FYI: I realize I could in this case just put "True" as the second parameter in the execute method but this is a cut-down version of my code to focus on the issue. I want to know how to put a variable in the execute, rather than hard code "True" in that spot.

Error message:

psycopg2.errors.SyntaxError: syntax error at or near "$" LINE 1: ...d_visit_last FROM tbl_users WHERE ( b_enabled = ($t_enabled...

Maurice Meyer · Accepted Answer · 2020-08-23 17:24:09Z

2

You might want to use a multiline string to define your SQL query, to provide more meaningful names you can use a dictionary to pass values to psycopg2.execute():

import psycopg2
conn = psycopg2.connect("dbname=mf port=5959 host=localhost user=mf_usr")
cur = conn.cursor()

sql = """
    SELECT
        id
    FROM tbl_users
    WHERE
        b_enabled = %(enabled)s
"""

print (cur.mogrify(sql, {'enabled': "True"}).decode('utf-8'))
# cur.execute(sql, {'enabled': "True"})

Output:

SELECT
    id
FROM tbl_users
WHERE
    b_enabled = 'True'

Please have a look at the official docs for further information.

answered Aug 23, 2020 at 17:24

Maurice Meyer

18.2k4 gold badges35 silver badges54 bronze badges

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

1 Comment

ScotterMonkey Over a year ago

Thank you! One other thing. Can I put the variable enabled in the spot you put "True" in the execute statement? Trying it now... IT WORKED: db_cursor.execute(q, {'t_enabled': t_enabled})

Collectives™ on Stack Overflow

Psycopg2 parameterized execute query

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related