sqlalchemy按月水平分表、python元类、动态映射表名automap_base\ 模型类

sqlalchemy按月水平分表、python元类、动态映射表名 模型类

SQLAlchemy==1.3.8
python==3.7

1.事先按月建表

• 根据sqlalchemy 模型类建表

from sqlalchemy.ext.declarative import declarative_base,
from sqlalchemy import Column, String
from sqlalchemy import create_engine, MetaData

url = f"""mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset={charset}"""
engine = create_engine(url, pool_size=5, pool_recycle=1800, pool_pre_ping=True, encoding='utf-8')
Base = declarative_base(engine)


class TestTable(Base):
    __tablename__ = 'test_table'
    id = Column(Integer, primary_key=True)
    table_name = Column(String(50), unique=True)


def create_sharding_table(index=None):
	new_class = type(f"MyClass_{index}", (Base,), dict(__tablename__=f"test_table_{index}", id=Column(Integer, primary_key=True)))
    return new_class

def create_table():
	# 用元类创建多个model class
	my_class_list = [create_more_tables(x) for x in range(12)]
	Base.metadata.create_all()

if __name__ == '__main__':
	create_table()

2.根据DB中已有的表 映射模型类

• 方式1：sqlalchemy 的automap_base映射

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative.api import DeclarativeMeta
from sqlalchemy import create_engine, MetaData

url = f"""mysql+pymysql://{user}:{password}@{host}:{port}/{db}?charset={charset}"""
engine = create_engine(url, pool_size=5, pool_recycle=1800, pool_pre_ping=True, encoding='utf-8')

def to_dict(self):
    return {c.name: getattr(self, c.name) for c in self.__table__.columns}

class DbSharding(object):
        def __init__(self, map_tables=None):
        """
        映射db模型
        :param map_tables: list 默认无参数, 映射库里的所有表。如果tables有参数, 则只映射指定表
        """
        self.tables = map_tables
        if not map_tables:
            self.auto_base = automap_base()
            self.auto_base.prepare(engine, reflect=True)
        else:
            self.metadata = MetaData()
            self.metadata.reflect(engine, only=map_tables)
            self.auto_base = automap_base(metadata=self.metadata)
            self.auto_base.prepare()

    def get_model(self, table_name):
        """
        根据数据库已存在的表名动态映射模型类
        :param table_name: str
        :return:
        """
        if not table_name:
            return None
        try:
            model: DeclarativeMeta = getattr(self.auto_base.classes, table_name)
            model.to_dict = to_dict
            return model
        except AttributeError:
            logger.error(f"table '{table_name}' not exist")
            raise

if __name__ == '__main__':
    db = DbSharding()
    model = db.get_model("test_table_1")
    # print(model.to_dict())
    res = session.query(model).filter(model.id == 1).one()
    print(res.month)
    res_dict = res.to_dict()
    print(res_dict)
    session.close()

• 方式2：元编程动态生成表名对应的模型类

class TestTable(object):
    _mapper = {}

    @staticmethod
    def model(month):
        table_name = f"test_table_{str(month)}"
        class_name = f"TestTable_{str(month)}"
        ModelClass = TestTable._mapper.get(class_name)
        if ModelClass is None:
            ModelClass = type(class_name,
                              (Base,),
                              dict(__tablename__=table_name,
                                   __module__=__name__,
                                   __name__=class_name,
                                   id=Column(Integer, primary_key=True),
                                   month=Column(Integer)))
        TestTable._mapper[class_name] = ModelClass
        # cls = ModelClass()
        # return cls
        return ModelClass


if __name__ == '__main__':
    TestTableClass= TestTable.model(1)
    res = session.query(TestTableClass).filter(TestTableClass.id == 1)
    print(res)

3.代码里相应查询插入修改，先根据月份获取对应模型类