0%

Python操作MySQL数据库

Python DB API


https://www.python.org/dev/peps/pep-0249/

Python DB API是Python官方提供的一套访问数据库的统一接口规范

在没有Python DB API之前,Python访问数据库的接口程序混乱,当要对业务使用的数据库进行切换时,需要对Python应用程序进行大量的更改,迁移成本相当的高,因此Python官方提供了一套访问数据库的统一接口规范,实现了Python应用程序对多种数据库访问方式的统一。

Python DB API包含的内容

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

Python开发MySQL环境的配置

Python2

下载MySQL-Python并安装,安装程序会自动检测Python客户端的位置:

https://sourceforge.net/projects/mysql-python/#

Python3

1
2
pip install PyMySQL
pip isntall PyMySQL3

数据库连接对象Connection


  • 连接对象:建立Python客户端与数据库的网络连接
  • 创建方法:MySQLdb.Connect(参数)/pymysql.connect(参数)
参数名 类型 说明
host 字符串 MySQL服务器地址
port 数字 MySQL服务器端口号
user 字符串 用户名
passwd 字符串 密码
db 字符串 数据库名称
charset 字符串 连接编码
  • connection对象支持的方法
方法名 说明
cursor() 使用该连接创建并返回游标
commit() 提交当前事务
rollback() 回滚当前事务
close() 关闭连接

示例代码

1
create database test
1
2
3
4
5
6
7
8
9
10
11
12
import pymysql

conn = pymysql.connect(
host='127.0.0.1',
port = 3306,
user='root',
passwd='westos',
charset='utf8',
db='test'
)

print(conn)

游标对象Cursor


  • 游标对象:用于执行查询和获取结果
  • Cursor对象支持的方法:
参数名 说明
execute(op[,args]) 执行一个数据库查询或命令
fetchone() 取的结果集的下一行
fetchmany(size) 获取结果集的下几行
fetchall() 获取结果集中剩下的所有行
rowcount 最近一次execute返回数据的行数或影响行数
close() 关闭游标对象

演示select数据


流程

示例代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use test
create table user (
userid int not null auto_increment,
username varchar(100),
primary key(userid)
)engine=innodb auto_increment=1 default charset='utf8';
insert into user(userid,username) values(1,'NAME1');
insert into user(userid,username) values(2,'NAME2');
insert into user(userid,username) values(3,'NAME3');
insert into user(userid,username) values(4,'NAME4');
insert into user(userid,username) values(5,'NAME5');
insert into user(userid,username) values(6,'NAME6');
insert into user(userid,username) values(7,'NAME7');
insert into user(userid,username) values(8,'NAME8');
insert into user(userid,username) values(9,'NAME9');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import pymysql

conn = pymysql.connect(
host='127.0.0.1',
port = 3306,
user='root',
passwd='westos',
charset='utf8',
db='test'
)

cur = conn.cursor()

sql = "select * from user"
cur.execute(sql)

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

cur.close()
conn.close()

事务


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

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

注意:开发过程中一般关闭自动提交事务功能,设置conn.autocommit(False)

演示insert/update/delete数据


流程

示例代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
use test
create table user (
userid int not null auto_increment,
username varchar(100),
primary key(userid)
)engine=innodb auto_increment=1 default charset='utf8';
insert into user(userid,username) values(1,'NAME1');
insert into user(userid,username) values(2,'NAME2');
insert into user(userid,username) values(3,'NAME3');
insert into user(userid,username) values(4,'NAME4');
insert into user(userid,username) values(5,'NAME5');
insert into user(userid,username) values(6,'NAME6');
insert into user(userid,username) values(7,'NAME7');
insert into user(userid,username) values(8,'NAME8');
insert into user(userid,username) values(9,'NAME9');
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
import pymysql

conn = pymysql.connect(
host='127.0.0.1',
port = 3306,
user='root',
passwd='westos',
charset='utf8',
db='test'
)

cur = conn.cursor()

sql_insert = "insert into user(userid,username) values(10,'name10')"
sql_update = "update user set username='name91' where userid=9"
sql_delete = "delete from user where userid<3"

try:
cur.execute(sql_insert)
print(cur.rowcount)
cur.execute(sql_update)
print(cur.rowcount)
cur.execute(sql_delete)
print(cur.rowcount)
conn.commit()
except Exception as e:
print(e)
conn.rollback()

cur.close()
conn.close()

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


流程

示例代码

1
2
3
4
5
6
7
create table account (
accountid int,
money int,
primary key(accountid)
)engine=innodb default charset='utf8';
insert into account(accountid,money) values(11,110);
insert into account(accountid,money) values(12,10);
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
# -*- coding:UTF8 -*-
import pymysql
import sys

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

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

def has_enough_money(self,source_accountid,money):
cur = self.conn.cursor()
try:
sql = "select * from account where accountid=%s and money>=%s" % (source_accountid,money)
cur.execute(sql)
print("have_enought_money:"+sql)
rs = cur.fetchall()
if len(rs) != 1:
raise Exception("账号%s没有足够的钱" % source_accountid)
finally:
cur.close()

def reduce_money(self,source_accountid,money):
cur = self.conn.cursor()
try:
sql = "update account set money=money-%s where accountid=%s" % (money,source_accountid)
cur.execute(sql)
print("reduce_money:"+sql)
if cur.rowcount != 1:
raise Exception("账号%s减款失败" % source_accountid)
finally:
cur.close()

def add_money(self,target_account,money):
cur = self.conn.cursor()
try:
sql = "update account set money=money+%s where accountid=%s" % (money, target_account)
cur.execute(sql)
print("add_money:" + sql)
if cur.rowcount != 1:
raise Exception("账号%s加法失败" % target_account)
finally:
cur.close()

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

if __name__ == "__main__":
source_accountid = sys.argv[1]
target_accountid = sys.argv[2]
money = sys.argv[3]

conn = pymysql.connect(
host='127.0.0.1',
port = 3306,
user='root',
passwd='westos',
charset='utf8',
database='test'
)

tr_money = TransferMoney(conn)

try:
tr_money.transfer(source_accountid,target_accountid,money)
except Exception as e:
print("出现问题:"+str(e))
finally:
conn.close()

执行结果:

G:\PythonProject\python-mysql>python connmysql.py 11 12 100
check_acct_available:select * from account where accountid=11
have_enought_money:select * from account where accountid=11 and money>=100
reduce_money:update account set money=money-100 where accountid=11
add_money:update account set money=money+100 where accountid=12

参考链接:http://www.imooc.com/learn/475


- - - - - - - - - 本文结束啦感谢您阅读 - - - - - - - - -