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:
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.
Contact Us