Partial JSON Update Using SQLAlchemy Expression

In this article, we will learn how to use SQLAlchemy expression to update partial JSON in Python.

SQLAlchemy is a Python library that provides a set of high-level abstractions for working with relational databases. It allows developers to work with databases in a more Pythonic way, by providing an object-relational mapping (ORM) layer that maps Python classes to database tables and allows developers to interact with the database using Python objects.

Database tables contain various fields to store different types of data such as numeric data, string data, datetime data types. JSON is also one of the types that can be stored. In this article, we are going to see how we can update the specific JSON field using SQLAlchemy and Python.

Prerequisites:

• Install any database (eg. sqlite3, PostgreSQL etc).
• Install SQLAlchemy module in Python.

Creating a Database using SQLAlchemy expression

Here, we will use sqlite3 database which will be stored in the local directory we can also use another database such as MySQL, PostgreSQL .etc.

Step 1: Import all required modules and then define the path of the database that we are going to create.

Step 2: Using declarative_base() function to create a base class that database classes should inherit, this base class provides a set of pre-defined attributes and methods necessary for creating and interacting with database tables.

Step 3: Using create_engine() function to create a new database engine instance it takes a single argument called a URL and is responsible for managing the connection to the database and executing SQL commands. sessionmaker() creates a session for objects bound to a particular database engine, using sessionmaker() we can create multiple local sessions as required.

Step 4: Next, we create a database called User which is a class inheriting the Base we created above, it contains a table called users and users have multiple attributes such as id which is an Integer, string username, and a JSON column called info. When we print any User object __repr__ returns the objects with its username, this is useful for debugging.

Step 5: The create_all() function inspects the models defined in our code, generates the necessary SQL commands to create the corresponding tables, and executes those commands against the specified database engine. This Python file is saved with the “main.py” name.

Python3

# Importing required modules
from sqlalchemy import Column, String, Integer,\
    create_engine, JSON
from sqlalchemy.orm import declarative_base,\
    sessionmaker
from sqlalchemy import update, func
import os

# Defining the path of database.
BASE_DIR = os.path.dirname(os.path.realpath(__file__))
connection_string = "sqlite:///"+os.path.join(BASE_DIR,
                                              'site.db')

# Create a base class
Base = declarative_base()

# Create a new database engine instance
engine = create_engine(connection_string,
                       echo=True)

# Creates a session for objects
Session = sessionmaker()
local_session = Session(bind=engine)

# Defining the schema of the table
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer(),
                primary_key=True)
    username = Column(String(25),
                      nullable=False,
                      unique=True)
    info = Column(JSON,
                  nullable=True)

    def __repr__(self):
        return f"<User username={self.username}>"


Base.metadata.create_all(engine)

Output:

Adding Users using SQLAlchemy expression

To add users to our table users we have to create another file or can perform the above operations in the same file in which we made the database. As specified above sessionmaker() let us create various local session according to requirement here we are creating another local_session which will be bonded to the engine that we made earlier. Here we created three users, using add() function we added them, and commit() function performs these changes to our database. We can retrieve data by performing query operations to the database and filter() function can be used to retrieve required data.

Python3

# Importing required modules
from main import User, Session, engine

# Create a session
local_session = Session(bind=engine)

# Store table data to the variable
user1 = User(id=1, username="aditya",
             info={'dob': '21', 'subject': 'math'})
user2 = User(id=2, username="timmy",
             info={'dob': '22', 'subject': 'science'})
user3 = User(id=3, username="sushant",
             info={'dob': '23', 'subject': 'programming'})

# Add data to the session
local_session.add(user1)
local_session.add(user2)
local_session.add(user3)

# Perform the changes to the database.
local_session.commit()

# Retrieve data and print it
result = local_session.query(User).\
    filter(User.id == "1").first()
print(result)

Output:

Updating JSON Field using SQLAlchemy expression

By creating a new file import required functions and classes from the main file and SQLAlchemy creates another local session to perform the update. The following code performs a query on the User database and filters data using a specified id. Sqlite3 offers a function called json_set() that allows us to update a JSON value in a database column.

Syntax: json_set(JSON_DOCUMENT, PATH, NEW_VALUE)

Parameters:

• JSON_DOCUMENT: It is the name of the JSON column we want to update.
• PATH: It is the path to the JSON value we want to update.
• NEW_VALUE: It is the new value we want to set for the specified JSON path.

The below code updates the subject field in the info column where the id is 1 and changes subject to programming.

Python3

# Importing required modules
from sqlalchemy import update, func
from main import User, Session, engine

# Creates a session
local_session = Session(bind=engine)

# Declare variables
value = 'programming'
id = 1

# Update the JSON column data
update_table = local_session.query(User).\
    filter(User.id == id).update({
        'info': func.json_set(
            User.info,
            "$.subject",
            value
        )
    }, synchronize_session='fetch')

# Commit the changes in database
local_session.commit()

Output:

Table data after updating JSON: