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 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