Mysql数据库工具类
新建MysqlHelper/DBUtils,实现操作数据库代码的 复用!
分析
数据库操作分为两类
非查询
# 增加,删,修改 都是非查询! # 实现方法: execute(sql语句,不同参数列表) # 增删改区别: sql语句不同, 参数个数不同!--->可以独一个共用方法,把sql和参数传过来! # 增删改功能都返回: 影响行数据! def my_execute(sql,params): ,,,, return num
查询
# 查询1条 游标.fetchone() # 查询多条 游标.fetchall()
连接数据库相关参考所有功能都需要--->放在类的初始化方法中init
获取数据库连接: 所有方法都需要!--->可以独立出来!
init方法,负责初始连接数据库需要各种参数!
class MysqlHelper(): # 初始化方法 def __init__(self,主机地址,端口,用户名,密码,字符集) self.主机地址=主机地址 .... def my_execute(sql,prames): pass def connect(): ...... # 使用 db = MysqlHelper(Ip地址,端口.用户名,密码.字符集) # 增加 num =db.my_execute(sql语句,参数列表) if num>0: print('成功') else: print('失败')
代码实现
import pymysql # 导入数据库驱动模块! class MysqlHelper(): def __init__(self,host,port,user,passwd,db,charset='utf8'): self.host= host self.port = port self.user = user self.passwd = passwd self.db = db self.charset=charset #self.conn = None def connect(self): '''功能1: 获取连接''' self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset) self.cursor =self.conn.cursor() def close(self): '''功能2:释放资源''' self.cursor.close() #关游标 self.conn.close()# 关连接 def my_execute(self,sql,params): ''' 增删改通用功能 :param sql: sql语句 :param params: 参数列表 :return: num 影响行数 ''' num =0 # 1. 打开连接 self.connect() num = self.cursor.execute(sql,params) self.conn.commit() self.close() #释放资源 return num def get_one(self,sql,params): ''' 查询1条 :param sql: sql语句 :param params: 参数列表 :return: 1条结果 ''' result =None #1.打开链接 self.connect() # 2.执行查询 self.cursor.execute(sql,params) # 3 逐行抓取 result = self.cursor.fetchone() #4.释放资源 self.close() return result def get_all(self, sql, params): ''' 查询所有 :param sql: sql语句 :param params: 参数列表 :return: 1条结果 ''' result = () self.connect() self.cursor.execute(sql, params) result = self.cursor.fetchall() self.close() return result if __name__ == '__main__': # 实例化对象 db = MysqlHelper('localhost', 3306, 'root', 'root', '09c2') #增加 #insert into students values(default,'张A','男',20,'2020-11-05','176','山西') ''' sql = 'insert into students values(default,%s,%s,%s,%s,%s,%s)' params=['张B','男',20,'2020-11-05','176','山西'] num = db.my_execute(sql,params) print(num) ''' # 查1条 sql = 'select * from students where age =%s' params=(12) result =db.get_one(sql,params) #((),()) print(result) studetns = db.get_all(sql,params) for stu in studetns: print(f'名字:{stu[1]}')
错误
[SQL] insert into students values(default,'张三','男',20,'2020-11-05','176','山西') [Err] 1062 - Duplicate entry '张三' for key 'name' # 名字重复! Duplicate:重复!
学生管理系统
from MysqlHelper import MysqlHelper #导入mysql工具类 from datetime import datetime #导入日期模块 def add_stu(): # 1.实例化工具类 db = MysqlHelper('localhost', 3306, 'root', 'root', '09c2') # 2. 调用增加方法 #insert into students values(default,'张A','男',20,'2020-11-05','176','山西') sql = 'insert into students values(default,%s,%s,%s,%s,%s,%s)' params=['张D','男',20,datetime.now(),'176','山西'] num =db.my_execute(sql,params) return num def del_stu(name): '''删除''' db = MysqlHelper('localhost', 3306, 'root', 'root', '09c2') sql = 'delete from students where name =%s' params=[name] num = db.my_execute(sql,params) return num def update_stu(oldName,name,age,sex,phone,addr): db = MysqlHelper('localhost', 3306, 'root', 'root', '09c2') sql ='update students set name =%s,age=%s,sex=%s,phone=%s,addr=%s where name = %s' params = (name,age,sex,phone,addr,oldName) return db.my_execute(sql,params) def get_all(): db = MysqlHelper('localhost', 3306, 'root', 'root', '09c2') sql = 'select * from students' students = db.get_all(sql,None) for stu in students: print(f'名字:{stu[1]},性别:{stu[2]}') def get_by_name(name): db = MysqlHelper('localhost', 3306, 'root', 'root', '09c2') sql = 'select * from students where name =%s' params = (name) students = db.get_all(sql,params) for stu in students: print(f'名字:{stu[1]},性别:{stu[2]}') if __name__ == '__main__': '''增加 if add_stu() > 0: print('成功') else: print('失败') ''' ''' 删除 if del_stu('张D') >0: print('成功') else: print('失败') ''' '''修改 if update_stu('zhang',"张小三",40,'女','110110','陕西')>0: print('修改成功') else: print('失败') ''' del_stu('张D')