Usually with SQLAlchemy ORM, you define your model classes and let SQLAlchemy create the tables for you or by a migration tool such as Alembic. The attributes in your model class will be used to create the table and columns with appropriate data types. But what if the tables are already created and you want to use SQLAlchemy ORM? I had this situation where I had the tables created by Django application and another application needed to access data for analyzing from those tables. I didn’t want to add Django as a dependency in my analysis package just to use Django ORM.
After a bit of research I found two approaches:
Define model class with appropriate columns for each table
This approach works but is a bit tedious. I already implemented ORM mapping in Django and I didn’t want to do that again in SQLAlchemy. Also, if table structure changes, then I need to remember to update in both places. But it has its benefits. First we get intellisense while developing. Second, we don’t need to define all columns if we are only going to work with subset of columns. Note that if you plan to write to the database, then you’ll need to define all ‘non-null’ columns.
Let SQLAlchemy create a class automatically by inspecting the tables
Second option is to let SQLAlchemy figure the table structure automatically. An extension called
automapcan generate mapped classes and relationships. The downside to this approach is that we don’t get any intellisense on class attributes during development. It didn’t matter for me that much because I only needed to work with few tables and I knew their structure well but working with many tables with many columns might be a bit difficult and can be error prone. Check the code below on how to generate model classes automatically.
import sqlalchemy from sqlalchemy.ext.automap import automap_base from sqlalchemy.orm import sessionmaker from config import POSTGRES_URI engine = sqlalchemy.create_engine(POSTGRES_URI, pool_pre_ping=True) Session = sessionmaker(bind=engine) # these two lines perform the "database reflection" to analyze tables and relationships Base = automap_base() Base.prepare(engine, reflect=True) # there are many tables in the database but I want `products` and `categories` # only so I can leave others out Product = Base.classes.products Category = Base.classes.categories # for debugging and passing the query results around # I usually add as_dict method on the classes def as_dict(obj): data = obj.__dict__ data.pop('_sa_instance_state') return data # add the `as_dict` function to the classes for c in [Product, Category]: c.as_dict = as_dict
Now we can use the mapped classes
Category as any other SQLAlchemy model classes.
q = session.query(Product.id, Product.title) q = q.filter(Product.price > 100) # e.g return this as a rest-api response products = [r.as_dict() for r in q]
For more information about
automap extension see Automap