Python 数据库交互与封装

from MySQLdb import *
class MysqlHelper:
    def __init__(self,host,port,db,user,passwd,charset='utf8'):
        self.host = host
        self.port = port
        self.db = db
        self.user = user
        self.passwd = passwd
        self.charset = charset
    def open(self):
        self.conn=connect(host=self.host,port=self.port,db=self.db,user=self.user,passwd=self.passwd,charset=self.charset)
        self.cursor = self.conn.cursor()
    def close(self):
        self.cursor.close()
        self.conn.close()
    #用于修改数据
    def cud(self,sql,params):
        try:
            self.open()

            self.cursor.execute(sql,params)
            self.conn.commit()

            self.close()
            print("ok")
        except:
            print("未成功")
    #用于查询数据
    def all(self,sql,params):
        try:
            self.open()

            self.cursor.execute(sql,params)
            result = self.cursor.fetchall()
            self.close()

            return result

        except:

            print("没有成功啊")

类的调用:

from MyHerSql import MysqlHelper
#name = input("请输入学生姓名")
#id = input("请输入学生编号")
#参数化,防止sql注入
# sql = 'update students set name =%s where id = %s'
# params = [name,id]
#
# sqlhelper =MysqlHelper(host = 'localhost',port = 3306,db = 'python3',user ='root',passwd = '123',charset='utf8')
# sqlhelper.cud(sql,params)
params = []
sql = 'select id,name from students where id < 5'
sqlhelper = MysqlHelper(host='localhost', port=3306, db='python3', user='root', passwd='123', charset='utf8')
result = sqlhelper.all(sql,params)
print(result)