#!/usr/bin/python
# -*- coding: utf8 -*-
'''
測試中選會提供的 final_T.json 資料
'''
import os
import sys
import datetime
import time
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)
db = pymysql.connect(host="localhost",user="user",password="password",database="vote2020" )
cursor = db.cursor()
cursor.execute('SET NAMES utf8')
filename="final_T.json"
with open(filename, 'r') as f:
data = json.load(f)
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:
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 final"
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 final (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 final 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)
# 更新 st 資料表
if reccount==0:
strSQL="INSERT INTO st (st_time) VALUES ('%s')" % (ST)
cursor.execute(strSQL)
db.commit()
cursor.close()
db.close()
留言列表
