Python操作MySql数据库

本篇是慕课网课程Python操作MySql数据库的笔记

Python DB API

Python DB API:Python访问数据库的统一接口规范

包括的内容:

  • 数据库连接对象connection
  • 数据库交互对象cursor
  • 数据库异常类exceptions

使用Python DB API访问数据库流程:

开始 –> 创建connection –> 获取cursor –> 执行查询、执行命令、获取数据、处理数据 –> 关闭cursor –> 关闭connection –> 结束

Python MySQL开发环境

  • 教程里是Python2.7,而我的是Python3.6
  • MySQL,我的是5.7
  • 教程里开发工具是Eclipse+Pydev,我用的是Pycharm
  • PyMySQL(Python2.x里是MySQLdb可以通过pip安装:
    1
    pip install PyMySQL

因为Python统一了数据库连接的接口,所以pymysqlMySQLdb的使用方式是类似的

数据库连接对象connection

连接对象connection:建立Python客户端与数据库的网络连接

创建方法:pymysql.Connect(参数)

参数名 类型 说明
host 字符串 MySQL服务器地址
post 数字 MySQL服务器端口号
user 字符串 用户名
passwd 字符串 密码
db 字符串 数据库名称
charset 字符串 连接编码

connection对象支持的方法:

方法名 说明
cursor() 使用该连接创建并返回游标
commit() 提交当前事务
rollback() 回滚当前事务
close() 关闭连接

数据库的游标对象cursor

游标对象cursor:用于执行查询和获取结果

cursor对象支持的方法:

方法名 说明
execute(op[,args]) 执行一个数据库查询和命令(CRUD)
fetchone() 取得结果集的下一行
fetchmany(size) 获取结果集的下几行
fetchall() 获取结果集中剩下的所有行(没有被遍历的所有行)
rowcount 最近一次execute返回数据的行数或影响行数(不是方法)
close() 关闭游标对象
  • execute方法:执行SQL、将结果从数据库获取到客户端
  • fetch*方法:移动rownumber(它相当于一个指针),返回数据

select查询数据

过程:

开始 –> 创建connection –> 获取cursor –> 使用cursor.execute()执行SELECT语句 –> 使用cursor.fetch*()获取并处理数据 –> 关闭cursor –> 关闭connection –> 结束

例:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
conn = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='123456',
db='pymysql',
charset='utf8' # 注意不是utf-8!
)

cursor = conn.cursor()

sql = "SELECT * FROM user"
cursor.execute(sql)

rs = cursor.fetchall()
for row in rs:
print("userid = %s, username = %s" % row)

cursor.close()
conn.close()

Insert/Update/Delete数据

过程:

开始 –> 创建connection –> 获取cursor –> 使用cursor.execute()执行INSERT/UPDATE/DELETE语句 –> 出现异常?(否:使用conn.commit()提交事务/是:使用conn.rollback()回滚事务) –> 关闭cursor –> 关闭connection –> 结束

这里就要使用try...except...语句判断是否出现异常

事务:访问和更新数据库的一个程序执行单元

  • 原子性:事务中包括的诸操作要么都做,要么都不做
  • 一致性:事务必须使数据库从一致性状态变到另一个一致性状态
  • 隔离性:一个事务的执行不能被其他事务干扰
  • 持久性:食物一旦提交,它对数据库的改变就是永久性的

注意:MySQL引擎不能设置为MyISAM,因为它不支持事务

开发中怎样使用事务?

  • 关闭自动commit:设置conn.autocommit(FALSE)
  • 正常结束事务:conn.commit()
  • 异常结束事务:conn.rollback()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
sql_insert = "INSERT INTO user (userid, username) VALUES (10, 'name10')"
sql_update = "UPDATE user SET username='name99' WHERE userid=9"
sql_delete = "DELETE FROM user WHERE userid<3"

try:
cursor.execute(sql_insert)
print(cursor.rowcount)
cursor.execute(sql_update)
print(cursor.rowcount)
cursor.execute(sql_delete)
print(cursor.rowcount)

conn.commit() # 上面三条语句就是一个事务,成功执行就可以commit
except Exception as e:
print(e)
conn.rollback() # 出现异常就rollback回滚

银行转账实例(账户A给账户B转账100元)

过程:

开始事务 –> 检查账户A和账户B是否可用 –> 检查账户A是否有100元 –> 账户A减去100元,账户B加上100元 –> 出现异常则回滚事务,否则提交事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
import sys
import pymysql


class TransferMoney():
def __init__(self, conn):
self.conn = conn

def transfer(self, source_acctid, target_acctid, money):
try:
self.check_acct_available(source_acctid)
self.check_acct_available(target_acctid)
self.has_enough_money(source_acctid, money)
self.reduce_money(source_acctid, money)
self.add_money(target_acctid, money)
self.conn.commit()
except Exception as e:
self.conn.rollback()
raise e

def check_acct_available(self, acctid):
cursor = self.conn.cursor()
try:
sql = "SELECT * FROM account WHERE acctid=%s" % acctid
cursor.execute(sql)
print("check_acct_available: " + sql)
rs = cursor.fetchall()
if len(rs) != 1:
raise Exception("账号%s不存在" % acctid)
finally:
cursor.close()

def has_enough_money(self, acctid, money):
cursor = self.conn.cursor()
try:
sql = "SELECT * FROM account WHERE acctid=%s AND money>%s" % (acctid, money)
cursor.execute(sql)
print("has_enough_money: " + sql)
rs = cursor.fetchall()
if len(rs) != 1:
raise Exception("账号%s没有足够的金额" % acctid)
finally:
cursor.close()

def reduce_money(self, acctid, money):
cursor = self.conn.cursor()
try:
sql = "UPDATE account SET money=money-%s WHERE acctid=%s" % (money, acctid)
cursor.execute(sql)
print("reduce_money: " + sql)
rs = cursor.fetchall()
if cursor.rowcount != 1: # SQL语句影响的行数不是1
raise Exception("账号%s减款失败" % acctid)
finally:
cursor.close()

def add_money(self, acctid, money):
cursor = self.conn.cursor()
try:
sql = "UPDATE account SET money=money+%s WHERE acctid=%s" % (money, acctid)
cursor.execute(sql)
print("add_money: " + sql)
rs = cursor.fetchall()
if cursor.rowcount != 1: # SQL语句影响的行数不是1
raise Exception("账号%s加款失败" % acctid)
finally:
cursor.close()


if __name__ == "__main__":
source_acctid = sys.argv[1] # 这些参数在run configuration里找到parameters设置
target_acctid = sys.argv[2]
money = sys.argv[3]

conn = pymysql.Connect(
host='localhost',
port=3306,
user='root',
passwd='123456',
db='pymysql',
charset='utf8' # 注意不是utf-8!
)

tr_money = TransferMoney(conn)

try:
tr_money.transfer(source_acctid, target_acctid, money)
except Exception as e:
print("出现异常:" + str(e))
finally:
conn.close()