SQLAlchemy Code Generator

4.0.3 · active · verified Sun Apr 12

sqlacodegen is a command-line tool that automatically generates SQLAlchemy model code from an existing database schema. It introspects tables, columns, types, and relationships, producing Python files ready for use with SQLAlchemy 2.0+. The current version is 4.0.3, and it receives updates as SQLAlchemy evolves.

Warnings

breaking Breaking change in generated model syntax between SQLAlchemy 1.x and 2.x. `sqlacodegen` versions 3.0+ (including 4.x) generate models compatible with SQLAlchemy 2.0+ (e.g., using `Mapped` and `Column` as a function). Older `sqlacodegen` 2.x versions generated SQLAlchemy 1.x style models (e.g., `Column` as a class attribute).
Fix: Ensure you are using `sqlacodegen` version 3.0 or higher for SQLAlchemy 2.0+ projects. If migrating, review the generated code for 2.0 idioms and adapt existing 1.x code manually.
gotcha By default, `sqlacodegen` overwrites existing files if you use the `--outfile` flag without additional safeguards. It does not perform intelligent merging or incremental updates, treating each run as a fresh generation.
Fix: Always use a version control system (e.g., Git) and review changes (`git diff`) before committing. Consider piping the output to `diff` or a temporary file first, or integrating `sqlacodegen` into a script that handles file comparison and merging.
gotcha sqlacodegen strictly reflects the existing database schema. It does not infer ORM-specific features like back-references, complex relationships not explicitly defined by foreign keys, or custom business logic/methods often added to ORM models.
Fix: After generating the initial models, you will need to manually add ORM-specific relationships (e.g., `relationship()`, `backref`), methods, and other custom logic to enhance your models beyond the raw schema reflection. Treat the generated code as a starting point.
gotcha Generated Python class and attribute names might not always conform to Python's `snake_case` or project-specific naming conventions, especially for multi-word table or column names, or in environments where `CamelCase` is prevalent in the database.
Fix: Manually refactor names in the generated models to match your project's Python naming conventions. For consistent transformations, consider creating a custom Jinja2 template for `sqlacodegen` (an advanced option) or using a post-processing script.

Install

pip install sqlacodegen Install sqlacodegen

Imports

CodeGenerator
```
from sqlacodegen.codegen import CodeGenerator
```
sqlacodegen is primarily a command-line tool. Direct programmatic import of internal classes like CodeGenerator is generally for advanced use cases or integration, not typical model generation. Most users execute it via the 'sqlacodegen' CLI command.

Quickstart

This quickstart demonstrates how to use `sqlacodegen` to generate SQLAlchemy models from a simple SQLite database. It first creates a dummy database with a 'users' table, then executes the `sqlacodegen` command-line tool via `subprocess`, printing the generated models to the console. For real-world use, you would direct the output to a Python file using the `--outfile` flag.

import subprocess
import os
from sqlalchemy import create_engine, Column, Integer, String, MetaData, Table

# Create a dummy SQLite database in memory
db_url = 'sqlite:///test.db'
engine = create_engine(db_url)
metadata = MetaData()

# Define a simple table
table_name = 'users'
users_table = Table(
    table_name,
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String(50), nullable=False),
    Column('email', String(100), unique=True)
)

# Create the table in the database
metadata.create_all(engine)

# Run sqlacodegen as a subprocess
# For demonstration, we'll print to stdout. Use --outfile to save to a file.
try:
    result = subprocess.run(
        ['sqlacodegen', db_url],
        capture_output=True,
        text=True,
        check=True
    )
    print("\n--- Generated SQLAlchemy Models ---")
    print(result.stdout)
    print("-----------------------------------")
except subprocess.CalledProcessError as e:
    print(f"Error running sqlacodegen: {e}\nStdout: {e.stdout}\nStderr: {e.stderr}")
except FileNotFoundError:
    print("Error: sqlacodegen command not found. Please ensure it's installed and in your PATH.")
finally:
    # Clean up the dummy database file
    if os.path.exists('test.db'):
        os.remove('test.db')

view raw JSON →