How to use the psycopg2 Json adaptation In JSON
The following code demonstrates how psycopg2’s Json adaptation can be used instead of the standard json.dumps(). In order to pass a Python object to the database as a query argument, you can use the Json adapter imported from psycopg2.extras.
Python
import psycopg2 from psycopg2.extras import Json # CREATE A PSYCOPG2 CONNECTION conn = psycopg2.connect(**{ "database" : "geeks", "user" : "postgres", "password" : "password", "host" : "localhost", "port" : 5432, }) # CREATE DICT OBJECT TO BE INSERTED TO DB dict_obj = { "name": "Suhas Hegde", "skill": "PL/SQL", "experience": 3} # CREATE A CURSOR USING THE CONNECTION OBJECT curr = conn.cursor() # EXECUTE THE INSERT QUERY curr.execute(f''' INSERT INTO json_table(id, json_col) VALUES ('JSON002', %s) ''', [Json(dict_obj)]) # COMMIT THE REQUESTS IN QUEUE conn.commit() # CLOSE THE CONNECTION conn.close() |
Output:
Output viewed in pgAdmin
In the above code, we are trying to insert a new JSON entry with id as JSON002. The entire process remains the same as in the earlier code except for the fact that we have used psycopg2.extras.Json instead of json.dumps.
Psycopg2 – Insert dictionary as JSON
In this article, we are going to see how to insert a dictionary as JSON using Psycopg2 and Python.
Python dict objects can be or rather should be stored in database tables as JSON datatype. Since most of the SQL databases have a JSON datatype. This allows using these objects or key-value pairs in the front-end or in an API to process the request that is being placed from them respectively.
Contact Us