Psycopg2 INSERT query string composition with execute_values

Question

I am trying to insert data to a table in Postgresql using Psycopg2's execute_values(). However, the job keeps on failing when I use "string composition". Is it possible to use string composition with execute_values() ?:

table='my_tab'
schema='my_schema'
col_lst = ['emp_id', 'emp_name', 'emp_age', 'emp_salary']
rec_lst = [('1234', 'John', 35, 10000), ('5678', 'Mike', 42, 15000), ('3452', 'Jason', 33, 15000)]
 
query = sql.SQL("INSERT INTO {schema}.{table} ({cols}) VALUES ({values});")
col_str = sql.SQL(', ').join(sql.Identifier(col) for col in col_lst)
place_holders = sql.SQL(', ').join(sql.Placeholder() * len(col_lst))
query = query.format(schema=sql.Identifier(schema), table=sql.Identifier(table), cols=col_str, values=place_holders)

psycopg2.extras_execute_values(cursor, query, rec_lst, page_size=1000)

Error: The query contains more than one '%s' placeholder...

Can anyone please help fix the same.

Thanks

1) This emp_age' should be 'emp_age' and is throwing everything else off. Not sure if this is copy/paste error or mistake in actual code? 2) INSERT INTO {schema}.{table} ... can be INSERT INTO {table} ... with table=sql.Identifier(schema, table). — Adrian Klaver
– Adrian Klaver, Commented Aug 20, 2022 at 20:18
Also execute_values needs to have the sql string just have ... VALUES %. So you don't need to do place_holders = ... and ...values=place_holders. — Adrian Klaver
– Adrian Klaver, Commented Aug 20, 2022 at 20:26

Adrian Klaver · Accepted Answer · 2022-08-20 20:50:24Z

Working example

Create table:

create table my_tab(emp_id integer, emp_name varchar, emp_age integer, emp_salary integer);

\d my_tab
                      Table "public.my_tab"
   Column   |       Type        | Collation | Nullable | Default 
------------+-------------------+-----------+----------+---------
 emp_id     | integer           |           |          | 
 emp_name   | character varying |           |          | 
 emp_age    | integer           |           |          | 
 emp_salary | integer           |           |          |

Python code:

import psycopg2
from psycopg2 import sql
from psycopg2.extras import execute_values

table = 'my_tab'
schema = 'public'
col_lst = ['emp_id', 'emp_name', 'emp_age', 'emp_salary']
rec_lst = [('1234', 'John', 35, 10000), ('5678', 'Mike', 42, 15000), ('3452', 'Jason', 33, 15000)]

con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()

query = sql.SQL("INSERT INTO {table}({cols}) values %s")\
.format(table=sql.Identifier(schema, table), 
        cols=sql.SQL(", ").join(map(sql.Identifier, col_lst)))


print(query.as_string(con))                                                                                                                                               
INSERT INTO "public"."my_tab"("emp_id", "emp_name", "emp_age", "emp_salary") values %s

execute_values(cur, query, rec_lst)
con.commit()

cur.execute("select * from my_tab")
cur.fetchall()    
                                                                                                                                                        
[(1234, 'John', 35, 10000),
 (5678, 'Mike', 42, 15000),
 (3452, 'Jason', 33, 15000)]

Collectives™ on Stack Overflow

Psycopg2 INSERT query string composition with execute_values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related