181118-Python之Mysql基本使用篇

文章目录
  1. I. 基本使用篇
    1. 1. 基本环境
    2. 2. db连接
    3. 3. 插入
    4. 4. 更新
    5. 5. 查询
    6. 6. 关闭连接
    7. 7. 使用测试
  2. II. 其他
    1. 1. 一灰灰Blog: https://liuyueyi.github.io/hexblog
    2. 2. 声明
    3. 3. 扫描关注

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

评论

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×