![]()
一个简单的python查询数据库
1 import pymysql
2
3
4 def insertsql():
5 # 建立数据库连接
6 connection_sql = pymysql.connect(
7 host='127.0.0.1',
8 port=3306,
9 user='root',
10 password='root',
11 db='5_10',
12 charset='utf8'
13 )
14 # 获取游标
15 cursor = connection_sql.cursor(pymysql.cursors.DictCursor)
16 str1 = '1'
17 str2 = '18'
18 # sql = 'select * from staff where id> %s and age>%s'
19 sql1 = 'select * from staff where id= %s'
20 try:
21 # cursor.execute(sql, [str1, str2])
22 cursor.execute(sql1, str1)
23 a = cursor.fetchall()
24 print(a)
25 connection_sql.commit()
26 except Exception as e:
27 connection_sql.rollback()
28 raise e
29 # 关闭游标
30 cursor.close()
31 # 关闭连接
32 connection_sql.close()
33
34 insertsql()
一个封装的py——MySQL
1 import pymysql
2
3
4 # username : adil
5 # password : helloyyj
6
7
8 class DataBaseHandle(object):
9 ''' 定义一个 MySQL 操作类'''
10
11 def __init__(self, host, username, password, database, port):
12 '''初始化数据库信息并创建数据库连接'''
13 # 下面的赋值其实可以省略，connect 时 直接使用形参即可
14 self.host = host
15 self.username = username
16 self.password = password
17 self.database = database
18 self.port = port
19 self.db = pymysql.connect(
20 host=self.host,
21 user=self.username,
22 password=self.password,
23 db=self.database,
24 port=self.port,
25 charset="utf8"
26
27 )
28 self.cursor = self.db.cursor(pymysql.cursors.DictCursor)
29
30
31
32
33
34
35 def insertDB(self, sql):
36 ''' 插入数据库操作 '''
37
38
39 try:
40 # 执行sql
41 self.cursor.execute(sql)
42
43 # tt = self.cursor.execute(sql) # 返回 插入数据 条数 可以根据 返回值 判定处理结果
44 # print(tt)
45 self.db.commit()
46
47 # query = 'insert into 表名(列名1, 列名2, 列名3, 列名4, 列名5, 列名6) values(%s, %s, %s, %s, %s, %s)'
48 # self.cursor.execute(sql)
49
50 except:
51 # 发生错误时回滚
52 self.db.rollback()
53 finally:
54 self.cursor.close()
55
56 def deleteDB(self, sql):
57 ''' 操作数据库数据删除 '''
58
59 try:
60 # 执行sql
61 self.cursor.execute(sql)
62 # tt = self.cursor.execute(sql) # 返回 删除数据 条数 可以根据 返回值 判定处理结果
63 # print(tt)
64 self.db.commit()
65 except:
66 # 发生错误时回滚
67 self.db.rollback()
68 finally:
69 self.cursor.close()
70
71 def updateDb(self, sql):
72 ''' 更新数据库操作 '''
73
74
75 try:
76 # 执行sql
77 self.cursor.execute(sql)
78 # tt = self.cursor.execute(sql) # 返回 更新数据 条数 可以根据 返回值 判定处理结果
79 # print(tt)
80 self.db.commit()
81 except:
82 # 发生错误时回滚
83 self.db.rollback()
84 finally:
85 self.cursor.close()
86
87 def selectDb(self, sql):
88 ''' 数据库查询 '''
89
90 try:
91 self.cursor.execute(sql) # 返回 查询数据 条数 可以根据 返回值 判定处理结果
92
93 data = self.cursor.fetchall() # 返回所有记录列表
94
95 print(data)
96
97 # 结果遍历
98 for row in data:
99 name = row["name"]
100 age = row["age"]
101 # 遍历打印结果
102 print('age = %s, name = %s' % (age, name))
103 except:
104 print('Error: unable to fecth data')
105 finally:
106 self.cursor.close()
107
108 def closeDb(self):
109 ''' 数据库连接关闭 '''
110 self.db.close()
111
112
113 if __name__ == '__main__':
114 DbHandle = DataBaseHandle('127.0.0.1', 'root', 'root', 'class202107', 3306,)
115
116 # DbHandle.insertDB("insert into students (name,age,gender) values ('随便起名',19,'男')")
117 # DbHandle.deleteDB("delete from students where name='随便起名'")
118 DbHandle.selectDb("select * from students where id > 10")
119
120
121
122 DbHandle.closeDb()
再次封装一个做查询，一个做非查询
1 # 一个做查询，一个做非查询
2 import pymysql
3
4
5 # username : adil
6 # password : helloyyj
7
8
9 class DataBaseHandle(object):
10 ''' 定义一个 MySQL 操作类'''
11
12 def __init__(self, host, username, password, database, port):
13 '''初始化数据库信息并创建数据库连接'''
14 # 下面的赋值其实可以省略，connect 时 直接使用形参即可
15 self.host = host
16 self.username = username
17 self.password = password
18 self.database = database
19 self.port = port
20 self.db = pymysql.connect(
21 host=self.host,
22 user=self.username,
23 password=self.password,
24 db=self.database,
25 port=self.port,
26 charset="utf8"
27
28 )
29 self.cursor = self.db.cursor(pymysql.cursors.DictCursor)
30
31 def select_info(self, sql):
32 try:
33 self.cursor.execute(sql)
34 data = self.cursor.fetchall()
35 return data
36 except:
37 print('Error: unable to fecth data')
38 finally:
39 self.cursor.close()
40
41 def no_select_info(self, sql):
42 try:
43 result = self.cursor.execute(sql)
44 print(result)
45 self.db.commit()
46 return result
47 except Exception as e:
48 self.db.rollback()
49 raise e
50 finally:
51 self.cursor.close()
52
53
54 if __name__ == '__main__':
55 DbHandle = DataBaseHandle('127.0.0.1', 'root', 'root', '5_10', 3306, )
56
57 DbHandle.no_select_info("insert into stu (name,age) values ('随便起名1',19)")
58 DbHandle.select_info("select * from students where id < 10")
![]()
posted on
浙公网安备 33010602011771号