SQLAlchemy is a popular Object-Relational Mapping (ORM) library for Python that provides a high-level, Pythonic interface for interacting with databases. It allows you to define database tables as Python classes and perform CRUD operations using Python code.

Installing SQLAlchemy

Before we dive into defining tables and mapping classes, let’s make sure SQLAlchemy is installed. You can install it using pip:

pip install SQLAlchemy

Defining Tables

To define a table in SQLAlchemy, we use the Table class and specify the table name and its columns. Each column is defined with a name, data type, and optional constraints.

Here’s an example of defining a simple users table with id, name, and email columns:

from sqlalchemy import Table, Column, Integer, String, MetaData

metadata = MetaData()

users = Table('users', metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('email', String, unique=True)
)

In the above code snippet, we import the necessary classes from SQLAlchemy and create a MetaData object. We then define the users table with three columns: id, name, and email. The id column is the primary key, and the email column is marked as unique.

Mapping Classes

After defining the table, we can map it to a Python class using the mapper function provided by SQLAlchemy. This allows us to interact with the table using instances of the mapped class.

Here’s an example of mapping the users table to a Python class:

from sqlalchemy import mapper

class User:
    def __init__(self, name, email):
        self.name = name
        self.email = email

mapper(User, users)

In the above code snippet, we define a User class with name and email attributes. We then use the mapper function to map the User class to the users table.

CRUD Operations

Once the table is defined and mapped to a Python class, we can perform CRUD operations using instances of the mapped class.

Creating a Record

To create a new record in the table, we first need to create an instance of the mapped class and then add it to a session and commit the changes.

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///mydb.sqlite')
Session = sessionmaker(bind=engine)
session = Session()

user = User('John Doe', 'johndoe@example.com')
session.add(user)
session.commit()

In the above code snippet, we create a SQLite database engine and a session. We then create a new User instance, add it to the session, and commit the changes.

Reading Records

To retrieve records from the table, we can use the session to query the mapped class:

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

for user in users:
    print(user.name, user.email)

In the above code snippet, we retrieve all User instances from the users table and iterate over them to print their names and emails.

Updating a Record

To update a record, we retrieve it from the table, modify its attributes, and commit the changes:

user = session.query(User).filter_by(name='John Doe').first()
user.email = 'john@example.com'
session.commit()

In the above code snippet, we retrieve the User instance with the name ‘John Doe’, update its email, and commit the changes.

Deleting a Record

To delete a record, we first retrieve it from the table and then delete it from the session:

user = session.query(User).filter_by(name='John Doe').first()
session.delete(user)
session.commit()

In the above code snippet, we retrieve the User instance with the name ‘John Doe’, delete it from the session, and commit the changes.

Conclusion

SQLAlchemy provides a powerful and flexible way to define database tables and map them to Python classes. With SQLAlchemy, you can easily perform CRUD operations using Python code, abstracting away the complexities of SQL queries.