Use PostgreSQL data type (jsonb) from Python
I investigated how to use the data side peculiar to PostgreSQL from Python, so I will record it.
This is about the jsonb type.
This content is an excerpt of the content presented at the 51st Nagaoka IT Developer Study Group. The slide is here.
The Java version is here.
Use PostgreSQL from Python
When using PostgreSQL from an application, use it via the DB API. DB API has psycopg2 as one of the implementations in the specification. psycopg2 uses libpq, the official PostgreSQL C client library, internally.
Source code
https://github.com/civic/postgresql-python-json
Use jsonb
| id | info |
|---|---|
| 1 | {"a": 100, "b": "Hello"} |
| 2 | {"a": 200, "b": "World"} |
CREATE TABLE json_test(
id integer,
info jsonb
)
Using jsonb from low-level API (psycopg2)
SELECT
cur.execute(
"SELECT info FROM json_test WHERE id=1"
)
row = cur.fetchone()
print(row[0])
# {'b': 'Hello', 'a': 100}
If you get the json column, you can get it with dict (Python dictionary type).
INSERT (1)
from psycopg2.extras import Json
#Updated with json object
cur.execute(
"INSERT INTO json_test(info) VALUES(%s)",
[
Json({"a":30, "b": "update"})
]
)
Set the parameters in the Json object provided by psycopg2. Use psycopg2.extras.Json. dict → Json.
INSERT (2)
import json
#Convert dict to string and pass it to SQL
cur.execute(
"INSERT INTO json_test(info) VALUES(jsonb(%s))",
[
json.dumps({"a":30, "b": "update"}) //dict→str
]
)
A pattern that sets parameters as a JSON string and casts them to jsonb in an SQL statement. In this case, it can be used in the interface of DB API.
Using jsonb from high level API (SQLAlchemy)
SELECT / INSERT
from sqlalchemy.dialects.postgresql import JSONB
class JsonTest(Base):
__tablename__ = 'json_test'
id = Column(Integer, primary_key=True)
info = Column(JSONB) #JSONB type columns and declarations
...
For SQLAlchemy, which provides OR mapper functionality, the postgresql dialect can be used for column definitions.
Recommended Posts