84 lines
3.1 KiB
Python
84 lines
3.1 KiB
Python
|
import requests
|
||
|
|
import pymysql
|
||
|
|
from datetime import datetime
|
||
|
|
import time
|
||
|
|
|
||
|
|
def get_bea_data(year):
|
||
|
|
url = ("https://apps.bea.gov/api/data?&UserID=146B5757-D9E3-442C-B6AC-ADE9E6B71114&method=GetData&DataSetName=GDPbyIndustry&Year=%s&Industry=ALL&tableID=15&Frequency=Q&ResultFormat=JSON" % year)
|
||
|
|
response = requests.get(url)
|
||
|
|
return response.json()['BEAAPI']['Results'][0]['Data']
|
||
|
|
|
||
|
|
def update_database(cursor, data):
|
||
|
|
industry_map = {
|
||
|
|
'Agriculture, forestry, fishing, and hunting': 'VAPGDPAFH',
|
||
|
|
'Mining': 'VAPGDPM',
|
||
|
|
'Construction': 'VAPGDPC',
|
||
|
|
'Manufacturing': 'VAPGDPMA',
|
||
|
|
'Retail trade': 'VAPGDPR',
|
||
|
|
'Wholesale trade': 'VAPGDPW',
|
||
|
|
'Utilities': 'VAPGDPU',
|
||
|
|
'Transportation and warehousing': 'VAPGDPT',
|
||
|
|
'Information': 'VAPGDPI',
|
||
|
|
'Finance, insurance, real estate, rental, and leasing': 'VAPGDPFIRL',
|
||
|
|
'Professional and business services': 'VAPGDPPBS',
|
||
|
|
'Educational services, health care, and social assistance': 'VAPGDPHCSA',
|
||
|
|
'Arts, entertainment, recreation, accommodation, and food services': 'VAPGDPAF',
|
||
|
|
'Other services, except government': 'CPGDPOSEG',
|
||
|
|
'Government': 'Federation',
|
||
|
|
'State and local': 'State_local'
|
||
|
|
}
|
||
|
|
|
||
|
|
for entry in data:
|
||
|
|
year = entry["Year"]
|
||
|
|
quarter = entry["Quarter"]
|
||
|
|
new_time = f"{year}Q{quarter}"
|
||
|
|
industry = entry["IndustrYDescription"]
|
||
|
|
value = entry["DataValue"]
|
||
|
|
|
||
|
|
if industry in industry_map:
|
||
|
|
column = industry_map[industry]
|
||
|
|
|
||
|
|
cursor.execute("SELECT quarterly FROM COVITGDP WHERE quarterly = %s", (new_time,))
|
||
|
|
result = cursor.fetchone()
|
||
|
|
if result:
|
||
|
|
cursor.execute(f"SELECT {column} FROM COVITGDP WHERE quarterly = %s", (new_time,))
|
||
|
|
old_value = cursor.fetchone()[0]
|
||
|
|
|
||
|
|
if old_value != value:
|
||
|
|
cursor.execute(f"UPDATE COVITGDP SET {column} = %s WHERE quarterly = %s", (value, new_time))
|
||
|
|
else:
|
||
|
|
print(f"No update needed for {column} for {new_time}")
|
||
|
|
|
||
|
|
else:
|
||
|
|
if column == 'VAPGDPAFH':
|
||
|
|
cursor.execute("INSERT INTO COVITGDP (quarterly, VAPGDPAFH) VALUES (%s, %s)", (new_time, value))
|
||
|
|
else:
|
||
|
|
cursor.execute(f"INSERT INTO COVITGDP (quarterly, {column}) VALUES (%s, %s) ON DUPLICATE KEY UPDATE {column} = VALUES({column})", (new_time, value))
|
||
|
|
|
||
|
|
def main():
|
||
|
|
years = 2025
|
||
|
|
|
||
|
|
while True:
|
||
|
|
try:
|
||
|
|
db = pymysql.connect(host="127.0.0.1",user="root",password="2GS@bPYcgiMyL14A",database="Macroeconomics",port=4423)
|
||
|
|
cursor = db.cursor()
|
||
|
|
|
||
|
|
data = get_bea_data(years)
|
||
|
|
update_database(cursor, data)
|
||
|
|
db.commit()
|
||
|
|
except pymysql.MySQLError as e:
|
||
|
|
print(f"Database connection error: {e}")
|
||
|
|
break
|
||
|
|
except Exception as e:
|
||
|
|
print(f"An error occurred: {e}")
|
||
|
|
finally:
|
||
|
|
if 'cursor' in locals():
|
||
|
|
cursor.close()
|
||
|
|
if 'db' in locals():
|
||
|
|
db.close()
|
||
|
|
|
||
|
|
time.sleep(86400)
|
||
|
|
|
||
|
|
if __name__ == "__main__":
|
||
|
|
main()
|