Esempio n. 1
0
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)
Esempio n. 2
0
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'])
Esempio n. 3
0
    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()
Esempio n. 4
0
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,
    )
Esempio n. 5
0
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'])