Python MySQL 数据库

Python 操作mysql

1.mysql 数据库连接

• 上下文管理

import pymysql


class DBConnect:
    HOST = "127.0.0.1"
    PORT = 3306
    USER = "root"
    PASSWD = "qwer1234"
    DB_NAME = "day29"
    CHARTSET = "utf8"

    def __init__(self):
        """
            初始化连接 mysql
        """
        self.conn = coon = pymysql.connect(
                                host=self.HOST,
                                port=self.PORT,
                                user=self.USER,
                                passwd=self.PASSWD,
                                db=self.DB_NAME,
                                charset=self.CHARTSET
                            )
        self.__cursor = coon.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    @property
    def cursor(self):
        """
        1. property 以属性的方式将 cursor 返回， obj.cursor => obj.cursor()
        2. 获取cursor属性
        """
        return self.__cursor

    # 执行sql语句
    def execute(self, sql, **kwargs):
        """
        防止sql注入的形式，执行sql语句
        :param sql: sql语句: insert into table_name (id, name) value (%(id)s, %(name)s)
        :param kwargs: **{id:1, name: "hxc"}
        """
        self.cursor.execute(sql, kwargs)
        self.conn.commit()

    # 查询一条数据
    def fetchone(self, sql, **kwargs):
        """
        查询一条数据
        :param sql: select * from table_name where id=%(id)s, name=%(name)s;
        :param kwargs: **{"id": 1, "name": "hxc"}
        """
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    # 查询多条数据
    def fetchall(self, sql, **kwargs):
        """
        查询多条数据
        """
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

    # 以事务的方式执行sql语句
    def exec_begin(self, sql_list: list):
        """
        :param sql_list: [{"sql": "insert into table_name (id, name)", "kwargs": {"id": 1, "name": "hxc"} }]
        """
        # 开启事务
        self.conn.begin()
        try:
            for sql_dict in sql_list:
                self.execute(sql_dict['sql'], **sql_dict['kwargs'])
            print("执行成功")
        except Exception:
            print("出现异常 进行回滚")
            self.conn.rollback()
        finally:
            self.conn.commit()

    def __exit__(self, exc_type, exc_val, exc_tb):
        """
        退出是关闭流
        """
        self.conn.close()
        self.cursor.close()

with DBConnect() as db:
  pass

2.mysql 数据库池连接

• 单例模式

"""
	1. 对象初始化 创建连接池对象
	2. 创建获取conn、cursor对象的方法
	3. 创建关闭conn、cursor对象的方法
	4. 创建执行sql语句的方法
	5. 创建 获取单条 查寻语句执行结果的方法
	6. 创建 获取多条 查血语句执行结果的方法
"""
import pymysql
from dbutils.pooled_db import PooledDB


class DBPool:

    def __init__(self):
        self.pool = PooledDB(
            creator=pymysql,  # 使用数据库连接模块
            maxconnections=5,  # 最大连接数
            mincached=2,  # 初始化时，连接池中至少创建的空闲的连接， 0 或 None 表示不创建
            maxcached=5,  # 连接池最多闲置的连接
            blocking=True,  # 连接池中如果没有可用连接 是否阻塞等待 True 等待； False 不等待报错
            setsession=[],  # 开始会话前执行的命令列表
            ping=0,  # 检查是否服务可用
            host="172.27.135.11",
            port=3306,
            user='root',
            password='Troila12#$',
            database='homework_4',
            charset='utf8'
        )

    def get_coon_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_coon_cursor()
        cursor.execute(sql, **kwargs)
        conn.commit()
        self.close_conn_cursor(conn, cursor)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_coon_cursor()
        result = cursor.fetchone(sql, kwargs)
        self.close_conn_cursor(conn, cursor)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_coon_cursor()
        result = cursor.fetchall(sql, **kwargs)
        self.close_conn_cursor(conn, cursor)
        return result


db = DBPool()