def get_colname(table_name):
with dbconn() as db:
cursor = db.cursor()
sql = "select COLUMN_NAME from information_schema.COLUMNS where table_name='{}' and table_schema='mlp'".format(
table_name)
cursor.execute(sql)
names = cursor.fetchall()
for i in range(len(names)):
names[i] = names[i]['COLUMN_NAME']
return names
def main():
quandl.ApiConfig.api_key = "-a9RmMjaPBg-phm3w83Y"
mysql_conn = create_engine(
"mysql+mysqldb://root:[email protected]:3306/mlp").connect()
for col in col_names:
# Get the newest date
with dbconn() as db:
cursor = db.cursor()
cursor.execute(
"SELECT DATE FROM NEWEST_DATE WHERE NAME='{}'".format(col))
newest_id = cursor.fetchone()['DATE']
print('Fetching: ', col)
data = quandl.get(col, start_date=newest_id)
if len(data) > 1:
data = data.reset_index()
data['Date'] = data['Date'].apply(lambda x: x.strftime('%Y-%m-%d'))
data['Value'] = data['Value'].apply(lambda x: str(x))
data.to_sql(name=col,
con=mysql_conn,
if_exists='append',
index=False)
newest_date = data['Date'].iloc[-1]
print(newest_date)
with dbconn() as db:
print("Saving to database")
cursor = db.cursor()
sql = """INSERT INTO NEWEST_DATE (NAME, DATE) VALUES ('{}','{}')
ON DUPLICATE KEY UPDATE DATE='{}';""".format(
col, newest_date, newest_date)
cursor.execute(sql)
db.commit()
else:
print("Nothing new")
def fred_search(request, name):
print("Searching for ", name)
with dbconn() as db:
try:
cur = db.cursor()
name = 'FRED/' + name.upper()
sql = "select * from `{}`".format(name)
cur.execute(sql)
data = cur.fetchall()
print('Sending to front-end')
return HttpResponse(obj2json(data),
content_type="application/json")
except ProgrammingError:
error = "Cannot find table {}. Please try again.".format(name)
return HttpResponse(errorRep(error),
content_type="application/json")
def fetch_record(date, index):
while True:
try:
index += 1
print("Fetching %d record" % index)
sdr_fetch(
"https://kgc0418-tdw-data2-0.s3.amazonaws.com/slices/SLICE_COMMODITIES_{}_{}.zip".format(date, index))
print()
except error.HTTPError:
# Update newest date to
print(index)
date = date + '_' + str(index - 1)
with dbconn() as db:
print("Saving stop point to database", date)
cursor = db.cursor()
sql = """INSERT INTO NEWEST_DATE (NAME, DATE) VALUES ('dtcc','{}')
ON DUPLICATE KEY UPDATE DATE='{}';""".format(date, date)
cursor.execute(sql)
db.commit()
break
def init_update():
quandl.ApiConfig.api_key = "-a9RmMjaPBg-phm3w83Y"
mysql_conn = create_engine(
"mysql+mysqldb://root:[email protected]:3306/mlp").connect()
for col in col_names:
print('Fetching: ', col)
data = quandl.get(col)
data = data.reset_index()
data['Date'] = data['Date'].apply(lambda x: x.strftime('%Y-%m-%d'))
data['Value'] = data['Value'].apply(lambda x: str(x))
data.to_sql(name=col, con=mysql_conn, if_exists='replace', index=False)
newest_date = data['Date'].iloc[-1]
print(newest_date)
with dbconn() as db:
print("Saving to database")
cursor = db.cursor()
sql = """INSERT INTO NEWEST_DATE (NAME, DATE) VALUES ('{}','{}')
ON DUPLICATE KEY UPDATE DATE='{}';""".format(
col, newest_date, newest_date)
cursor.execute(sql)
db.commit()
def main():
# Get newest date from database
with dbconn() as db:
cursor = db.cursor()
cursor.execute("SELECT DATE FROM NEWEST_DATE WHERE NAME='dtcc'")
newest_id = cursor.fetchone()
if newest_id is None:
print("No data from newest date")
date = str(datetime.today().strftime('%Y_%m_%d'))
index = 0
print(newest_id)
else:
newest_id = newest_id['DATE'].split('_')
date = '_'.join(newest_id[:-1])
index = int(newest_id[-1])
print("Reading from stop point!", index)
utc = get_utc()
# Fetching the newest record of this date
fetch_record(date, index)
# Coming to a new date
if utc != date:
print("Having new date info")
fetch_record(utc, 0)
df = pd.DataFrame()
print("Reading csv and combining them together")
name_list = os.listdir('zip_files/')
for f_name in name_list:
if f_name.endswith('csv'):
csv = pd.read_csv('zip_files/' + f_name, index_col=False)
df = df.append(csv)
os.remove('zip_files/' + f_name)
# df = pd.read_csv('full.csv', index_col=False)
if len(df) > 0:
print("Uploading to database")
# mysql_conn = create_engine("mysql+mysqldb://root:[email protected]:3306/mlp").connect()
# df.to_sql(name='dtcc', con=mysql_conn, if_exists='append', index=False)
df = df.replace(nan, ' ', regex=True)
df_swap = df[df['TAXONOMY'].str.contains('Exotic|Option') == False]
df_swap.drop_duplicates(['TAXONOMY'], keep='last', inplace=True)
df_option = df[df['TAXONOMY'].str.contains('Exotic|Option')]
df_option.drop_duplicates(['TAXONOMY'], keep='last', inplace=True)
with dbconn() as db:
cursor = db.cursor()
if len(df_swap) > 0:
table_name = 'COM/SWAP'
col_name = get_colname(table_name)
col_str = "{}".format(','.join(col_name))
for i in range(0, len(df_swap)):
value_str = "{}".format(','.join(
['%r' % df_swap.iloc[i][name] if type(df_swap.iloc[i][name]) != float else str(round(
df_swap.iloc[i][name], 4)) for name in col_name]))
print(value_str)
# print("Updating Commodities swap", date)
sql = """REPLACE INTO `{}` ({}) VALUES ({});""".format(table_name, col_str, value_str)
print(sql)
cursor.execute(sql)
if len(df_option) > 0:
table_name = 'COM/OPTION'
col_name = get_colname(table_name)
col_str = "{}".format(','.join(col_name))
for i in range(0, len(df_option)):
value_str = "{}".format(','.join(
['%r' % df_option.iloc[i][name] for name in col_name]))
print(value_str)
# print("Updating Commodities swap", date)
sql = """REPLACE INTO `{}` ({}) VALUES ({});""".format(table_name, col_str, value_str)
print(sql)
cursor.execute(sql)
db.commit()
else:
print("Already newest")