复制代码, 保存为.py文件, 在需要使用的模块调用即可(需要手动修改所需连接数据库信息)
from sqlalchemy import func
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
class DbOperation:
def __init__(self, db_name):
# 创建数据库引擎
self.engine = create_engine('mysql+pymysql://用户名:密码@IP:3306/{}?charset=utf8'.format(db_name),
pool_size=100, max_overflow=100)
def get_session(self):
"""
获取数据库session
:return:
"""
db_session = sessionmaker(bind=self.engine)
return db_session()
@staticmethod
def close_session(session):
"""
提交数据, 关闭session
:param session:
:return:
"""
session.commit()
session.close()
def add(self, modal):
"""
添加一条数据到对应数据表
:param modal: 模型对象
:return:
"""
session = self.get_session()
result = session.add(modal)
self.close_session(session)
return result
def add_all(self, modal_list):
"""
批量添加数据到数据表
:param modal_list: 模型对象列表
:return:
"""
session = self.get_session()
result = session.add_all(modal_list)
self.close_session(session)
return result
def delete_by_filter(self, model, filter_):
"""
通过条件删除
:param model: 模型
:param filter_: 条件
:return:
"""
session = self.get_session()
result = session.query(model).filter(filter_).delete()
self.close_session(session)
return result
def query_all(self, model):
"""
查询对应模型的全部数据
:param model: 模型对象
:return:
"""
session = self.get_session()
result = session.query(model).all()
session.close()
return result
def query_list_by_filter(self, model, filter_, sort=None):
"""
通过条件查询, 可选排序
:param model: 模型对象
:param filter_: 过滤条件
:param sort: 可选 - 排序
:return:
"""
session = self.get_session()
if sort is None:
result = session.query(model).filter(filter_).all()
else:
result = session.query(model).filter(filter_).order_by(sort).all()
session.close()
return result
def exec_sql(self, sql):
"""
执行sql语句
:param sql: 语句
:return:
"""
session = self.get_session()
result = session.execute(sql)
self.close_session(session)
return result
def get_count(self, modal):
"""
获取表数据条数
:param modal: ex-> User.id
:return:
"""
session = self.get_session()
count = session.query(func.count(modal)).scalar()
session.close()
return count