python操作数据库(三)数据库操作类的封装

观察前面的操作可以发现,python操作数据库除了sql语句及参数不同,其它语句都是一样的,为了避免重复造轮子,我们可以封装一个数据库操作类来简化我们的数据库操作。具体步骤如下
创建MysqlHelper.py文件,定义类
import pymysql
import pymysql.cursors

class MysqlHelper():
def init(self,host,db,user,passwd):
self.host=host
self.db=db
self.user=user
self.passwd=passwd

def connect(self):
    self.conn=pymysql.connect(host=self.host,db=self.db,user=self.user,passwd=self.passwd)
    self.cursor=self.conn.cursors()

def close(self):
    self.cursor.close()
    self.conn.close()

def get_one(self,sql,params=()):
    result=None
    try:
        self.connect()
        self.cursor.execute(sql, params)
        result = self.cursor.fetchone()
        self.close()
    except Exception as e:
        print(e.message)
    return result

def get_all(self,sql,params=()):
    list=()
    try:
        self.connect()
        self.cursor.execute(sql,params)
        list=self.cursor.fetchall()
        self.close()
    except Exception as e:
        print(e.message)
    return list

def insert(self,sql,params=()):
    return self.__edit(sql,params)

def update(self, sql, params=()):
    return self.__edit(sql, params)

def delete(self, sql, params=()):
    return self.__edit(sql, params)

def __edit(self,sql,params):
    count=0
    try:
        self.connect()
        count=self.cursor.execute(sql,params)
        self.conn.commit()
        self.close()
    except Exception as e:
        print(e.message)
    return count

添加

创建testInsertWrap.py文件,使用封装好的帮助类完成插入操作
#encoding=utf8
from MysqlHelper import *

sql='insert into students(sname,gender) values(%s,%s)'
sname=input("请输入用户名:")
gender=input("请输入性别,1为男,0为女")
params=[sname,bool(gender)]

mysqlHelper=MysqlHelper('localhost','test1','root','mysql')
count=mysqlHelper.insert(sql,params)
if count==1:
print('ok')
else:
print('error')
查询一个

创建testGetOneWrap.py文件,使用封装好的帮助类完成查询最新一行数据操作
#encoding=utf8
from MysqlHelper import *

sql='select sname,gender from students order by id desc'

helper=MysqlHelper('localhost','test1','root','mysql')
one=helper.get_one(sql)
print(one)

标签: none

添加新评论