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)