python中mysql基础操作

160 阅读 0 评论 106 点赞

我是靠谱客的博主无奈大雁，这篇文章主要介绍python中mysql基础操作，现在分享给大家，希望可以做个参考。

复制代码

import pymysql
# 连接数据库
db = pymysql.connect(
host='localhost',
user='test',
password="liang4",
port=3306,
database='test',
autocommit=False
)
# 创建游标
cursor = db.cursor()
# # 创建表格student-(name, sex, age, score)
# cursor.execute('drop table if EXISTS student')
# 如果存在则删除student
# sql = '''
#
create table `student` (
#
num int(8) primary key auto_increment,
#
name varchar(30) not null,
#
sex varchar(5),
#
age int(2),
#
score float(3,1)
#
)
#
'''
# cursor.execute(sql)
# # 插入单条数据
# sql = '''
#
insert into student (name, sex, age, score) values ('liang', 'girl', 25, 60)
#
'''
# cursor.execute(sql)
# db.commit()
# 提交事务，必须加这个
# # 插入多条数据
# sql = '''
#
insert into student (name, sex, age, score) values (%s, %s, %s, %s)
#
'''
# add_data_list = [
#
('wang', 'boy', 24, 59),
#
('yuan', 'girl', 26, 100)
# 100输出99.9，因为前面设置的score float(3,1)
# ]
# cursor.executemany(sql, add_data_list)
# db.commit()
# # 数据表更新
# sql = '''
#
update student set score=%s where num=%s
# '''
# cursor.execute(sql, (90, 1))
# db.commit()
# # 数据表删除
# sql = '''
#
delete from student where num=2
# '''
# try:
#
cursor.execute(sql)
#
db.commit()
#
print('succeed!')
# except:
#
# 如果出现异常，回滚（要求autocommit=False，autocommit在pymysql.connect中设置）
#
db.rollback()
#
print('failed!')
# finally:
#
cursor.close()
#
db.close()
# # 查询数据表
# sql = '''
#
select * from student where age=25
# '''
# try:
#
cursor.execute(sql)
#
result = cursor.fetchall()
# 把所有行都取出来
#
print(type(result))
#
print(result)
#
for row in result:
#
num = row[0]
#
name = row[1]
#
sex = row[2]
#
age = row[3]
#
score = row[4]
#
print('num:', num, 'name:', name, 'sex:', sex, 'age:', age, 'score:', score)
# except Exception as e:
#
print(e)
#
print('failed')
# finally:
#
cursor.close()
#
db.close()
# 关闭游标,关闭数据库连接
cursor.close()
db.close()

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
125
126
127
128
129
130
131
import pymysql
# 连接数据库
db = pymysql.connect(
host='localhost',
user='test',
password="liang4",
port=3306,
database='test',
autocommit=False
)
# 创建游标
cursor = db.cursor()
# # 创建表格student-(name, sex, age, score)
# cursor.execute('drop table if EXISTS student')
# 如果存在则删除student
# sql = '''
#
create table `student` (
#
num int(8) primary key auto_increment,
#
name varchar(30) not null,
#
sex varchar(5),
#
age int(2),
#
score float(3,1)
#
)
#
'''
# cursor.execute(sql)
# # 插入单条数据
# sql = '''
#
insert into student (name, sex, age, score) values ('liang', 'girl', 25, 60)
#
'''
# cursor.execute(sql)
# db.commit()
# 提交事务，必须加这个
# # 插入多条数据
# sql = '''
#
insert into student (name, sex, age, score) values (%s, %s, %s, %s)
#
'''
# add_data_list = [
#
('wang', 'boy', 24, 59),
#
('yuan', 'girl', 26, 100)
# 100输出99.9，因为前面设置的score float(3,1)
# ]
# cursor.executemany(sql, add_data_list)
# db.commit()
# # 数据表更新
# sql = '''
#
update student set score=%s where num=%s
# '''
# cursor.execute(sql, (90, 1))
# db.commit()
# # 数据表删除
# sql = '''
#
delete from student where num=2
# '''
# try:
#
cursor.execute(sql)
#
db.commit()
#
print('succeed!')
# except:
#
# 如果出现异常，回滚（要求autocommit=False，autocommit在pymysql.connect中设置）
#
db.rollback()
#
print('failed!')
# finally:
#
cursor.close()
#
db.close()
# # 查询数据表
# sql = '''
#
select * from student where age=25
# '''
# try:
#
cursor.execute(sql)
#
result = cursor.fetchall()
# 把所有行都取出来
#
print(type(result))
#
print(result)
#
for row in result:
#
num = row[0]
#
name = row[1]
#
sex = row[2]
#
age = row[3]
#
score = row[4]
#
print('num:', num, 'name:', name, 'sex:', sex, 'age:', age, 'score:', score)
# except Exception as e:
#
print(e)
#
print('failed')
# finally:
#
cursor.close()
#
db.close()
# 关闭游标,关闭数据库连接
cursor.close()
db.close()