http://www.kardel.xyz/blog/sqlalchemypython%E6%9C%80%E5%B8%B8%E7%94%A8orm-%E4%B8%89/
基于之前两篇文章,我们可以实现使用SQLAlchemy对单表的简单增删改查操作,本文将整合之前的逻辑,实现一个简单的工具类,封装出增删改查的接口,方便使用。
对于复杂查询,级联查询的适配,则需在query中实现更为详细的逻辑,此处暂不考虑,如有兴趣,请参考下面两篇文章自己封装下。
https://www.jianshu.com/p/9771b0a3e589
https://www.cnblogs.com/Xjng/p/4902498.html
言归正传,我们构造一个BaseSqlHelper类,首先实现init函数
init
def __init__(self, engine, auto_commit=True, logger=None):
try:
self.auto_commit = auto_commit
if engine.startswith('sqlite'):
connect_args = {'check_same_thread': False}
if logger:
self.engine = create_engine(engine, echo=True, connect_args=connect_args)
else:
self.engine = create_engine(engine, echo=False, connect_args=connect_args)
else:
if logger:
self.engine = create_engine(engine, echo=True)
else:
self.engine = create_engine(engine, echo=False)
self.engine.logger = logger
self.session = sessionmaker(bind=self.engine)()
except Exception as err:
raise Exception(str(err))
我们预留了三个参数:
engine为连接使用的字符串
auto_commit针对add操作是否需手动执行session.commit()
logger为第一篇提到的,将sqlalchemy的echo信息写入自身项目日志
通过构造方法,我们拿到关键属性 self.session
接下来,我们先来实现create和drop操作,十分简单
create/drop
def create(self, model):
model.metadata.create_all(self.engine)
def drop(self, model):
model.metadata.drop_all(self.engine)
insert
def insert(self, model):
self.session.add(model)
if self.auto_commit:
self.commit()
select
这是最为复杂的地方,此处我们只简单处理下常用的查询逻辑
def select(self, model, param=dict()):
"""
:param model:
:param param:
columns : eg: ['id', 'name'] 若为空,则查对象,即返回一个model的list
filters : eg: ['id>8 and name like "xxx"', 'id < 10']
order_by/order_by_descs: eg: ['id', 'name']
limit: eg: dict(start=1, end=3)
get_count: eg: 1
:return: query 生成器 或者 count数
"""
columns = param.get('columns')
if columns:
query = self.session.query(map(lambda x: model.__dict__.get(x), columns))
else:
query = self.session.query(model)
filters = param.get('filters')
if filters:
for filter_item in filters:
query = query.filter(text(filter_item))
order_bys = param.get('order_by')
if order_bys:
query = query.order_by(set(map(lambda x: model.__dict__.get(x), order_bys)))
order_by_descs = param.get('order_by_descs')
if order_by_descs:
query = query.order_by(set(map(lambda x: model.__dict__.get(x).desc(), order_by_descs)))
limit_num = param.get('limit')
if limit_num:
query = query.limit(limit_num)
count = param.get('get_count')
if count:
return query.count()
else:
return query
delete
def delete(self, model, param):
query = self.select(model, param)
query.delete()
if self.auto_commit:
self.commit()
update
def update(self, model, param):
"""
:param model:
:param param:
update
:return:
"""
if param.get('update'):
query = self.select(model, param)
query.update(param.get('update'), synchronize_session='fetch')
if self.auto_commit:
self.commit()
至此,关于python ORM的文章我们先告一段落,之前在使用SQLAlchemy时,发现缺少对于键冲突的兼容处理,于是自己实现了insert_dup函数,主要是通过对类属性的解析,拿到primarykey或者unique的字段信息,通过select查询后,再做处理。此处就不贴了,后续抽空整理下,补充于文末:
def insert_dup(self, models, action='update'):
"""
:param models: model对象列表
:param action: model对象列表
:return: 该函数不自动提交,当我们修改model的值,无需重复insert
"""
for model in models:
model_cls = model.__class__
filter_query = ''
update_dict = dict()
for column in model_cls.__dict__.get('__table__').columns:
if column.primary_key or column.unique:
filter_query = filter_query + "{column} like '{model_column}'".format(
column=str(column).split('.')[-1],
model_column=model.__dict__.get(str(column).split('.')[-1])
) + ' or '
update_dict[str(column).split('.')[-1]] = model.__dict__.get(str(column).split('.')[-1])
filter_query = filter_query.strip(' or ')
if filter_query:
query = self.select(model_cls, dict(filters=[filter_query]))
if query.first():
if action == 'update':
query.update(update_dict, synchronize_session='fetch')
elif action == 'ignore':
return
else:
self.session.add(model)
if self.auto_commit:
self.commit()