def query_for_str(self, query, args=None): result = self.execute_fetchone(query, args) if not result: return '' if self.dict_cursor: return get_string(result.popitem()[1]) else: return get_string(result[0])
def _reformat_ip_address(ip): try: ip_address = get_string(get_ip_address(ip)) ls = [] for _ia in ip_address.split('\t'): if _ia and _ia not in ls: ls.append(_ia) return ''.join(ls) except Exception, e: warn(15100, 'reformat_ip_address', '', ip, get_string(e)) return ''
def commit(self): try: self.conn.commit() return True except Exception as e: error(15002, 'commit_execute', get_string(e), '', format_exc()) return False
def update_many_rowcount(self, query, args=None): try: return self.executemany_rowcount(query, args) except Exception as e: error(15006, 'execute_update', query, sequence_to_string(args), get_string(e)) return 0
def insert_many(self, query, args=None): try: return self.executemany_rowcount(query, args) except Exception as e: error(15005, 'execute_insert', query, sequence_to_string(args), get_string(e)) return 0
def delete(self, query, args=None): try: self.execute(query, args) return self.cursor.rowcount except Exception as e: error(15007, 'execute_delete', query, sequence_to_string(args), get_string(e)) return 0
def update_many(self, query, args=None): try: self.executemany(query, args) return True except Exception as e: error(15006, 'execute_update', query, sequence_to_string(args), get_string(e)) return False
def execute_fetchone(self, query, args=None): try: self.execute(query, args) return self.fetchone() except Exception as e: error(15000, 'execute_fetchone', query, sequence_to_string(args), get_string(e)) return None
def close(self): try: if getattr(self, 'cursor', None) is not None: self.cursor.close() self.cursor = None if getattr(self, 'conn', None) is not None: self.conn.close() self.conn = None except Exception as e: error(15004, 'close_db_connection', get_string(e), '', format_exc())
def add_ip(ip, user_id=''): result = False with db_conn_guard(dict_cursor=True) as conn: sql, args = '', () try: sql = 'SELECT * FROM t_log_ip_user WHERE user_id=%s AND ip=%s' ip_user = conn.execute_fetchone(sql, (user_id, ip)) if ip_user: log_id = ip_user.get('log_id') ip_address = ip_user.get('ip_address') sql = ''' UPDATE t_log_ip_user SET count=count+1{0} WHERE log_id=%s '''.format('' if ip_address else ', ip_address=%s') args = log_id if ip_address else (_reformat_ip_address(ip), log_id) else: sql = ''' INSERT INTO t_log_ip_user (user_id, ip, ip_address, count) VALUES (%s, %s, %s, 1) ''' args = (user_id, ip, _reformat_ip_address(ip)) count = conn.execute_rowcount(sql, args) if count: sql = 'SELECT * FROM t_log_ip_count WHERE ip=%s' ip_count = conn.execute_fetchone(sql, ip) if ip_count: ip_address = ip_count.get('ip_address') sql = ''' UPDATE t_log_ip_count SET count=count+1{0} WHERE ip=%s '''.format('' if ip_address else ', ip_address=%s') args = ip if ip_address else (_reformat_ip_address(ip), ip) else: sql = ''' INSERT INTO t_log_ip_count (ip, ip_address, count) VALUES (%s, %s, 1) ''' args = (ip, _reformat_ip_address(ip)) count = conn.execute_rowcount(sql, args) if count: result = conn.commit() except Exception, e: warn(15101, 'add_ip_log', sql, 'ip=%s, user_id=%s' % (ip, user_id), get_string(e)) conn.rollback() print e
def db_query_for_str(sql, args=None, default='', tb_name='', query_id=None, time_min=None, time_max=None): is_sharding, table_list = get_table_name_list(tb_name, query_id, time_min, time_max) if is_sharding and not table_list: return default with db_conn_guard() as conn: if table_list: result = '' for tn in table_list: query_sql = sql.replace('$$tb_name$$', tn) result = conn.query_for_str(query_sql, args) if result: break else: result = conn.query_for_str(sql, args) return get_string(result, default) or default
def get_table_name_list(tb_name, query_id=None, time_min=None, time_max=None): table_name_list = [] if not tb_name: return False, table_name_list query_id = get_int(query_id) int_time_min = get_int(time_min) int_time_max = get_int(time_max) if int_time_max and int_time_min > int_time_max: return True, table_name_list tb_name = tb_name.lower() tb_setting = SHARDING_TABLES.get(tb_name, {}) if tb_setting: redis_key = R_KEY_SEPARATE.join([R_SHARDING_TB, tb_name]) redis_value = get_cache(redis_key, 24*60*60) primary_key = tb_setting.get('primary_key') time_field = tb_setting.get('field') if not redis_value: # 如果redis中没有分表的信息,生成分表信息,并存放到redis redis_value = [] day_separate = tb_setting.get('day_separate', []) separate_len = len(day_separate) count_sql = ''' SELECT MIN({0}) AS id_min, MAX({0}) AS id_max, MIN({1}) AS time_min, MAX({1}) AS time_max FROM %s '''.format(primary_key, time_field) with db_conn_guard(dict_cursor=True) as conn: for i in xrange(separate_len): from_days = day_separate[i] query_tb = '%s_%s' % (tb_name, from_days) if i > 0 else tb_name tb_value = conn.execute_fetchone(count_sql % query_tb) if not tb_value or not tb_value.get('time_min'): tb_value = {} if not tb_value.get('time_min'): if i == 0: begin_time = datetime.now() - timedelta(days=from_days-1) tb_value = { 'id_min': 0, 'id_max': 0, 'time_min': get_date_string(begin_time) + '000000', 'time_max': get_date_string() + '235959' } else: tb_value = {} elif i > 0 and not redis_value[0].get('id_min') and tb_value.get('id_max'): redis_value[0]['id_min'] = get_int(tb_value.get('id_max')) + 1 tb_value['table_name'] = query_tb redis_value.append(tb_value) set_cache(redis_key, redis_value, 24*60*60) if i == (separate_len - 1): query_tb_1 = '%s_%s' % (tb_name, 'history') tb_value_1 = conn.execute_fetchone(count_sql % query_tb_1) if not tb_value_1: tb_value_1 = {} tb_value_1['table_name'] = query_tb_1 redis_value.append(tb_value_1) set_cache(redis_key, redis_value, 24*60*60) # 从redis的分表信息中查找符合查询条件的表名 redis_len = len(redis_value) for i in xrange(redis_len): rv = redis_value[i] tn = get_string(rv.get('table_name')) if query_id: # 根据编号查询 id_min = get_int(rv.get('id_min')) id_max = get_int(rv.get('id_max')) if (i == 0 and query_id >= id_min) or (id_min <= query_id <= id_max): table_name_list = [tn] break elif int_time_min or int_time_max: # 根据时间查询 tb_time_min = get_int(rv.get('time_min')) tb_time_max = get_int(rv.get('time_max')) if tb_time_min: if tn == tb_name: # 如果是热表且时间大于热表最小时间,则需要查询热表 if tb_time_min and (tb_time_min <= int_time_min or tb_time_min <= int_time_max): table_name_list.append(tn) else: if tb_time_min <= int_time_max <= tb_time_max: table_name_list.append(tn) if not int_time_min: break elif tb_time_min <= int_time_min <= tb_time_max: table_name_list.append(tn) break elif tb_time_max < int_time_min: break elif tb_time_min <= int_time_max and int_time_min: table_name_list.append(tn) else: table_name_list.append(tn) return True, table_name_list return False, [tb_name]
def rollback(self): try: self.conn.rollback() except Exception as e: print e error(15003, 'rollback_execute', get_string(e), '', format_exc())