def get(self):
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
sql = "select id,data1,data2,data3,data4,created_time from table_1 where created_time + interval 10 second > now()"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
if len(result['data']) == 0:
raise Exception('No data in recent 10 seconds.')
data_list = []
for item in result['data']:
tmp = dict()
tmp['Id'] = item['id']
tmp['Data1'] = item['data1']
tmp['Data2'] = item['data2']
tmp['Data3'] = item['data3']
tmp['Data4'] = item['data4']
tmp['CreatedTime'] = item['created_time']
data_list.append(tmp)
return {'DataList': data_list}
def put(self):
# param check
parser = reqparse.RequestParser()
parser.add_argument('DataIds', required=True)
args = parser.parse_args()
data_ids = args.get('DataIds').strip()
data_id_list = data_ids.split(',')
if len(data_id_list) == 0:
raise Exception('Invalid DataIds')
for data_id in data_id_list:
if not data_id.isdigit():
raise Exception('Invalid DataIds')
# update the flag to 1, which means important
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
data_ids = args.get('DataIds')
sql = "update table_1 set flag = 1 where id in ({}) and flag = 0".format(
data_ids)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
return {'DataIds': data_ids}
def copy_important_data():
# connect
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
return
# detect data to copy
sql = "select id from table_1 where flag = 1 order by id desc limit 1"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
return
if len(result['data']) == 0:
return
max_important_id_1 = result['data'][0]['id']
sql = "select original_id from table_2 order by original_id desc limit 1"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
return
if len(result['data']) == 0:
max_important_id_2 = 0
else:
max_important_id_2 = result['data'][0]['original_id']
if max_important_id_1 == max_important_id_2:
return
elif max_important_id_1 < max_important_id_2:
Log.error('table 1 data is abnormal.')
return
else:
# start to copy
sql = "select * from table_1 where id > {id} and flag = 1".format(
id=max_important_id_2)
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
return
# insert data
value_list = []
for item in result['data']:
value = "({id},{data1},{data2},{data3},{data4},{flag},'{created_time}',now())".format(
id=item['id'],
data1=item['data1'],
data2=item['data2'],
data3=item['data3'],
data4=item['data4'],
flag=item['flag'],
created_time=item['created_time'])
value_list.append(value)
# if the data is large, segmentation is required
sql = "insert into table_2(original_id,data1,data2,data3,data4,flag,original_created_time,created_time) values" + ','.join(
value_list)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])
def get(self):
parser = reqparse.RequestParser()
parser.add_argument('PageSize', type=int)
parser.add_argument('PageNo', type=int)
args = parser.parse_args()
page_size = args.get('PageSize')
page_no = args.get('PageNo')
if page_size is None:
page_size = 100
elif page_size <= 0:
raise Exception('Invalid PageSize')
if page_no is None:
page_no = 1
elif page_no <= 0:
raise Exception('Invalid PageNo')
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
sql = "select count(*) from table_2"
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
total_records = result['data'][0]['count(*)']
offset = (page_no - 1) * page_size
sql = "select data1,data2,data3,data4,original_created_time,original_id from table_2 order by id desc " \
"limit {0},{1}".format(offset, page_size)
result = mysql.read(sql)
if result['code'] != 0:
Log.error(result['info'])
raise Exception(result['info'])
if len(result['data']) == 0:
raise Exception('No data in recent 10 seconds.')
data_list = []
for item in result['data']:
tmp = dict()
tmp['Data1'] = item['data1']
tmp['Data2'] = item['data2']
tmp['Data3'] = item['data3']
tmp['Data4'] = item['data4']
tmp['Id'] = item['original_id']
tmp['CreatedTime'] = item['original_created_time']
data_list.append(tmp)
rsp = dict()
rsp['DataList'] = data_list
rsp['PageSize'] = page_size
rsp['PageNo'] = page_no
rsp['TotalRecords'] = total_records
return rsp
def get_new_data():
# get data
try:
rsp = requests.get('http://localhost:5000/producer/data')
except Exception as e:
Log.error("ask api for new data error[{}]".format(str(e)))
return
if rsp.status_code != 200:
Log.error("get data error[{}]".format(rsp.text))
return
result = rsp.json()
if result['Code'] != 200:
Log.error("get data error[{}]".format(result['Message']))
return
# insert data
value_list = []
for item in result['Data']['DataList']:
value = "({Data1},{Data2},{Data3},{Data4},now())".format(
Data1=item['Data1'],
Data2=item['Data2'],
Data3=item['Data3'],
Data4=item['Data4'])
value_list.append(value)
db_conf = conf['DATABASE']
mysql = Mysql(db_conf['HOST'], db_conf['PORT'], db_conf['USER'],
db_conf['PASSWORD'], db_conf['DATABASE'])
result = mysql.connect()
if result['code'] != 0:
Log.error(result['info'])
return
# if the data is large, segmentation is required
sql = "insert into table_1(data1,data2,data3,data4,created_time) values" + ','.join(
value_list)
result = mysql.write(sql)
if result['code'] != 0:
Log.error(result['info'])