dynamic_table_example_2.py

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, DateTime


defined_tables = {}

def dynamic_table_wrapper(func):
    '''
    Decorate a function that declare dynamic table.
    It expects that the function being decorated
    takes declarative_base as a first input and this base has the database engine
    bound to it.
    '''
    def wrapped(Base, *args, **kwargs):
        key = (
            Base.metadata.bind.url.database,
            func.__name__,
            args,
            tuple([(k, v) for k, v in kwargs.iteritems()])
        )
        existing_table = defined_tables.get(key)
        if existing_table is not None:
            return existing_table
        table = func(Base, *args, **kwargs)
        defined_tables[key] = table
        return table
    return wrapped


@dynamic_table_wrapper
def Trasaction(Base, year, month, day):
    month = "{:02d}".format(month)
    day = "{:02d}".format(day)

    class Trasaction(Base):
        __tablename__ = 'card_trasaction_{year}_{month}_{day}'.format(
            year=year, month=month, day = day
        )
        __table_args__ = (
            {'extend_existing':True,
             'mysql_engine': 'InnoDB'},
        )
        id = Column(Integer, primary_key=True, autoincrement=True)
        timestamp = Column(DateTime, nullable=False, index=True)
        amount = Column(Integer, nullable=False, index=True)
        card_num = Column(String(24), nullable=False, index=True)
    return Trasaction