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?

• user7070613: Columns are identifiers you can't use parameter substitution for them. In docs see SQL composition for ways to do this.
• Life is complex: Did you try my answer? If it didn't work please provide me the error or the issue you encountered.
• adamn11: @Lifeiscomplex we ended up going with another solution that didn't involve using psycopg2. Thank you though!

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

• user7070613: 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.
• user7070613: Nor have mentioned where tableName is coming from. If you post an answer please make it contained enough to be usable.
• Life is complex: @AdrianKlaver I updated my answer. The insert_records_with_execute_values() was linked to this psycopg2.extras.execute_values in my DatabaseConnection code.
• user7070613: 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.