w3resource

Python SQLAlchemy: Using SQLAlchemy ORM for Database Interactions

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.
    

    Become a Patron!

    Follow us on Facebook and Twitter for latest update.