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.
- 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.
- Session = sessionmaker(bind=engine): Creates a session factory bound to the engine to handle transactions and queries.
- Base.metadata.create_all(engine): Generates the users table in the database based on the model definition.
- session = Session(): Creates a new session to interact with the database.
- 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'.
- 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.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics