107 lines
3.6 KiB
Python
107 lines
3.6 KiB
Python
#!/usr/bin/env python3
|
|
#-*- coding:utf-8 -*-
|
|
import pymysql
|
|
import taos
|
|
import time
|
|
|
|
con_mysql = pymysql.connect(host='127.0.0.1',
|
|
database='locaiong',
|
|
user='root',
|
|
password='password',
|
|
charset='utf8')
|
|
|
|
con_taos = taos.connect(host="127.0.0.1",
|
|
user="root",
|
|
password="password",
|
|
database="locaiong",
|
|
port=6030,
|
|
config="/etc/taos",
|
|
timezone="Asia/Shanghai")
|
|
|
|
"""
|
|
DROP DATABASE IF EXISTS location;
|
|
CREATE DATABASE location;
|
|
ALTER DATABASE location CACHEMODEL 'both';
|
|
ALTER DATABASE location CACHESIZE 16;
|
|
ALTER DATABASE location KEEP 120;
|
|
|
|
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=pymysql.cursors.DictCursor)
|
|
cursor_mysql.execute('SELECT Id FROM location.location ORDER BY Id DESC LIMIT 1;')
|
|
countlines = cursor_mysql.fetchall()
|
|
cursor_mysql.close()
|
|
count = countlines[0]['Id']
|
|
|
|
i = 0
|
|
j = 10000
|
|
taoscnt = 0
|
|
for i in range(0, count, 10000):
|
|
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 \
|
|
WHERE Id > %d \
|
|
AND Id <= %d \
|
|
GROUP BY LoginName,DeviceIMEI;' % (i,j))
|
|
tbnamecur = cursor_mysql.fetchall()
|
|
cursor_mysql.close()
|
|
|
|
for tbname,loginname,username,deviceimei,devicemodel,devicetype in tbnamecur:
|
|
cursor_taos = con_taos.cursor()
|
|
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.`{}` USING location.location TAGS (\'{}\',\'{}\',\'{}\',\'{}\',{}) VALUES ".format(tbname,loginname,username,deviceimei,devicemodel,devicetype).replace(",)" ,",False)" )
|
|
if submission:
|
|
for row in submission:
|
|
sql += '(\'{}\',{},{},{}) '.format(row["LocationTime"],row["Lat"],row["Lng"],row["Speed"])
|
|
sql += ";"
|
|
#print(sql)
|
|
cursor_taos.execute(sql)
|
|
taoscnt += cursor_taos.rowcount
|
|
stime = time.time() - start_time
|
|
avgrow = int(taoscnt / stime)
|
|
print("数据已迁移%d行,耗时%d秒,平均行%d/秒" % (taoscnt,stime,avgrow))
|
|
cursor_taos.close()
|
|
else:
|
|
i += 10000
|
|
j += 10000
|
|
continue
|
|
i += 10000
|
|
j += 10000
|
|
|
|
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_taos.close() |