CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
zeroKnowl
zeroKnowl

Reputation: 3

inserting array of JSON objects

I am using a stored procedure accepting a parameter of type JSON[]. Assuming a list similar to listJson = [{"value":"v1"}, {"value" : "v2"}], I would like to invoke the following method of psycopg2:

cursor.execute("call my_stored_procedure(%XXX)",(listJson,))

but I don't know how to replace %XXX to make the placeholder compatible with the list. I unsuccessfully tried %s::json[] and %s::jsonb[]. If it is not possible to use the method above, I would appreciate if somebody could point me to an other strategy to perform the call. Thanks in advance.

Upvotes: 0

Views: 89

Answers (3)

Ionut Ticus
Ionut Ticus

Reputation: 2789

What about using psycopg2.extras.Json:

cursor.execute("CALL my_stored(%s)",(Json(list),))

Also, try to use better variable names such as values instead of list because that's a built-in type.

Upvotes: 1

Abelisto
Abelisto

Reputation: 15614

import psycopg2, json
...
list = [{"value":"v1"}, {"value" : "v2"}]
listJson = [json.dumps(x) for x in list]
cursor.execute("call my_stored_procedure(%s::json[])",(listJson,))

Upvotes: 0

jjanes
jjanes

Reputation: 44167

You don't have any JSON to start with, you just have a list of dict. Is that what you want to have in python?

>>> listJson = [{"value":"v1"}, {"value" : "v2"}]
>>> type(listJson)
<type 'list'>
>>> type(listJson[0])
<type 'dict'>
>>> type(listJson[1])
<type 'dict'>

You can make a list of strings which each contain valid JSON syntax, and call with that:

listJson = ['{"value":"v1"}', '{"value" : "v2"}']
cursor.execute("CALL my_stored(%s::json[])",(listJson,))

Upvotes: 0

Related Questions