PostgreSQL document based in python

Question

based on business requirement, we need to get the data from PostgreSQL document based database in python

PostgreSQL:

select info from "spyConfig" where info @> '{"type" : "processmap"}';

its working as expected in postgresql interface, same query i want to execute in python.

cur.execute("SELECT info FROM spyConfig WHERE info @> %s", 
{'type':'processmap'})

its not working in python, even i want to pass table as parameter, i have tried here and research on it but its not working in python. please give me suggestion to solve this and let me know if any details to add here, i will update as need, Thanks.

in one you select info and in the other you use id. Maybe there is a problem with curly brace escaping? — progmatico
– progmatico, Commented Mar 7, 2018 at 15:32
Practically a duplicate of psycopg2 insert python dictionary as json, Create/Insert Json in Postgres with requests and psycopg2 — Ilja Everilä
– Ilja Everilä, Commented Mar 7, 2018 at 15:35
^^ I would say that one is pretty much spot on to what he needs as well — gold_cy
– gold_cy, Commented Mar 7, 2018 at 15:47

gold_cy · Accepted Answer · 2018-03-07 15:59:10Z

2

The problem is you're not passing your dictionary the right way, you should be passing it like this:

from psycopg2.extras import Json

cur.execute("SELECT info FROM spyConfig WHERE info @> %s", [Json({'type':'processmap'})])

If you want to add the table name also then

from psycopg2.extensions import AsIs
from psycopg2.extras import Json

kwargs = {'table': AsIs('spyConfig'),
          'q': [Json({'type':'processmap'})]}

cur.execute("SELECT info FROM %(table)s WHERE info @> %(q)s", kwargs)

edited Mar 7, 2018 at 15:59

answered Mar 7, 2018 at 15:42

gold_cy

14.2k4 gold badges27 silver badges55 bronze badges

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

1 Comment

Sanjay Over a year ago

cur.execute("SELECT info FROM %s WHERE info @> %s", ["spyConfig", Json({'type':'processmap'})]) i am getting below error: psycopg2.ProgrammingError: syntax error at or near "'spyConfig'" in the postgresql data base created a table as "spyConfig" with quotes

Collectives™ on Stack Overflow

PostgreSQL document based in python

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related