pymysql 操作MySQL数据库

pymysql 是一个用于在 Python 中操作 MySQL 数据库的库，它提供了与 MySQL 数据库进行连接、查询、插入、更新等操作的功能。

一、安装PyMySQL

有2种方式途径：

1.pip命令安装：pip install PyMySQL；

2.通过pycharm选择文件->设置->解释器搜索PyMySQL并安装。

二、使用PyMySQL

1.连接数据库

和数据库建立一个传输数据的连接通道，需要用到pymysql下的connect()方法，pymysql.connect() 方法返回的是Connections模块下的Connection 实例，connect() 方法传参就是在给Connection类的 init 初始化魔术方法参数，也可以理解为 connect() 方法就是在创建新的 Connetion 类。

 2.操作数据库

在使用pymysql.connect() 方法与数据库建立连接后，想要操作数据库时，就需要使用游标 Cursor，通过连接好的数据库（此处为db）调用 cursor() 方法即可返回一个新的游标对象，在连接没有关闭之前，游标对象可以反复使用。

cursor = db.cursor()

数据库操作需要使用Cursor类的实例（即通过 db.cursor() 创建的 cursor 游标对象）提供的 execute() 方法，执行SQL语句，成功则返回结果。

2.1 查询操作

sql = "select * from table"

response1 = cursor.execute(sql)

response2 = cursor.fetchall

这里有两个response返回，

response1是对cursor.execute(sql) 的返回数据接收，这个返回可能并不是你想要的返回值，因为它返回的是查询到的个数，是个int类型的数字

response2是调用了fetchall方法：查询时获取结果集中的所有行，一行构成一个元组，然后再将这些元组返回（即嵌套元组）.

当调用 cursor.execute(sql) 方法查询数据的时候，如果执行成功，获取查询的结果集的方法如下：

2.2插入操作

sql = "insert into user_pwd(username,password) values('vera', '1234')"
cursor.execute(sql)

 这里插入数据的时候插入的表名有个需要注意的地方，插入表名的时候最好带着库名，例如：sql = "insert into database.table(username, password) values('xxx', '123')"，否则可能会出现如下错误：

 ps：连接查询的数据为历史数据（获取实时数据的方法），如果对数据即时性要求很高，或者说数据随时可能发生变化，那么close的使用将会很重要。close是将当前连接关闭。注意，这里和sqlalchemy的close不太相同，cur.close()是关闭游标，但是仅仅关闭游标是不够的，创建新的游标查询的依旧是历史数据，只有将当前db.connect数据库连接关闭，即db.close()后在重新创建连接才有效，才可以得到最新的数据；研究了一下，其实和mysql的事务有关，所以另外一种方法就是：db.commit()提交事务，即时只是查询，也将当前事务提交，开启新的事务，连接就会重新获取当前最新的内容。否则查询到的数据依旧是历史数据，而不是最新的数据。 -- 在sqlalchemy中，是通过创建引擎以及连接池的方式实现的，一个连接池中可以有多个连接，调用session.close()则是将当前连接关闭，和这里的db.close()一样，但是sqlalchemy有连接池的存在，关闭后若想继续使用连接，连接池会自动创建好连接，直接使用session即可，不需要重新创建连接，但pymysql不行，pymysql是单独创建一个连接，没有连接池的概念。

if cursor:
    cursor.close()
db.close()

2.3sql语句中有变量如何传值

变量使用%s代替，一定要注意sql语句中为非数值类型，%s也需要加单引号！！！

方法1：sql中有1个变量时，可以使用占位符%s替代变量

sql = "insert into xxx values(test) ('%s')" %(test)


cursor.execute(sql)
db.commit
db.close()

方法2：sql中有多个变量时，使用对个占位符替代变量，需要注意的是在传入的多个变量值列表时顺序是从左到右依次替代

sql = "update xxx(表名) set  sort1=%s, sort2=%s, sort3=%s where sort4=%s" 


cursor.execute(sql,('a','b','c','d'))
db.commit
db.close()

ps：1.方法1中的字符串替换的方式容易让攻击者有机可乘，通过sql注入攻击获取数据库数据，建议使用方法2的方式在sql中引入变量；

2.使用方法1时，查询语句中占位值如是非数字时需要用引号，方法2中无论占位值是否为数字均不需要引号。

2.4format函数格式化字符串

如果直接使用sql = 'select * from t_carowner_recharge where order_num = ' + order_num拼接后的sql中order_num没有带引号会导致查询报错

通过str.format方法格式化字符串

sql = 'select * from t_carowner_recharge where order_num = ' + "'{}'".format(order_num)

也可以写成

sql = "select * from t_carowner_recharge where order_num = '{}'".format(order_num)

三、数据库操作方法封装及调用

实际使用过程中建议将数据库的增删改查等方法封装好，在使用时直接调用即可，便于后期脚本维护，相关封装代码如下：

import pymysql


# 封装数据库工具类
class SqlUtli(object):
    # 添加类默认值
    conn = None

    # 连接数据库
    @classmethod
    def connect_mysql(cls):
        # 判断conn是否为空，如果是在创建连接
        if cls.conn is None:
            # 根据实际连接信息配置
            cls.conn = pymysql.connect(host="xxx", 
                                       port=1234, 
                                       user="xxx", 
                                       password="xxx!",
                                       database="xxx", 
                                       charset="utf8")
        return cls.conn

    # 关闭数据库连接
    @classmethod
    def close_conn(cls):
        # 判断conn是否为空，不为空需要关闭
        if cls.conn is not None:
            cls.conn.close()
            cls.conn = None

    # 查询操作
    @classmethod
    def select(cls, sql):
        cursor = None
        res = None
        try:
            # 调用创建连接方法链接数据库
            cls.conn = cls.connect_mysql()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行sql语句
            cursor.execute(sql)
            # 获取返回结果集，fetchall()（获取所有的数据），fetchmany(size)（size指定获取多少条数据），fetchone()（获取第一条数据）
            res = cursor.fetchall()
        except Exception as err:
            print('查询sql出错啦！', print(str(err)))
        finally:
            # 关闭游标
            cursor.close()
            # 调用关闭连接方法，关闭数据连接
            cls.close_conn()
            # 将sql查询结果集返回到res
            return res

    # 新增、更新、删除操作，方法共享，不同的操作通过传入的sql区分
    @classmethod
    def insert_update_delete(cls, sql):
        cursor = None
        try:
            # 调用创建连接方法链接数据库
            cls.conn = cls.connect_mysql()
            # 获取游标
            cursor = cls.conn.cursor()
            # 执行更新语句
            cursor.execute(sql)
            # 获取影响行数
            print("影响行数", cls.conn.affected_rows())
            # 提交事务
            cls.conn.commit()
        except Exception as err:
            # 回滚事务
            cls.conn.rollback()
            print("增删改 sql执行失败： " + str(err))
        finally:
            # 关闭游标
            cursor.close()
            # 调用关闭连接方法，关闭数据连接
            cls.close_conn()

上述代码中无论查询还是更新等操作均需传入具体查询sql，涉及到变量查询时可用上面介绍的拼接sql方法进行组合。