101 lines
3.6 KiB
Python
101 lines
3.6 KiB
Python
#!/usr/bin/env python3
|
|
#-*- coding:utf-8 -*-
|
|
import pymysql
|
|
import taos
|
|
from taosrest import connect, TaosRestConnection, TaosRestCursor
|
|
import time
|
|
|
|
con_mysql = pymysql.connect(host='127.0.0.1',
|
|
database='location',
|
|
user='root',
|
|
password='password',
|
|
charset='utf8')
|
|
|
|
con_taosrest: TaosRestConnection = connect(url="http://127.0.0.1:6041",
|
|
user="root",
|
|
password="password",
|
|
timeout=30)
|
|
|
|
"""
|
|
DROP TABLE IF EXISTS location.location;
|
|
CREATE STABLE location.location ( \
|
|
locationtime TIMESTAMP, \
|
|
lat DOUBLE, \
|
|
lng DOUBLE, \
|
|
speed DOUBLE) \
|
|
TAGS (loginname NCHAR(50), \
|
|
username NCHAR(50), \
|
|
deviceimei NCHAR(50), \
|
|
devicemodel NCHAR(100), \
|
|
devicetype BOOL);
|
|
"""
|
|
|
|
start_time = time.time()
|
|
cursor_mysql = con_mysql.cursor()
|
|
cursor_mysql.execute('SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname , \
|
|
LoginName, \
|
|
MAX(UserName) AS UserName, \
|
|
DeviceIMEI, \
|
|
IFNULL(MAX(DeviceModel),\'\') AS DeviceModel, \
|
|
IFNULL(MAX(DeviceType),\'\') AS DeviceType\
|
|
FROM location.location \
|
|
GROUP BY LoginName,DeviceIMEI;')
|
|
tbnamecur = cursor_mysql.fetchall()
|
|
cursor_mysql.close()
|
|
|
|
for tbname,loginname,username,deviceimei,devicemodel,devicetype in tbnamecur:
|
|
table_start_time = time.time()
|
|
cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
|
|
cursor_mysql.execute('SELECT Id FROM location.location WHERE LoginName = \"%s\" ORDER BY Id DESC;' % loginname)
|
|
countlines = cursor_mysql.fetchall()
|
|
cursor_mysql.close()
|
|
count = countlines[0]['Id']
|
|
|
|
i = 0
|
|
j = 10
|
|
taoscnt = 0
|
|
for i in range(0, count, 100):
|
|
cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
|
|
cursor_mysql.execute(f'SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname, \
|
|
LocationTime, \
|
|
Lat, \
|
|
Lng, \
|
|
Speed \
|
|
FROM location.location \
|
|
WHERE LoginName = \"%s\" \
|
|
AND Id > %d \
|
|
AND Id <= %d;' % (loginname,i,j))
|
|
submission = cursor_mysql.fetchall()
|
|
cursor_mysql.close()
|
|
sql="INSERT INTO location.`%s` USING location.location TAGS (\'%s\',\'%s\',\'%s\',\'%s\',%s) VALUES " % (tbname,loginname,username,deviceimei,devicemodel,devicetype)
|
|
if submission:
|
|
for row in submission:
|
|
sql += '(\'{}\',{},{},{}) '.format(row["LocationTime"],row["Lat"],row["Lng"],row["Speed"])
|
|
sql += ";"
|
|
#print(sql)
|
|
cursor_taosrest: TaosRestCursor = con_taosrest.cursor()
|
|
cursor_taosrest.execute(sql)
|
|
taoscnt += cursor_taosrest.rowcount
|
|
cursor_taosrest.close()
|
|
else:
|
|
i += 10
|
|
j += 10
|
|
continue
|
|
i += 10
|
|
j += 10
|
|
print("子表'%s'已迁移%i行!" % (tbname,taoscnt))
|
|
table_end_time = time.time()
|
|
deltatime = table_end_time - table_start_time
|
|
totalhour = int(deltatime / 3600)
|
|
totalminute = int((deltatime - totalhour * 3600) / 60)
|
|
totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)
|
|
print("子表'%s'数据迁移完毕,耗时:%d小时%d分%d秒!" %(tbname,totalhour, totalminute, totalsecond))
|
|
end_time = time.time()
|
|
deltatime = end_time - start_time
|
|
totalhour = int(deltatime / 3600)
|
|
totalminute = int((deltatime - totalhour * 3600) / 60)
|
|
totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)
|
|
print("数据全部迁移完毕,总计耗时:%d小时%d分%d秒!" %(totalhour, totalminute, totalsecond))
|
|
|
|
con_mysql.close()
|
|
con_taosrest.close() |