SQLAlchemy and Alembic

 

SQLAlchemy and Alembic: Explained with Example

SQLAlchemy:

• A powerful Python library for interacting with relational databases.
• Provides an object-relational mapper (ORM) that lets you define your data model as Python classes and map them to tables in a database.
• Simplifies writing SQL queries and manipulating data through its object-oriented interface.

Alembic:

• A migration tool built on top of SQLAlchemy.
• Allows you to track changes to your database schema over time and manage upgrades and downgrades.
• Generates migration scripts as your data model evolves, providing version control for your database schema.

Example:

Let's consider a model that defines a User table with two attributes: id (primary key) and username.

Python code (SQLAlchemy):

Python

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class
 
User(Base):
    __tablename__ = "users"


    
id = Column(Integer, primary_key=True)
    username = Column(String(50), nullable=False)

engine = create_engine("sqlite:///database.db")
Base.metadata.create_all(engine)

# Creating new User objects and writing to the database
user1 = User(username="alice")
user2 = User(username="bob")

session = Session(engine)
session.add_all([user1, user2])
session.commit()

# Reading users from the database
users = session.query(User).all()
print(users)

Creating a Migration with Alembic:

1. Initialize Alembic:

alembic init

2. Generate a migration script for the initial schema:

alembic revision --autogenerate

This creates a migration script containing the necessary SQL statements to create the users table.

3. Upgrade the database schema:

alembic upgrade

This executes the migration script and creates the users table in the database.

Adding a new attribute to the model:

We can add a new attribute email to the User model:

Python

class User(Base):
    # ... existing code
    email = Column(String(100))

Base.metadata.alter(engine)

This will alter the existing users table in the database and add the email column.

4. Generate a new migration script:

alembic revision --autogenerate

5. Upgrade the database schema:

alembic upgrade

Benefits of using SQLAlchemy and Alembic:

• Code readability: Focuses on the data model structure rather than writing raw SQL queries.
• Maintainability: Easier to evolve the database schema and track changes.
• Version control: Migration scripts act as version control for the database schema.
• Portability: Code can be ported to different databases with minimal changes.

Remember: This is a simplified example. For real-world scenarios, you can define much more complex models with relationships, constraints, and other features.

You can find more example in internet also can check my github repo here https://github.com/dhirajpatra