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)
