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 requests
import os
import os.path
import datetime
from common import root
import csv
from db import conn
import psycopg2
legal = 'serecap'
username = 'serecapuser'
password = 'Welcome1'
r = requests.get('https://loans.markit.com/loanx/LoanXFacilityUpdates.csv?LEGALENTITY={0}&USERNAME={1}&PASSWORD={2}'.format(legal, username, password))
def convertToNone(v):
return v if v else None
date = str(datetime.date.today())
filename = os.path.join(root, "data", "Facility files", "facility_{0}.csv".format(date))
with open( filename, "wb") as fh:
fh.write(r.content)
# filename = os.path.join(common.root, "data", "Facility files", "facility_latest.csv")
# with open(filename, "r") as fh:
# reader = csv.reader(fh)
# reader.next()
# sqlstring = "INSERT into markit_facility(LoanXID, PMDID, IssuerName, dealname, facility_type," \
# "loanx_facility_type, initial_amount, initial_spread, maturity, industry, modified_time)" \
# "VALUES( {0} )".format( ",".join( ["%s"] * 11))
# for line in reader:
# newline = [convertToNone(v) for v in line] + [date]
# newline.pop(9) # remove the spread to maturity value
# common.cursor.execute(sqlstring, newline)
# common.conn.commit()
# common.cursor.execute("SELECT DISTINCT LoanXID from markit_facility")
# cusips = [c[0] for c in common.cursor.fetchall()]
# filename = os.path.join(common.root, "data", "Facility files", "facility_update_" + date + ".csv")
# with open( filename, "r") as fh:
# reader = csv.reader(fh)
# reader.next()
# sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
# for line in reader:
# newline = [convertToNone(v) for v in line]
# if newline[0] in cusips:
# common.cursor.execute("DELETE FROM markit_facility WHERE LoanXID = %s", (newline[0],))
# common.cursor.execute(sqlstring, newline)
# else:
# common.cursor.execute(sqlstring, newline)
with open( filename, "r") as fh:
reader = csv.reader(fh)
next(reader)
c = conn.cursor()
sqlstring = "INSERT INTO markit_facility VALUES( {0} )".format( ",".join(["%s"] * 13))
for line in reader:
newline = [convertToNone(v) for v in line]
c.execute(sqlstring, newline)
conn.commit()
conn.close()
|
