JSONB – Sqlalchemy

One of the most well-liked relational database management systems (RDBMS) in the world, PostgreSQL is quite strong and allows developers access to a wide range of complex capabilities. One of the most useful features provided by Postgres is the support for JSON data types, together with the ability to write SQL. PostgreSQL is capable of CRUD activities like reading and writing JSONB data (binary JSON format). The greatest option for working with relational databases is SQLAlchemy, one of the most well-liked Python ORM libraries. This gives us the ability to interface with the database and Python, as well as operate with the JSONB data format in PostgreSQL.

JSONB

A PostgreSQL data type called JSONB stores JSON (JavaScript Object Notation) data in binary format, which uses less storage space and processes more data quickly and efficiently than JSON stored in text format. A well-liked Python library for working with relational databases, such as PostgreSQL, is SQLAlchemy.

You may use the strength and agility of JSON data in the Postgres database while still using Python to query and update the JSON data by combining JSONB with SQLAlchemy. Postgres is a powerful open-source database and is widely used. We’ll show you how to use JSONB with SQLAlchemy by going over a few examples. We will talk about the basics of making a JSONB column, running JSON queries, and editing JSON data. 

Postgres stores data that is written in JSON format called JSONB, which is a binary format. This allows searching both index and nested JSON items within Postgres. We will use an ORM layer (SQLAlchemy) which makes it easier to interact and easier to work with JSONB data in PostgreSQL. SQLAlchemy uses JSON data and defines a JSONB column within our database. It also defines JSONB columns in the database schema using SQLAlchemy’s provision of a JSONB type. In addition to that, we are able to use the JSONB column to retrieve the data and carry out SQL operations on it.

In python, we need to use the PostgreSQL dialect of SQLAlchemy using the python package and wrapper psycopg2, which provides additional functionality for working with PostgreSQL-specific data types like JSONB. We also need to install the psycopg2 driver, which is a PostgreSQL adapter for Python.

Required Modules: 

pip install psycopg2

Stepwise Implementation:

For the purpose of this article, we will be using the following database schema:

Step 1: Create a table in the existing data in Postgres and define the column in the table as JSONB. 

from sqlalchemy import create_engine, Table, Column, Integer, MetaData, JSON
from sqlalchemy.dialects.postgresql import JSONB, insert

engine = create_engine('postgresql://vikadmin:dbpass@localhost:5432/vikashdb')
metadata = MetaData()


# reflect the database schema to the metadata
metadata.reflect(bind=engine)

users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', JSONB),
    extend_existing=True
)

metadata.create_all(engine)


# get the table object
table_name = 'users'
table = metadata.tables[table_name]

# print the table schema
print(table.__repr__())

Here, we have created a table named users with two columns: id and data. The data column is of type JSONB, which means it can store JSON data.

Output Image

Step 2: Now, let’s insert some data into this table:

data = {
    'name': 'Vik',
    'age': 30,
    'job_title': 'Data Scientist'
}

insert_stmt = insert(users).values(data=data)
conn = engine.connect()
conn.execute(insert_stmt)
conn.commit()

In this example, we have inserted a dictionary of JSON data into the data column of the user’s table. We used the insert function from the sqlalchemy.dialects.postgresql module to construct the SQL statement.

Step 3: To query this data, we can use the select function:

# Fetch the data 
select_stmt = users.select()
result = conn.execute(select_stmt)
row = result.fetchone()
print(row)

Output

Output Image

Step 4: We can also update the JSON data in the data column:

In this example, we have updated the data column for the row with an id equal to 1, setting the job_title key to Software Engineer.

# Update data

update_stmt = users.update().where(users.c.id == 1).values(
    data={'job_title': 'Software Engineer'})
conn.execute(update_stmt)
conn.commit()

Step 4: Selecting Specific JSONB Fields with SQLAlchemy 2.0:

In this example we explained the syntax to select Specific JSONB Fields.

• TableName.jsonb_column_name["target_dict"]["target_subdict"] is used to access a nested field within the JSONB column.
• .where(TableName.id_column == target_id_value) is used to filter the rows based on an id_column.

from sqlalchemy import select
from your_model import TableName

stmt = select(TableName.jsonb_column_name["target_dict"]["target_s\
					ubdict"]).where(TableName.id_column == target_id_value)
result = session.execute(stmt).fetchall()

Step 5: Parsing Results

By default, SQLAlchemy parses JSONB columns using the JSONB type with astext_type=Text(). This means the JSONB data is treated as text when fetched, allowing it to be converted into a Python dictionary (or other JSON-compatible data structures).

from sqlalchemy.dialects.postgresql import JSONB

# Assuming your_model is already defined
TableName.jsonb_column_name = Column(JSONB(astext_type=Text()))

When you query this column, the result can be treated as a tuple containing a dictionary (or JSON object). For example

result = session.execute(stmt).fetchall()
for row in result:
    jsonb_as_dict = row[0]  
    # Assuming the select statement only 
    # fetches the JSONB field
    # Now jsonb_as_dict can be used as a dictionary

With SQLAlchemy, we are able to insert data that is formatted as JSONB into the database. We begin by constructing a JSONB object utilizing Python, and after that, we use SQLAlchemy to insert it into the database.

Example 2:

from sqlalchemy import create_engine, Column, Integer, JSON
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy.dialects.postgresql import JSONB, insert


engine = create_engine('postgresql://vikadmin:dbpass@localhost:5432/vikashdb')
Session = sessionmaker(bind=engine)
session = Session()
Base = declarative_base()


class Employee(Base):
    __tablename__ = 'employees'
    id = Column(Integer, primary_key=True)
    info = Column(JSONB)


employee_info = {
    'name': 'Vik Singh',
    'age': 35,
    'job_title': 'ML Engineer'
}

employee = Employee(id=15, info=employee_info)
session.add(employee)
session.commit()

In this demonstration, we will develop an Employee class that will map to the table titled “Employees” in the corresponding database table. We use a JSONB type for the info column’s definition. After that, a Python dictionary called employee info is constructed and populated with the relevant employee data. After adding the newly created instance to the session, we proceed to construct an instance of the Employee class using the employee info dictionary. Next, we make sure that the database is up to date by committing the modifications.

GeeksforGeeks

Improve

Article Tags :

• Python
• Python-SQLAlchemy

Practice Tags :

• python