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.
This post is part of my journey to learn Python. You can find the other parts of this series here. You find the code for this post in my PythonFriday repository on GitHub.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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:
|
1 |
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.
Next
Automap is a valuable extension that saves you a lot of code to connect to an existing database. It is great for that purpose but limited in the way you can interact with tools like Alembic. Next week we do a recap on the various ways you can work with SQLAlchemy.