Python操作MySQL数据库

发表于 更新于 阅读次数： 本文字数： 1.4k 阅读时长 ≈ 5 分钟

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

参考文档