代码拉取完成,页面将自动刷新
''' 二次封装pymsql '''
import pymysql
config = {
'host':"192.168.19.185",
'port':3306,
'user':"root",
'passwd':"mg123456",
'database':"game_config", #设置要操作的数据库
"charset":"utf8", #设置字符集 支持中文
#'unix_socket':"/tmp/mysql.sock", #unix系统下的设置
#'db':"mysql",
#'db':"mysql",
}
class MySql():
''' 封装pymsql实现增删查改 '''
def __init__(self,**kwargs):
self.isLog = False #是否打印sql语句
self.conn = pymysql.connect(**kwargs) #链接数据库
self.cur = self.conn.cursor() #获取当前游标
def execute(self,sql):
''' 执行一条sql语句 '''
self.__logSql('execute:',sql)
self.cur.execute(sql)
datas = self.cur.fetchall()
return datas
def use_db(self,dbName):
return self.execute("use "+dbName)
def insert(self,table,**kwargs):
''' 只能插入一条数据 '''
fields = ""
values = ""
for k,v in kwargs.items():
fields += k+","
values += "'"+str(v)+"',"
fields = fields[:-1]
values = values[:-1]
sql = "insert into "+table +" ("+fields+") values ("+values+")"
return self.__insert(sql)
def insertMultiple(self,table,fields,values):
'''
可以插入多条或一条数据
table:表名
fields:字典名 list或者字符串
values:对应要插入的数据 list(插入多条是二维数组)或者字符串
'''
if len(fields)==0 or len(values)==0:
raise ValueError(" fields or values can't empty")
fs = self.__fmtInsertVal(fields,False) if isinstance(fields,list) else fields
if isinstance(values,list):
valStr=""
if isinstance(values[0],list):
values = [self.__fmtInsertVal(val) for val in values]
valStr = ",".join(values)
else:
valStr = self.__fmtInsertVal(values)
elif isinstance(values,str):
valStr= values
else:
raise ValueError("values must be str or list")
sql = "insert into "+table + " " + fs + " values "+valStr
'''
self.__logSql("insertMultiple sql:",sql)
ret = self.cur.execute(sql)
self.conn.commit()
'''
return self.__insert(sql)
def delete(self,table,conditions):
''' 删除数据 table:表名;conditions:字符串或者dict '''
_,cs = self.__fmtFieldCondition(None,conditions)
sql = "delete from "+table + " where "+cs
self.__logSql("delete sql:",sql)
ret = self.cur.execute(sql)
self.conn.commit()
return ret
def selectOne(self,table,fields,conditions):
self.__select(table,fields,conditions)
return self.cur.fetchone()
def selectAll(self,table,fields,conditions):
self.__select(table,fields,conditions)
return self.cur.fetchall()
def update(self,table,valueKw,conditions):
'''
更新数据库
valueKw 要更新的字段键值对字典或者直接字符串
conditions 查询条件 字典或者字符串
'''
_,kws = self.__fmtFieldCondition(None,valueKw)
_,cds = self.__fmtFieldCondition(None,conditions)
sql = "update "+table+" set "+kws+" where "+cds
self.__logSql("update sql:",sql)
ret = self.cur.execute(sql)
self.conn.commit()
return ret
def __fmtInsertVal(self,values,isQuo=True):
values = [ "'"+str(val)+"'" for val in values]
valStr = ",".join(values)
valStr = "("+valStr+")"
if not isQuo:
valStr = valStr.replace("'","")
return valStr
def __insert(self,sql):
self.__logSql("__insert:",sql)
ret = self.cur.execute(sql)
self.conn.commit()
return ret
def __select(self,table,fields,conditions):
fs,cs = self.__fmtFieldCondition(fields,conditions)
sql = "select "+fs+" from "+table + " where "+cs
self.__logSql("__select sql:",sql)
self.cur.execute(sql)
def __fmtFieldCondition(self,fields,conditions):
'''
格式化字段和条件
'''
fieldStr = ""
condiStr = ""
if fields:
if isinstance(fields,list):
fieldStr = ",".join(fields)
elif isinstance(fields,str):
fieldStr = fields
if conditions:
if isinstance(conditions,dict):
for k,v in conditions.items():
condiStr+=str(k) + "='"+str(v)+"',"
condiStr = condiStr[:-1]
if isinstance(conditions,str):
condiStr = conditions
return fieldStr,condiStr
def __logSql(self,*arg):
''' 打印sql日志 '''
if self.isLog:
print(*arg)
def close(self):
self.conn.close()
self.cur.close()
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。