介绍

介绍

今天开始我们来学习Python操作MySQL数据库的技巧，Python操作MySQL是借助pymysql这个库来实现的。pymysql库区别于PDO和JdbcTemplate，它只能用来操作MySQL，不能用来操作其他数据库，这一点和早期的PHP的php_mysqli差不多。

相信在不久的将来Python也会给出多数据库的集中解决方案出来吧！不过，今天我们还是先把pymysql的使用技巧学会吧~~

更新历史

• 增加了关于字典游标（DictCusor）的知识和使用技巧（21/04/24）

• 增加了关于事务机制和提交方式的知识和使用技巧（21/04/24）
  

安装

因为pymysql是Python的一个库包，我们可以通过pip直接安装它。

pip install pymysql

导入

在需要调用pymysql的地方导入这个库，就可以使用了。因为福哥后面的例子用到了MD5的哈希算法，所以福哥还导入了md5这个库，这个可以根据自己的情况选择。

import time
import pymysql
from hashlib import md5

连接

连接MySQL需要知道服务器的地址、用户名、密码。当pymysql连接服务器出现错误的时候会抛出异常，福哥在这里通过try ... catch捕获了一下这个异常。

try:
    cn = pymysql.connect(host="192.168.2.168", user="root", password="abcdef")
    cursor = cn.cursor()
except Exception as e:
    print(e)
    exit()

数据库列表

pymysql没有单独的方法用来获取数据库列表，福哥是通过执行“show databases”语句获取数据库列表信息的。这是一个最简单的查询语句的示例，大家看好了。

try:
    cursor.execute("show databases")
    databases = cursor.fetchall()
    print("数据库列表")
    print("")
    for database in databases:
        print(database[0])
    print("")
except Exception as e:
    print(e)

数据表列表

同样的pymysql也没有用来获取数据表列表的方法，福哥是通过执行“show tables”语句获取当前数据库的数据表列表的。

当前数据库和连接编码倒是可以通过创建数据库连接的时候指定，这也就是专用库的好处了！

连接

在创建数据库连接的时候设置数据库和连接编码。

try:
    cn = pymysql.connect(host="192.168.2.168", user="root", password="abcdef", db="tfums", charset="utf8")
    cursor = cn.cursor()
except Exception as e:
    print(e)
    exit()

查询

通过“show tables”获取当前数据库的数据表列表。

try:
    cursor.execute("show tables")
    tables = cursor.fetchall()
    print("数据表列表")
    print("")
    for table in tables:
        print(table[0])
    print("")
except Exception as e:
    print(e)

插入数据

插入数据

插入数据也是通过prepare组织一个SQL语句，然后通过execute方法执行SQL语句完成的。不过，通过向prepare传递参数可以达到动态组织SQL语句的目的，最重要的是：这种方式还可以防止SQL注入。

插入数据是否成功就看有没有异常抛出就可以了，没插入成功一定会有原因的嘛~~

try:
    sql = "insert into user (passwd, createDT, userFlag, userState, userName)" \
          " values ('%s', now(), %d, %d, '%s')"
    params = (md5("123456".encode("utf-8")).hexdigest(), 7, 1, "福哥教pymysql")
    print("插入数据")
    print("")
    cursor.execute(sql % params)
    cn.commit()
except Exception as e:
    print(e)
    cn.rollback()

第一次执行的时候不会报错，多次执行就会有下面的错误信息了！

获取自增列数值

刚刚福哥向用户表user插入了一条数据，用户表user的主键是自增列，要获取新数据的自增列的数值可以通过游标的属性lastrowid获得。

try:
    sql = "insert into user (passwd, createDT, userFlag, userState, userName)" \
          " values ('%s', now(), %d, %d, '%s')"
    params = (md5("123456".encode("utf-8")).hexdigest(), 7, 1, "福哥教pymysql")
    print("插入数据")
    print("")
    cursor.execute(sql % params)
    cn.commit()

    print("获取自增列数值")
    print("")
    print(cursor.lastrowid)
except Exception as e:
    print(e)
    cn.rollback()

更新数据

更新数据就是执行UPDATE语句了，更新语句的条件自然就是通过刚刚获取到的自增列的数值了。

更新是否成功也是看是否有异常抛出，如果没有抛出就是执行成功了！

我们还可以通过游标的rowcount获得本次更新语句影响了多少行的数据！

try:
    sql = "update user set lastLoginDT = now() where userID = %d"
    params = (32)
    print("更新数据")
    print("")
    cursor.execute(sql % params)
    cn.commit()
    effectedRows = cursor.rowcount
    print(effectedRows)
except Exception as e:
    print(e)
    cn.rollback()

删除数据

删除数据是通过DELETE语句完成的，删除语句的条件自然也是根据自增列的数值了。

删除是否成功也是通过是否有异常抛出判断的，如果没有异常抛出就表示执行成功了！

删除行数也可以通过游标的rowcount得到！

try:
    sql = "delete from user where userID = %d"
    params = (32)
    print("删除数据")
    print("")
    cursor.execute(sql % params)
    cn.commit()
    effectedRows = cursor.rowcount
    print(effectedRows)
except Exception as e:
    print(e)
    cn.rollback()

查询数据

查询语句就是SELECT语句，这是数据库操作里面最最常用的SQL语句了，单表查询、多表连接查询、子查询、分组查询、汇总查询、结果排序等等一系列的SQL查询技巧福哥今天不想在这里讲解，我们只用一个最简单的单表查询演示PDO是如何实现数据查询的就可以了~~

try:
    cursor.execute("select * from user order by userID desc")
    users = cursor.fetchall()
    print("数据表user的数据列表")
    print("")
    for user in users:
        print(str(user[0]) + "@" + user[7])
    print("")
except Exception as e:
    print(e)

注意事项

使用pymysql库操作MySQL数据库有一些注意事项，大家一定要认真看哦~~

• 连接MySQL和选择数据库可以放到包含文件里面，这样可以实现代码的复用。

• 无论自己的数据库是不是utf-8编码的都设置一下连接编码，毕竟你不能保证运行你的程序的环境一定是utf-8的。

• 尽量不要阻止pymysql抛出异常，有bug我们就解决bug，这样系统才健康！

字典游标

DictCursor

我们通过pymysql的游标查询出来的结果是（list）类型的，由于查询结果不包含字段名称很不便于编写代码，如果可以得到（dict）类型就好了。

其实pymysql库有一个游标属性可以将查询游标改成输出字典（dict）类型的结果，只不过默认情况下游标是数组（list）类型的结果。

示例

try:
    cn = pymysql.connect(host="192.168.2.168", user="root", password="abcdef", db="tfums", charset="utf8")
    cursorDict = cn.cursor(pymysql.cursors.DictCursor)
except Exception as e:
    print(e)
    exit()
    
try:
    cursorDict.execute("select * from user order by userID desc")
    users = cursorDict.fetchall()
    print("数据表user的数据列表")
    print("")
    for user in users:
        print(str(user["userID"]) + "@" + user["userName"])
    print("")
except Exception as e:
    print(e)

事务机制

InnoDB

默认情况下MySQL的数据库的引擎都是InnoDB类型的，而InnoDB类型的数据库是支持事务机制的，所谓事务机制简单解释就是会给每一个连接到MySQL服务器的终端分配一块内存，所有的数据和数据库里面的数据都是隔离的，在提交事务之前任何其他终端所做的更改都不会影响当前终端下面的数据。

pymysql相对于MySQL来说也是一个终端，在提交之前也不会受其他终端的更改的影响，所以在MySQL的控制台终端的更改在pymysql里面是查询不到的。

事务提交

如果想让pymysql里面可以查询到其他终端提交的最新数据的话，需要在每一次执行SELECT语句之后进行一次提交才可以。提交可以通过手动方式或者自动方式，福哥写了一个循环，每次查询user表的全部数据。

下面分别演示了手动提交和自动提交方式下的程序是如何编写的，大家可以参看看~~

手动提交

手动提交很简单，就是在使用游标执行SELECT语句之后，使用连接提交一下，再去获取数据就会是最新的了。

try:
    while True:
        cursorDict.execute("select * from user order by userID desc")
        cn.commit()
        users = cursorDict.fetchall()
        print("数据表user的数据列表")
        print("")
        for user in users:
            print(str(user["userID"]) + "@" + user["userName"])
        print("")
        # wait
        time.sleep(10)
except Exception as e:
    print(e)

自动提交

每次执行一条命令都要提交一下有点麻烦，怎么办？可以在创建MySQL连接的时候开启自动提交模式。

try:
    cn = pymysql.connect(host="192.168.2.168", user="root", password="abcdef", db="tfums", charset="utf8", autocommit=True)
    cursor = cn.cursor()
    cursorDict = cn.cursor(pymysql.cursors.DictCursor)
except Exception as e:
    print(e)
    exit()
    
try:
    while True:
        cursorDict.execute("select * from user order by userID desc")
        users = cursorDict.fetchall()
        print("数据表user的数据列表")
        print("")
        for user in users:
            print(str(user["userID"]) + "@" + user["userName"])
        print("")
        # wait
        time.sleep(10)
except Exception as e:
    print(e)

总结

今天福哥给童鞋们演示了使用pymysql库连接MySQL数据库进行各种常规操作的技巧，这里面都是一些简单的基础操作。不过，即使是复制的SQL语句也和今天福哥将的内容大同小异，举一反三就可以实现了。

细心的童鞋已经发现了，这一片Python的通过pymysql库操作MySQL的教程和PHP的通过PDO库操作MySQL如出一辙，福哥这样安排是有特别用意的，这样对比着学习会发现不同语言、不同库之间的差异，慢慢的就会发现天下语言是一家了！