【SQLAlchemy】python最常用ORM （三）

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()