mogrify() function with list of JSON values

Question

I have an array with JSON content like this

{
   "time":datetimeValue,
   "Type":"TypeValue",
   "ID":"IDValue",
   "ReceiveValue":{
      "Sensor1": Value1,
      "Sensor2":Value2,
      "Sensor3": Value3,
   },
   "TransmitValue":{
      "Sensor1": Value1,
      "Sensor2":Value2,
      "Sensor3": Value3,
   }
}

I tried to insert all of my records to PostgreSQL database in this array using this code

args_str = ','.join(self.cursor.mogrify("(%s,%s,%s,%s,%s)", x.values()) for x in self.records)
self.cursor.execute("INSERT INTO util VALUES " + (args_str))

But I got this error

'dict_values' object does not support indexing

Is there any way to solve this error or is there are any way to insert multiple records with mogrify function?

It is unclear how you expect your data to be transformed to a SQL insert statement. For the sample data given, how many rows would you expect to be inserted? Would it be 1 (with two array-type columns)? Would it be 8 (one row for each corresponding pair of values for columns 4 and 5 with the same value for columns 1 to 3)? Would it be 64 (one row for each combination of values for columns 4 and 5 and the same value for columns 1 to 3)? Please clarify. — Luke Woodward, Feb 06 '22 at 13:27
@LukeWoodward Firstly thank you for your feedback, actually i want to insert each JSON data in one row with all columns — Ogün Birinci, Feb 06 '22 at 13:34
@Ogün Birinci: Usually you dont need `mogrify` to insert data into a postgres database. Did you had a look at [Passing parameters to SQL queries](https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries) ? — Maurice Meyer, Feb 06 '22 at 14:51
@MauriceMeyer Hey, actually i already checked this documentation but in this solution https://stackoverflow.com/questions/8134602/psycopg2-insert-multiple-rows-with-one-query said that with mogrify() we can insert multiple rows 100x faster — Ogün Birinci, Feb 06 '22 at 14:56
1) FYI, `mogrify()` does not exist in the newest version of psycopg, `psycopg3` so don't be dependent on it. 2) There are better built in ways to speed up entering data [Fast execution](https://www.psycopg.org/docs/extras.html#fast-execution-helpers) or [Copy](https://www.psycopg.org/docs/usage.html#using-copy-to-and-copy-from). — Adrian Klaver, Feb 06 '22 at 16:58

Maurice Meyer · Answer 1 · 2022-02-06T15:33:16.970

1

You need to make a list out of x.values() and possibly convert the values to JSON (depending on your database table):

from psycopg2.extras import Json
# All values are converted to json!
args_str = b','.join(cur.mogrify('(%s,%s,%s,%s,%s)', [Json(x) for x in item.values()]) for item in data)
sql = b"INSERT INTO util VALUES " + args_str
print(sql)

Out:

b'INSERT INTO util VALUES (\'"Value1"\',\'"Value2"\',\'"Value3"\',\'{"Key1": "Value1", "Key2": "Value2", "Key3": "Value3", "Key4": "Value4", "Key5": "Value5", "Key6": "Value6", "Key7": "Value7", "Key8": "Value8"}\',\'{"Key1": "Value1", "Key2": "Value2", "Key3": "Value3", "Key4": "Value4", "Key5": "Value5", "Key6": "Value6", "Key7": "Value7", "Key8": "Value8"}\'),(\'"Value1"\',\'"Value2"\',\'"Value3"\',\'{"Key1": "Value1", "Key2": "Value2", "Key3": "Value3", "Key4": "Value4", "Key5": "Value5", "Key6": "Value6", "Key7": "Value7", "Key8": "Value8"}\',\'{"Key1": "Value1", "Key2": "Value2", "Key3": "Value3", "Key4": "Value4", "Key5": "Value5", "Key6": "Value6", "Key7": "Value7", "Key8": "Value8"}\')'

edited Feb 06 '22 at 15:33

answered Feb 06 '22 at 15:28

Maurice Meyer

17,279
4
30
47

Thank you for your answer but i got an error "Object of type datetime is not JSON serializable". Do you have any idea? Also i tried this and works, i know not effective solution but i don't have any other solution now args_str = ','.join(self.cursor.mogrify("(%s,%s,%s,%s,%s)", (x.get("time"), x.get("NEName"), x.get("ObjectID"), json.dumps(x.get("gponOltSidePonUtilRxPmIntervalTotalUtil")), json.dumps(x.get("gponOltSidePonUtilTxPmIntervalTotalUtil")))).decode('utf-8') for x in self.records) – Ogün Birinci Feb 06 '22 at 15:33
You should edit your question, showing an example of your real data. The error `Object of type datetime is not JSON serializable` is probably answered mutiple times on SO. – Maurice Meyer Feb 06 '22 at 16:17
Yes you're right, i edited. – Ogün Birinci Feb 06 '22 at 16:49

mogrify() function with list of JSON values

1 Answers1