#!/usr/bin/python
# -*- coding: utf8 -*-
import os
import sys
import datetime
import time
from threading import Timer
import json
import pymysql
# 檢查 Python 執行的版本
if not (sys.version_info.major == 3 and sys.version_info.minor >= 5):
print("This script requires Python 3.5 or higher!")
print("You are using Python {}.{}.{}".format(sys.version_info.major, sys.version_info.minor, sys.version_info.micro))
sys.exit(1)
import urllib.parse
import urllib.request
# 定時抓取資料
class RepeatedTimer(object):
def __init__(self, interval, function, *args, **kwargs):
self._timer = None
self.interval = interval
self.function = function
self.args = args
self.kwargs = kwargs
self.is_running = False
self.start()
def _run(self):
self.is_running = False
self.start()
self.function(*self.args, **self.kwargs)
def start(self):
if not self.is_running:
self._timer = Timer(self.interval, self._run)
self._timer.start()
self.is_running = True
def stop(self):
self._timer.cancel()
self.is_running = False
# 下載資料
def getVote():
now_time = time.time()
cdate = datetime.datetime.fromtimestamp(now_time).strftime('%Y%m%d-%H%M%S')
username = 'user'
password = 'password'
url = "https://tvdownload.2020.nat.gov.tw/data/json/running_T.json"
# Python 3.x
passman = urllib.request.HTTPPasswordMgrWithDefaultRealm()
passman.add_password(None, url, username, password)
authhandler = urllib.request.HTTPBasicAuthHandler(passman)
opener = urllib.request.build_opener(authhandler)
urllib.request.install_opener(opener)
u = urllib.request.urlopen(url)
out_fname = 'running_T.json-'+cdate
filename = os.path.join(data_path, out_fname)
f = open(filename, 'wb')
file_size = int(u.getheader("Content-Length"))
print("Downloading: %s Bytes: %s" % (out_fname, file_size))
buff=""
file_size_dl = 0
block_sz = 8192
while True:
buff = u.read(block_sz)
if not buff:
break
file_size_dl += len(buff)
f.write(buff)
status = r"%10d [%3.2f%%]" % (file_size_dl, file_size_dl * 100. / file_size)
status = status + chr(8)*(len(status)+1)
print(status,)
f.close()
print(time.time() - now_time)
print()
saveVote(filename)
# 資料存檔
def saveVote(filename):
DEBUG=True
# 設定資料庫
db = pymysql.connect(host="localhost",user="user",password="password",database="vote2020" )
cursor = db.cursor()
cursor.execute('SET NAMES utf8')
with open(filename, 'r') as f:
data = json.load(f)
if "ST" in data.keys():
ST=data["ST"]
strSQL ="SELECT count(*) as reccount FROM st"
strSQL+=" WHERE st_time='%s'" % (ST)
cursor = db.cursor()
cursor.execute(strSQL)
row = cursor.fetchone()
reccount, = row
if reccount==0 or DEBUG:
lstTC=data["TC"]
n=0
for tc in lstTC:
n+=1
prvCode =tc['prvCode']
cityCode=tc['cityCode']
deptCode=tc['deptCode']
tboxNo=tc['tboxNo']
candTksInfo=tc['candTksInfo']
for c in candTksInfo:
if c['candNo']==1:
candNo1=c['candNo']
tks1=c['tks']
candVictor1=c['candVictor']
tksRate1=c['tksRate']
elif c['candNo']==2:
candNo2=c['candNo']
tks2=c['tks']
candVictor2=c['candVictor']
tksRate2=c['tksRate']
elif c['candNo']==3:
candNo3=c['candNo']
tks3=c['tks']
candVictor3=c['candVictor']
tksRate3=c['tksRate']
else:
pass
prof1=tc['prof1']
prof2=tc['prof2']
prof3=tc['prof3']
prof4=tc['prof4']
prof5=tc['prof5']
prof6=tc['prof6']
prof7=tc['prof7']
profRate=tc['profRate']
deptTot=tc['deptTot']
deptRcv=tc['deptRcv']
tboxTot=tc['tboxTot']
tboxRcv=tc['tboxRcv']
strSQL ="SELECT count(*) as reccount FROM running"
strSQL+=" WHERE prvCode='%s' AND cityCode='%s' AND deptCode='%s' AND tboxNo=%d" % (prvCode, cityCode, deptCode, tboxNo)
cursor.execute(strSQL)
row = cursor.fetchone()
count, = row
if count==0:
# insert
strSQL ="INSERT INTO running (prvCode, cityCode, deptCode, tboxNo, "
strSQL+=" candNo1, tks1, candVictor1, tksRate1, "
strSQL+=" candNo2, tks2, candVictor2, tksRate2, "
strSQL+=" candNo3, tks3, candVictor3, tksRate3, "
strSQL+=" prof1, prof2, prof3, prof4, prof5, prof6, prof7, "
strSQL+=" profRate, deptTot, deptRcv, tboxTot, tboxRcv"
strSQL+=" ) VALUES ("
strSQL+=" '%s','%s','%s',%d," % (prvCode, cityCode, deptCode, tboxNo)
strSQL+=" %d,%d,'%s',%.2f," % (candNo1, tks1, candVictor1, tksRate1)
strSQL+=" %d,%d,'%s',%.2f," % (candNo2, tks2, candVictor2, tksRate2)
strSQL+=" %d,%d,'%s',%.2f," % (candNo3, tks3, candVictor3, tksRate3)
strSQL+=" %d,%d,%d,%d,%d,%d,%d," % (prof1, prof2, prof3, prof4, prof5, prof6, prof7)
strSQL+=" %.2f,%d,%d,%d,%d)" % (profRate, deptTot, deptRcv, tboxTot, tboxRcv)
cursor.execute(strSQL)
else:
# update
strSQL ="UPDATE running SET "
strSQL+=" candNo1=%d, tks1=%d, candVictor1='%s', tksRate1=%.2f, " % (candNo1, tks1, candVictor1, tksRate1)
strSQL+=" candNo2=%d, tks2=%d, candVictor2='%s', tksRate2=%.2f, " % (candNo2, tks2, candVictor2, tksRate2)
strSQL+=" candNo3=%d, tks3=%d, candVictor3='%s', tksRate3=%.2f, " % (candNo3, tks3, candVictor3, tksRate3)
strSQL+=" prof1=%d, prof2=%d, prof3=%d, prof4=%d, prof5=%d, prof6=%d, prof7=%d, " % (prof1, prof2, prof3, prof4, prof5, prof6, prof7)
strSQL+=" profRate=%.2f, deptTot=%d, deptRcv=%d, tboxTot=%d, tboxRcv=%d" % (profRate, deptTot, deptRcv, tboxTot, tboxRcv)
strSQL+=" WHERE prvCode='%s' AND cityCode='%s' AND deptCode='%s' AND tboxNo=%d" % (prvCode, cityCode, deptCode, tboxNo)
cursor.execute(strSQL)
if reccount==0:
strSQL="INSERT INTO st (st_time) VALUES ('%s')" % (ST)
print(strSQL)
print()
cursor.execute(strSQL)
db.commit()
cursor.close()
db.close()
else:
print("running_T.json 格式錯誤！")
if __name__ == "__main__":
data_path = "./data"
if (os.path.isdir(data_path)):
pass
else:
print("Directory not found!")
os.mkdir(data_path, 0o755)
print("starting...")
getVote()
# 定時下載，間隔 180 秒
rt = RepeatedTimer(180, getVote)
while True:
try:
time.sleep(1)
except KeyboardInterrupt:
rt.stop()
break
print("\n\n")
print("end")
留言列表
