1 Star 0 Fork 0

kongfu/py3Utils

加入 Gitee
与超过 1200万 开发者一起发现、参与优秀开源项目,私有仓库也完全免费 :)
免费加入
文件
该仓库未声明开源许可证文件(LICENSE),使用请关注具体项目描述及其代码上游依赖。
克隆/下载
mysql.py 5.07 KB
一键复制 编辑 原始数据 按行查看 历史
kongfu 提交于 2019-06-06 15:53 . 练习代码更新
''' 二次封装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()
Loading...
马建仓 AI 助手
尝试更多
代码解读
代码找茬
代码优化
Python
1
https://gitee.com/kongfu/py3Utils.git
git@gitee.com:kongfu/py3Utils.git
kongfu
py3Utils
py3Utils
master

搜索帮助