def Insert(self, tabName, dataMap):
'''
实现插入数据功能
输入参数
tabName:表名
dataMap:更新字段、数据,可接收数据类型:dict
'''
if not dataMap:
print "Error, When call pyutil.db.mellow Insert tabName=", tabName, ",dataMap:", dataMap
return -1, -1
if isinstance(dataMap, dict):
dataMap = db_util.ParseUtime(
common_util.FilterMap(dataMap, self.Columns(tabName)))
names, values = [], []
for k, v in dataMap.items():
if v is None or v == "":
continue
names.append("`%s`" % (str(k)))
if isinstance(v, int) or isinstance(v, long):
values.append(str(v))
elif isinstance(v, str) or isinstance(v, unicode):
values.append("'" + v + "'")
elif isinstance(v, float):
values.append(str(v))
else:
print "Error unknowed type of val:", v, ",type=", type(
v), ",key=", k
return -1, -1
lang = "INSERT INTO `%s`(%s) VALUES(%s)" % (tabName, " , ".join(names),
",".join(values))
return self.magic.execute(lang, autocommit=True)
def Find(self,
tabName,
fieldList=[],
indexMap={},
limit=(),
orderby=((), "asc"),
out=list):
'''
实现查找数据功能
tabName:表名
fieldList:查询字段名称
不传递参数，则查询所有列
传递参数str,只查询一个字段，如fieldList='name',fieldList="phone_number as 'phone_no'"
传递参数list,查询一个或多个字段["user_id", "name AS uame"]
indexMap:查询条件
传递参数dict: fieldList = {'id': 1，"age": ("in",["18","20"])，"id_c": ("like",["13","55"])}
传递参数str: fieldList = "where id = 1"
传递参数list,tupple: fieldList = [[[id],[2]]]
limit:查询数据条数限制,如limit=(0,2)
orderby:排序方式,可接收数据类型:list,tupple
out:输出数据格式，支持list(默认),dict
'''
if isinstance(indexMap, dict):
indexMap = common_util.FilterMap(indexMap, self.Columns(tabName))
fieldsLang = Lang.Fields(fieldList)
whereLang = Lang.Where(indexMap)
limitLang = " LIMIT %s, %s" % (str(limit[0]), str(
limit[1])) if limit and len(limit) == 2 else ""
orderbyLang = Lang.OrderBy(orderby)
lang = "SELECT %s FROM %s %s %s %s" % (fieldsLang, tabName, whereLang,
orderbyLang, limitLang)
return self.__excute_and_parse_datetime(lang, out)
def UpOrInsert(self, tabName, dataMap, indexMap):
if isinstance(indexMap, dict):
indexMap = common_util.FilterMap(indexMap, self.Columns(tabName))
if self.Exist(tabName, indexMap):
return self.Update(tabName, dataMap, indexMap)
else:
return self.Insert(tabName, dataMap)
def Update(self, tabName, dataMap, indexMap):
'''
根据条件更新数据
tabName:表名
dataMap:更新的字段和更新数据
indexMap:更新条件,可接收数据类型:dict,str,list,tupple
'''
if not indexMap or not dataMap:
return None
columns = self.Columns(tabName)
if isinstance(dataMap, dict):
dataMap = common_util.FilterMap(dataMap, columns)
if isinstance(indexMap, dict):
indexMap = common_util.FilterMap(indexMap, columns)
lang = "UPDATE `%s` SET %s %s" % (tabName, Lang.Assign(dataMap),
Lang.Where(indexMap))
return self.magic.execute(lang, autocommit=True)
def Count(self, tabName, indexMap={}):
'''
查询符合条件的记录数
tabName:表名
indexMap:查询条件,可接收数据类型:dict(默认),str,list,tupple
'''
indexMap = common_util.FilterMap(indexMap, self.Columns(tabName))
lang = "SELECT count(1) FROM `%s` %s " % (tabName,
Lang.Where(indexMap))
rows = self.magic.query(lang)
return rows[0][0] if rows else 0
def Delete(self, tabName, indexMap={}):
if isinstance(indexMap, dict):
indexMap = common_util.FilterMap(indexMap, self.Columns(tabName))
url = self.__url(tabName, indexMap, "DELETE")
print "Delete, url:", url
result = http_util.Request(url, method="DELETE")
jsn = json.loads(result)
if jsn.get('code', 0) == 1:
return jsn.get('data', None)
else:
return None
def Update(self, tabName, dataMap, indexMap):
if isinstance(indexMap, dict):
indexMap = common_util.FilterMap(indexMap, self.Columns(tabName))
url = self.__url(tabName, indexMap, "PUT")
print "Update, uri=", url, ",tabName=", tabName, ",data:", json.dumps(
dataMap), "method:", "PUT"
result = http_util.Request(url, json.dumps(dataMap), method="PUT")
jsn = json.loads(result)
if jsn.get('code', 0) == 1:
return jsn.get('data')
else:
return None
def Max(self, tabName, field, indexMap={}):
'''
根据条件查找记录最大值
tabName:表名
field:查询字段名称
indexMap:查询条件,可接收数据类型:dict(默认),str,list,tupple
'''
indexMap = common_util.FilterMap(indexMap, self.Columns(tabName))
whereLang = Lang.Where(indexMap)
lang = "SELECT MAX(`%s`) FROM `%s` %s " % (field, tabName, whereLang)
rows = self.magic.query(lang)
if len(rows) > 0:
return rows[0][0]
else:
return None
def InsertOrNot(self, tabName, dataMap, indexMap):
'''
插入数据,如果数据存在,则不进行操作
tabName:表名
dataMap:要操作字段、数据,数据类型:dict
indexMap:限制条件,可接收数据类型:dict(默认),str,list,tupple
'''
if not indexMap or not dataMap or not isinstance(dataMap, dict):
return -1, -1
dataMap = db_util.ParseUtime(
common_util.FilterMap(dataMap, self.Columns(tabName)))
if self.Exist(tabName, indexMap):
return 0, 1
else:
return self.Insert(tabName, dataMap)
def UpOrInsert(self, tabName, dataMap, indexMap):
'''
插入数据,如果存在,则进行更新
tabName:表名
dataMap:要操作字段、数据,数据类型:dict
indexMap:限制条件,可接收数据类型:dict(默认),str,list,tupple
'''
if not indexMap or not dataMap or not isinstance(dataMap, dict):
return -1, -1
dataMap = db_util.ParseUtime(
common_util.FilterMap(dataMap, self.Columns(tabName)))
if self.Exist(tabName, indexMap):
lang = "UPDATE `%s` SET %s %s" % (tabName, Lang.Assign(dataMap),
Lang.Where(indexMap))
return self.magic.execute(lang, autocommit=True)
else:
return self.Insert(tabName, dataMap)