python3-MySQL增删改查、事务

python3操作MySQL数据库需要用到pymysql库

如果没有安装pymysql库，在命令行下执行如下命令安装

pip3 install PyMySQL

不支持pip命令参考菜鸟教程的安装方法

https://www.runoob.com/python3/python3-mysql.html

先发一下实验用的表

CREATE TABLE `chat_vcode` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `phone` varchar(255) DEFAULT NULL,
  `vcode` varchar(255) DEFAULT NULL,
  `code_time` bigint(13) DEFAULT NULL,
  `inuse` int(2) unsigned NOT NULL COMMENT '0无效 1有效',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;

安装后pymysql库后，先引用

import pymysql

我们通过自定义构造的一个类来实现数据库增删改查

class StudyPyMySql:

    __db_server = ''
    __db_user = ''
    __db_password = ''
    __db_name = ''
    __test_table = 'chat_vcode'

    # construct
    def __init__(self, spm_server, spm_user, spm_password, spm_name):
        self.__db_server = spm_server
        self.__db_user = spm_user
        self.__db_password = spm_password
        self.__db_name = spm_name
        # connect db
        self.__db = pymysql.connect(self.__db_server, self.__db_user, self.__db_password, self.__db_name)

    # 单条数据插入测试
    def test_insert(self):
        spm_cursor = self.__db.cursor()
        sql = """INSERT INTO `%s` \
                (`phone`, `vcode`, `code_time`, `inuse`) \
                VALUES ('%s', '%s', '%s', '%s')""" % (self.__test_table, 'testphone', 'testvcode', 0, 1)
        try:
            spm_cursor.execute(sql)
            self.__db.commit()
            print('test_insert成功')
        except Exception as e:
            # 如果发生错误则回滚
            self.__db.rollback()
            print('test_insert失败: %s' % e)

    # 多条数据插入测试
    def test_insert_many(self):
        spm_cursor = self.__db.cursor()
        sql = """INSERT INTO `chat_vcode` (`phone`, `vcode`, `code_time`, `inuse`) VALUES (%s, %s, %s, %s)"""
        t = (("testphone1", "testvcode1", 0, 1),
             ("testphone2", "testvcode2", 0, 1),
             ("testphone3", "testvcode3", 0, 1))
        try:
            spm_cursor.executemany(sql, t)
            self.__db.commit()
            print('test_insert_many成功')
        except Exception as e:
            # 如果发生错误则回滚
            self.__db.rollback()
            print('test_insert_many失败: %s' % e)

    # 数据更新操作
    def test_update(self):
        spm_cursor = self.__db.cursor()
        sql = """UPDATE `%s` SET `%s`='%s' WHERE `%s`='%s'""" % \
              (self.__test_table, 'phone', 'testphone11', 'phone', 'testphone1')
        try:
            spm_cursor.execute(sql)
            if spm_cursor.rowcount < 1:
                print('test_update失败: 没有可更新的内容')
            else:
                self.__db.commit()
                print('test_update成功')
        except Exception as e:
            self.__db.rollback()
            print('test_update失败: %s' % e)

    # 数据查询操作
    def test_select(self):
        spm_cursor = self.__db.cursor()
        sql = """SELECT * FROM `%s` WHERE `id`>0""" % self.__test_table
        try:
            spm_cursor.execute(sql)
            spm_res = spm_cursor.fetchall()
            for row in spm_res:
                list_id = row[0]
                list_phone = row[1]
                list_verify_code = row[2]
                list_create_time = row[3]
                list_inuse = row[4]
                # 打印结果
                print("id=%s,phone=%s,verify_code=%s,create_time=%s,inuse=%s" % \
                      (list_id, list_phone, list_verify_code, list_create_time, list_inuse))
        except Exception as e:
            print('test_select 失败: %s' % e)

    # 数据库删除操作
    def test_delete(self):
        spm_cursor = self.__db.cursor()
        sql = """DELETE FROM `%s` WHERE `phone`='testphone2'""" % self.__test_table
        try:
            spm_cursor.execute(sql)
            if spm_cursor.rowcount < 1:
                print('delete_test失败: 没有可删除的内容')
            else:
                self.__db.commit()
                print('test_delete成功')
        except Exception as e:
            self.__db.rollback()
            print('delete_test失败: %s' % e)

在执行数据批量插入时，注意sql模板语句，待传参数只能是字段名对应的数值，不能将表名或其他（如字段名）也作为待传参数:
```
sql = """INSERT INTO `chat_vcode` (`phone`, `vcode`, `code_time`, `inuse`) VALUES (%s, %s, %s, %s)"""
```
t = (("testphone1", "testvcode1", 0, 1),
("testphone2", "testvcode2", 0, 1),
("testphone3", "testvcode3", 0, 1))
上面这种写法是正确的，看下面的写法:
```
sql = """INSERT INTO `%s` (`phone`, `vcode`, `code_time`, `inuse`) VALUES (%s, %s, %s, %s)"""
```
t = (("chat_vcode", "testphone1", "testvcode1", 0, 1),
("chat_vcode", "testphone2", "testvcode2", 0, 1),
("chat_vcode", "testphone3", "testvcode3", 0, 1))
这里我们将表名也作为了待传参数，这样执行会直接报错，无法提交事务。
代码优化，在方法执行完毕后调用 self.__db.close() 或者在析构函数中执行此条语句。

调试

if __name__ == "__main__":
    spm = StudyPyMySql(
        '127.0.0.1',
        'root',
        'root',
        'studypy'
    )
    # spm.test_select()
    # spm.test_insert()
    # spm.test_insert_many()
    # spm.test_update()
    # spm.test_delete()

代码已经过测试，全部运行正常，python --version = 3.7.4rcl