psycopg2: formatting data values in execute_values

Question

I'm trying to insert multiple rows into my PostgreSQL table using the execute_many function in psycopg2. I can get it to work with simple values, I can't get it work with more complex data.

The following illustrates what I'm trying to do:

dataset = [[1,'2020-11-01 00:00:00', 1],[2,'localtimestamp', None],[3, '2020-11-01 00:00:00', '(select otherid from myothertable where otherid = 99)']]
queryText = "insert into mytable (id,timestamp,fk_id) values %s"
execute_values(cursor,querytext,dataset,template=None)

As you can see I'm trying to populate a field using the localtimestamp function, and another field with an ID from an FKed table using an embedded select.

When the insert query is created in the execute_many function the localtimestamp and embedded select are wrapped in quotes, which is fair enough as they are strings. So my question is, is there a way of telling execute_values not to enclose a data value in quotes?

I can bypass the need to use localtimestamp relatively easily by populating dataset with an ISO date string, but getting the embedded query working is more complicated. I'm trying not to build the query myself and just using conn.execute(...) as for obvious reasons, that's not a safe approach.

Any suggestions gratefully received. Thanks

You are trying to mix values with SQL, and that is a really bad idea. Your code does not work because psycopg2 guards against SQL injection. — Mike Organek
– Mike Organek, Commented Nov 20, 2020 at 10:52
As @MikeOrganek said that is not going to work as execute_values is expecting just that values not a query. Not sure why you think conn.execute() is not safe? — Adrian Klaver
– Adrian Klaver, Commented Nov 20, 2020 at 15:14
Yup, unfortunately that's exactly what I'm trying to do. I'm aware of the possibility of SQL injection and account for that in the code. But thanks for your reply. So, basically for very good reasons, psycopg2 wont let me do what I want to do. I'll have handle this differently. Cheers. — bill74
– bill74, Commented Nov 20, 2020 at 15:51
@AdrianKlaver: "not safe" as in open to SQL injection if I don't ensure the SQL is clean, and I was hoping I could reply on psycopg2 to help me with that. But it looks like I'm going to have to build my onw SQL and use conn.execute() (which I was doing first anyway, I just thought there was a more elegant solution) — bill74
– bill74, Commented Nov 20, 2020 at 15:59
execute() will accept parameters. There is also the sql module to help build dynamic queries safely. If you are the one building the queries then I don't see a problem. — Adrian Klaver
– Adrian Klaver, Commented Nov 20, 2020 at 16:09

Maurice Meyer · Accepted Answer · 2020-11-20 16:24:47Z

1

You could use AsIs, to prevent your subqueries get quoted:

dataset = [[1,'2020-11-01 00:00:00', 1],[2,'2020-11-01 00:00:00', None],[3, '2020-11-01 00:00:00', "(select id from __users where lastname = 'Reinisch')"]]

for i, data in enumerate(dataset):
    if isinstance(data[2], str):
        data[2] = AsIs(data[2])
    # same here for the functions

queryText = "insert into mytable (id,ts,fk_id) values %s"
execute_values(cursor,queryText,dataset,template=None)

cursor.execute('select * from mytable')
pprint.pprint(cursor.fetchall())

Out:

[(1, datetime.datetime(2020, 11, 1, 0, 0), 1),
 (2, datetime.datetime(2020, 11, 1, 0, 0), None),
 (3, datetime.datetime(2020, 11, 1, 0, 0), 366211)]

Note:

I didn't check for postgres functions, but should work the same or something like SELECT localtimestamp(), but as mentioned it is not save!

edited Nov 20, 2020 at 16:24

answered Nov 20, 2020 at 16:08

Maurice Meyer

18.2k4 gold badges35 silver badges54 bronze badges

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

1 Comment

Adrian Klaver Over a year ago

I would be careful of this for the reasons Daniele Varrazzo(piro) mentions in this SO answer

Collectives™ on Stack Overflow

psycopg2: formatting data values in execute_values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related