Python SQLAlchemy: Using SQLAlchemy ORM for Database Interactions

Last update on August 31 2024 17:21:21 (UTC/GMT +8 hours)

Introduction to Python SQLAlchemy ORM

SQLAlchemy is a powerful SQL toolkit and Object-Relational Mapping (ORM) library for Python. The ORM layer provides a way to interact with your database using Python objects rather than writing raw SQL queries.

This tutorial introduces the basics of SQLAlchemy ORM, including setting up a database, defining models, and performing CRUD operations. Each example is designed to show a specific aspect of interacting with a database using SQLAlchemy ORM.

Example 1: Setting Up the Database and Engine

This example shows how to set up the database engine and session. 'create_engine' initializes the connection to the database, 'declarative_base' creates a base class for ORM classes, and 'sessionmaker' creates a session factory bound to the engine.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine
from sqlalchemy.orm import declarative_base, sessionmaker

# Create an SQLite database engine; replace with your database URL
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Create a session factory
Session = sessionmaker(bind=engine)

Explanation:

Import Necessary Modules:

create_engine: Used to create a connection to the database.
declarative_base: Base class for creating SQLAlchemy models.
sessionmaker: Factory for creating new SQLAlchemy sessions.

Create a Database Engine:

engine = create_engine('sqlite:///example.db', echo=True):

Creates a connection to the SQLite database named example.db.
echo=True enables logging of SQL commands for debugging purposes.

Define the Declarative Base:

Base = declarative_base():

Creates a base class for defining ORM models (mapped database tables).

Create a Session Factory:

Session = sessionmaker(bind=engine):

Configures a session factory that will connect to the specified database engine (engine).
Sessions are used to manage transactions and execute queries against the database.

Example 2: Defining a Model

This example demonstrates how to define a model class that maps to a database table. The User class represents a table with columns id, name, and age.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# Create an SQLite database engine; replace with your database URL
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Define a model class that inherits from Base
class User(Base):
    __tablename__ = 'users'  # Define the name of the table

    id = Column(Integer, primary_key=True)  # Define the primary key column
    name = Column(String)  # Define a regular column
    age = Column(Integer)  # Define another column

# Create a session factory
Session = sessionmaker(bind=engine)

# Create the tables in the database
Base.metadata.create_all(engine)

Output:

2020-08-30 22:23:19,146 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-30 22:23:19,147 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2020-08-30 22:23:19,147 INFO sqlalchemy.engine.Engine [raw sql] ()
2020-08-30 22:23:19,149 INFO sqlalchemy.engine.Engine COMMIT

Explanation:

Import Modules from SQLAlchemy:

create_engine, Column, Integer, String are imported from sqlalchemy for database and table operations.
declarative_base, sessionmaker are imported from sqlalchemy.orm for ORM model creation and session handling.

Create an SQLite Database Engine:

engine = create_engine('sqlite:///example.db', echo=True) creates an engine to connect to an SQLite database named example.db.
echo=True enables logging of SQL queries to the console.

Create a Base Class for ORM Models:

Base = declarative_base() creates a base class for defining ORM models (tables).

Define a Model Class (User):

class User(Base) defines a table named users that inherits from the Base class.
__tablename__ = 'users' specifies the name of the table in the database.
id = Column(Integer, primary_key=True) defines an id column as the primary key.
name = Column(String) defines a name column to store string data.
age = Column(Integer) defines an age column to store integer data.

Create a Session Factory:

Session = sessionmaker(bind=engine) creates a session factory to manage database sessions.

Create Tables in the Database:

Base.metadata.create_all(engine) generates the users table in the example.db database if it doesn't already exist.

Example 3: Adding Records to the Database

This example shows how to add a new record to the database. A new User instance is created and added to the session, then committed to save the changes.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker
	
# Create an SQLite database engine; replace with your database URL
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Define a model class that inherits from Base
class User(Base):
    __tablename__ = 'users'  # Define the name of the table

    id = Column(Integer, primary_key=True)  # Define the primary key column
    name = Column(String)  # Define a regular column
    age = Column(Integer)  # Define another column

# Create a session factory
Session = sessionmaker(bind=engine)

# Create the tables in the database
Base.metadata.create_all(engine)

# Create a new session
session = Session()

# Create a new user instance
new_user = User(name='Antelmo Nuray', age=30)

# Add the user to the session
session.add(new_user)

# Commit the transaction to save changes
session.commit()

Output:

2020-08-30 22:34:16,400 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-30 22:34:16,401 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2020-08-30 22:34:16,401 INFO sqlalchemy.engine.Engine [raw sql] ()
2020-08-30 22:34:16,402 INFO sqlalchemy.engine.Engine COMMIT
2020-08-30 22:34:16,404 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-30 22:34:16,405 INFO sqlalchemy.engine.Engine INSERT INTO users (name, age) VALUES (?, ?)
2020-08-30 22:34:16,405 INFO sqlalchemy.engine.Engine [generated in 0.00050s] ('Antelmo Nuray', 30)
2020-08-30 22:34:16,407 INFO sqlalchemy.engine.Engine COMMIT

Explanation:

Import Modules:

from sqlalchemy import create_engine, Column, Integer, String: Imports necessary functions and classes from SQLAlchemy to define database columns and manage database connections.
from sqlalchemy.orm import declarative_base, sessionmaker: Imports tools to create base classes for models and to manage database sessions.

Create Database Engine:

engine = create_engine('sqlite:///example.db', echo=True): Creates an SQLite database connection to a file named example.db. The echo=True parameter enables SQL query logging for debugging.

Create Base Class:

Base = declarative_base(): Creates a base class (Base) for all model classes, using SQLAlchemy's declarative_base() function to set up ORM mappings.

Define the User Model:

class User(Base): Defines a User class that inherits from Base to represent the users table in the database.
__tablename__ = 'users': Sets the name of the table in the database to "users".
id = Column(Integer, primary_key=True): Defines the id column as an integer primary key.
name = Column(String): Defines the name column as a string.
age = Column(Integer): Defines the age column as an integer.

Create Session Factory:

Session = sessionmaker(bind=engine): Creates a session factory bound to the engine, allowing for database session management.

Create Tables in the Database:

Base.metadata.create_all(engine): Generates the database tables based on the models defined (in this case, the User table).

Create a New Session:

session = Session(): Creates a new session instance to interact with the database.

Create a New User Instance:

new_user = User(name='Antelmo Nuray', age=30): Creates a new User object with the name set to 'Antelmo Nuray' and age set to 30.

Add the User to the Session:

session.add(new_user): Adds the new User object to the session, preparing it to be inserted into the database.

Commit the Transaction:

session.commit(): Commits the session transaction to save the changes (inserting the new user) into the database.

Example 4: Querying Records from the Database

This example demonstrates how to retrieve all records from the User table and print their details.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# Create an SQLite database engine; replace with your database URL
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Define a model class that inherits from Base
class User(Base):
    __tablename__ = 'users'  # Define the name of the table

    id = Column(Integer, primary_key=True)  # Define the primary key column
    name = Column(String)  # Define a regular column
    age = Column(Integer)  # Define another column

# Create a session factory
Session = sessionmaker(bind=engine)

# Create the tables in the database
Base.metadata.create_all(engine)

# Create a new session
session = Session()

# Create a new user instance
new_user = User(name='Lorena Borna', age=30)

# Add the user to the session
session.add(new_user)

# Commit the transaction to save changes
session.commit()

# Query all users
users = session.query(User).all()

# Print user details
for user in users:
    print(f'ID: {user.id}, Name: {user.name}, Age: {user.age}')

Output:

2020-08-30 22:47:43,639 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-30 22:47:43,639 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2020-08-30 22:47:43,640 INFO sqlalchemy.engine.Engine [raw sql] ()
2020-08-30 22:47:43,641 INFO sqlalchemy.engine.Engine COMMIT
2020-08-30 22:47:43,642 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-30 22:47:43,644 INFO sqlalchemy.engine.Engine INSERT INTO users (name, age) VALUES (?, ?)
2020-08-30 22:47:43,644 INFO sqlalchemy.engine.Engine [generated in 0.00053s] ('Lorena Borna', 30)
2020-08-30 22:47:43,646 INFO sqlalchemy.engine.Engine COMMIT
2020-08-30 22:47:43,651 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-30 22:47:43,652 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.age AS users_age 
FROM users
2020-08-30 22:47:43,653 INFO sqlalchemy.engine.Engine [generated in 0.00049s] ()
ID: 1, Name: Antelmo Nuray, Age: 30
ID: 2, Name: Lorena Borna, Age: 30

Explanation:

Import Modules:

from sqlalchemy import create_engine, Column, Integer, String: Imports necessary components for SQLAlchemy, including functions to create a database engine and define columns in a table.
from sqlalchemy.orm import declarative_base, sessionmaker: Imports functions for creating a base class for declarative models and a session factory.

Create Database Engine:

engine = create_engine('sqlite:///example.db', echo=True): Initializes an SQLite database engine with a specified URL (example.db). The echo=True parameter enables logging of SQL queries.

Create Base Class:

Base = declarative_base(): Creates a base class for defining database models. This class will be used to create table classes that will map to database tables.

Define Model Class:

class User(Base): Defines a User class that inherits from Base, representing the users table in the database.

__tablename__ = 'users': Specifies the name of the table in the database.
id = Column(Integer, primary_key=True): Defines an integer column id that serves as the primary key.
name = Column(String): Defines a column name to store user names.
age = Column(Integer): Defines a column age to store user ages.

Create Session Factory:

Session = sessionmaker(bind=engine): Creates a session factory bound to the engine, allowing interaction with the database.

Create Tables:

Base.metadata.create_all(engine): Creates the tables defined in the Base metadata (User table in this case) in the database.

Create a New Session:

session = Session(): Creates a new session object for interacting with the database.

Create a New User Instance:

new_user = User(name='Lorena Borna', age=30): Creates a new User instance with the name 'Lorena Borna' and age 30.

Add User to Session:

session.add(new_user): Adds the new user instance to the current session, marking it for insertion into the database.

Commit Transaction:

session.commit(): Commits the transaction, saving the new user record to the database.

Query All Users:

users = session.query(User).all(): Queries all records from the User table and stores them in the users list.

Print User Details:

for user in users: print(f'ID: {user.id}, Name: {user.name}, Age: {user.age}'): Iterates over the list of users and prints each user's ID, name, and age.

Example 5: Filtering Records

This example demonstrates how to filter records based on a condition. It retrieves users whose age is greater than 25.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# Create an SQLite database engine; replace with your database URL
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Define a model class that inherits from Base
class User(Base):
    __tablename__ = 'users'  # Define the name of the table

    id = Column(Integer, primary_key=True)  # Define the primary key column
    name = Column(String)  # Define a regular column
    age = Column(Integer)  # Define another column

# Create a session factory
Session = sessionmaker(bind=engine)

# Create the tables in the database
Base.metadata.create_all(engine)

# Create a new session
session = Session()

# Query users with age greater than 25
filtered_users = session.query(User).filter(User.age > 25).all()

# Print filtered user details
for user in filtered_users:
    print(f'ID: {user.id}, Name: {user.name}, Age: {user.age}')

Output:

2020-08-31 09:37:17,516 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 09:37:17,517 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2020-08-31 09:37:17,517 INFO sqlalchemy.engine.Engine [raw sql] ()
2020-08-31 09:37:17,519 INFO sqlalchemy.engine.Engine COMMIT
2020-08-31 09:37:17,520 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 09:37:17,522 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.age AS users_age 
FROM users 
WHERE users.age > ?
2020-08-31 09:37:17,523 INFO sqlalchemy.engine.Engine [generated in 0.00084s] (25,)
ID: 1, Name: Antelmo Nuray, Age: 30
ID: 2, Name: Lorena Borna, Age: 30

Explanation:

Import Necessary Modules:

Imports essential SQLAlchemy modules (create_engine, Column, Integer, String) and ORM utilities (declarative_base, sessionmaker).

Create an SQLite Database Engine:

engine = create_engine('sqlite:///example.db', echo=True): Establishes a connection to an SQLite database named example.db. The echo=True parameter enables SQL query logging.

Create a Base Class for Declarative Class Definitions:

Base = declarative_base(): Creates a base class for defining ORM models. All ORM model classes will inherit from this base class.

Define a Model Class (User):

class User(Base): Defines a User class as an ORM model that represents the users table in the database.
__tablename__ = 'users': Sets the name of the table in the database as users.

id, name, and age are defined as columns in the users table:

id = Column(Integer, primary_key=True): An Integer column that serves as the primary key.
name = Column(String): A String column to store the user's name.
age = Column(Integer): An Integer column to store the user's age.

Create a Session Factory:

Session = sessionmaker(bind=engine): Creates a session factory, binding it to the previously created database engine. This factory will be used to create session objects for interacting with the database.

Create the Tables in the Database:

Base.metadata.create_all(engine): Generates the SQL commands necessary to create the tables defined by ORM models (like User) in the connected database (example.db).

Create a New Session:

session = Session(): Instantiates a session object from the session factory to interact with the database (execute queries, transactions, etc.).

Query Users with Age Greater Than 25:

filtered_users = session.query(User).filter(User.age > 25).all(): Queries the users table to retrieve all users whose age is greater than 25.

Print Filtered User Details:

Loops through the filtered_users list and prints the details (ID, name, age) of each user retrieved from the query.

Example 6: Updating Records

This example shows how to update an existing record. It finds a user by name and updates their age.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# Create an SQLite database engine; replace with your database URL
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Define a model class that inherits from Base
class User(Base):
    __tablename__ = 'users'  # Define the name of the table

    id = Column(Integer, primary_key=True)  # Define the primary key column
    name = Column(String)  # Define a regular column
    age = Column(Integer)  # Define another column

# Create a session factory
Session = sessionmaker(bind=engine)

# Create the tables in the database
Base.metadata.create_all(engine)

# Create a new session
session = Session()

# Query a user to update
user_to_update = session.query(User).filter(User.name == 'Lorena Borna').first()

# Update user details
if user_to_update:
    user_to_update.age = 31
    session.commit()  # Commit the changes to the database
else:
    print("User not found.")

Output:

2020-08-31 10:11:27,947 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 10:11:27,948 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2020-08-31 10:11:27,948 INFO sqlalchemy.engine.Engine [raw sql] ()
2020-08-31 10:11:27,950 INFO sqlalchemy.engine.Engine COMMIT
2020-08-31 10:11:27,951 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 10:11:27,953 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.age AS users_age 
FROM users 
WHERE users.name = ?
 LIMIT ? OFFSET ?
2020-08-31 10:11:27,953 INFO sqlalchemy.engine.Engine [generated in 0.00050s] ('Lorena Borna', 1, 0)
2020-08-31 10:11:27,956 INFO sqlalchemy.engine.Engine UPDATE users SET age=? WHERE users.id = ?
2020-08-31 10:11:27,956 INFO sqlalchemy.engine.Engine [generated in 0.00077s] (31, 4)
2020-08-31 10:11:27,958 INFO sqlalchemy.engine.Engine COMMIT

Explanation:

Import necessary modules:

Imports required SQLAlchemy modules such as create_engine, Column, Integer, String for database interactions, and declarative_base, sessionmaker for ORM.

Create an SQLite database engine:

create_engine('sqlite:///example.db', echo=True): Creates an SQLite database engine named example.db. The echo=True parameter enables logging of SQL queries generated by SQLAlchemy.

Create a base class for declarative class definitions:
Base = declarative_base(): Creates a base class for defining ORM models, which is used to define the database tables.

Define a model class (User):

class User(Base): Defines a model class User that represents the users table in the database.
__tablename__ = 'users': Specifies the table name as users.
id = Column(Integer, primary_key=True): Defines an id column as the primary key with integer type.
name = Column(String): Defines a name column with string type.
age = Column(Integer): Defines an age column with integer type.

Create a session factory:

Session = sessionmaker(bind=engine): Creates a session factory bound to the engine to handle transactions and queries.

Create the tables in the database:

Base.metadata.create_all(engine): Generates the users table in the database based on the model definition.

Create a new session:

session = Session(): Creates a new session to interact with the database.

Query a user to update:

user_to_update = session.query(User).filter(User.name == 'Lorena Borna').first(): Queries the database for the first User object with the name 'Lorena Borna'.

Update user details:

if user_to_update: Checks if the user was found.
user_to_update.age = 31: Updates the age attribute of the user to 31.
session.commit(): Commits the changes to save them in the database.
else: print("User not found."): Prints "User not found." if no user matches the criteria.

Example 7: Deleting Records

This example demonstrates how to delete a record from the database. It retrieves a user and then deletes them.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# Create an SQLite database engine; replace with your database URL
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Define a model class that inherits from Base
class User(Base):
    __tablename__ = 'users'  # Define the name of the table

    id = Column(Integer, primary_key=True)  # Define the primary key column
    name = Column(String)  # Define a regular column
    age = Column(Integer)  # Define another column

# Create a session factory
Session = sessionmaker(bind=engine)

# Create the tables in the database
Base.metadata.create_all(engine)

# Create a new session
session = Session()

# Query a user to delete
user_to_delete = session.query(User).filter(User.name == 'Antelmo Nuray').first()

# Delete the user if found
if user_to_delete:
    session.delete(user_to_delete)
    session.commit()  # Commit the changes to the database
    print("User deleted successfully.")
else:
    print("User not found.")

Output:

20204-08-31 10:22:12,273 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 10:22:12,274 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2020-08-31 10:22:12,275 INFO sqlalchemy.engine.Engine [raw sql] ()
2020-08-31 10:22:12,276 INFO sqlalchemy.engine.Engine COMMIT
2020-08-31 10:22:12,278 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 10:22:12,279 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.age AS users_age 
FROM users 
WHERE users.name = ?
 LIMIT ? OFFSET ?
2020-08-31 10:22:12,280 INFO sqlalchemy.engine.Engine [generated in 0.00054s] ('Antelmo Nuray', 1, 0)
2020-08-31 10:22:12,283 INFO sqlalchemy.engine.Engine DELETE FROM users WHERE users.id = ?
2020-08-31 10:22:12,283 INFO sqlalchemy.engine.Engine [generated in 0.00070s] (1,)
2020-08-31 10:22:12,285 INFO sqlalchemy.engine.Engine COMMIT
User deleted successfully.

Explanation:

Import necessary modules:

create_engine, Column, Integer, and String are imported from sqlalchemy to manage database connections and table structures.
declarative_base and sessionmaker are imported from sqlalchemy.orm for defining ORM models and managing sessions.

Create an SQLite database engine:

engine = create_engine('sqlite:///example.db', echo=True) creates an engine for SQLite, connecting to the database file example.db. echo=True enables SQL logging.

Create a base class for declarative class definitions:

Base = declarative_base() defines a base class for declarative ORM models.

Define the User model class:

class User(Base) creates a class that maps to the users table in the database.
__tablename__ = 'users' sets the name of the table.
id, name, and age are columns in the table, with id serving as the primary key.

Create a session factory:

Session = sessionmaker(bind=engine) creates a session factory bound to the database engine.

Create the tables in the database:

Base.metadata.create_all(engine) creates all tables defined by ORM models in the database if they do not exist.

Create a new session:

session = Session() creates a new session for interacting with the database.

Query a user to delete:

user_to_delete = session.query(User).filter(User.name == 'Antelmo Nuray').first() retrieves the first user with the name 'Antelmo Nuray' from the database.

Delete the user if found:

if user_to_delete: checks if a user is found.
session.delete(user_to_delete) deletes the user.
session.commit() commits the changes to the database to save the deletion.
Prints "User deleted successfully." if the user is found and deleted; otherwise, prints "User not found."

Example 8: Handling Transactions

This example shows how to handle transactions, including committing changes and rolling back in case of an error.

Code:

# Import necessary modules from SQLAlchemy
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

# Create an SQLite database engine
engine = create_engine('sqlite:///example.db', echo=True)

# Create a base class for declarative class definitions
Base = declarative_base()

# Define a model class that inherits from Base
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

# Create a session factory
Session = sessionmaker(bind=engine)

# Create the tables in the database
Base.metadata.create_all(engine)


try:
    # Create a new session
    session = Session()

    # Add new user records
    user1 = User(name='Saima Maura', age=20)
    user2 = User(name='Yeva Carin', age=25)

    session.add(user1)
    session.add(user2)

    # Commit the transaction
    session.commit()

except Exception as e:
    print(f'Error: {e}')
    if 'session' in locals():  # Ensure session is defined before rollback
        session.rollback()  # Rollback in case of an error

Output:

2020-08-31 10:41:28,366 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 10:41:28,367 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2020-08-31 10:41:28,368 INFO sqlalchemy.engine.Engine [raw sql] ()
2020-08-31 10:41:28,369 INFO sqlalchemy.engine.Engine COMMIT
2020-08-31 10:41:28,370 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2020-08-31 10:41:28,372 INFO sqlalchemy.engine.Engine INSERT INTO users (name, age) VALUES (?, ?) RETURNING id
2020-08-31 10:41:28,372 INFO sqlalchemy.engine.Engine [generated in 0.00007s (insertmanyvalues) 1/2 (ordered; batch not supported)] ('Saima Maura', 20)
2020-08-31 10:41:28,374 INFO sqlalchemy.engine.Engine INSERT INTO users (name, age) VALUES (?, ?) RETURNING id
2020-08-31 10:41:28,375 INFO sqlalchemy.engine.Engine [insertmanyvalues 2/2 (ordered; batch not supported)] ('Yeva Carin', 25)
2020-08-31 10:41:28,376 INFO sqlalchemy.engine.Engine COMMIT

Explanation:

Import necessary modules from SQLAlchemy:

Imports create_engine, Column, Integer, String from sqlalchemy to define the database and its schema.
Imports declarative_base, sessionmaker from sqlalchemy.orm to create a base class and manage sessions.

Create an SQLite database engine:

Uses create_engine('sqlite:///example.db', echo=True) to create an SQLite database named example.db and sets echo=True to output SQL queries for debugging.

Create a base class for declarative class definitions:

Calls declarative_base() to create a base class (Base) that all ORM models will inherit from.

Define a model class that inherits from Base:

Defines a class User which represents the users table in the database.

id: Primary key column (Integer).
name: Regular column (String).
age: Regular column (Integer).

Create a session factory:

Uses sessionmaker(bind=engine) to create a Session factory bound to the database engine.

Create the tables in the database:

Calls Base.metadata.create_all(engine) to create the tables defined by the ORM models (like User) in the database.

Try block to handle database operations safely:

Begins a try block to handle any exceptions that might occur during database operations.

Create a new session:

Instantiates a new session object (session = Session()).

Add new user records:

Creates two new User instances (user1 and user2).
Adds the user instances to the session using session.add().

Commit the transaction:

Commits the session to save the changes (new user records) to the database (session.commit()).

Exception handling block:

Catches any exceptions that occur during the database operations.
Prints an error message (print(f'Error: {e}')).
Checks if the session object exists before calling session.rollback() to revert any changes in case of an error.