Python SQLAlchemy model with dynamic table name¶

Normally, a SQLAlchemy model class corresponds to a single database table. But sometimes, we want a model to represent multiple tables. For example, we may want to split a big table into multiple ones separated by years, that is, one table for one year's data.

When you are not using an ORM library, it's easy, because sql statements are naturally dynamic strings. But, how can we do it with an ORM like SQLAlchemy?

Code Example¶

Assume that we use multiple tables to save logs. Table names are logs_2019, logs_2020, logs_2021, logs_2022 and so on. These tables share the same columns. The only difference between each other is table name.

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

# define columns in an abstract model class
class Log(Base):
    __abstract__ = True  # this line is necessary
    # the following columns (id, content, user_id) are just examples
    id = Column(BIGINT, primary_key=True, autoincrement=True)
    content = Column(VARCHAR(200), nullable=False)
    user_id = Column(INTEGER)


# build a model class with a specific table name
def get_log_model(year):
    tablename = 'logs_%s' % year  # dynamic table name
    class_name = 'Log%s' % year  # dynamic class name
    Model = type(class_name, (Log,), {
        '__tablename__': tablename
    })
    return Model


# Log2021 corresponds to table "logs_2021"
Log2021 = get_log_model(2021)
# use the dynamically built model in the same way as regular models 
print(session.query(Log2021).count())  # row count of table "logs_2021"

# another dynamic model
Log2022 = get_log_model(2022)  # Log2022 corresponds to table "logs_2022"
print(session.query(Log2022).count())  # row count of table "logs_2022"

As the comments say, there are three steps:

1. Define table columns in an abstract model class.
2. Build a model class based on the abstract model and give it a specific table name.
3. Use the built model class like a regular one.