pymysql模块封装

1.pymysql模块安装

pip install pymysql 

2.pymysql模块封装代码

"""
    此模块封装了python访问mysql一些方法
    使用:
        推荐通过with xxx [as 别名] 
        语句来管理上下文,避免最后忘记关掉游标和连接！！！

"""
import pymysql


class PyMyDB:
    def __init__(self, user, password, host, port, database):
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.database = database
        self.conn = None
        self.cursor = None

    def connection(self):
        """
        sql server 数据库连接
        :return:
        """
        self.conn = pymysql.connect(user=self.user,
                                    password=self.password,
                                    host=self.host,
                                    port=self.port,
                                    database=self.database,
                                    )
        self.cursor = self.conn.cursor()

    def close(self):
        """
        关闭连接
        :return:
        """
        self.cursor.close()
        self.conn.close()

    def __enter__(self):
        """
        上下文管理魔术方法
        :return:
        """
        self.connection()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        上下文管理魔术方法
        :param exc_type:
        :param exc_val:
        :param exc_tb:
        :return:
        """
        self.close()

    def select_one(self, sql):
        """
        查询方法
        生成器模式，防止内存撑爆
        :return:
        """
        self.cursor.execute(sql)
        while True:
            data = self.cursor.fetchone()
            if data:
                yield data
            else:
                break

    def select_all(self, sql, params=None):
        """
        查询方法
        :return:
        """
        self.cursor.execute(sql, params)
        data = self.cursor.fetchall()
        return data

    def execute_sql(self, sql, params=None):
        """
        执行语句
        :param sql:
        :return:
        """
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
        except Exception as e:
            self.conn.rollback()
            raise TypeError(str(e))

    def sql_delete(self, sql, params=None):
        """
        删除方法
        :return:
        """
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
            row_count = self.cursor.rowcount
        except Exception as e:
            self.conn.rollback()
            raise
        return row_count

    def insert(self, sql, params=None):
        """
        插入方法
        :return:
        """
        try:
            self.cursor.execute(sql, params)
            self.conn.commit()
            row_count = self.cursor.rowcount
        except Exception as e:
            self.conn.rollback()
            raise

        return row_count

    def update(self, sql):
        """
        插入方法
        :return:
        """
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            row_count = self.cursor.rowcount
        except Exception as e:
            self.conn.rollback()
            raise

        return row_count

    def insertmany(self, sql, params):
        """
        插入方法
        :return:
        """
        try:
            self.cursor.executemany(sql, params)
            self.conn.commit()
        except Exception as e:
            self.cursor = self.conn.cursor()
            self.cursor.executemany(sql, params)
            self.conn.commit()


if __name__ == '__main__':
    mysql = PyMyDB('user', 'password', 'host', 'port', 'database')
    with mysql:
        mysql.select_one('your sql here')    # 查一条记录
        mysql.select_all('your sql here')    # 查记录
        mysql.update('your sql here')        # 更新记录
        mysql.delete('your sql here')        # 删除记录
        mysql.insert('your sql here')        # 插入记录
        mysql.insertmany('your sql here')    # 一次插入多条记录