86 lines
3.4 KiB
Python
86 lines
3.4 KiB
Python
import requests
|
|
import pymysql
|
|
from datetime import datetime
|
|
import time
|
|
old_transaction_mtd_amt=0
|
|
condition=True
|
|
db = pymysql.connect(host="127.0.0.1",user="root",password="2GS@bPYcgiMyL14A",database="Macroeconomics",port=4423)
|
|
cursor = db.cursor()
|
|
while condition:
|
|
page = requests.get("https://api.fiscaldata.treasury.gov/services/api/fiscal_service/v1/accounting/dts/public_debt_transactions?fields=record_date,transaction_type,security_type,transaction_mtd_amt&sort=-record_date")
|
|
page = page.json()
|
|
page = page['data']
|
|
# 获取网站最新一条数据时间
|
|
page_data = page[0:23]
|
|
for data in page_data:
|
|
sql = "select date from USTreasuriesSize order by date desc limit 1"
|
|
cursor.execute(sql)
|
|
old_date = cursor.fetchall()
|
|
old_date = str(old_date)
|
|
old_date = old_date[20:-11]
|
|
old_date = old_date.replace(' ', '')
|
|
old_date = old_date.replace(',', '/')
|
|
date_string = old_date
|
|
format = '%Y/%m/%d'
|
|
old_date = datetime.strptime(date_string, format)
|
|
|
|
record_date = data['record_date']
|
|
transaction_type = data['transaction_type']
|
|
security_type=data['security_type']
|
|
transaction_mtd_amt=data['transaction_mtd_amt']
|
|
record_date = record_date.replace('-', '/')
|
|
format = '%Y/%m/%d'
|
|
record_date = datetime.strptime(record_date, format)
|
|
# 判断数据库最新一条数据时间和网站最新一条数据时间
|
|
if record_date!=old_date:
|
|
sql = "insert into USTreasuriesSize(date)values('%s')" % (record_date)
|
|
cursor.execute(sql)
|
|
db.commit()
|
|
sql = "select id from USTreasuriesSize order by id desc limit 1"
|
|
cursor.execute(sql)
|
|
id = cursor.fetchall()
|
|
id = id[0][0]
|
|
for data in page_data:
|
|
transaction_type = data['transaction_type']
|
|
security_type = data['security_type']
|
|
transaction_mtd_amt = data['transaction_mtd_amt']
|
|
if transaction_type == 'Issues':
|
|
if security_type == 'Bills':
|
|
transaction_mtd_amt= old_transaction_mtd_amt + int(transaction_mtd_amt)
|
|
sql1 = "update USTreasuriesSize set TBill=%s where id=%s" % (transaction_mtd_amt, id)
|
|
cursor.execute(sql1)
|
|
db.commit()
|
|
old_transaction_mtd_amt=transaction_mtd_amt
|
|
|
|
elif security_type == 'Notes':
|
|
sql2 = "update USTreasuriesSize set TNote=%s where id=%s" % (transaction_mtd_amt, id)
|
|
cursor.execute(sql2)
|
|
db.commit()
|
|
|
|
elif security_type == 'Bonds':
|
|
sql3 = "update USTreasuriesSize set TBond=%s where id=%s" % (transaction_mtd_amt, id)
|
|
cursor.execute(sql3)
|
|
db.commit()
|
|
|
|
elif security_type == 'Inflation-Protected Securities Increment':
|
|
sql4 = "update USTreasuriesSize set TIPS=%s where id=%s" % (transaction_mtd_amt, id)
|
|
cursor.execute(sql4)
|
|
db.commit()
|
|
else:
|
|
continue
|
|
else:
|
|
continue
|
|
else:
|
|
continue
|
|
|
|
old_transaction_mtd_amt=0
|
|
time.sleep(21600)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|