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