Parameterize SQL Queries with psycopg2

Question

I am trying to safely pass in a query string and parameters to psycopg2, but am having trouble with formatting lists into the query. In the first file, I have a list of columns and values to pass through to another function that executes the query.

columns = ['id', 'name', 'address']
values = ['1234', 'john', '123 road']
query = """INSERT INTO table %s VALUES (%s)"""
parameters = (tuple(columns), tuple(values))

res = update(query, parameters)

In the second file I take the query string and parameter and execute them:

def update(query_string: str, query_parameters: tuple):
    with db.transaction() as txn:
        txn.execute(query_string, query_parameter)

But I get an error saying:

LINE 1: INSERT INTO table ('id', 'name', 'address')...
                           ^
Syntax Error

What would be the correct way to pass in the column and values list into the query string?

Columns are identifiers you can't use parameter substitution for them. In docs see SQL composition for ways to do this. — Adrian Klaver
– Adrian Klaver, Commented May 19, 2021 at 21:45
Did you try my answer? If it didn't work please provide me the error or the issue you encountered. — Life is complex
– Life is complex, Commented Jun 2, 2021 at 12:20
@Lifeiscomplex we ended up going with another solution that didn't involve using psycopg2. Thank you though! — adamn11
– adamn11, Commented Jun 2, 2021 at 15:24

Life is complex · Accepted Answer · 2021-05-21 04:34:15Z

1

I would recommend reviewing these links for additional details on how to generate SQL dynamically using psycopg:

main page: https://www.psycopg.org/docs/sql.html
section: https://www.psycopg.org/docs/sql.html#psycopg2.sql.Placeholder

Based on your use case you can pass the list of columns name and values into a table with psycopg2 this way.

def update(query_string: str, query_parameters: tuple):
    with db.transaction() as txn:
        txn.execute(query_string, query_parameter)


tableName = "your_table_name"
columns = ["id", "name", "address"]
values = ["1234", "john", "123 road"]


sql_insert_command = sql.SQL("""INSERT INTO {} ({}) VALUES {}""").format(
        sql.Identifier(tableName),
        sql.SQL(', ').join(map(sql.Identifier, columns)),
        sql.SQL(', ').join(sql.Placeholder()*len(values)))


update(sql_insert_command, values)

edited May 21, 2021 at 4:34

answered May 19, 2021 at 21:45

Life is complex

15.8k5 gold badges34 silver badges72 bronze badges

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

4 Comments

Adrian Klaver Over a year ago

There is no insert_records_with_execute_values() in psycopg2 so this not the way to do it with psycopg2. See the sql for the builtin way.

Adrian Klaver Over a year ago

Nor have mentioned where tableName is coming from. If you post an answer please make it contained enough to be usable.

Life is complex Over a year ago

@AdrianKlaver I updated my answer. The insert_records_with_execute_values() was linked to this psycopg2.extras.execute_values in my DatabaseConnection code.

Adrian Klaver Over a year ago

You still haven't dealt with the OPs need to dynamically add column names. Save yourself a lot of trouble and look at the link I sent earlier. It addresses all this with a built in procedure.

Collectives™ on Stack Overflow

Parameterize SQL Queries with psycopg2

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