SQLAlchemySQLAlchemy is a powerful and flexible Python SQL toolkit and Object Relational Mapper (ORM) that provides a full suite of well-known persistence patterns for access to a wide variety of SQL databases.
Its primary purpose is to allow developers to work with databases using Python objects, abstracting away the complexities of raw SQL queries and database-specific syntax. It supports multiple database backends, including SQLite, PostgreSQL, MySQL, Oracle, and Microsoft SQL Server, allowing for database agnosticism in application development.
Key features and concepts of SQLAlchemy:
1. SQL Expression Language: This is SQLAlchemy's foundational layer, providing a programmatic way to construct SQL statements. It allows you to build `SELECT`, `INSERT`, `UPDATE`, `DELETE` queries using Python objects and expressions, offering a high degree of flexibility and control without writing raw SQL strings.
2. ORM (Object Relational Mapper): Built on top of the SQL Expression Language, the ORM provides a higher-level abstraction. It allows you to define Python classes that map directly to database tables (declarative models), and instances of these classes map to rows in those tables. This enables developers to interact with database records as ordinary Python objects, handling object persistence, relationships, and queries.
3. Engine: The `Engine` is the starting point for any SQLAlchemy application. It's an object that manages a database connection pool and a dialiect, which knows how to communicate with a specific database type (e.g., PostgreSQL, MySQL). You create an `Engine` using a database URL.
4. Session: The `Session` object is the primary way you interact with the database when using the ORM. It represents a "unit of work" and manages the persistence of objects. When you add, modify, or delete objects within a session, SQLAlchemy tracks these changes. The changes are only committed to the database when you call `session.commit()`.
5. Metadata: SQLAlchemy allows you to define database schema (tables, columns, constraints) programmatically using `MetaData` objects. This can be done explicitly or implicitly through declarative ORM models.
6. Relationships: The ORM provides robust tools for defining relationships between models (e.g., one-to-many, many-to-one, many-to-many), allowing you to navigate related objects in a natural Pythonic way.
In essence, SQLAlchemy bridges the gap between the relational world of databases and the object-oriented world of Python applications, making database interactions more intuitive, maintainable, and less error-prone.
Example Code
import os
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
--- 1. Define the Database Engine and Base ---
Use SQLite in-memory for simplicity, or a file-based one
database_url = "sqlite:///./sql_app.db" Connect to a file-based SQLite database
Create an Engine. The Engine is the starting point of SQLAlchemy.
It handles the database connection and dialect.
engine = create_engine(database_url, echo=False) echo=True for SQL logging
Base class for our declarative models
Base = declarative_base()
--- 2. Define the ORM Model ---
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True, index=True)
name = Column(String, index=True)
email = Column(String, unique=True, index=True)
def __repr__(self):
return f"<User(id={self.id}, name='{self.name}', email='{self.email}')>"
--- 3. Create Tables in the Database ---
This command creates all tables defined by Base's subclasses in the database
Base.metadata.create_all(bind=engine)
--- 4. Create a Session and Interact with the Database ---
SessionLocal is a factory for Session objects.
Each instance of SessionLocal will be a database session.
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Function to get a database session (useful in larger applications)
def get_db():
db = SessionLocal()
try:
yield db
finally:
db.close()
Example usage:
Get a session instance
db = next(get_db())
try:
print("\n--- Adding New Users ---")
Create new User objects
user1 = User(name="Alice", email="alice@example.com")
user2 = User(name="Bob", email="bob@example.com")
user3 = User(name="Charlie", email="charlie@example.com")
Add them to the session
db.add(user1)
db.add(user2)
db.add(user3)
Commit the changes to the database
db.commit()
Refresh the objects to get their database-assigned IDs
db.refresh(user1)
db.refresh(user2)
db.refresh(user3)
print(f"Added: {user1}")
print(f"Added: {user2}")
print(f"Added: {user3}")
print("\n--- Querying Users ---")
Query all users
all_users = db.query(User).all()
print("All users:")
for user in all_users:
print(user)
Query a user by email
bob = db.query(User).filter(User.email == "bob@example.com").first()
print(f"\nUser with email bob@example.com: {bob}")
print("\n--- Updating a User ---")
Update Bob's name
if bob:
bob.name = "Robert"
db.add(bob) Add the modified object back to the session
db.commit()
db.refresh(bob)
print(f"Updated Bob to: {bob}")
Verify update
updated_bob = db.query(User).filter(User.email == "bob@example.com").first()
print(f"Verified update: {updated_bob}")
print("\n--- Deleting a User ---")
Delete Charlie
charlie = db.query(User).filter(User.name == "Charlie").first()
if charlie:
db.delete(charlie)
db.commit()
print(f"Deleted: {charlie}")
Verify deletion
remaining_users = db.query(User).all()
print("Remaining users:")
for user in remaining_users:
print(user)
finally:
Close the session
db.close()
Clean up the database file for consistent re-runs if it's file-based
if os.path.exists("./sql_app.db"):
os.remove("./sql_app.db")
print("\nCleaned up sql_app.db")