def query_all(cls): """ 从数据库中查询所有数据 :param cls: 数据表对应的类 :return: 符合条件的对象列表 """ query_statement = db_session.query(cls) sql_logger.debug(query_statement) return query_statement.all()
def query_by_id(cls, id): """ 从数据库中查询id为指定id的数据 :param cls: 数据表对应的类 :param id: 数据的id :return: 符合条件的对象 """ query_statement = db_session.query(cls).filter(cls.id == id) sql_logger.debug(query_statement) return query_statement.first()
def query_all_left_join(cls_left, cls_right, key_left=None, key_right=None): """ 左外连接两张表,获取两张表连接后的集合 :param cls_left: 左表对应的类 :param cls_right: 右表对应的类 :param key_left: 左表连接的列名,为空时默认连接id :param key_right: 右表连接的列名,为空时默认连接id :return: 两个类的对象元组列表,例[(obj_left1, obj_right1), (obj_left2, obj_right2), (...), ...] """ if key_left and key_right: query_statement = db_session.query(cls_left, cls_right)\ .outerjoin(cls_right, getattr(cls_left, key_left) == getattr(cls_right, key_right))\ .order_by(cls_left.id) sql_logger.debug(query_statement) return query_statement.all() else: query_statement = db_session.query(cls_left, cls_right)\ .outerjoin(cls_right)\ .order_by(cls_left.id) sql_logger.debug(query_statement) return query_statement.all()
def query_by_condition(cls, **condition): """ 从数据库中查询条件为指定条件的数据 :param cls: 数据表对应的类 :param condition: 查询条件字典 :return: 符合条件的对象列表 """ query_statement = db_session.query(cls) for key, value in condition.items(): query_statement = query_statement.filter(getattr(cls, key) == value) sql_logger.debug(query_statement) return query_statement.all()
def query_all_self_join(cls1, cls2, key1=None, key2=None): """ 自连接一张表,获取该表自连接后的集合 :param cls1: 该表对应的类 :param cls2: 该表对应的类 :param key1: 表1连接的列名,为空时默认连接id :param key2: 表2连接的列名,为空时默认连接id :return: 两个类的对象元组列表,例[(obj1_1, obj2_1), (obj1_2, obj2_2), (...), ...] """ cls1_alias = aliased(cls1) cls2_alias = aliased(cls2) if key1 and key2: query_statement = db_session.query(cls1_alias, cls2_alias)\ .outerjoin(cls2_alias, getattr(cls1_alias, key1) == getattr(cls2_alias, key2))\ .order_by(cls1_alias.id) sql_logger.debug(query_statement) return query_statement.all() else: query_statement = db_session.query(cls1_alias, cls2_alias)\ .outerjoin(cls2_alias)\ .order_by(cls1_alias.id) sql_logger.debug(query_statement) return query_statement.all()