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 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={}):
'''
删除指定数据
tabName:表名
indexMap:限制条件,可接收数据类型:dict(默认),str,list,tupple
'''
if not indexMap:
print "Error!!! Dangrous, Must not delete empty indexMap, tabName:", tabName
return
lang = "DELETE FROM `%s` %s" % (tabName, Lang.Where(indexMap))
return self.magic.execute(lang, autocommit=True)
def Exist(self, tabName, indexMap):
'''
判断指定条件的数据是否存在
tabName:表名
indexMap:限制条件,可接收数据类型:dict,str,list,tupple
'''
if not indexMap:
return False
lang = "SELECT count(1) FROM `%s` %s " % (tabName,
Lang.Where(indexMap))
rows = self.magic.query(lang)
return True if len(rows) > 0 and int(rows[0][0]) > 0 else False
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 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)
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)