CODEWITHSUNDEEP
pythonsqlpostgresqlpsycopg2
adamn11
adamn11

Reputation: 311

Parameterize SQL Queries with psycopg2

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?

Upvotes: 1

Views: 2931

Answers (1)

Life is complex
Life is complex

Reputation: 15639

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

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)

Upvotes: 1

Related Questions