How Do I Create All Tables In SQLAlchemy?

Creating all tables in SQLAlchemy is typically done with a single function call:

Base.metadata.create_all(engine). The key is ensuring that SQLAlchemy has a reference to every model you've defined, which is most reliably handled by importing all your model files before calling create_all().

This article provides a detailed guide on how to create tables using the Declarative style, the most common approach for ORM applications.

Prerequisites

• A Python environment with SQLAlchemy installed: pip install sqlalchemy
• A database driver for your database (e.g., psycopg2 for PostgreSQL, mysql-connector-python for MySQL, or the built-in sqlite3 for SQLite).

Step 1: Set up the database connection

First, create a SQLAlchemy Engine that manages the connection to your database.

from sqlalchemy import create_engine
# For a SQLite database, which is good for examples
engine = create_engine("sqlite:///my_database.db")
# For other databases, the connection string will differ:
# PostgreSQL: engine = create_engine("postgresql+psycopg2://user:password@host/dbname")
# MySQL: engine = create_engine("mysql+mysqlconnector://user:password@host/dbname")

Use code with caution.

Step 2: Define the declarative base

The Declarative Base is a class that all of your model classes will inherit from. It acts as a central registry for all your table and model definitions.

from sqlalchemy.orm import DeclarativeBase
class Base(DeclarativeBase):
    pass

Use code with caution.

Step 3: Define your ORM models

Now, define your table schemas by creating Python classes that inherit from Base. SQLAlchemy will use these class definitions to generate the corresponding SQL CREATE TABLE statements.

In this example, we define two models, User and Address, and establish a relationship between them.

File: models.py

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, Mapped, mapped_column
from .database import Base
class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String(30))
    fullname: Mapped[str | None]
    addresses: Mapped[list["Address"]] = relationship(back_populates="user")
    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"
class Address(Base):
    __tablename__ = "addresses"
    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    email_address: Mapped[str]
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    user: Mapped["User"] = relationship(back_populates="addresses")
    def __repr__(self) -> str:
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"

Use code with caution.

Step 4: Import all models

SQLAlchemy's create_all() method only knows about the models that have been imported and registered with the Base's metadata. This is a crucial step if your models are spread across multiple files.

File: main.py

from sqlalchemy import create_engine
from database import Base # Import Base from the database setup file
from models import User, Address # Import all model classes
# Create the engine (same as Step 1)
engine = create_engine("sqlite:///my_database.db")
def create_database():
    """Create all tables defined in the models."""
    print("Creating database tables...")
    Base.metadata.create_all(engine)
    print("Tables created successfully.")
if __name__ == "__main__":
    create_database()

Use code with caution.

Step 5: Run the script

With all the pieces in place, execute your main script.

python main.py

Use code with caution.

When you run this, SQLAlchemy will inspect the Base.metadata object, find all the models that inherited from Base (like User and Address), and issue the necessary CREATE TABLE statements to your database. You should see output similar to this:

Creating database tables...
Tables created successfully.

Your database file (my_database.db for SQLite) will now contain the users and addresses tables. SQLAlchemy is smart enough to handle foreign key dependencies and will create the tables in the correct order.

Considerations and best practices

Importing all models

The most common reason create_all() seems to fail is that a model file was never imported. All models must be imported somewhere before create_all() is called so that their definitions are registered with the Base.metadata object. The best practice is to put all your model imports in one central place, as shown in main.py.

Handling existing tables

The create_all() method is non-destructive and conditional. It will only create a table if it doesn't already exist in the database. It will not modify or update existing tables.

Managing schema changes (Migrations)

For applications that need to evolve their database schema over time, relying on create_all() is not sufficient. If you need to add, remove, or modify columns on an existing table, you should use a database migration tool like Alembic.

Alembic works with your SQLAlchemy models to:

• Generate migration scripts: Compares your models to your database and generates scripts containing ALTER TABLE statements.
• Apply migrations: Runs the scripts to update your database schema in a controlled, versioned manner.

Dropping all tables

For testing or development purposes, you might want to wipe the database clean. SQLAlchemy provides a complementary method to do the opposite of create_all():

Base.metadata.drop_all(engine)

Use code with caution.

This method will drop all tables registered with Base.metadata, in reverse order of dependency.