def update_all_accessible_tables(schema, list_table, only): failed_updates = [] if not only: accessibles, non_accessibles = list_accessible_tables(schema) if list_table: logger.info( f"Schema {schema} has {len(accessibles) + len(non_accessibles)} tables" ) logger.info(f"{accessibles + non_accessibles}") return failed_updates if not accessibles: logger.warn(f"No accessible tables in {schema}") return failed_updates tables2update = accessibles else: tables2update = list(only) logger.info(f"Updating {len(tables2update)} tables: {tables2update}") for table in tables2update: try: wrds_update(table, schema) except (KeyboardInterrupt, SystemExit): logger.error("user interruption, exiting") raise except: logger.error( f"Exception happened during updating {schema}.{table}") logger.error(traceback.format_exception(*(sys.exc_info()))) failed_updates.append(f"{schema}.{table}") return failed_updates
def update_all_accessible_tables(schema): accessibles, _ = list_accessible_tables(schema) if not accessibles: logger.warn(f"No accessible tables in {schema}") else: logger.info(f"Updating {len(accessibles)} tables") for table in accessibles: wrds_update(table, schema)
def update_equities(year): updated = wrds_update("pr_equities_" + str(year), "rpna", rename="group=group_") if updated: engine.execute("CREATE INDEX ON rpna.pr_equities_" + str(year) + " (rp_entity_id, rpna_date_utc)") return updated
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update, run_file_sql from wrds2postgres.wrds2postgres import make_engine, wrds_id engine = make_engine() wrds_update("act_epsus", "ibes") wrds_update("actpsumu_epsus", "ibes") wrds_update("actu_epsus", "ibes") updated = wrds_update("detu_epsus", "ibes") if updated: engine.execute("SET maintenance_work_mem='1999MB'") engine.execute("CREATE INDEX ON ibes.detu_epsus (ticker, revdats)") updated = wrds_update("det_xepsus", "ibes") if updated: engine.execute("CREATE INDEX ON ibes.det_xepsus (ticker, revdats)") wrds_update("det_epsus", "ibes") wrds_update("id", "ibes") wrds_update("idsum", "ibes") wrds_update("surpsum", "ibes") wrds_update("statsum_epsus", "ibes") updated = wrds_update("statsumu_epsus", "ibes") if updated: engine.execute("SET maintenance_work_mem='1999MB'") engine.execute("CREATE INDEX ON ibes.statsumu_epsus (ticker, statpers)") wrds_update("det_guidance", "ibes") if updated: engine.execute("SET maintenance_work_mem='1999MB'")
#!/usr/bin/env python3 from sqlalchemy import MetaData from wrds2postgres.wrds2postgres import wrds_update, run_file_sql, make_engine engine = make_engine() avail_years = range(2000, 2019) updated = wrds_update("rp_entity_mapping", "rpna") def update_equities(year): updated = wrds_update("pr_equities_" + str(year), "rpna", rename="group=group_") if updated: engine.execute("CREATE INDEX ON rpna.pr_equities_" + str(year) + " (rp_entity_id, rpna_date_utc)") return updated updated = [update_equities(year) for year in avail_years] def select(cols): sql = "SELECT " + ", ".join(cols) return sql def get_sql(year, cols): return (select(cols) + "\nFROM rpna.pr_equities_%s\n" % year)
engine = make_engine() # Update Treasury yield table crsp.tfz_ft # From wrds: # The error is correct, the table "tfz_ft," does not exist. Behind the scenes this web # query form is joining two tables on the fly. The tables this query is joining are # "crsp.tfz_idx" and either "crsp.tfz_dly_ft" or "crsp.tfz_mth_ft" (depending on if # you want daily or monthly data) by the variable "kytreasnox." # Here are some links to the information about these tables: # https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=77140 # https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=77137 # https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=137&file_id=77147 tfz_idx = wrds_update("tfz_idx", "crsp", fix_missing=True) tfz_dly_ft = wrds_update("tfz_dly_ft", "crsp", fix_missing=True) if tfz_idx or tfz_dly_ft: sql = """ DROP TABLE IF EXISTS crsp.tfz_ft; CREATE TABLE crsp.tfz_ft AS (SELECT kytreasnox, tidxfam, ttermtype, ttermlbl, caldt, rdtreasno, rdcrspid, -- to_timestamp(rdcrspid, 'YYYYMMDD.HH24MISS') as rdcrspid, tdyearstm, tdduratn, tdretadj, tdytm, tdbid, tdask, tdnomprc, tdnomprc_flg, tdaccint FROM crsp.tfz_idx INNER JOIN crsp.tfz_dly_ft USING (kytreasnox)) """ engine.execute(sql)
#!/usr/bin/env python3 from time import gmtime, strftime from wrds2postgres.wrds2postgres import wrds_update, make_engine, run_file_sql engine = make_engine() updated = wrds_update("g_exrt_dly", "comp") updated = wrds_update("g_funda", "comp", fix_missing=True, rename="do=do_") if updated: engine.execute("CREATE INDEX ON comp.g_funda (gvkey)") updated = wrds_update("g_secd", "comp") if updated: engine.execute("CREATE INDEX ON comp.g_secd (gvkey)") updated = wrds_update("g_security", "comp") updated = wrds_update("g_company", "comp") updated = wrds_update("sec_history", "comp") ecupdated = wrds_update("idxcst_his", "comp", rename="from=fromdt") updated = wrds_update("anncomp", "comp") if updated: engine.execute("CREATE INDEX ON comp.anncomp (gvkey)") updated = wrds_update("adsprate", "comp") if updated: engine.execute("CREATE INDEX ON comp.adsprate (gvkey, datadate)") updated = wrds_update("co_hgic", "comp")
#!/usr/bin/env python3 from sqlalchemy import create_engine import os, sys dbname = os.getenv("PGDATABASE") host = os.getenv("PGHOST", "localhost") wrds_id = os.getenv("WRDS_ID") engine = create_engine("postgresql://" + host + "/" + dbname) from wrds2postgres import wrds2postgres # engine.execute("CREATE ROLE risk") # engine.execute("GRANT USAGE ON SCHEMA risk TO risk") update = wrds2postgres.wrds_update("vavoteresults", "risk") if update: sql = """ ALTER TABLE risk.vavoteresults ALTER COLUMN companyid TYPE integer; ALTER TABLE risk.vavoteresults ALTER COLUMN meetingid TYPE integer; ALTER TABLE risk.vavoteresults ALTER COLUMN itemonagendaid TYPE integer; ALTER TABLE risk.vavoteresults ALTER COLUMN seqnumber TYPE integer; UPDATE risk.vavoteresults SET voterequirement = 0.6667 WHERE voterequirement=66.67; UPDATE risk.vavoteresults SET base = 'F+A' WHERE base='F A'; UPDATE risk.vavoteresults SET base = 'F+A+AB' WHERE base IN ('F A AB', 'F+A+B'); UPDATE risk.vavoteresults
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update updated = wrds_update("wciklink_gvkey", "wrdssec") updated = wrds_update("wciklink_names", "wrdssec") updated = wrds_update("wciklink_cusip", "wrdssec", drop="tmatch")
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update, run_file_sql from wrds2postgres.wrds2postgres import make_engine, wrds_id wrds_update("fx", "frb") wrds_update("rates", "pwt")
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update updated = wrds_update("firm_ratio", "wrdsapps", force=True)
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update updated = wrds_update("issuer", "cusipm") updated = wrds_update("issue", "cusipm")
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update, run_file_sql from wrds2postgres.wrds2postgres import make_engine, wrds_id engine = make_engine() updated = wrds_update("amend", "tfn") updated = wrds_update("avgreturns", "tfn") updated = wrds_update("company", "tfn", fix_cr=True) updated = wrds_update("form144", "tfn") updated = wrds_update("header", "tfn") updated = wrds_update("idfhist", "tfn") updated = wrds_update("idfnames", "tfn") updated = wrds_update("rule10b5", "tfn") updated = wrds_update("table1", "tfn") updated = wrds_update("table2", "tfn") updated = wrds_update("s12", "tfn") updated = wrds_update("s12type1", "tfn") updated = wrds_update("s12type2", "tfn") updated = wrds_update("s12type3", "tfn") updated = wrds_update("s12type4", "tfn") updated = wrds_update("s34", "tfn") updated = wrds_update("s34type1", "tfn") updated = wrds_update("s34type2", "tfn") updated = wrds_update("s34type3", "tfn") updated = wrds_update("s34type4", "tfn")
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update dealscan_tables = [ "borrowerbase", "company", "currfacpricing", "dealamendment", "dealpurposecomment", "facility", "facilityamendment", "facilitydates", "facilityguarantor", "facilitypaymentschedule", "facilitysecurity", "facilitysponsor", "financialcovenant", "financialratios", "lendershares", "link_table", "lins", "marketsegment", "networthcovenant", "organizationtype", "package", "packageassignmentcomment", "performancepricing", "performancepricingcomments", "sublimits", "dbo_df_fac_dates_data" ] for table in dealscan_tables: wrds_update(table, "dealscan")
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update updated = wrds_update("mflink1", "mfl", force=True) updated = wrds_update("mflink2", "mfl")
""" the_table = Table(table, MetaData(), schema=schema, autoload=True, autoload_with=engine) columns = the_table.c col_lst = [col.name for col in columns if col.name.startswith("is_") and not isinstance(col.type, Boolean)] modify_lst = [mod_col(col, schema, table, engine) for col in col_lst] if modify_lst: print("Columns changed to boolean", modify_lst) return modify_lst updated = wrds_update("feed25person", "audit") updated = wrds_update("namesauditorsinfo", "audit") # Partially working; need to add part4_3_text* columns updated = wrds_update("nt", "audit", drop="match: closest: prior: part4_3_text: ") # updated = wrds_update("nt", "audit", keep="nt_notify_key", force=True, alt_table_name="nt_other") updated = wrds_update("auditnonreli", "audit", drop="prior: match: closest: eventdate:") updated = wrds_update("bankrupt", "audit", drop="match: closest: prior:") if updated: engine.execute(""" ALTER TABLE audit.bankrupt ALTER COLUMN bank_key TYPE integer; ALTER TABLE audit.bankrupt ALTER COLUMN bankruptcy_type TYPE integer; ALTER TABLE audit.bankrupt ALTER COLUMN law_court_fkey TYPE integer; ALTER TABLE audit.bankrupt ALTER COLUMN court_type_code TYPE integer USING court_type_code::integer;
#!/usr/bin/env python3 from wrds2postgres.wrds2postgres import wrds_update, run_file_sql from wrds2postgres.wrds2postgres import make_engine, wrds_id wrds_update("msfabond", "markit") wrds_update("cds", "markit")
#!/usr/bin/env python3 from sqlalchemy import create_engine from wrds2postgres.wrds2postgres import wrds_update, run_file_sql, make_engine from wrds2postgres.wrds2postgres import make_engine, wrds_id engine = make_engine() updated = wrds_update("wrds_gvkey", "ciq", fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_gvkey (companyid)") updated = wrds_update("wciklink_gvkey", "wrdssec") updated = wrds_update("wciklink_names", "wrdssec") # updated = wrds_update("wciklink_cusip", "wrdssec") updated = wrds_update("wrds_cusip", "ciq", fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_cusip (companyid)") updated = wrds_update("wrds_cik", "ciq", fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_cik (companyid)") updated = wrds_update("wrds_keydev", "ciq", fix_missing=True) if updated: engine.execute("CREATE INDEX ON ciq.wrds_keydev (keydeveventtypeid)") engine.execute("CREATE INDEX ON ciq.wrds_keydev (companyid)") wrds_update("wrds_professional", "ciq", fix_cr=True) wrds_update("ciqkeydeveventtype", "ciq", fix_cr=True) wrds_update("ciqkeydevobjectroletype", "ciq", fix_cr=True)