#91: Automap for SQLAlchemy ORM

The mapping for SQLAlchemy ORM takes a lot of initial work – often too much work for just a few queries against an existing database. However, there is an extension that offers a quicker way to work with a rudimentary object model and the nice parts of ORM.

Pre-Requisites

For this post we need the Northwind database for SQL Server and a driver to connect to SQL Server.

Automap

SQLAlchemy comes with the built-in extension automap that helps you to access a pre-existing database. It is a great help when you want a quick access to someone else’s database.

You do not need to install anything special; you only need to import automap_base to your script:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()

engine = create_engine("mssql+pyodbc://localhost/Northwind?driver=SQL Server?Trusted_Connection=yes")
# reflect the tables
Base.prepare(engine, reflect=True, schema='dbo')

# mapped classes use table name.
Customers = Base.classes.Customers

# Show the metadata
# for t in Base.metadata.sorted_tables:
#         print(f"\nTable {t.name}:")
#         for c in t.columns:
#             print(f"{c} ({c.type})")

session = Session(engine)

customers = session.query(Customers).limit(5).all()

for c in customers:
    print(f"{c.CompanyName} - {c.ContactName}: {c.Phone}")

You create an engine with a connection string and hand it to automap. The magic happens on line 9, where automap uses reflection to map the tables to objects. All the names (for the objects and the properties) are the same as in the database.

We can run our script and it prints the first 5 customers:

python .\sqlalchemy_orm_automap_sqlserver.py

Alfreds Futterkiste – Maria Anders: 030-0074321
Ana Trujillo Emparedados y helados – Ana Trujillo: (5) 555-4729
Antonio Moreno Taquería – Antonio Moreno: (5) 555-3932
Around the Horn – Thomas Hardy: (171) 555-7788
Berglunds snabbköp – Christina Berglund: 0921-12 34 65

If you want to see what automap all detected, you can uncomment lines 15 to 18.

Limitations

Automap does a lot in the background. If you want to do more than just reading data, you should have a close look at the documentation, especially around relationship detection and what the default settings are for cascade delete.

With reflection all tables are mapped by default, even those you do not need. That may result in warnings like this one even when you have no intention of accessing the table it belongs to:

SAWarning: Did not recognize type 'hierarchyid' of column 'DocumentNode'

Without an explicit mapping, you cannot change your models and use Alembic to update the structure of your database. Should this need arise, you can go for the full mapping and use the approach I wrote about here to handle the existing tables.

#91: Automap for SQLAlchemy ORM

Pre-Requisites

Automap

Limitations

Next