SQLAlchemy provides a robust foundation for database interaction in Python, combining the power of raw SQL with the elegance of object-relational mapping. This quick start guide is designed to get you writing efficient, reliable database code in minutes rather than hours. You will learn how to configure connections, define models, and execute queries with confidence.
Installing SQLAlchemy and Database Drivers
Before writing any code, you need the correct packages installed in your environment. The core SQLAlchemy library handles the ORM and connection pooling, while a specific database driver allows communication with your chosen system. For PostgreSQL, psycopg2-binary is a popular choice, and for MySQL, mysqlclient is commonly used.
You can install everything you need using pip. We recommend installing SQLAlchemy alongside the appropriate driver for your database. Running this command ensures you have the necessary tools to define your first mapped classes and establish a session.
pip install sqlalchemy psycopg2-binary
pip install sqlalchemy mysqlclient
pip install sqlalchemy sqlite3
Establishing a Database Connection
The engine is the starting point for any SQLAlchemy application, acting as a pool of connections to your database. You create it using a database URL, which specifies the driver, username, password, host, port, and database name. This URL tells SQLAlchemy exactly how to locate and authenticate with your server.
Once the engine is created, you can use it to generate sessions. A session provides a workspace for loading, changing, and saving objects. It handles the unit of work pattern, ensuring that all changes are flushed to the database only when you are ready to commit.
Defining Your Data Models
Declarative base allows you to map Python classes to database tables. By inheriting from a base class, you instruct SQLAlchemy to track your attributes and generate the appropriate schema. Each attribute corresponds to a column, and you define the type and constraints directly in the class definition.
Here is a basic example of a model representing a user in a system. The id field is typically set as the primary key, which ensures every row is unique. The name and email fields are configured as strings with length limits, enforcing data integrity at the application level.
Example Model Code
Python Class | Resulting Database Column
class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(50)) email = Column(String(120), unique=True) | users table with columns: id (INT, PK), name (VARCHAR(50)), email (VARCHAR(120), UNIQUE)
Creating Tables and Managing Schema
SQLAlchemy can generate the actual tables in the database based on your model definitions. Calling Base.metadata.create_all(engine) compares your class definitions to the existing schema and adds any missing tables. This feature is incredibly useful during development, as it eliminates the need for manual SQL scripts to set up your environment.
It is important to note that this method is generally intended for local development and testing. In production environments, changes are usually managed through migration scripts generated by tools like Alembic. This ensures that schema updates are applied safely without losing existing data.
