mysql数据库的封装

数据库封装类
import pymysql
import json
class My_databa(object):
    def __init__(self,pwd='****',name='表名',user='root',host='localhost',port=3306):
        self.conn=pymysql.connect(user=user,host=host,db=name,port=port,password=pwd,charset='utf8')
        self.cursor=self.conn.cursor()
    def __enter__(self):#with调用方法
        return self
    def __del__(self):#析构方法
        try:
            self.cursor.close()
            self.conn.close()
        except:
            pass
#插入方法
    def insert(self,table,*args):
        for x in args:
            keys=''
            values=''
            for key,value in x.items():
                keys=keys+key+','
                values = values + '"' + str(value) + '"' + ','
            values = values.strip(',')
            keys = keys.strip(',')
            sql = 'insert into {table}({keys}) values({values})'.format(table=table,keys=keys, values=values)
            print(sql)
            self.cursor.execute(sql)
        self.conn.commit()
    #删除操作
    def delete(self,table,where):
        try:
            sql = 'delete from {table} where {where}'.format(table=table, where=where)
            self.cursor.execute(sql)
            # 提交事务
            self.conn.commit()
            return self.cursor.rowcount
        except Exception as e:
            print(e)
            self.conn.rollback()
#更新
    def updata(self,table,where,data):
        try:
            mystr = ''
            for i, v in data.items():
                # print(i,v)
                mystr = mystr + '{i}="{v}"'.format(i=i, v=v) + ','
            mystr = mystr.strip(',')
            # print(mystr.strip(','))
            sql = 'update {table} set {data} where {where}'.format(table=table, data=mystr, where=where)
            print(sql)
            self.cursor.execute(sql)
            self.conn.commit()
            return True
        except Exception as e:
            print(e)
            self.conn.rollback()
        finally:
            self.conn.close()

    def __exit__(self,*args):#with结束调用方法
        self.__del__()
if __name__=='__main__':
    # with open('list.txt', 'r', encoding='utf-8')as f:
    #     h=json.load(f)
    with My_databa(pwd='***',name='ipdaili')as db:
        # db.insert('listip','h')
        db.delete('listip','id=3')
        db.updata('listip','id=30000',data={'ip':'0000000'})

数据库封装函数
import pymysql

#查询多条数据
def select_all(sql):
    try:
        #打开数据库链接
        db =pymysql.connect('localhost','root','123456','ai5_1',charset='utf8')
        #2.创建游标
        cursor =db.cursor(pymysql.cursors.DictCursor)
        #3使用游标操作sql
        cursor.execute(sql)
        #4使用游标取得结果
        res =cursor.fetchall()
        return res
    except Exception as e:
        print(e)
    finally:
        # 5.关闭数据库链接
        db.close()
#查询一条数据
def select_one(sql):
    try:
        #打开数据库链接
        db =pymysql.connect('localhost','root','123456','ai5_1',charset='utf8')
        #2.创建游标
        cursor =db.cursor(pymysql.cursors.DictCursor)
        #3使用游标操作sql
        cursor.execute(sql)
        #4使用游标取得结果
        res =cursor.fetchone()
        #5.关闭数据库链接
        db.close()
        return res
    except Exception as e:
        print(e)
    finally:
        db.close()
#插入操作，参数2个---1:表名  2：字典{字段名1:值1，字段名2:值2，}
def insert(table,data):
    try:
        # 打开数据库链接
        db =pymysql.connect('localhost','root','123456','ai5_1',charset='utf8')
        #创建游标
        cursor=db.cursor()
        #使用游标执行sql
        keys = ','.join(data.keys())
        values = ''
        for v in data.values():
            # values += str(v)+','
            values = values + '"'+str(v)+'"' + ','
        values=values.strip(',')
        sql='insert into {table}({keys}) values({values})'.format(table=table,keys=keys,values=values)
        cursor.execute(sql)
        #提交事务
        db.commit()
        return True
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        db.close()

#删除操作 delete from 表名 where 条件
def delete(table,where=0):
    try:
        # 打开数据库链接
        db = pymysql.connect('localhost', 'root', '123456', 'ai5_1', charset='utf8')
        # 创建游标
        cursor = db.cursor()
        # 使用游标执行sql
        sql='delete from {table} where {where}'.format(table=table,where=where)
        cursor.execute(sql)
         #提交事务
        db.commit()
        return cursor.rowcount
    except Exception as e:
        print(e)
        db.rollback()
    finally:
        db.close()

#修改操作：update 表名 set 字段名='值'，字段名2='值' where 条件
def update(table,data,where):
    try:
        #打开数据库
        #db=pymysql.connect(HOST,USER,PASSWORD,DB,PORT,charset=CHARSET)
        db = pymysql.connect('localhost', 'root', '123456', 'ai5_1', charset='utf8')
        #创建游标
        cursor= db.cursor()
        #使用游标执行操作
        #data={'age': 120}
        set =''
        for k,v in data.items():
           set = set +'%s = "%s" ,'%(k,v)
        set =set.strip(',')
        sql='update {table} set {set} where {where}'.format(table=table,set=set,where=where)
        print(sql)
        cursor.execute(sql)
        #提交操作
        db.commit()
    except Exception as e:
        db.rollback()
    finally:
        # 关闭数据库
        db.close()