python3操作MySQL数据库

这是python3下的MySQL基本操作。其他类型的数据库用法基本一样。就是库的名字不同。因为python官方很早之前就规定了数据库第三方库的借口，来避免API混乱的情况。

安装与准备

这是python3的库，所以windows下安装不会像python2那样各种奇葩VC错误。是比较方便的傻瓜安装。

• Windows平台下: py -3 -m pip install PyMySQL
• Linux：python3 pip install PyMySQL

当然，引入的时候：import pymysql

---

数据库连接对象connection

Function	描述
connection	创建connection对象
cursor()	使用该链接创建+返回游标
commit()	提交当前事务
rollback()	回滚当前事务
close()	关闭连接

介绍一下connection的参数

1. host mysql服务器地址
2. port 数字类型 端口
3. user 用户名
4. passwd 密码
5. db 数据库名称
6. charset 连接编码，需要显式指明编码方式

上一段代码示例：

1. conn = pymysql.Connect(host= '127.0.0.1',port= 3306,user= 'root',passwd= 'dyx240030',db= 'imooc',charset= 'utf8')
2. cursor = conn.cursor()
3. print(conn)
4. print(cursor)
5. cursor. close()
6. conn. close()

1. OUT:
2. < pymysql .connections .Connection object at 0 x00000051C15BFDA0>
3. < pymysql .cursors .Cursor object at 0 x00000051C15BFD68>

---

数据库游标对象cursor

Function	描述
execute(op[,args])	执行一个数据库查询和命令
fetchone()	取得结果集下一行
fetchmany(size)	取得结果集size行
fetchall()	取得结果集剩下所有行
rowcount	最近一次execute返回数据的行数或影响行数
close()	关闭cursor

代码实现：

1. conn = pymysql.Connect(host= '127.0.0.1',port= 3306,user= 'root',passwd= 'dyx240030',db= 'imooc',charset= 'utf8')
2. cursor = conn.cursor()
3. sql = "select * from user"
4. cursor.execute(sql)
5. print( "cursor.excute:",cursor.rowcount)
7. rs = cursor.fetchone()
8. print( "rs:",rs)
10. for each in cursor.fetchmany( 2):
11. print( each)
12. print()
13. for each in cursor.fetchall():
14. print( each)

1. OUT:
2. cursor.excute: 4
3. rs: ( '1', 'name1')
4. ( '2', 'name2')
5. ( '3', 'name3')
7. ( '4', 'name4')

---

更新数据库insert/update/delete

不同于select操作，这三个操作修改了数据库内容，所以需要commit()，否则数据库没有做相应的更改，但是也不会报错。

按照一般的思路，一般是以下套路：

1. 关闭自动commit:conn.autocommit(False)
2. 出现：conn.rowback()回滚
3. 未出现：conn.commit()

下面这段脚本，实现insert/update/delete操作。其实这种检错模式不对，这里只做简单raise，后面有更好的方法。

1. conn = pymysql.Connect(host='127.0.0.1',port=3306,user='root',passwd='dyx240030',db='imooc',charset='utf8')
2. conn.autocommit(False)
3. cursor = conn.cursor()
5. sqlInsert = " insert into user(userid,username) values( '6', 'name6') "
6. sqlUpdate = " update user set username= 'name41' where userd= '4' "
7. sqlDelete = " delete from user where userid= '1' "
8. try:
9. cursor.execute(sqlInsert)
10. print(cursor.rowcount)
11. cursor.execute(sqlUpdate)
12. print(cursor.rowcount)
13. cursor.execute(sqlDelete)
14. print(cursor.rowcount)
16. conn.commit()
17. except Exception as e:
18. print("Reason: ",e)
19. conn.rollback()
21. cursor.close()
22. cursor.close()

1. [ OUT]:
2. 1
3. Reason: ( 1054, "Unknown column 'userd' in 'where clause'")

实例 银行转账

可以看一下类思想的SQL操作，其中之前提到过的高级报错模式用到了之前看似无用的rowcount函数，通过查看操作对于数据库的影响来检错。

1. import os
2. import sys
3. import pymysql
5. class transferMoney(object):
6. def __init__(self,conn):
7. self.conn = conn
8. def transfer(self,sourceID,targetID,money):
9. # 其他函数中若是有错会抛出异常而被检测到。
10. try:
11. self.checkIdAvailable(sourceID)
12. self.checkIdAvailable(targetID)
13. self.ifEnoughMoney(sourceID,money)
14. self.reduceMoney(sourceID,money)
15. self.addMoney(targetID,money)
16. self.conn.commit()
17. except Exception as e:
18. self.conn.rollback()
19. raise e
20. def checkIdAvailable(self,ID):
21. cursor = self.conn.cursor()
22. try:
23. sql = "select * from account where id = %d" % ID #select语句判断可以用len(rs)
24. cursor.execute(sql)
25. rs= cursor.fetchall()
26. if len(rs) != 1: # 数据库类思想的报错模式，检查操作对数据库的影响条目。没有达到目标，抛出异常
27. raise Exception( "账号 %d 不存在"%ID)
28. finally:
29. cursor.close()
31. def ifEnoughMoney(self,ID,money):
32. cursor = self.conn.cursor()
33. try:
34. sql = "select * from account where id = %d and money>=%d" % (ID,money)
35. cursor.execute(sql)
36. rs= cursor.fetchall()
37. if len(rs) != 1:
38. raise Exception( "账号 %d 不存在 %d Yuan"%(ID,money))
39. finally:
40. cursor.close()
42. def reduceMoney(self,ID,money):
43. cursor = self.conn.cursor()
44. try:
45. sql = "update account set money = money-%d where id=%d"%(money,ID)
46. cursor.execute(sql)
47. if cursor.rowcount != 1:
48. raise Exception( "失败减钱")
49. finally:
50. cursor.close()
52. def addMoney(self,ID,money):
53. cursor = self.conn.cursor()
54. try:
55. sql = "update account set money = money+%d where id=%d"%(money,ID)
56. cursor.execute(sql)
57. if cursor.rowcount != 1:
58. raise Exception( "失败加款")
59. finally:
60. cursor.close()
63. if __name__== "__main__":
64. if len(sys.argv)>= 2:
65. sourceID = int(sys.argv[ 1])
66. targetID = int(sys.argv[ 2])
67. money = int(sys.argv[ 3])
69. conn = pymysql.Connect(host= '127.0.0.1',port= 3306,user= 'root',passwd= 'dyx240030',db= 'imooc',charset= 'utf8')
70. trMoney = transferMoney(conn)
72. try:
73. trMoney.transfer(sourceID,targetID,money)
74. except Exception as e:
75. print( "出现问题"+str(e))
76. finally:
77. conn.close()

---

我踩过的坑。。。(这里是Python3哦)

'NoneType' object has no attribute 'encoding' ，之前指明的charset必须是"UTF8",不是"utf-8"/"UTF-8"

MySQL语句后面必须有';'，否则不会报错，也难以发现

数据库insert/update/delete操作需要commit()

在构造命令的时候，注意用 " 包裹起来，因为SQL语句字符串需要 ' 包裹。所以，" 比较简单的避免歧义。