封装mysql数据库_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()