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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
import psycopg2
import os
import os.path
import datetime
from datetime import date
import pdb
import csv
if os.name =='nt':
root = "//WDsentinel/share/CorpCDOs/data/markit"
elif os.name == 'posix':
root = '/home/share/CorpCDOS/data/markit'
def convertToNone(s):
return None if (s=='' or s=='NA') else s
conn = psycopg2.connect(database="ET",
user="et_user",
password="Serenitas1",
host="192.168.1.108")
cursor = conn.cursor()
fields = ["LoanX ID", "Issuer", "Deal Name", "Facility", "Industry", \
"Current SP Rating", "Current Moodys Rating", "Initial Amount",\
"Final Maturity", "Initial Spread", "Bid", "Offer", "Depth", \
"Spread To Maturity"]
latestdate = cursor.execute("SELECT MAX(pricingdate) from markit_prices")
latestdate = cursor.fetchone()[0]
sql_fields = ["LoanXID", "Issuer", "DealName", "Facility", "Industry", "SP",
"Moodys", "Amount", "Maturity", "Spread", "Bid", "Offer", "Depth",
"STM", "PricingDate"]
for filename in os.listdir(root):
date = datetime.datetime.strptime(filename.split("_")[2].split(".")[0], '%Y-%m-%d').date()
# only update the new data
if latestdate is None or date > latestdate:
with open( os.path.join(root, filename) ) as fh:
dr = csv.DictReader(fh)
data = []
for line in dr:
try:
line['Final Maturity'] = \
datetime.datetime.strptime(line['Final Maturity'], '%d-%b-%y')
except ValueError:
if line['Final Maturity'] !='':
print 'Maturity non empty'
if line['Depth'] == 'implied':
line['Depth'] = 0
try:
temp = [convertToNone(line[key]) for key in fields]
except KeyError:
pdb.set_trace()
data.append(temp)
for row in data:
sqlstring = "INSERT INTO markit_prices({0}) " \
"VALUES({1})".format(",".join(sql_fields), ",".join(["%s"] * len(sql_fields)))
cursor.execute(sqlstring, tuple(row) + (date,))
conn.commit()
|
