diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/Dawn/utils.py | 51 | ||||
| -rw-r--r-- | python/dawn_utils.py | 28 |
2 files changed, 59 insertions, 20 deletions
diff --git a/python/Dawn/utils.py b/python/Dawn/utils.py index 99451080..2ce8beb8 100644 --- a/python/Dawn/utils.py +++ b/python/Dawn/utils.py @@ -23,9 +23,9 @@ def load_counterparties(engine): 'Valuation Note': 'notes'}, inplace=True) counterparties.to_sql('counterparties', engine, if_exists='append', index=False) -def load_trades(engine): - blotter = pd.read_excel("/home/share/Daily/blotter.xlsm", 'Bonds', - skiprows = [0, 1, 2, 3, 4]) +def load_trades(engine, schema=None): + blotter = pd.read_excel("/home/serenitas/Daily/Blotter.xlsm", 'Bonds', + skiprows=[0, 1, 2, 3, 4]) blotter.dropna(axis=0, subset=['Deal ID'], inplace=True) blotter = blotter.iloc[:,2:] blotter.drop(['Unnamed: %s' % (i,) for i in range(19, 28)] + @@ -50,9 +50,43 @@ def load_trades(engine): blotter['cashaccount'] = 'V0NSCLMAMB' blotter['id'] = blotter['id'].str.replace('[A-Z_]', '').astype('int') blotter.loc[blotter.asset_class == 'CLO','id'] = blotter.loc[blotter.asset_class == 'CLO','id'] + 5 - blotter.to_sql('bonds', engine, if_exists='append', index=False) + blotter.to_sql('bonds', engine, if_exists='append', index=False, schema=schema) return blotter +def load_trades_futures(engine, schema=None): + blotter = pd.read_excel("/home/serenitas/Daily/Blotter.xlsm", 'Futures', + skiprows=[0, 1, 2, 3, 4]) + blotter.dropna(axis=0, subset=['Deal ID'], inplace=True) + blotter = blotter.iloc[:,2:] + blotter.drop(['Unnamed: %s' % (i,) for i in range(25, 27)] + \ + ['Deal Type', 'Asset Class', 'Initial Margin Currency', 'Client', 'State'], + axis=1, inplace=True) + blotter.rename(columns = {'Trade Date': 'trade_date', + 'Settlement Date': 'settle_date', + 'Cash Account': 'cashaccount', + 'Strategy': 'folder', + 'Quantity': 'quantity', + 'Commission': 'commission', + 'CP Alias': 'cp_code', + 'Description': 'security_desc', + 'Buy/Sell': 'buysell', + 'Price': 'price', + 'Deal ID': 'id', + 'Exchange': 'exchange', + 'Ticker': 'bbg_ticker', + 'Maturity': 'maturity', + 'Action': 'action', + 'Custodian': 'custodian', + 'Trade Currency': 'currency', + 'Swap Type': 'swap_type', + 'Counterparty': 'cp_code'}, inplace=True) + blotter.buysell = blotter.buysell.apply(lambda x: x=='Buy') + blotter['action'] = 'NEW' + blotter['cashaccount'] = 'IANSCLMAFU' + blotter['swap_type'] = 'FUTURE' + blotter['id'] = blotter['id'].str.replace('[A-Z_]', '').astype('int') + blotter.to_sql('futures', engine, if_exists='append', index=False, schema=schema) + return blotter def bump_rev(filename): pattern = "([^r]*)(\srev(\d)|).pdf" @@ -64,5 +98,10 @@ def simple_serialize(obj): return dumps({c.name: getattr(obj, c.name) for c in obj.__table__.columns}) if __name__=="__main__": - engine = create_engine('postgresql://dawn_user@debian/dawndb') - load_trades(engine) + qa = False + if qa: + engine = create_engine('postgresql://qa@debian/dawndb') + load_trades_futures(engine, 'qa') + else: + engine = create_engine('postgresql://dawn_user@debian/dawndb') + load_trades_futures(engine) diff --git a/python/dawn_utils.py b/python/dawn_utils.py index cd7396e1..1c49d591 100644 --- a/python/dawn_utils.py +++ b/python/dawn_utils.py @@ -2,14 +2,15 @@ from sqlalchemy.sql import text from sqlalchemy import create_engine import os -def create_trigger_function(conn): - auto_dealid = text(""" +def create_trigger_function(db): + # need to escape % with %% + auto_dealid = db.DDL(""" CREATE OR REPLACE FUNCTION auto_dealid() RETURNS TRIGGER AS $$ DECLARE stub text; sqlstr text; BEGIN - sqlstr:= 'UPDATE '|| TG_TABLE_NAME ||' SET %s WHERE id = %L AND dealid is NULL'; + sqlstr:= 'UPDATE '|| TG_TABLE_NAME ||' SET %%s WHERE id = %%L AND dealid is NULL'; IF (TG_TABLE_NAME = 'bonds') THEN stub := 'SC_'; @@ -24,33 +25,32 @@ identifier = COALESCE(identifier, cusip, isin)', NEW.id); ELSIF (TG_TABLE_NAME = 'swaptions') THEN stub := 'SWPTN'; sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); + ELSIF (TG_TABLE_NAME = 'futures') THEN + stub := 'SCFUT'; + sqlstr := format(sqlstr, 'dealid = $1||id', NEW.id); END IF; EXECUTE sqlstr USING stub; RETURN NEW; END; $$ language plpgsql""") - conn.execute(auto_dealid) + db.event.listen(db.metadata, 'before_create', auto_dealid) -def create_triggers(): +def create_triggers(db): trigger = db.DDL("""CREATE TRIGGER dealid AFTER INSERT ON %(fullname)s FOR EACH ROW EXECUTE PROCEDURE auto_dealid()""") for name, tb in db.metadata.tables.items(): - db.event.listen(tb, 'after_create', trigger) + if not name.endswith('counterparties'): + db.event.listen(tb, 'after_create', trigger) if __name__ == "__main__": """ This script will create the tables and triggers for the Dawn app. If schema is not None, it will create it under a specific schema""" - engine = create_engine('postgresql://qa@debian/dawndb') - conn = engine.connect() - schema = 'qa' #conn.execute("CREATE SCHEMA IF NOT EXISTS {}".format(schema)) - conn.execute("SET search_path TO %s", (schema,)) - create_trigger_function(conn) - conn.close() - + #conn.execute("SET search_path TO %s", (schema,)) os.environ['CONF'] = 'config.ini' from Dawn import db - create_triggers() + create_trigger_function(db) + create_triggers(db) db.create_all() |