CODEWITHSUNDEEP
pythonsqldatabasepostgresqlpsycopg2
bill74
bill74

Reputation: 11

psycopg2: formatting data values in execute_values

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

Upvotes: 1

Views: 1303

Answers (1)

Maurice Meyer
Maurice Meyer

Reputation: 18136

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!

Upvotes: 1

Related Questions