def maintenance(database_options, quandl_key, quandl_ticker_source,
database_list, threads, quandl_update_range,
csidata_update_range, symbology_sources):
"""
:param database_options: Dictionary of the postgres database options
:param quandl_key: Optional string of the Quandl API key
:param quandl_ticker_source: String of which source the Quandl data should
use when determining which codes to download (csidata, quandl)
:param database_list: List of strings indicating which Quandl databases
should have their codes downloaded (WIKI, GOOG, YAHOO)
:param threads: Integer of the threads to run when downloading Quandl codes
:param quandl_update_range: Integer of the number of days before the
Quandl codes should be updated
:param csidata_update_range: Integer of the number of days before the CSI
Data factsheet should be updated
:param symbology_sources: List of strings of which symbology sources
should be created (csi_data, tsid, quandl_wiki)
"""
print('Starting Security Master table maintenance function. This can take '
'some time to finish if large databases are used. If this fails, '
'rerun it after a few minutes.')
# Create the SQL tables if they don't already exist
create_database(admin_user=database_options['admin_user'],
admin_password=database_options['admin_password'],
database=database_options['database'],
user=database_options['user'])
main_tables(database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'])
data_tables(database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'])
events_tables(database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'])
LoadTables(database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'],
tables_to_load=tables_to_load,
load_tables=userdir['load_tables'])
# Always extract CSI values, as they are used for the symbology table
CSIDataExtractor(database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'],
db_url=csidata_url,
data_type=csidata_type,
redownload_time=csidata_update_range)
if quandl_ticker_source == 'quandl':
QuandlCodeExtract(database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'],
quandl_token=quandl_key,
database_list=database_list,
database_url=database_url,
update_range=quandl_update_range,
threads=threads)
create_symbology(database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'],
source_list=symbology_sources)
NASDAQSectorIndustryExtractor(
database=database_options['database'],
user=database_options['user'],
password=database_options['password'],
host=database_options['host'],
port=database_options['port'],
db_url=nasdaq_sector_industry_url,
exchange_list=nasdaq_sector_industry_extractor_exchanges,
redownload_time=nasdaq_sector_industry_redownload_time)
if __name__ == '__main__':
from utilities.user_dir import user_dir
userdir = user_dir()
create_database(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'])
main_tables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'])
data_tables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'])
events_tables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'])
LoadTables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'],
tables_to_load=['data_vendor', 'exchanges'])
def test_table_creation(self):
create_database(database=self.db_name, user=self.user)
main_tables(database=self.db_name, user=self.user,
password=self.password, host=self.host, port=self.port)
data_tables(database=self.db_name, user=self.user,
password=self.password, host=self.host, port=self.port)
events_tables(database=self.db_name, user=self.user,
password=self.password, host=self.host, port=self.port)
tables_to_create = ['fundamental_data', 'daily_prices', 'finra_data',
'minute_prices', 'conference_calls', 'dividends',
'earnings', 'exchange', 'economic_events',
'ipo_pricings', 'symbology', 'splits',
'csidata_stock_factsheet', 'baskets',
'basket_values', 'indices', 'quandl_codes',
'data_vendor', 'option_chains', 'tick_prices',
'tick_prices_stream']
tables_created = []
extra_table = []
missing_table = []
conn = psycopg2.connect(database=self.db_name, user=self.user,
password=self.password, host=self.host,
port=self.port)
with conn:
cur = conn.cursor()
cur.execute("""SELECT table_name
FROM information_schema.tables
WHERE table_schema='public'
AND table_type='BASE TABLE'""")
tables_exists = cur.fetchall()
if tables_exists:
for table in tables_exists:
tables_created.append(table[0])
if table[0] not in tables_to_create:
extra_table.append(table[0])
for table in tables_to_create:
if table not in tables_created:
missing_table.append(table)
cur.close()
conn.close()
if missing_table:
print('Missing tables: %s' % missing_table)
if extra_table:
print('Extra tables: %s' % extra_table)
self.assertEqual(len(tables_to_create), len(tables_created))
self.assertEqual(len(missing_table), 0)
self.assertEqual(len(extra_table), 0)
# Connect as the server super user to drop the test database
conn = psycopg2.connect(database=self.userdir['main_db'],
user=self.userdir['main_user'],
password=self.userdir['main_password'],
host=self.userdir['main_host'],
port=self.userdir['main_port'])
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
with conn:
cur = conn.cursor()
cur.execute("""DROP DATABASE IF EXISTS %s""" % self.db_name)
cur.close()
conn.close()
def maintenance(
database_options,
quandl_key,
quandl_ticker_source,
database_list,
threads,
quandl_update_range,
csidata_update_range,
symbology_sources,
):
"""
:param database_options: Dictionary of the postgres database options
:param quandl_key: Optional string of the Quandl API key
:param quandl_ticker_source: String of which source the Quandl data should
use when determining which codes to download (csidata, quandl)
:param database_list: List of strings indicating which Quandl databases
should have their codes downloaded (WIKI, GOOG, YAHOO)
:param threads: Integer of the threads to run when downloading Quandl codes
:param quandl_update_range: Integer of the number of days before the
Quandl codes should be updated
:param csidata_update_range: Integer of the number of days before the CSI
Data factsheet should be updated
:param symbology_sources: List of strings of which symbology sources
should be created (csi_data, tsid, quandl_wiki)
"""
print(
"Starting Security Master table maintenance function. This can take "
"some time to finish if large databases are used. If this fails, "
"rerun it after a few minutes."
)
# Create the SQL tables if they don't already exist
create_database(
admin_user=database_options["admin_user"],
admin_password=database_options["admin_password"],
database=database_options["database"],
user=database_options["user"],
)
main_tables(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
)
data_tables(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
)
events_tables(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
)
LoadTables(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
tables_to_load=tables_to_load,
load_tables=userdir["load_tables"],
)
# Always extract CSI values, as they are used for the symbology table
CSIDataExtractor(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
db_url=csidata_url,
data_type=csidata_type,
redownload_time=csidata_update_range,
)
if quandl_ticker_source == "quandl":
QuandlCodeExtract(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
quandl_token=quandl_key,
database_list=database_list,
database_url=database_url,
update_range=quandl_update_range,
threads=threads,
)
create_symbology(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
source_list=symbology_sources,
)
NASDAQSectorIndustryExtractor(
database=database_options["database"],
user=database_options["user"],
password=database_options["password"],
host=database_options["host"],
port=database_options["port"],
db_url=nasdaq_sector_industry_url,
exchange_list=nasdaq_sector_industry_extractor_exchanges,
redownload_time=nasdaq_sector_industry_redownload_time,
)
if __name__ == '__main__':
from utilities.user_dir import user_dir
userdir = user_dir()
create_database(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'])
main_tables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'])
data_tables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'])
events_tables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'])
LoadTables(database=userdir['postgresql']['pysecmaster_db'],
user=userdir['postgresql']['pysecmaster_user'],
password=userdir['postgresql']['pysecmaster_password'],
host=userdir['postgresql']['pysecmaster_host'],
port=userdir['postgresql']['pysecmaster_port'],
tables_to_load=['data_vendor', 'exchanges'])