You can also use the ‘?’ operator like that: SELECT ‘{“key_a”:1}’::jsonb ? ‘key_a’ And if you need to query by nested key, use like this: SELECT ‘{“key_a”: {“nested_key”: “a”}}’::jsonb -> ‘key_a’ ? ‘nested_key’ See http://www.postgresql.org/docs/9.5/static/functions-json.html NOTE: Only for jsonb type.
Use RealDictCursor: import psycopg2.extras cur = conn.cursor(cursor_factory = psycopg2.extras.RealDictCursor) cur.execute(“SELECT * from review”) res = cur.fetchall() This gives you a list with rows as real python dictionaries instead of “advanced psycopg2 list”.
apt-get install libpq-dev helped me on debian squeeze From comments: apt-get install python-dev-is-python3
According to the official documentation: If you need to generate dynamically an SQL query (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module. The sql module is new in psycopg2 version 2.7. It has the following syntax: from psycopg2 import sql cur.execute( sql.SQL(“insert into {table} values (%s, … Read more
For MacOS users After trying all the above methods (which did not work for me on MacOS 10.14), that one worked : Install openssl with brew install openssl if you don’t have it already. add openssl path to LIBRARY_PATH : export LIBRARY_PATH=$LIBRARY_PATH:/usr/local/opt/openssl/lib/ install psycopg2 with pip pip3 install psycopg2
The read_sql docs say this params argument can be a list, tuple or dict (see docs). To pass the values in the sql query, there are different syntaxes possible: ?, :1, :name, %s, %(name)s (see PEP249). But not all of these possibilities are supported by all database drivers, which syntax is supported depends on the … Read more
$ sudo ln -s /Library/PostgreSQL/9.2/lib/libssl.1.0.0.dylib /usr/lib $ sudo ln -s /Library/PostgreSQL/9.2/lib/libcrypto.1.0.0.dylib /usr/lib I encountered this error while working on Django. I have it working on virtualenv with Django==1.3 but not on Django==1.5 where I have to issue the commands above. In OS X El Capitan you can’t do these links without disabling system protection but … Read more
This did the trick for me: cursor.execute(“””SELECT table_name FROM information_schema.tables WHERE table_schema=”public””””) for table in cursor.fetchall(): print(table)
I ran pip install psycopg2-binary and it worked like charm More info about the binary package Python 3 pip3 install psycopg2-binary
psycopg2cffi (Updated 2015) psycopg2cffi is yet another psycopg2-compatible replacement and should provide the best PostgreSQL performance with PyPy. Add this to your settings.py to remain compatible with both: try: import psycopg2 except ImportError: # Fall back to psycopg2cffi from psycopg2cffi import compat compat.register() psycopg2-ctypes (2012) I also know that some people are using psycopg2-ctypes. This … Read more