Dynamic SQL query Psycopg2 values problem

Question

Using Python and psycopg2 I am trying to build a dynamic SQL query to insert rows into tables.

The variables are: 1. Table name 2. Variable list of column names 3. Variable list of values, ideally entering multiple rows in one statement

The problems I have come across are the treatment of string literals from Python to SQL and psycopg2 trying to avoid you exposing your code to SQL injection attacks.

Using the SQL module from psycopg2, I have resolved dynamically adding the Table name and List of columns. However I am really struggling with adding the VALUES. Firstly the values are put into the query as %(val)s and seem to be passed literally like this to the database, causing an error.

Secondly, I would then like to be able to add multiple rows at once.

Code below. All help much appreciated :)

   import psycopg2 as pg2
   from psycopg2 import sql
   conn = pg2.connect(database='my_dbo',user='***',password='***')

   cols = ['Col1','Col2','Col3']
   vals = ['val1','val2','val3']

   #Build query
   q2 = sql.SQL("insert into my_table ({}) values ({})") \
   .format(sql.SQL(',').join(map(sql.Identifier, cols)), \
   sql.SQL(',').join(map(sql.Placeholder,vals)))

When I print this string as print(q2.as_string(conn)) I get:

insert into my_table ("Col1","Col2","Col3") values %(val1)s,%(val2)s,%(val3)s

And then when i try and a execute such a string I get the following error:

ProgrammingError: syntax error at or near "%" LINE 1: ... ("Col1","Col2","Col3") values (%(val1)s...
^

You forgot the parens arount the values. Should be sql.SQL("insert into my_table ({}) values( {})")... instead of ...values {}")... — Serge Ballesta
Thanks for the reply Serge. I added the brackets but still get error. Updated my post accordingly though — Andy

Andy Andy · Accepted Answer · 2019-04-20T20:37:23

Ok I solved this. Firstly use Literal rather than Placeholder, secondly put your row values together as tuples within a tuple, loop through adding each tuple to a list as literals and then drop in at the end when building the query.

Dynamic SQL query Psycopg2 values problem

1 Answers