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")