import MySQLdb
class sqlconn(object):
#定义数据库连接的类
def __init__(self,host,user,passwd,db):
self.host=host;
self.user=user;
self.passwd=passwd;
self.db=db
def conn(self):
#定义数据库连接的方法
try:
conn=MySQLdb.connect(host='%s'%self.host,
user='%s'%self.user,
passwd='%s'%self.passwd,
db='%s'%self.db)
cursor = conn.cursor()
return cursor,conn
# 返回数据库连接的状态
except Exception as e:
print(e)
class sql_method(sqlconn):
#定义数据库的方法类
def __init__(self,host,user,passwd,db):
sqlconn.__init__(self,host,user,passwd,db)
def select(self,key_list,table,**kwargs):
#定义数据库基本查询的函数，其中需要传入两个参数，key_list需要以列表的方式将要查询的列输入，table为要进行查询的表。kwargs为约束条件，可以选择性输入。
cursor,conn=sqlconn.conn(self)
#进行数据库连接
s_list=""
#定义空字符串，其作用是将列表转换为字符串，该字符串是数据库查询的基本的索引
for index,i in enumerate(key_list):
#将列表重新组合为字符串
if len(key_list)==1:
s_list=i
else:
if index ==0:
s_list=i
else:
s_list=s_list+","+i
s_dict=""
j=0
while j < len(kwargs):
for key,value in kwargs.items():
if j == 0:
if isinstance (value,int) :
s_dict="where %s=%s"%(key,value)
else:
s_dict="where %s='%s'"%(key,value)
else:
if isinstance (value,int):
s_dict=s_dict + " and " +"%s=%s"%(key,value)
else:
s_dict=s_dict + " and " +"%s='%s'"%(key,value)
j+=1
sql = '''
select %s from %s %s
'''% (s_list,table,s_dict)
#定义数据库基本查询语句
try:
cursor.execute(sql)
results = cursor.fetchall()
# 获取数据库查询的结果
if len(results)==0:
#如果输出结果长度是0，则表示没有查询到东西
print("Empty set")
else:
for row in results:
print(row)
#按行输出结果
except:
print("unable to fetch data")
#如果语法错误，则输出无法查询到数据。
def create(self,table,**kwargs):
#定义新增数据库表的函数
cursor, conn = sqlconn.conn(self)
sql_show = "SHOW TABLES LIKE '%{table}%'".format(table=table)
#确认将要创建的数据库是否存在的查询语句
cursor.execute(sql_show)
res = cursor.fetchall()
if len(res)==0:
#如果为0，则代表不存在，可以创建
s=""
#s代表后续要创建的索引
i = 0
while i< len(kwargs):
#将要创建的索引进行拼接
for key, value in kwargs.items():
if i==0:
s=key + " "+value
else:
s=s+","+key+" "+value
i+=1
sql_create = '''create table %s (%s)''' % (table, s)
#定义创建表的语句
try:
cursor.execute(sql_create)
#创建表
conn.commit()
except Exception as e:
#报错打印并回滚
print(e)
conn.rollback()
else:
# 如果为其他值，则代表要创建的表存在，并打印
print("%s has been existed!" % table)
def insert(self,table,*args):
#表插入数据，其中args需要使用列表，args需要使用列表方式，每一组数据使用一个列表
cursor, conn = sqlconn.conn(self)
s=""
#定义要插入的数据
for index,key in enumerate(args):
#要将插入的数据进行拼接，需要注意的是，我们将插入的列表先转成成元组，再将元组转换为字符串进行拼接
if index==0:
s=str(tuple(key))
else:
s=s+","+str(tuple(key))
sql_insert="insert into %s values %s"%(table,s)
#定义数据库注入的语句
try:
cursor.execute(sql_insert)
# 数据进行插入
conn.commit()
except Exception as e:
# 报错打印并回滚
print(e)
conn.rollback()
def truncate(self,table):
#清空表,初始化表
cursor, conn = sqlconn.conn(self)
sql_truncate = "truncate table %s"%table
try:
cursor.execute(sql_truncate)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def del_data(self,table,**kwargs):
#删除表中内容，目前只能单条删除
cursor, conn = sqlconn.conn(self)
i=0
s=""
# #将获取到的字典值进行拼接，作为sql删除语句中的变量
while i < len(kwargs):
for key,value in kwargs.items():
if i == 0:
if isinstance (value,int) :
s="where %s=%s"%(key,value)
else:
s="where %s='%s'"%(key,value)
else:
if isinstance (value,int):
s=s + " and " +"%s=%s"%(key,value)
else:
s=s + " and " +"%s='%s'"%(key,value)
i+=1
sql_delete = "delete from %s %s" % (table,s)
try:
cursor.execute(sql_delete)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def add_key(self,table,id):
# 添加主键
cursor, conn = sqlconn.conn(self)
try:
sql_add_key="Alter table %s add primary key(%s)"%(table,id)
cursor.execute(sql_add_key)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def drop_key(self, table):
# 删除主键
cursor, conn = sqlconn.conn(self)
try:
sql_drop_key = "Alter table %s drop primary key" % table
cursor.execute(sql_drop_key)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def update(self,table,field,field_value,**kwargs):
# 更新值
cursor, conn = sqlconn.conn(self)
i = 0
s = ""
# #将获取到的字典值进行拼接，作为sql更新语句中的变量
while i < len(kwargs):
for key, value in kwargs.items():
if i == 0:
if isinstance(value, int):
s = "where %s=%s" % (key, value)
else:
s = "where %s='%s'" % (key, value)
else:
if isinstance(value, int):
s = s + " and " + "%s=%s" % (key, value)
else:
s = s + " and " + "%s='%s'" % (key, value)
i += 1
sql_drop_key = "update %s set %s='%s' %s" % (table, field, field_value, s)
try:
cursor.execute(sql_drop_key)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def add_column(self,table,column,type):
#增加列
cursor, conn = sqlconn.conn(self)
try:
sql_add_colum="Alter table %s add %s %s"%(table,column,type)
cursor.execute(sql_add_colum)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def del_column(self,table,column):
#删除列
cursor, conn = sqlconn.conn(self)
try:
sql_del_colum = "Alter table %s drop %s " % (table, column)
cursor.execute(sql_del_colum)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def copy_table(self,tab_new,tab_old):
# 根据已有的表创建新表
cursor, conn = sqlconn.conn(self)
try:
sql_copy_table = "create table %s like %s " % (tab_new, tab_old)
cursor.execute(sql_copy_table)
conn.commit()
except Exception as e:
print(e)
conn.rollback()
def multi_table_select(self,left_table,right_table,key_value,**kwargs):
# 多表查询
cursor, conn = sqlconn.conn(self)
s=""
i=0
while i < len(kwargs):
for key,value in kwargs.items():
if i == 0:
if isinstance (value,int) :
s="where %s=%s"%(key,value)
else:
s="where %s='%s'"%(key,value)
else:
if isinstance (value,int):
s=s + " and " +"%s=%s"%(key,value)
else:
s=s + " and " +"%s='%s'"%(key,value)
i+=1
try:
sql_copy_table = "select * from %s join %s using(%s) %s " % (left_table, right_table,key_value,s)
cursor.execute(sql_copy_table)
res=cursor.fetchall()
if len(res)==0:
print("Empty set")
else:
for row in res:
print(row)
except Exception as e:
print(e)
conn.rollback()
调用方法：
首先需要将连接的数据库实例化：
s2=sql_method(‘192.168.163.129’,‘zgy’,‘zgy’,‘device’)
实例化完成之后，再进行调用
s2.select([’*’],‘tianjin’,id=1,ip=‘10.0.23.66’)
s2.create(“wuhu”,id=‘int’,dev_name=‘varchar(50)’,ip=‘varchar(15)’)
s2.insert(‘wuhu’,[4,‘RT4’,‘4.1.1.1’])
s2.truncate(‘wuhu’)
s2.delete(‘wuhu’,id=1)
s2.add_key(‘wuhan’,‘id’)
s2.drop_key(‘wuhan’)
s2.update(‘wuhu’,‘dev_name’,‘RT11’,id=3,ip=‘1.1.1.3’)
s2.add_column(‘wuhu’,‘mac’,‘varchar(15)’)
s2.del_column(‘wuhu’,‘mac’)
s2.copy_table(‘xinjiang’,‘wuhu’)
s2.multi_table_select(‘wuhu’,‘lldp’,‘id’,ip=‘1.1.1.4’)
4768
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
