pymysql的使用

最新推荐文章于 2023-12-27 10:12:30 发布

wht@92

最新推荐文章于 2023-12-27 10:12:30 发布

阅读量878

点赞数

分类专栏：知识图谱文章标签： mysql 数据库 python

本文链接：https://blog.csdn.net/weixin_42376686/article/details/122827551

版权

知识图谱专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1、连接

pymysql的代码连接很简单，下面时连接mysql获取connection，从connection获取cursor进行操作；

#!/usr/bin/env python
# -*- encoding: utf-8 -*-

# here put the import lib
import pymysql
# MYSQL_USER 是用户信息
from base_settings import MYSQL_USER


class BaseMySQL(object):
    """
    MySQL的基础类
    """

    def __init__(self):
        """
        初始化
        :return:
        """
        self.conn = self.connect()
        #
        self.dd = "djofdjf"

    def connect(self):
        """
        连接
        :return:
        """
        try:
            # 连数据库
            conn = pymysql.connect(
                host=MYSQL_USER['host'],
                port=MYSQL_USER['port'],
                db=MYSQL_USER['db'],
                user=MYSQL_USER['user'],
                # password=MYSQL_USER['password']
            )
            return conn
        except Exception as e:
            print("MySQL连接异常：{}".format(e))

2、测试一

#!/usr/bin/env python
# -*- encoding: utf-8 -*-

# here put the import lib
import pymysql
from base_settings import MYSQL_USER
from BaseMySQL import BaseMySQL


class PyMysql(BaseMySQL):

    def __init__(self):
        """初始化"""
        super(PyMysql, self).__init__()
        print(self.conn)

    def check_it(self):
        print(self.dd)
        # 使用cursor()方法创建一个dict格式的游标对象
        cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        # 使用execute() 方法执行SQL查询
        cursor.execute("select count(id) as total from Product")
        # 使用fetchone() 方法获取单条数据
        data = cursor.fetchone()
        print("--- now have {} data".format(data['total']))
        # 关闭数据库连接
        cursor.close()
        self.conn.close()

if __name__ == '__main__':
    py_mysql = PyMysql()
    py_mysql.check_it()

3、使用with进行操作优化

```python
#!/usr/bin/env python
# -*- encoding: utf-8 -*-

# here put the import lib
import pymysql
from timeit import default_timer
from BaseMySQL import BaseMySQL


class UsingPyMsql(BaseMySQL):
    """使用with的方法进行优化"""

    def __init__(self, commit=True, log_time=True, log_label="总用时"):
        """
        初始化
        :param commit: 是否在最后提交事务（设置为False的时候方便单元测试）
        :param log_time: 是否打印程序运行时间
        :param log_label: 自定义log的文字
        """
        super(UsingPyMsql, self).__init__()
        # 打印时间位
        self._log_time = log_time
        # 提交事务位
        self._commit = commit
        # log文字
        self._log_label = log_label

    def __enter__(self):
        """

        :return:
        """
        # 如果需要记录时间
        if self._log_time is True:
            self._start = default_timer()
        # 在进入的时候自动获取连接和cursor
        conn = self.conn
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        """
        退出
        :param exc_info:
        :return:
        """
        # 提交事务
        if self._commit:
            self._conn.commit()
        # 在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print("-- %s: %.6f 秒" % (self._log_label, diff))

    @property
    def cursor(self):
        return self._cursor

def check_it():
    """
    测试
    :return:
    """
    with UsingPyMsql(log_time=True) as um:
        um.cursor.execute("select count(id) as total from Product")
        data = um.cursor.fetchone()
        print("-- 当前数量：%d" % data["total"])


if __name__ == '__main__':
    check_it()

4、增删改查

class UseMysqlFun(object):
    """
    增删改查
    """

    def __init__(self):
        """
        初始化
        """
        pass

    def sleect_one_data(self, cursor):
        """
        查看单条记录
        :param cursor:
        :return:
        """
        sql = "select * from Product"
        cursor.execute(sql)
        data = cursor.fetchone()
        return data

    def select_one(self, cursor):
        """查看单条数据"""
        sql = "select * from Product"
        cursor.execute(sql)
        data = cursor.fetchone()
        print("--- 已找到名字为{0}的商品".format(data['name']))
        return data['name']

    def select_one_by_name(self, cursor, name):
        """
        根据名称查询
        :param cursor:
        :param name:
        :return:
        """
        sql = "select * from Product where name = %s"
        params = name
        cursor.execute(sql, params)
        data = cursor.fetchone()
        print("--- 商品{0}".format(data))
        if data:
            print('--- 已找到名字为{}的商品'.format(data['name']))
        else:
            print('--- 名字为{}的商品已经没有了。'.format(name))

    def fetch_list_by_filter(self, cursor, pk):
        """
        查找记录
        :param cursor:
        :param pk:
        :return:
        """
        sql = "select * from Product where id > %d" %pk
        cursor.execute(sql)
        data_list = cursor.fetchall()
        print('--- 总数：{}'.format(len(data_list)))
        return data_list

    def create_one(self):
        """
        新增单条记录
        :return:
        """
        with UsingPyMsql(log_time=True) as um:
            sql = "insert into Product(name, remark) values(%s, %s)"
            params = ('男士双肩背包1', '这个是非常好的背包')
            um.cursor.execute(sql, params)
            # 查看结果
            self.select_one(um.cursor)

    def update_by_pk(self, cursor, name, pk):
        """
        更新
        :param cursor:
        :param name:
        :param pk:
        :return:
        """
        sql = "update Product set name = '%s' where id = %d" % (name, pk)
        cursor.execute(sql)

    def get_count(self, cursor):
        """查看数据"""
        cursor.execute("select count(id) as total from Product")
        # 使用fetchone() 方法获取单条数据
        data = cursor.fetchone()
        print("-- 当前数量： {0}".format(data['total']))

    def delete_all(self, cursor):
        """
        删除
        :param cursor:
        :return:
        """
        cursor.execute('delete from Product')

    def delete_one(self, cursor, name):
        """
        删除单条记录
        :param cursor:
        :return:
        """
        sql = 'delete from Product where name = %s'
        params = name
        cursor.execute(sql, params)
        print("--- 已删除名字为{0}的商品".format(name))

    def create_many(self):
        """
        生成多条记录
        :return:
        """
        with UsingPyMsql(log_time=True) as um:
            # 清空值前的测试记录
            self.delete_all(um.cursor)
            # 填充
            for i in range(0, 1000):
                sql = "insert into Product(name, remark) values(%s, %s)"
                params = ('男士双肩包{}'.format(i), '这个是非常好的背包{}'.format(i))
                um.cursor.execute(sql, params)

            # 查看结果
            self.get_count(um.cursor)

    def check_delect_one(self):
        with UsingPyMsql(log_time=True) as um:
            # 查找一条记录
            name = self.select_one(um.cursor)
            # 删除
            # self.delete_one(um.cursor, name)
            # 查看是否继续存在
            self.select_one_by_name(um.cursor, name)

    def check_update(self):
        """
        更新
        :return:
        """
        with UsingPyMsql(log_time=True) as um:
            # 查看一条记录
            data = self.sleect_one_data(um.cursor)
            pk = data['id']
            print('--- 商品{0}：'.format(data))

            # 修改名字
            new_name = '单肩包'
            self.update_by_pk(um.cursor, new_name, pk)
            # 查看
            self.select_one_by_name(um.cursor, new_name)

    def fetch_list(self):
        """
        查找
        :return:
        """
        with UsingPyMsql(log_time=True) as um:
            # 查找id大于800的记录
            data_list = self.fetch_list_by_filter(um.cursor, 800)
            # 查找id大于10000的记录
            data_list = self.fetch_list_by_filter(um.cursor, 10000)
    # 分页查询，分页查询主要是用了mysql 的limit 特性, 和pymysql 没太大关系, 代码如下:
    def fetch_page_data(self, cursor, pk, page_size, skip):
        """
        分页查询
        :param cursor:
        :param pk:
        :param page_size:
        :param skip:
        :return:
        """
        sql = "select * from Product where id > %d limit %d, %d" % (pk, skip, page_size)
        cursor.execute(sql)
        data_list = cursor.fetchall()
        print('--- 总数：{}'.format(len(data_list)))
        print('--- 数据：{}'.format(data_list))
        return data_list

    def check_page(self):
        """
        分页查询
        :return:
        """
        with UsingPyMsql(log_time=True) as um:
            page_size = 10
            pk = 500
            for page_no in range(1, 6):
                print("----- 第%d页数据" % page_no)
                skip = (page_no - 1) * page_size
                self.fetch_page_data(um.cursor, pk, page_size, skip)

5、封装

#!/usr/bin/env python
# -*- encoding: utf-8 -*-

# here put the import lib
import pymysql
from timeit import default_timer
from BaseMySQL import BaseMySQL


class UsingPyMsql(BaseMySQL):
    """使用with的方法进行优化"""

    def __init__(self, commit=True, log_time=True, log_label="总用时"):
        """
        初始化
        :param commit: 是否在最后提交事务（设置为False的时候方便单元测试）
        :param log_time: 是否打印程序运行时间
        :param log_label: 自定义log的文字
        """
        super(UsingPyMsql, self).__init__()
        # 打印时间位
        self._log_time = log_time
        # 提交事务位
        self._commit = commit
        # log文字
        self._log_label = log_label

    def __enter__(self):
        """

        :return:
        """
        # 如果需要记录时间
        if self._log_time is True:
            self._start = default_timer()
        # 在进入的时候自动获取连接和cursor
        conn = self.conn
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        """
        退出
        :param exc_info:
        :return:
        """
        # 提交事务
        if self._commit:
            self._conn.commit()
        # 在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print("-- %s: %.6f 秒" % (self._log_label, diff))

    def get_count(self, sql, params=None, count_key="count(id)"):
        """
        返回统计值
        :param sql:
        :param params:
        :param count_key:
        :return:
        """
        self.cursor.execute(sql, params)
        data = self.cursor.fetchone()
        if not data:
            return 0
        return data[count_key]

    def fetch_one(self, sql, params=None):
        """
        查找一个
        :param sql:
        :param params:
        :return:
        """
        self.cursor.execute(sql, params)
        return self.cursor.fetchone()

    def fetch_all(self, sql, params=None):
        """
        查找所有
        :param sql:
        :param params:
        :return:
        """
        self.cursor.execute(sql, params)
        return self.cursor.fetchall()

    def fetch_by_pk(self, sql, pk):
        self.cursor.execute(sql, (pk, ))
        return self.cursor.fetchall()

    def update_by_pk(self, sql, params=None):
        self.cursor.execute(sql, params)

    @property
    def cursor(self):
        return self._cursor

6、使用连接池

在上面的使用过程中, 每个请求都会开启一个数据库连接. 如果连接数太多, 数据库很快就会报错. 如何调整数据库的连接数增加并发性能算是个比较有技术含量的话题, 我打算放到高级篇里再介绍. 现在这里要让你知道的是: 数据库这么返回连接是不行的, 必须要使用连接池.
连接池代码当然不用自己动手, python的世界那么大~ 先安装DBUtils, 如下:

#!/usr/bin/env python
# -*- encoding: utf-8 -*-

# here put the import lib
import pymysql
from timeit import default_timer
# 2.0以上的调用方法变更了
# from DBUtils.PooledDB import PooledDB
from dbutils.pooled_db import PooledDB


class DMysqlConfig(object):
    """
    :param mincached:连接池中空闲连接的初始数量
    :param maxcached:连接池中空闲连接的最大数量
    :param maxshared:共享连接的最大数量
    :param maxconnections:创建连接池的最大数量
    :param blocking:超过最大连接数量时候的表现，为True等待连接数量下降，为false直接报错处理
    :param maxusage:单个连接的最大重复使用次数
    :param setsession:optional list of SQL commands that may serve to prepare
        the session, e.g. ["set datestyle to ...", "set time zone ..."]
    :param reset:how connections should be reset when returned to the pool
        (False or None to rollback transcations started with begin(),
        True to always issue a rollback for safety's sake)
    :param host:数据库ip地址
    :param port:数据库端口
    :param db:库名
    :param user:用户名
    :param passwd:密码
    :param charset:字符编码
    """

    def __init__(self, host, db, user, password, port=3306):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.password = password

        self.charset = 'UTF8'
        self.minCached = 10
        self.maxCached = 20
        self.maxShared = 10
        self.maxConnection = 100

        self.blocking = True
        self.maxUsage = 100
        self.setSession = None
        self.reset = True


class DMysqlPoolConn():
    """
    用连接池来返回数据库连接
    """
    __pool = None

    def __init__(self, config):
        if not self.__pool:
            self.__class__.__pool = PooledDB(
                creator=pymysql,
                maxconnections=config.maxConnection,
                mincached=config.minCached,
                maxcached=config.maxCached,
                maxshared=config.maxShared,
                blocking=config.blocking,
                maxusage=config.maxUsage,
                setsession=config.setSession,
                charset=config.charset,
                host=config.host,
                port=config.port,
                database=config.db,
                user=config.user,
                password=config.password,
            )

    def get_conn(self):
        return self.__pool.connection()

7、在程序的开始初始化一个连接池

host = 'localhost'
port = 3306
db = 'mysql_test'
user = 'mysql_test'
password = 'mysql_test'

db_config = DMysqlConfig(host, db, user, password, port)

g_pool_connection = DMysqlPoolConn(db_config)


class UsingPyMsql(object):
    """使用with的方法进行优化"""

    def __init__(self, commit=True, log_time=True, log_label="总用时"):
        """
        初始化
        :param commit: 是否在最后提交事务（设置为False的时候方便单元测试）
        :param log_time: 是否打印程序运行时间
        :param log_label: 自定义log的文字
        """
        # 打印时间位
        self._log_time = log_time
        # 提交事务位
        self._commit = commit
        # log文字
        self._log_label = log_label

    def __enter__(self):
        """

        :return:
        """
        # 如果需要记录时间
        if self._log_time is True:
            self._start = default_timer()
        # 从连接池获取数据库连接
        # 在这里进行了修改
        conn = g_pool_connection.get_conn()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        conn.autocommit = False

        self._conn = conn
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        """
        退出
        :param exc_info:
        :return:
        """
        # 提交事务
        if self._commit:
            self._conn.commit()
        # 在退出的时候自动关闭连接和cursor
        self._cursor.close()
        self._conn.close()

        if self._log_time is True:
            diff = default_timer() - self._start
            print("-- %s: %.6f 秒" % (self._log_label, diff))

    def get_count(self, sql, params=None, count_key="count(id)"):
        """
        返回统计值
        :param sql:
        :param params:
        :param count_key:
        :return:
        """
        self.cursor.execute(sql, params)
        data = self.cursor.fetchone()
        if not data:
            return 0
        return data[count_key]

    def fetch_one(self, sql, params=None):
        """
        查找一个
        :param sql:
        :param params:
        :return:
        """
        self.cursor.execute(sql, params)
        return self.cursor.fetchone()

    def fetch_all(self, sql, params=None):
        """
        查找所有
        :param sql:
        :param params:
        :return:
        """
        self.cursor.execute(sql, params)
        return self.cursor.fetchall()

    def fetch_by_pk(self, sql, pk):
        self.cursor.execute(sql, (pk, ))
        return self.cursor.fetchall()

    def update_by_pk(self, sql, params=None):
        self.cursor.execute(sql, params)

    @property
    def cursor(self):
        return self._cursor