学习笔记 : Python 操作 MySQL 数据库之基本的 CURD
🐍 学习完 Python 的基础知识后,当然需要进阶地学习关于 Python 的数据库编程知识啦~ 下面是我写的一个操作 MySQL 数据库的基本增删改查,其对于和我一样初学 Python 的同学来说,非常具有参考与学习价值哟~
- tb_user : 数据库表结构
1
2
3
4
5
6
7CREATE TABLE `tb_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`sex` char(1) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`phone` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
- dbconfig.ini : 存储数据库的配置信息
1
2
3
4
5
6[DATABASE_CONFIGURATION_INFOS]
host = localhost
user = root
password =
database =
charset = utf8
- Utils.py : 连接数据库的工具类
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"""
Project name : python-learning
Description : A SIMPLE TOOL FOR OPERATING DATABASE
Author : 黄宇辉
Version : 1.0
Website : https://yubuntu0109.github.io/
Created on 11/12/2019-9:30 PM
"""
import configparser
import pymysql
class DBUtils:
"""
get the databse configuration infos from the specified file
"""
def getConnConfig():
try:
configpar = configparser.ConfigParser()
configpar.read("dbconfig.ini", encoding='utf-8')
connConfig_items = dict(configpar.items('DATABASE_CONFIGURATION_INFOS'))
return connConfig_items
except FileNotFoundError as e:
print("CATCH THE EXCEPTION INFO : %s" % e)
"""
get the database connection object
"""
def getConnection():
try:
global connection # defined a global connection object
connConfig = DBUtils.getConnConfig() # get the database configuration infos
connection = pymysql.connect(host=connConfig.get('host'),
user=connConfig.get('user'),
password=connConfig.get('password'),
database=connConfig.get('database'),
charset=connConfig.get('charset'))
return connection
except Exception as e:
print("FAIL TO CONNECT TO THE DATABASE : %s" % e)
- CURD.py : 操作数据库的测试类
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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124"""
Project name : python-learning
Description : BASIC CURD ON MYSQL DATABASE
Author : 黄宇辉
Version : 1.0
Website : https://yubuntu0109.github.io/
Created on 11/11/2019-10:08 PM
"""
import pymysql
from Utils import DBUtils # format : from filename import classname
class CURDTest:
def __init__(self):
global connection # defined a global variable
connection = DBUtils.getConnection() # get the database connection
"""
select the specified user info by id
"""
def selectById(cls, userid):
try:
# create the cursor object
with connection.cursor() as cursor:
# defined the sql statement
sql = 'SELECT id,name,sex,phone FROM tb_user WHERE id = %s'
# execute the specified sql statement
cursor.execute(sql, [userid])
# get the result
row = cursor.fetchone()
# ouput the result
if row is not None:
print('ID:{0} - NAME:{1} - SEX:{2} - PHONE:{3}'.format(row[0], row[1], row[2], row[3]))
else:
print("NOT FOUND THE SPECIFIED USER INFO BY ID : %s" % userid)
# catch the exception about database
except pymysql.DatabaseError as e:
print(e)
# release the connection resource
finally:
connection.close()
"""
select all of user infos
"""
def selectAll(cls):
try:
with connection.cursor() as cursor:
sql = 'SELECT id,name,sex,phone FROM tb_user'
cursor.execute(sql)
result_set = cursor.fetchall()
for row in result_set:
print('ID:{0} - NAME:{1} - SEX:{2} - PHONE:{3}'.format(row[0], row[1], row[2], row[3]))
except pymysql.DatabaseError as e:
print(e)
finally:
connection.close()
"""
insert the user info
"""
def addUser(cls, username, sex, telephone):
try:
with connection.cursor() as cursor:
sql = 'INSERT INTO tb_user(name,sex,phone) VALUE (%s,%s,%s)'
affectedcount = cursor.execute(sql, (username, sex, telephone))
print('inserted successfully and the affected count : %s' % affectedcount)
connection.commit()
except pymysql.DatabaseError as e:
print(e)
connection.rollback()
finally:
connection.close()
"""
update the user info
"""
def updateUser(cls, username, sex, telephone, userid):
try:
with connection.cursor() as cursor:
sql = 'UPDATE tb_user SET name = %s, sex = %s, phone = %s WHERE id = %s'
affectedcount = cursor.execute(sql, (username, sex, telephone, userid))
print('updated successfully and the affected count : %s' % affectedcount)
connection.commit()
except pymysql.DatabaseError as e:
print(e)
connection.rollback()
finally:
connection.close()
"""
delete the user info
"""
def delUser(cls, userid):
try:
with connection.cursor() as cursor:
sql = 'DELETE FROM tb_user WHERE id = %s'
affectedcount = cursor.execute(sql, userid)
print("deleted successfully and the affected count : %s" % affectedcount)
connection.commit()
except pymysql.DatabaseError as e:
print(e)
connection.rollback()
finally:
connection.close()
"""
test
"""
curd = CURDTest()
curd.selectAll()
# curd.selectById(userid=1)
# curd.addUser(username='addUser', sex='m', telephone='00000000000')
# curd.updateUser(username='updateUser', sex='f', telephone='11111111111', userid=1)
# curd.delUser(userid=1)
- 程序运行结果如下所示( 仅供参考 ) :
1
2ID:1 - NAME:黄宇辉 - SEX:男 - PHONE:11111111111
ID:2 - NAME:燕子 - SEX:女 - PHONE:00000000000
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
Comment