0

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.

Sanjay
  • 75
  • 10
  • @veeram please help on this – Sanjay Mar 07 '18 at 15:24
  • 1
    in one you select `info` and in the other you use `id`. Maybe there is a problem with curly brace escaping? – progmatico Mar 07 '18 at 15:32
  • Practically a duplicate of [psycopg2 insert python dictionary as json](https://stackoverflow.com/questions/31796332/psycopg2-insert-python-dictionary-as-json/31796487), [Create/Insert Json in Postgres with requests and psycopg2](https://stackoverflow.com/questions/47747766/create-insert-json-in-postgres-with-requests-and-psycopg2) – Ilja Everilä Mar 07 '18 at 15:35
  • 1
    Somewhat related https://stackoverflow.com/questions/47407488/django-raw-sql-json-params-not-being-executed-as-expected-syntax-error – Ilja Everilä Mar 07 '18 at 15:43
  • ^^ I would say that one is pretty much spot on to what he needs as well – gold_cy Mar 07 '18 at 15:47

1 Answers1

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)
gold_cy
  • 13,648
  • 3
  • 23
  • 45
  • 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 – Sanjay Mar 07 '18 at 15:54