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)