python连接mysql与方法的封装

 1 import pymysql
 2 
 3 class Model(object):
 4     def __init__(self, username=‘root‘, password=‘123456‘, database=‘demo‘, 
 5                  port=3306, host=‘localhost‘):
 6         # 创建连接
 7         self.connection = pymysql.connect(user=username, password=password, database=database,
 8                                           port=port, host=host, cursorclass=pymysql.cursors.DictCursor)
 9         # 创建游标
10         self.cursor = self.connection.cursor()
11 
12     # 查询所有数据
13     def fetchall(self, sql):
14         try:
15             self.__execute(sql)
16             return self.cursor.fetchall()
17         except Exception as error:
18             print(error)
19 
20     # 查询多条数据
21     def fetchmany(self, sql, size=1):
22         try:
23             self.__execute(sql)
24             return self.cursor.fetchmany(size)
25         except Exception as error:
26             print(error)
27 
28     # 查询一条数据
29     def fetchone(self, sql):
30         try:
31             self.__execute(sql)
32             return self.cursor.fetchone()
33         except Exception as error:
34             print(error)
35 
36     # 增删改的方法
37     def change(self, sql):
38         try:
39             self.__execute(sql)
40             self.connection.commit()
41         except Exception as error:
42             print(error)
43 
44     # 执行的私有方法
45     def __execute(self, sql):
46         self.cursor.execute(sql)
47 
48     # 关闭连接和游标
49     def __del__(self):
50         self.connection.close()
51         self.cursor.close()

 1 from data_connect import model
 2 
 3 # 实例化Model类
 4 employee = model.Model()
 5 res = employee.fetchall(‘select nickname from employee where job="头领"‘)[0]
 6 res1 = employee.fetchmany(‘select nickname from employee where job="头领"‘, 2)
 7 res2 = employee.fetchone(‘select nickname from employee where name = "宋江"‘)
 8 # print(res2)
 9 
10 # 插入一条语句
11 employee.change(‘insert into employee (name)values ("关羽")‘)
12 # 删除一条语句
13 employee.change(‘delete from employee where name="关羽"‘)
14 # 更新一条语句
15 # obj.change(‘update employee set name="张飞"where id=8004‘)