Python常用代码: 使用sqlalchemy库操作mysql数据库

复制代码, 保存为.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