1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
|
import os.path
import datetime
from datetime import date
import csv
from common import root
from db import conn, with_connection
import sys
def convertToNone(s):
return None if (s=='' or s=='NA') else s
fields_corp = ["Cusip", "Price", "PricingDate", "Issuer", "Maturity", "Coupon",
"CouponType", "Frequency", "Spread", "Libor_floor", "loan_margin",
"covlite", "secondlien", "defaulted", "Source"]
root = os.path.join(root, "data", "bloomberg")
@with_connection
def upload_data(conn, p_date):
sqlstring = "INSERT INTO bloomberg_corp({0}) " \
"VALUES({1})".format(",".join(fields_corp), ",".join(["%s"] * len(fields_corp)))
with conn.cursor() as c:
c.execute("select cusip, pricingdate from historical_bloomberg_corp(%s)", (date.today(),))
corpcusips = dict(c)
allrows = []
with open( os.path.join(root, "bloomberg_datacorp_" + p_date + ".csv"), "r") as fh:
dr = csv.DictReader(fh)
for line in dr:
if line["LAST_UPDATE_DT"] != 'NA':
line["LAST_UPDATE_DT"] = \
datetime.datetime.strptime(line["LAST_UPDATE_DT"], '%Y-%m-%d').date()
else:
line["LAST_UPDATE_DT"] = datetime.datetime.strptime(p_date, '%Y-%m-%d').date()
line["PX_LAST"] = None
if line["MATURITY"] != 'NA':
line["MATURITY"] = datetime.datetime.strptime(line["MATURITY"], '%Y-%m-%d')
if line['CUSIP'] not in corpcusips or corpcusips[line['CUSIP']] < line['LAST_UPDATE_DT']:
allrows.append(tuple([convertToNone(line[field]) for field in dr.fieldnames]))
with conn.cursor() as c:
c.executemany(sqlstring, allrows)
conn.commit()
if __name__=="__main__":
upload_data(sys.argv[1])
conn.close()
|
