def insert_route(url):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers=ptxheader()).text
for route in json.loads(res):
for subroute in route["SubRoutes"]:
try:
# 有的第一個子路線的路線編號會硬加0在後面
if subroute["SubRouteName"]["Zh_tw"][-1] == "0" and len(
subroute["SubRouteName"]["Zh_tw"]) > len(
route["RouteID"]):
number = subroute["SubRouteName"]["Zh_tw"][:-1]
else:
number = subroute["SubRouteName"]["Zh_tw"]
if route["DepartureStopNameEn"] != "" and route[
"DestinationStopNameEn"] != "":
eheadsign = route["DepartureStopNameEn"] + " - " + route[
"DestinationStopNameEn"]
else:
eheadsign = None
cursor.execute(
"exec dbo.xp_insertBus ?,?,?,?,?,?,?", "公路客運", number,
number,
subroute["Headsign"].replace("→",
" - ").replace("─", " - "),
eheadsign, subroute["SubRouteUID"], subroute["Direction"])
cursor.commit()
except:
continue
del cursor
connect.close()
def insert_stop(url, city):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers=ptxheader()).text
for stop in json.loads(res):
# 資料可能有可能無
try:
if stop["StopName"]["En"] == "":
estop = None
else:
estop = stop["StopName"]["En"]
except:
estop = None
try:
lon = stop["StopPosition"]["PositionLon"]
except:
lon = None
try:
lat = stop["StopPosition"]["PositionLat"]
except:
lat = None
try:
cursor.execute("exec dbo.xp_insertStop ?,?,?,?,?,?,?,?,?", city,
stop["StopName"]["Zh_tw"], estop,
stop["UpdateTime"][0:19], stop["StopUID"], lon, lat,
None, None)
cursor.commit()
except:
continue
del cursor
connect.close()
def insert_timetable(url):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers=ptxheader()).text
for train in json.loads(res):
try:
# 以車次時刻加密成md5
md5 = hashlib.md5()
md5.update(json.dumps(train["StopTimes"]).encode())
# 判斷是否需建班次
sql = "select TID from Transportation where Number = " + train[
"DailyTrainInfo"]["TrainNo"] + " and MD5 = '" + md5.hexdigest(
) + "'"
cursor.execute(sql)
if cursor.fetchone() is not None:
pass
else:
# insert Transportation
cursor.execute(
"exec dbo.xp_insertTrain ?,?,?,?,?,?,?,?,?,?,?,?", "高鐵",
None, None, None, None, train["DailyTrainInfo"]["TrainNo"],
train["DailyTrainInfo"]["StartingStationID"],
train["DailyTrainInfo"]["StartingStationName"]
["Zh_tw"].replace("臺", "台"),
train["DailyTrainInfo"]["EndingStationID"],
train["DailyTrainInfo"]["EndingStationName"]
["Zh_tw"].replace("臺", "台"),
train["DailyTrainInfo"]["Direction"], md5.hexdigest())
cursor.commit()
# insert ST and TS
for stop in train["StopTimes"]:
cursor.execute(
"exec dbo.xp_insertTrainST ?,?,?,?,?,?,?,?",
"高鐵", train["DailyTrainInfo"]["TrainNo"],
md5.hexdigest(), stop["StopSequence"],
stop["StationID"],
stop["StationName"]["Zh_tw"].replace("臺", "台"),
stop["ArrivalTime"], stop["DepartureTime"])
cursor.commit()
# insert Dailytimetable
year, month, day = train["TrainDate"].split("-")
cursor.execute("exec dbo.xp_insertDailyTimetable ?,?,?,?,?,?",
"高鐵", year, month, day,
train["DailyTrainInfo"]["TrainNo"], md5.hexdigest())
cursor.commit()
except:
continue
del cursor
connect.close()
def insert_stop(url):
connect = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers = ptxheader()).text
for stop in json.loads(res):
try:
cursor.execute("exec dbo.xp_insertStop ?,?,?,?,?,?,?,?,?", "台鐵", stop["StationName"]["Zh_tw"].replace("臺","台"), stop["StationName"]["En"],
stop["UpdateTime"][0:19], stop["StationID"], stop["StationPosition"]["PositionLon"], stop["StationPosition"]["PositionLat"], stop["StationPhone"],
stop["StationAddress"])
cursor.commit()
except:
continue
del cursor
connect.close()
def insert_price(url):
connect = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers = ptxheader()).text
traintype = {0:["自強","普悠瑪","太魯閣"], 1:["自強","普悠瑪","太魯閣"], 5:["莒光"], 6:["莒光"],
10:["復興","區間車","區間快"], 11:["復興","區間車","區間快"], 15:["普快車"], 16:["普快車"]}
tickettype = {0:"全票", 1:"優待票", 5:"全票", 6:"優待票", 10:"全票", 11:"優待票", 15:"全票", 16:"優待票"}
note = {0:None, 1:"孩童票、敬老票、愛心票", 5:None, 6:"孩童票、敬老票、愛心票", 10:None, 11:"孩童票、敬老票、愛心票", 15:None, 16:"孩童票、敬老票、愛心票"}
for price in json.loads(res):
try:
for j in [0,1,5,6,10,11,15,16]:
for traintypes in traintype[j]:
cursor.execute("exec dbo.xp_insertPrice ?,?,?,?,?,?,?,?,?,?","台鐵", price["OriginStationID"], price["OriginStationName"]["Zh_tw"].replace("臺","台"),
price["DestinationStationID"], price["DestinationStationName"]["Zh_tw"].replace("臺","台"), traintypes, tickettype[j], int(price["Fares"][j]["Price"]), note[j], price["UpdateTime"][0:19])
cursor.commit()
except:
continue
def update(city):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(
"http://ptx.transportdata.tw/MOTC/v2/Bus/DataVersion/City/" + city +
"?$format=JSON",
headers=ptxheader()).text
versionID = json.loads(res)["VersionID"]
sql = "select VersionID from DataVersion where Name = '" + city + "'"
cursor.execute(sql)
if cursor.fetchone()[0] == versionID:
return 0
else:
sql = "update DataVersion set VersionID = " + str(
versionID) + ", Since = getdate() where Name = '" + city + "'"
cursor.execute(sql)
cursor.commit()
return 1
def update(url):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers=ptxheader()).text
date = (datetime.now()).strftime("%Y-%m-%d")
year, month, day = date.split("-")
#先將班次動態清除
cursor.execute(
"update Transportation set PassRank = NULL, DelayTime = NULL")
cursor.commit()
for train in json.loads(res):
try:
numbers = train["TrainNo"]
cursor.execute("exec dbo.xp_updateTrainState ?,?,?,?,?,?,?,?",
"台鐵", year, month, day, numbers, train["StationID"],
train["StationName"]["Zh_tw"].replace("臺", "台"),
train["DelayTime"])
cursor.commit()
except:
continue
def insert_ST(url, city):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers=ptxheader()).text
if "台北市" in city or "新北市" in city:
uid = "RouteUID"
else:
uid = "SubRouteUID"
for route in json.loads(res):
# 以停靠站點加密成md5
md5 = hashlib.md5()
md5.update(json.dumps(route["Stops"]).encode())
# 判斷路線是否更動或為新增的路線
cursor.execute("exec dbo.xp_checkBus ?,?,?,?", city, route[uid],
route["Direction"], md5.hexdigest())
cursor.commit()
sql = """select * from Class C, CO, Object O, Transportation T, TS
where C.CID = CO.CID and CO.OID = O.OID and O.OID = T.TID and T.TID = TS.TID
and C.Type = 102 and C.NamePath like '%' + '
""" + city + "' and T.Number = '" + route[
uid] + "' and T.Direction = " + str(
route["Direction"]) + " and T.MD5 = '" + md5.hexdigest() + "'"
cursor.execute(sql)
if cursor.fetchone() is None:
for stop in route["Stops"]:
try:
cursor.execute("exec dbo.xp_insertBusST ?,?,?,?,?,?", city,
route[uid], route["Direction"],
stop["StopUID"], stop["StopName"]["Zh_tw"],
stop["StopSequence"])
cursor.commit()
except:
continue
del cursor
connect.close()
def insert_price(url):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers=ptxheader()).text
price = json.loads(res)
for i in range(0, 3, 1):
try:
cursor.execute(
"exec dbo.xp_insertPrice ?,?,?,?,?,?,?,?,?,?", "高鐵",
price[0]["OriginStationID"],
price[0]["OriginStationName"]["Zh_tw"].replace("臺", "台"),
price[0]["DestinationStationID"],
price[0]["DestinationStationName"]["Zh_tw"].replace("臺", "台"),
price[0]["Fares"][i]["TicketType"], "全票",
int(price[0]["Fares"][i]["Price"]), None,
price[0]["SrcUpdateTime"][0:19])
cursor.commit()
except:
continue
del cursor
connect.close()
def insert_route(url, city):
connect = pyodbc.connect(
'DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers=ptxheader()).text
if "台北市" in city or "新北市" in city:
for route in json.loads(res):
try:
ename = route["RouteName"]["En"]
except:
ename = None
try:
if route["DepartureStopNameZh"] != "" and route[
"DestinationStopNameZh"] != "":
cheadsign = route["DepartureStopNameZh"] + " - " + route[
"DestinationStopNameZh"]
else:
cheadsign = None
except:
cheadsign = None
try:
if route["DepartureStopNameEn"] != "" and route[
"DestinationStopNameEn"] != "":
eheadsign = route["DepartureStopNameEn"] + " - " + route[
"DestinationStopNameEn"]
else:
eheadsign = None
except:
eheadsign = None
cursor.execute("exec dbo.xp_insertBus ?,?,?,?,?,?,?", city,
route["RouteName"]["Zh_tw"], ename, cheadsign,
eheadsign, route["RouteUID"], 0)
cursor.commit()
cursor.execute("exec dbo.xp_insertBus ?,?,?,?,?,?,?", city,
route["RouteName"]["Zh_tw"], ename, cheadsign,
eheadsign, route["RouteUID"], 1)
cursor.commit()
else:
for route in json.loads(res):
for subroute in route["SubRoutes"]:
try:
ename = subroute["SubRouteName"]["En"]
except:
ename = None
try:
cheadsign = subroute["Headsign"].replace(
"<->",
" - ").replace("→", " - ").replace("─", " - ").replace(
"->", " - ").replace("－",
" - ").replace("-", " - ")
except:
try:
if route["DepartureStopNameZh"] != "" and route[
"DestinationStopNameZh"] != "":
cheadsign = route[
"DepartureStopNameZh"] + " - " + route[
"DestinationStopNameZh"]
else:
cheadsign = None
except:
cheadsign = None
try:
if route["DepartureStopNameEn"] != "" and route[
"DestinationStopNameEn"] != "":
eheadsign = route[
"DepartureStopNameEn"] + " - " + route[
"DestinationStopNameEn"]
else:
eheadsign = None
except:
eheadsign = None
cursor.execute("exec dbo.xp_insertBus ?,?,?,?,?,?,?", city,
subroute["SubRouteName"]["Zh_tw"], ename,
cheadsign, eheadsign, subroute["SubRouteUID"],
subroute["Direction"])
cursor.commit()
del cursor
connect.close()
def insert_timetable(url):
connect = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=iTraffic;')
cursor = connect.cursor()
res = requests.get(url, headers = ptxheader()).text
for train in json.loads(res):
try:
# 以車次時刻加密成md5
md5 = hashlib.md5()
md5.update(json.dumps(train["StopTimes"]).encode())
# 判斷是否需建班次
sql = "select TID from Transportation where Number = "+train["DailyTrainInfo"]["TrainNo"]+" and MD5 = '"+md5.hexdigest()+"'"
cursor.execute(sql)
if cursor.fetchone() is not None:
pass
else:
# insert Transportation
# 有的班次沒NoteEn會出錯
try:
Note_En = train["DailyTrainInfo"]["Note"]["En"]
except:
Note_En = ""
#有的班次沒有車種
if "Tze" in train["DailyTrainInfo"]["TrainTypeName"]["En"] :
if '普悠瑪' in train["DailyTrainInfo"]["TrainTypeName"]["Zh_tw"] :
ctraintype = "普悠瑪"; etraintype = "Puyuma"
elif '太魯閣' in train["DailyTrainInfo"]["TrainTypeName"]["Zh_tw"] :
ctraintype = "太魯閣"; etraintype = "Taroko"
else:
ctraintype = "自強"; etraintype = "Tze-Chiang"
elif "Chu" in train["DailyTrainInfo"]["TrainTypeName"]["En"] :
ctraintype = "莒光"; etraintype = "Chu-Kuang"
elif "Fu" in train["DailyTrainInfo"]["TrainTypeName"]["En"] :
ctraintype = "復興"; etraintype = "Fu-Hsing"
elif "Fast" in train["DailyTrainInfo"]["TrainTypeName"]["En"]:
ctraintype = "區間快"; etraintype = "Fast Local Train"
elif "Local" in train["DailyTrainInfo"]["TrainTypeName"]["En"]:
ctraintype = "區間車"; etraintype = "Local Train"
elif "Ordinary" in train["DailyTrainInfo"]["TrainTypeName"]["En"]:
ctraintype = "普快車"; etraintype = "Local Train"
cursor.execute("exec dbo.xp_insertTrain ?,?,?,?,?,?,?,?,?,?,?,?", "台鐵", ctraintype,etraintype,
train["DailyTrainInfo"]["Note"]["Zh_tw"], Note_En, train["DailyTrainInfo"]["TrainNo"],
train["DailyTrainInfo"]["StartingStationID"], train["DailyTrainInfo"]["StartingStationName"]["Zh_tw"].replace("臺","台"),
train["DailyTrainInfo"]["EndingStationID"], train["DailyTrainInfo"]["EndingStationName"]["Zh_tw"].replace("臺","台"),
train["DailyTrainInfo"]["Direction"], md5.hexdigest())
cursor.commit()
# insert ST and TS
for stop in train["StopTimes"] :
cursor.execute("exec dbo.xp_insertTrainST ?,?,?,?,?,?,?,?", "台鐵", train["DailyTrainInfo"]["TrainNo"], md5.hexdigest(),
stop["StopSequence"], stop["StationID"], stop["StationName"]["Zh_tw"].replace("臺","台"), stop["ArrivalTime"], stop["DepartureTime"])
cursor.commit()
# insert Dailytimetable
year, month, day = train["TrainDate"].split("-")
cursor.execute("exec dbo.xp_insertDailyTimetable ?,?,?,?,?,?", "台鐵", year, month, day, train["DailyTrainInfo"]["TrainNo"], md5.hexdigest())
cursor.commit()
except:
continue
del cursor
connect.close()