def search_poetry_widget(self,
keyword,
page=1,
count=20,
fields=None,
sort=None):
skip = (page - 1) * count
if skip < 0:
skip = 0
if not fields:
fields = 'id,title,content,author,dynasty,likes,banner'
else:
fields = ",".join(fields)
if sort:
sql_str = u"select {} from "\
u"poetry where match(title,content,author) against(\"{}\") "\
u"order by likes desc limit {},{}".\
format(fields, keyword, skip, count)
else:
sql_str = u"select {} from "\
u"poetry where match(title,content,author) against(\"{}\") "\
u"limit {},{}".\
format(fields, keyword, skip, count)
sql_str = sql_str.encode("utf-8")
ret = self.execute(sql_str)
LOGGER.info("sql: %s, ret: %s", sql_str, ret)
if not ret:
return []
return self.cur.fetchall()
def group_by(self, table, condition, group_by_fields, fields, page, count):
where = self.dict2cond(condition)
select_fields = ','.join(fields)
group_fields = self.list2fields(group_by_fields)
skip = (page - 1) * count
if condition:
sql_str = 'select {select_fields} from {table} where {condition} '\
'group by {group_fields} limit {start},{count}'.format(
select_fields=select_fields,
table=table,
condition=where,
group_fields=group_fields,
start=skip,
count=count)
else:
sql_str = 'select {select_fields} from {table} '\
'group by {group_fields} limit {start},{count}'.format(
select_fields=select_fields,
table=table,
group_fields=group_fields,
start=skip,
count=count)
LOGGER.info(sql_str)
try:
ret = self.execute(sql_str)
if not ret:
return []
return self.cur.fetchall()
except Exception as e:
LOGGER.error("ex: %s", e, exc_info=True)
return []
def insert_batch(self, table, datas, update=True):
'''
save data batch;
'''
fields = ''
values_list = []
if not datas or not isinstance(datas, list):
return False, "data is null or data format error"
keys = datas[0].keys()
for data in datas:
if not fields:
fields = self.list2fields(keys)
tmp = []
for field in keys:
tmp.append(data.get(field))
values_list.append(tmp)
values = ''
for i in range(len(keys)):
if values:
values = '%s, %s' % (values, '%s')
else:
values = '%s'
sql = 'insert into %s(%s) values(%s)' % (table, fields, values)
LOGGER.info("insert_batch:%s", sql)
ret = self.executemany(sql, values_list, update)
return ret, "OK"
def update_mutex(self, table, cond, data, insert=True):
'''
lock the data befor update;
if the data doesnot exists, insert it or not according to the insert;
'''
self.disconnect()
self.get_con()
where = self.dict2cond(cond)
LOGGER.info(where)
sql_stat = "select * from %s where %s for update" % (table, where)
succ = False
msg = ''
try:
ori_data = self.execute(sql_stat)
LOGGER.info(ori_data)
if not ori_data:
msg = self.insert(table, data)
succ = True
else:
msg = self.update(table, cond, data)
self.con.commit()
self.disconnect()
except Exception as ex:
msg = "ex: %s" % ex
return succ, msg
def save(self):
ori_content = self.find_by_content()
if ori_content:
return ori_content['id']
self._data.pop("id", '')
LOG.debug("save centence: %s", self.content)
return self._save(self._data)
def disabled_goods_by_id(self):
assert self.num_id
cond = {'num_id': self.num_id}
update_data = {'coupon_expire': 1}
LOG.info("disabled goods: %s", self.num_id)
searcher.delete_index(self.num_id)
return self._delete(cond)
def get_one(self, table, cond={}, fields=['*'], sort={}):
self.disconnect()
self.get_con()
field_str = self.list2fields(fields)
where = self.dict2cond(cond)
if where:
sql_str = 'select %s from `%s` where %s'\
% (field_str, table, where)
else:
sql_str = 'select %s from `%s`' % (field_str, table)
LOGGER.info("get_one: %s", sql_str)
if sort:
sort_sql = ''
sort_key = sort.keys()[0]
reverse = sort.values()[0]
if reverse >= 1:
sort_sql = 'order by %s ASC limit 1' % sort_key
else:
sort_sql = 'order by %s DESC limit 1' % sort_key
sql_str = '%s %s' % (sql_str, sort_sql)
ret = self.execute(sql_str, True)
if not ret:
self.disconnect()
return {}
return self.cur.fetchone()
def increase(self, table, condition, field='times', count=1):
where = self.dict2cond(condition)
sql_str = 'update `%s` set `%s`=`%s`+%s where %s'\
% (table, field, field, count, where)
LOGGER.info(sql_str)
ret = self.execute(sql_str, True)
if not ret:
return False
return True
def increaseMultiMutex(self,
table,
cond,
field,
count=1,
greater=0,
check=0,
data={},
uniqKey=""):
self.disconnect()
self.get_con()
where = self.dict2cond(cond)
if not where or not uniqKey:
return False, "cond is null or uniqKey is null"
sql_str = "select * from %s where %s for update" % (table, where)
LOGGER.info('increaseMultiMutex: %s' % sql_str)
try:
ret = self.execute(sql_str)
if not ret:
self.return_error()
return False, 'unknow Error execute:%s!' % sql_str
records = self.cur.fetchall()
for record in records:
original = record.get(field)
try:
original = long(original)
except ValueError:
self.return_error()
return False, '%s type is not int' % field
comp = original + count
if comp <= 0:
count = comp
# if check and comp < greater:
# self.return_error()
# return False, '%d is less than %d' % (original, -count)
update_count = 0 if comp < 0 else comp
if data:
data.update({field: update_count})
else:
data = {field: comp}
uniq = record.get(uniqKey)
update_cond = {uniqKey: uniq}
ret = self.update(table, update_cond, data, False)
if not ret:
self.return_error()
return False, 'unknow Error execute:%s' % 'update'
if comp >= 0:
break
self.con.commit()
self.disconnect()
return True, record
except Exception as ex:
# self.con.rollback()
self.disconnect()
return False, ex
def save_hint_data(self):
data = {}
LOG.info(self._data)
assert self.word and self.index is not None and self.openid \
and self.level
data['word'] = self.word
data['openid'] = self.openid
data['level'] = self.level
data['index'] = self.index
data['created'] = int(time.time() * 1000)
return self._save(data)
def delete(self, table, cond):
where = self.dict2cond(cond)
if where:
sql_str = 'delete from `{}` where {}'.format(table, where)
else:
return False
LOGGER.info("delete: %s", sql_str)
ret = self.execute(sql_str, True)
if not ret:
return False
return ret
def update(self, table, cond, data, update=True):
where = self.dict2cond(cond)
set_data = self.dict2cond(cond=data, data=1)
if where:
sql_str = 'update `%s` set %s where %s' % (table, set_data, where)
else:
sql_str = 'update `%s` set %s' % (table, set_data)
LOGGER.info("update: %s" % sql_str)
ret = self.execute(sql_str, update)
if not ret:
return False
return ret
def match_data(self, table, condition, fields, page, count):
if not fields:
fields = ['*']
field_str = self.list2fields(fields)
where = self.dict2search(condition)
sql_str = 'select %s from `%s` where %s order by likes desc '\
'limit %s, %s' % (
field_str, table, where, (page - 1) * count, count)
LOGGER.info(sql_str)
ret = self.execute(sql_str)
if not ret:
return []
return self.cur.fetchall()
def increaseMutex(self,
table,
cond,
field,
count=1,
greater=0,
check=0,
data={}):
self.disconnect()
self.get_con()
where = self.dict2cond(cond)
if where:
sql_str = "select * from %s where %s for update" % (table, where)
else:
sql_str = "select * from %s for update" % table
LOGGER.info('increaseMutex: %s' % sql_str)
try:
ret = self.execute(sql_str)
if not ret:
self.return_error()
return False, 'unknow Error execute:%s!' % sql_str
record = self.cur.fetchone()
if not record:
# self.con.rollback()
self.return_error()
return False, 'Not Found Record %s' % where
original = record.get(field)
try:
original = long(original)
except Exception as e:
self.return_errro()
return False, '%s type is not int' % field
comp = original + count
if check and comp < greater:
self.return_error()
return False, '%d is less than %d' % (original, -count)
if data:
data.update({field: comp})
else:
data = {field: comp}
ret = self.update(table, cond, data, False)
if not ret:
self.return_error()
return False, 'unknow Error execute:%s' % 'update'
self.con.commit()
self.disconnect()
return True, record
except Exception as e:
# self.con.rollback()
self.disconnect()
return False, e
def save(self):
assert self.num_id and self.coupon_total_count is not None and self.price and\
self.title and self.coupon_amount is not None
old_goods = self.find_goods_by_id()
if old_goods:
ret = old_goods['_id']
update_data = self._get_update_data(old_goods)
update_data.update({
'update_time': int(time.time() * 1000),
'coupon_expire': 0
})
ret = self.update(update_data)
LOG.info("update goods: %s, data: %s, ret: %s", self.num_id,
update_data, ret)
else:
ret = self._save(self._data)
LOG.info("save goods: %s, id: %s", ret, self.num_id)
return str(ret)
def insert(self, table, data, update=True):
SQL_FORMAT = "insert into `%s` (%s) values (%s)"
keys = data.keys()
values = data.values()
field_str = self.list2fields(keys)
value_str = self.list2values(values)
sql_str = SQL_FORMAT % (table, field_str, value_str)
LOGGER.info("insert: %s" % sql_str)
try:
ret = self.execute(sql_str, update)
if not ret:
return False, "unknow Error"
if update:
self.con.commit()
return ret, "OK"
except Exception as e:
LOGGER.error("ex: %s", e, exc_info=True)
return False, e
def transaction(self, **kwargs):
self.disconnect()
self.get_con()
result = True
msg = ''
try:
for table, data in kwargs.items():
if isinstance(data, list):
successed, msg = self.insert_batch(table,
data,
update=False)
if not successed:
result = False
msg = 'insert into %s error: %s' % (table, msg)
break
else:
msg += 'save to %s, %d records' % (table, successed)
else:
successed, msg = self.insert(table, data, update=False)
if not successed:
result = False
msg = 'insert into %s error: %s' % (table, msg)
break
else:
msg += 'save to %s, %d records' % (table, successed)
if result:
LOGGER.info('commit')
self.con.commit()
else:
self.con.rollback()
except Exception as e:
self.con.rollback()
result = False
msg = "Exception happened: %s" % e
finally:
self.disconnect()
return result, msg
def get(self, table, cond={}, page=1, count=20, fields=['*'], sort={}):
field_str = self.list2fields(fields)
where = self.dict2cond(cond)
if where:
sql_str = 'select %s from `%s` where %s'\
% (field_str, table, where)
else:
sql_str = 'select %s from `%s`' % (field_str, table)
if sort:
sort_sql = ''
sort_key = sort.keys()[0]
reverse = sort.values()[0]
if reverse >= 1:
sort_sql = 'order by %s ASC' % sort_key
else:
sort_sql = 'order by %s DESC' % sort_key
sql_str = '%s %s' % (sql_str, sort_sql)
skip = (page - 1) * count
sql_str += ' limit %s, %s' % (skip, count)
LOGGER.info(sql_str)
ret = self.execute(sql_str, True)
if not ret:
return []
return self.cur.fetchall()
def updateMutexCheck(self, table, cond, data, status, fields=['*']):
'''
update the record with data according to the original value
of status with mutex.
parameters:
table <---> table name.
cond <---> query condition.
data <---> update data.
status<---> check the status, if not match, then did not update.
'''
self.disconnect()
self.get_con()
SQL_STR = "select %s from `%s` where %s for update"
where = self.dict2cond(cond)
fields_str = self.list2fields(data.keys())
# values_str = self.list2values(data.values())
sql_str = SQL_STR % (fields_str, table, where)
LOGGER.info("updateMutexCheck: %s" % sql_str)
result = False
msg = ''
try:
ret = self.execute(sql_str)
record = self.cur.fetchone()
if not record:
self.con.rollback()
result = False
msg = "Not Found Record By %s" % where
else:
flag = 1
for check_key, check_value in status.items():
value = record.get(check_key)
if isinstance(check_value, (int, str, unicode)):
if value != check_value:
flag = 0
break
elif isinstance(check_value, list):
if value not in check_value:
flag = 0
elif isinstance(check_value, dict):
for op, c_value in check_value.items():
express = "%s %s %s" % (value, op, c_value)
if not eval(express):
flag = 0
break
else:
flag = 0
if not flag:
break
if flag == 0:
self.con.rollback()
result = False
msg = "status check did not match"
else:
ret = self.update(table, cond, data)
self.con.commit()
result = ret
msg = "OK"
except Exception as ex:
self.con.rollback()
result = False
msg = ex
finally:
self.disconnect()
return result, msg
