def setUp(self): userdir = user_dir() self.database = userdir['postgresql']['pysecmaster_db'] self.user = userdir['postgresql']['pysecmaster_user'] self.password = userdir['postgresql']['pysecmaster_password'] self.host = userdir['postgresql']['pysecmaster_host'] self.port = userdir['postgresql']['pysecmaster_port'] cur_posix_time = str(datetime.now().timestamp()) cur_posix_time = cur_posix_time[:cur_posix_time.find('.')] self.yahoo_fin_url = { 'root': 'https://query1.finance.yahoo.com/v7/finance/download/', 'start_date': 'period1=0', # First POSIX time (whole hist) 'end_date': 'period2=' + cur_posix_time, # Cur POSIX time 'interval': 'interval=', # 1d, 1w, 1mo: (daily, wkly, mthly) 'events': 'events=', # history, div, split 'cookie': 'crumb=', # Cookie value } self.csv_wo_data = 'test_yahoo_daily_wo_data.csv' with open(self.csv_wo_data, 'w') as csv_file: writer = csv.writer(csv_file) writer.writerow(['tsid', 'date_tried']) self.exchanges_df = self.query_exchanges()
def setUp(self): self.userdir = user_dir()['postgresql'] self.db_name = self.userdir['pysecmaster_test_db'] self.user = self.userdir['pysecmaster_test_user'] self.password = self.userdir['pysecmaster_test_password'] self.host = self.userdir['pysecmaster_test_host'] self.port = self.userdir['pysecmaster_test_port']
def create_database(admin_user='******', admin_password='******', database='pysecmaster', user='******'): """ Determine if the provided database exists within the postgres server. If the database doesn't exist, create it using the provided user as the owner. This requires connecting to the default database before psycopg2 is able to send an execute command. NOTE: The provided user must have a valid login role within postgres before they are able to log into the server and create databases. :param admin_user: String of the database admin user :param admin_password: String of the database admin password :param database: String of the database to create :param user: String of the user who should own the database """ userdir = user_dir()['postgresql'] conn = psycopg2.connect(database=userdir['main_db'], user=admin_user, password=admin_password, host=userdir['main_host'], port=userdir['main_port']) conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) try: with conn: cur = conn.cursor() cur.execute("""SELECT datname FROM pg_catalog.pg_database WHERE lower(datname)=lower('%s')""" % database) database_exist = cur.fetchone() if not database_exist: cur.execute("""CREATE DATABASE %s OWNER %s""" % (database, user)) else: print('The %s database already exists.' % database) cur.close() except psycopg2.Error as e: conn.rollback() print('Failed to create the %s database' % database) print(e) return except conn.OperationalError: print('Unable to connect to the SQL Database in create_database. Make ' 'sure the database address/name are correct.') return except Exception as e: print(e) raise SystemError( 'Error: An unknown issue occurred in create_database')
def setUp(self): userdir = user_dir() quandl_token = userdir['quandl']['quandl_token'] db_url = ['https://www.quandl.com/api/v1/datasets/', '.csv'] self.qd = QuandlDownload(quandl_token=quandl_token, db_url=db_url) self.csv_wo_data = 'test_quandl_codes_wo_data.csv' with open(self.csv_wo_data, 'w') as csv_file: writer = csv.writer(csv_file) writer.writerow(['q_code', 'date_tried'])
def create_database(admin_user='******', admin_password='******', database='pysecmaster', user='******'): """ Determine if the provided database exists within the postgres server. If the database doesn't exist, create it using the provided user as the owner. This requires connecting to the default database before psycopg2 is able to send an execute command. NOTE: The provided user must have a valid login role within postgres before they are able to log into the server and create databases. :param admin_user: String of the database admin user :param admin_password: String of the database admin password :param database: String of the database to create :param user: String of the user who should own the database """ userdir = user_dir()['postgresql'] conn = psycopg2.connect(database=userdir['main_db'], user=admin_user, password=admin_password, host=userdir['main_host'], port=userdir['main_port']) conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) try: with conn: cur = conn.cursor() cur.execute("""SELECT datname FROM pg_catalog.pg_database WHERE lower(datname)=lower('%s')""" % database) database_exist = cur.fetchone() if not database_exist: cur.execute("""CREATE DATABASE %s OWNER %s""" % (database, user)) else: print('The %s database already exists.' % database) cur.close() except psycopg2.Error as e: conn.rollback() print('Failed to create the %s database' % database) print(e) return except conn.OperationalError: print('Unable to connect to the SQL Database in create_database. Make ' 'sure the database address/name are correct.') return except Exception as e: print(e) raise SystemError('Error: An unknown issue occurred in create_database')
def setUp(self): userdir = user_dir() self.database = userdir['postgresql']['pysecmaster_db'] self.user = userdir['postgresql']['pysecmaster_user'] self.password = userdir['postgresql']['pysecmaster_password'] self.host = userdir['postgresql']['pysecmaster_host'] self.port = userdir['postgresql']['pysecmaster_port'] self.nasdaq_sector_industry_url = 'http://www.nasdaq.com/screening/' \ 'companies-by-industry.aspx?' self.nasdaq_sector_industry_extractor_exchanges = [ 'NASDAQ', 'NYSE', 'AMEX' ] self.nasdaq_sector_industry_redownload_time = 0
def setUp(self): userdir = user_dir() self.database = userdir['postgresql']['pysecmaster_db'] self.user = userdir['postgresql']['pysecmaster_user'] self.password = userdir['postgresql']['pysecmaster_password'] self.host = userdir['postgresql']['pysecmaster_host'] self.port = userdir['postgresql']['pysecmaster_port'] self.google_fin_url = { 'root': 'http://www.google.com/finance/getprices?', 'ticker': 'q=', 'exchange': 'x=', 'interval': 'i=', # 60; 60 seconds is the shortest interval # 'sessions': 'sessions=ext_hours', 'period': 'p=', # 20d; 15d is the longest period for min 'fields': 'f=d,c,v,o,h,l', } # order doesn't change anything self.exchanges_df = self.query_exchanges()
except conn.OperationalError: raise SystemError( "Unable to connect to the %s database in " "pull_minute_prices. Make sure the database " "address/name are correct." % database ) except Exception as e: print(e) raise SystemError("Error: Unknown issue occurred in pull_minute_prices") if __name__ == "__main__": from utilities.user_dir import user_dir userdir = user_dir() test_database = userdir["postgresql"]["pysecmaster_db"] test_user = userdir["postgresql"]["pysecmaster_user"] test_password = userdir["postgresql"]["pysecmaster_password"] test_host = userdir["postgresql"]["pysecmaster_host"] test_port = userdir["postgresql"]["pysecmaster_port"] test_query_type = "ticker" # index, ticker test_tsid = "AAPL.Q.0" test_data_vendor_id = 15 # pySecMaster_Consensus # test_data_vendor_id = 12 # Google_Finance test_beg_date = "1950-01-01 00:00:00" test_end_date = "2016-12-30 00:00:00" frequency = "daily" # daily, minute
item in the database, along with translating different source's codes Database data download tasks: Downloads Quandl data based on the download selection criteria using either the official Quandl Codes or implied codes from CSI Data. Downloads Google Finance minute stock data. Can either append only the new data, or replace part of the existing data. Future expansions: Implement daily option chain data (from Google or Yahoo). ''' ############################################################################### # Database maintenance options: userdir = user_dir() csidata_type = 'stock' # stock, commodity # Don't change these unless you know what you are doing database_url = ['https://www.quandl.com/api/v2/datasets.csv?query=*&' 'source_code=', '&per_page=300&page='] # http://www.csidata.com/factsheets.php?type=stock&format=html csidata_url = 'http://www.csidata.com/factsheets.php?' tables_to_load = ['data_vendor', 'exchanges'] symbology_sources = ['csi_data', 'tsid', 'quandl_wiki', 'quandl_goog', 'seeking_alpha', 'yahoo'] nasdaq_sector_industry_url = 'http://www.nasdaq.com/screening/' \ 'companies-by-industry.aspx?' nasdaq_sector_industry_extractor_exchanges = ['NASDAQ', 'NYSE', 'AMEX']
def main(verbose=False): """ Move all values from the minute_prices table of the sqlite_database to the postgre database. :param verbose: Boolean of whether to print debugging statements """ sqlite_database = 'C:/Users/joshs/Programming/Databases/pySecMaster/' \ 'pySecMaster_m.db' from utilities.user_dir import user_dir userdir = user_dir() postgre_database = userdir['postgresql']['pysecmaster_db'] postgre_user = userdir['postgresql']['pysecmaster_user'] postgre_password = userdir['postgresql']['pysecmaster_password'] postgre_host = userdir['postgresql']['pysecmaster_host'] postgre_port = userdir['postgresql']['pysecmaster_port'] table = 'minute_prices' # Get a list of unique tsids from the sqlite database's table tsid_list = query_all_tsids_from_table(database=sqlite_database, table=table) # Iterate through each tsid from the unique list for tsid in tsid_list: tsid_start = time.time() # Query all of the tsid's table prices from the sqlite database existing_price_df = query_all_tsid_prices(database=sqlite_database, table=table, tsid=tsid) if len(existing_price_df) > 0: # Add the source column using 'tsid' between data_vendor_id and tsid existing_price_df.insert(1, 'source', 'tsid') # Change the tsid column name to 'source_id' existing_price_df.rename(columns={'tsid': 'source_id'}, inplace=True) # existing_price_df['date'].tz_localize('UTC') existing_price_df['date'] = \ pd.to_datetime(existing_price_df['date'], utc=True) # print(existing_price_df.head()) # existing_price_df.to_csv('%s_min_prices.csv' % tsid) # Insert all the prices into the postgre database insert_df_to_db(database=postgre_database, user=postgre_user, password=postgre_password, host=postgre_host, port=postgre_port, price_df=existing_price_df, table=table, verbose=verbose) else: # Should never happen, since the tsid wouldn't have been used anyway if verbose: print('No existing data found in the Sqlite3 database for %s' % tsid) if verbose: print('Verifying the %s times for %s took %0.2f seconds' % (table, tsid, time.time() - tsid_start))