I. 基本使用篇

这里主要使用mysql的基本使用姿势,也就是最常见的增删改查,这里主要是使用pymysql这个包来操作

1. 基本环境

mysql环境

mysql环境搭建,基本配置如下

1
2
3
4
5
6
ip: 127.0.0.1
port: 3306
user: root
password:
database: test
table: user

表结构如下

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(80) NOT NULL DEFAULT '' COMMENT '用户名',
`pwd` varchar(26) NOT NULL DEFAULT '' COMMENT '密码',
`isDeleted` tinyint(1) NOT NULL DEFAULT '0',
`created` varchar(13) NOT NULL DEFAULT '0',
`updated` varchar(13) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

python环境

Python3.7 + pymysql

2. db连接

不管什么语言,使用mysql的第一步都是建立连接,然后才能执行具体的sql语句,第一步的建连如下

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
import pymysql


def register():
'''
数据库连接相关的初始化操作
:return:
'''
# 打开数据库连接
db = pymysql.connect(host='127.0.0.1', port=3306,
user='root', password='',
db='test', charset='utf8')

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

# 使用 execute() 方法执行 SQL 查询
cursor.execute("SELECT VERSION()")

# 使用 fetchone() 方法获取单条数据.
data = cursor.fetchone()

print("Database version : %s " % data)
return db, cursor

db, cursor = register()

建立连接主要就是pymysql.connect这一行的调用,成功之后会返回mysql的连接对象db;然后可以通过它获取后续进行sql语句执行的cursor对象;这两个对象后续都会使用

3. 插入

插入命令简单来说就是两步

  • cursor.execute(sql) : 执行sql语句
  • db.commit() : 提交sql执行的结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
def insertDb(cursor, db):
'''
测试向表中新插入一条数据的case
:param cursor:
:param db:
:return:
'''
print("-------------- start to insert one record ----------------")
# 插入一条数据
cursor.execute('''
insert into user(`name`, `pwd`, `isDeleted`, `created`, `updated`) values('test_{0}', '123456', 0, {0}, {0})
'''.format(int(time.time())))
# 提交修改,在插入、修改、删除时,需要执行下面这个命令,否则sql的执行结果不会提交到mysql(类似事物中的提交概念)
db.commit()
print("cursor.execute effect: ", cursor.rowcount)
print("-------------- end to insert one record ----------------\n")

# 查询db的使用方式
print("-------------- start to query one record ----------------")
cursor.execute("select * from user")
# 表示取得结果集下一行
data = cursor.fetchone()
print("fetch result", data)
print("-------------- end to query one record ----------------\n")

4. 更新

更新与删除其实和插入的步骤都一样,无非是sql语句不同罢了

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
def updateDb(cursor, db):
'''
测试更新db的case
:param cursor:
:param db:
:return:
'''
# 开始更新db
print("-------------- start to update one record ----------------")
cursor.execute('''
update user set isDeleted=1 where id=1 limit 1
''')
# 再未提交之前,查看结果
cursor.execute('''
select * from user where id=1
''')
data = cursor.fetchone()
print("query before update res: ", data)

# 提交执行
db.commit()

# 查看提交之后的结果
cursor.execute('''
select * from user where id=1
''')
data = cursor.fetchone()
print("query after update res: ", data)
print("-------------- end to update one record ----------------\n")

5. 查询

查询与前面最大的区别就是不在需要执行 db.commit() 这一行了,直接cursor.execute即可,why?因为mysql的MVVC快照读方式

对于查询,最主要的就是对返回结果的处理,有取一条,取多条,取所有的三种使用方式

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
def queryDb(cursor, db):
'''
查询db的使用case
:param cursor:
:param db:
:return:
'''
print("-------------- start to query records ----------------")

cursor.execute('''
select * from user limit 10
''')
# 取结果集合中的第一条数据
record = cursor.fetchone()
print("record1:", record)
print("----split------")

# 取结果集合中的两条,然后依次打印
records = cursor.fetchmany(2)
print("record2:", records)

print("----split------")

# 通过遍历方式,逐一获取所有的结果
for record in cursor.fetchall():
print("record:", record)

print("-------------- end to query records ----------------\n")

6. 关闭连接

1
2
3
4
5
6
7
8
def close(db):
'''
关闭数据库连接
:param db:
:return:
'''
# 关闭数据库连接
db.close()

7. 使用测试

测试方式

1
2
3
4
5
6
db, cursor = register()
for i in range(0, 11):
insertDb(cursor, db)
updateDb(cursor, db)
queryDb(cursor, db)
close(db)

输出结果如下

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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
Database version : 5.7.22 
-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to insert one record ----------------
cursor.execute effect: 1
-------------- end to insert one record ----------------

-------------- start to query one record ----------------
fetch result (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
-------------- end to query one record ----------------

-------------- start to update one record ----------------
query before update res: None
query after update res: None
-------------- end to update one record ----------------

-------------- start to query records ----------------
record1: (12, '一灰灰_1542431218', '123456', 0, '1542431218', '1542431218')
----split------
record2: ((13, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296'), (14, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296'))
----split------
record: (15, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (16, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (17, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (18, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (19, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (20, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
record: (21, '一灰灰_1542431296', '123456', 0, '1542431296', '1542431296')
-------------- end to query records ----------------

II. 其他

1. 一灰灰Bloghttps://liuyueyi.github.io/hexblog

一灰灰的个人博客,记录所有学习和工作中的博文,欢迎大家前去逛逛

2. 声明

尽信书则不如,已上内容,纯属一家之言,因个人能力有限,难免有疏漏和错误之处,如发现bug或者有更好的建议,欢迎批评指正,不吝感激

3. 扫描关注

一灰灰blog

QrCode

知识星球

goals