def upload_popularity_data(popularity_data):
conn = start_db_connection()
with closing(conn.cursor()) as cur:
cur.execute('''SELECT name FROM popularity_type''')
popularity_types = [x[0] for x in cur.fetchall()]
current_date = datetime.datetime.now().date()
for pd in popularity_data:
try:
cur.execute('''INSERT INTO popularity_point (artist_id, update_date)
VALUES (%s, %s)''', (pd['id'], current_date))
for pop_type in popularity_types:
if not pd[pop_type]:
continue
cur.execute('''INSERT INTO popularity_value (pp_id,
pt_id, value) SELECT pp.id, pt.id, %s
FROM popularity_point pp, popularity_type pt WHERE
pp.update_date = %s AND pp.artist_id = %s
AND pt.name = %s''',
(pd[pop_type], current_date, pd['id'], pop_type))
conn.commit()
except psycopg2.IntegrityError as e:
print 'Attempted Multiple Upload', pd
conn.rollback()
conn.close()
def get_min_db_date():
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
cur.execute('SELECT MAX(earningsdate) FROM equityearningsdates')
last_date = cur.fetchone()[0]
conn.close()
return last_date
def upload_price_points(stubhub_prices):
current_datetime = datetime.datetime.now()
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
for stubhub_id, price_list in stubhub_prices.iteritems():
cur.execute('''INSERT INTO stubhub_point (sl_id,
update_time) SELECT id, %s FROM stubhub_listing WHERE
stubhubid = %s''', (current_datetime, stubhub_id))
conn.commit()
if not price_list:
continue
for p in price_list:
cur.execute('''INSERT INTO stubhub_zone (id, name)
SELECT %s,%s WHERE NOT EXISTS (SELECT * FROM
stubhub_zone WHERE id=%s)''', (p['zoneId'],
p['zoneName'], p['zoneId']))
cur.execute('''INSERT INTO stubhub_price (sp_id,
sz_id, min_price, max_price, min_ticket_quantity,
max_ticket_quantity, total_tickets, total_listings)
SELECT id,%s,%s,%s,%s,%s,%s,%s FROM (SELECT sp.id
FROM stubhub_point sp JOIN stubhub_listing sl
ON sp.sl_id=sl.id
WHERE sl.stubhubid=%s AND sp.update_time=%s)
as foo''', (p['zoneId'],
p['minTicketPrice'], p['maxTicketPrice'],p['minTicketQuantity'],
p['maxTicketQuantity'],p['totalTickets'],p['totalListings'],
stubhub_id, current_datetime))
conn.commit()
conn.close()
def get_seats_to_update():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT name, bitid, stubzone, eventid, stubhubid, v_name FROM
(SELECT sz.name, sz.id as stubzone, e.id as eventid, e.event_date as event_date,
sl.stubhubid, MAX(sp.update_time), e.bitid, v.name as v_name
FROM stubhub_listing sl
JOIN event e
ON e.id = sl.event_id
JOIN venue v
ON e.venue_id = v.id
JOIN event_artist ea
ON ea.event_id=e.id
JOIN artist a
ON ea.artist_id=a.id
JOIN stubhub_point sp
ON sp.sl_id = sl.id
JOIN stubhub_price spr
ON spr.sp_id = sp.id
JOIN stubhub_zone sz
ON sz.id=spr.sz_id
LEFT JOIN event_price ep
ON ep.event_id=e.id AND ep.sz_id=sz.id
WHERE e.event_date > now()
AND ep.event_id IS NULL AND ep.sz_id IS NULL
GROUP BY sz.name, e.bitid, sz.id, e.id, sl.stubhubid, v.name
ORDER BY e.bitid) as foo
ORDER BY event_date
''')
points = cur.fetchall()
conn.close()
return points
def upload_13f_holdings(self, info_tables, entry, quarter_date):
accession_nunber = entry['accession_nunber'].replace('-','')
print self.cik, accession_nunber, quarter_date
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing (conn.cursor()) as cur:
existing_entries = self.check_for_existing_form(cur, accession_nunber)
if existing_entries:
cur = self.delete_existing_entries(cur, accession_nunber)
try:
for it in info_tables:
cur.execute('''INSERT INTO form13fholdings (accessionnunber,
nameofissuer, titleofclass, cusip, value, sshprnamt,
sshprnamttype, putcall, investmentdiscretion, sole,
shared, none) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
%s,%s)''', (accession_nunber, it['nameOfIssuer'],
it['titleOfClass'], it['cusip'], it['value'],
it['sshPrnamt'], it['sshPrnamtType'], it['putCall'],
it['investmentDiscretion'], it['Sole'], it['Shared'],
it['None']))
cur = self.check_for_existing_cusip(cur, it['cusip'], accession_nunber)
cur.execute('''INSERT INTO form13flist (cik, filingdate, accessionnunber,
quarterdate, updated, filingtype) VALUES (%s,%s,%s,%s,%s,%s)
''', (self.cik, entry['filing_date'], accession_nunber,
quarter_date, entry['updated_time'],
entry['filing_type']))
conn.commit()
except IntegrityError as e:
print 'FAILED: ' + self.cik + accession_nunber
#***For Unique Value Vio, will also catch Null Vio, which is prob
print e
conn.close()
def get_upcoming_concerts(days=7):
conn = start_db_connection('AWS')
upcoming_concerts = None
with closing(conn.cursor()) as cur:
cur.execute('''SELECT a.name, e.onsale_date, e.event_date, v.name, v.capacity, v.city, v.state, mr_pop.value
FROM artist a
JOIN event_artist ea
ON ea.artist_id=a.id
JOIN event e
ON e.id=ea.event_id
JOIN venue v
ON v.id=e.venue_id
JOIN popularity_point pp
ON pp.artist_id = a.id
JOIN (SELECT pp.id, pv.pt_id, pv.value, foo.max
FROM popularity_point pp
JOIN popularity_value pv ON pv.pp_id=pp.id
JOIN
(SELECT pp.artist_id, pv.pt_id, MAX(pp.update_date)
FROM popularity_point pp
JOIN popularity_value pv ON pv.pp_id = pp.id
GROUP BY pp.artist_id, pv.pt_id
ORDER BY pp.artist_id) as foo
ON foo.artist_id=pp.artist_id
AND foo.pt_id=pv.pt_id
AND pp.update_date = foo.max) as mr_pop
ON pp.id=mr_pop.id
WHERE mr_pop.pt_id=1 AND e.onsale_date > now()
ORDER BY e.onsale_date, mr_pop.value DESC''')
upcoming_concerts = cur.fetchall()
conn.close()
return upcoming_concerts
def upload_popularity_data(popularity_data):
conn = start_db_connection()
with closing(conn.cursor()) as cur:
cur.execute('''SELECT name FROM popularity_type''')
popularity_types = [x[0] for x in cur.fetchall()]
current_date = datetime.datetime.now().date()
for pd in popularity_data:
#if not any(v for k,v in dict((i, pd[i]) for i in popularity_types).iteritems()):
# continue
cur.execute('''INSERT INTO popularity_point (artist_id, update_date)
SELECT artist.id, %s FROM artist WHERE name = %s''',
(current_date, pd['name']))
for pop_type in popularity_types:
if not pd[pop_type]:
continue
cur.execute('''INSERT INTO popularity_value (pp_id,
pt_id, value) SELECT pp.id, pt.id, %s
FROM popularity_point pp, popularity_type pt WHERE
pp.update_date = %s AND pp.artist_id = %s
AND pt.name = %s''',
(pd[pop_type], current_date, pd['id'], pop_type))
conn.commit()
conn.close()
def get_seats_to_update():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute(
'''SELECT name, bitid, stubzone, eventid, stubhubid, v_name FROM
(SELECT sz.name, sz.id as stubzone, e.id as eventid, e.event_date as event_date,
sl.stubhubid, MAX(sp.update_time), e.bitid, v.name as v_name
FROM stubhub_listing sl
JOIN event e
ON e.id = sl.event_id
JOIN venue v
ON e.venue_id = v.id
JOIN event_artist ea
ON ea.event_id=e.id
JOIN artist a
ON ea.artist_id=a.id
JOIN stubhub_point sp
ON sp.sl_id = sl.id
JOIN stubhub_price spr
ON spr.sp_id = sp.id
JOIN stubhub_zone sz
ON sz.id=spr.sz_id
LEFT JOIN event_price ep
ON ep.event_id=e.id AND ep.sz_id=sz.id
WHERE e.event_date > now()
AND ep.event_id IS NULL AND ep.sz_id IS NULL
GROUP BY sz.name, e.bitid, sz.id, e.id, sl.stubhubid, v.name
ORDER BY e.bitid) as foo
ORDER BY event_date
''')
points = cur.fetchall()
conn.close()
return points
def upload_events_to_db(event_data):
conn = start_db_connection()
with closing(conn.cursor()) as cur:
for li in event_data:
cur.execute('SELECT id FROM venue WHERE bitid = %s',
(li['bit_venue_id'], ))
ven_id = str(cur.fetchone()[0])
cur.execute(
'''INSERT INTO event (venue_id, bitID, event_date,
onsale_date) SELECT %s,%s,%s,%s WHERE NOT EXISTS (
SELECT * FROM event WHERE bitID = %s)''',
(ven_id, li['bit_event_id'], li['event_date'],
li['onsale_date'], li['bit_event_id']))
conn.commit()
for artist in li['artists']:
cur.execute(
'''INSERT INTO event_artist (artist_id, event_id)
SELECT artist.id, event.id FROM artist, event WHERE
artist.name = %s AND event.bitid = %s
AND NOT EXISTS (SELECT * FROM event_artist ea
JOIN artist a on a.id=ea.artist_id
JOIN event e on e.id = ea.event_id
WHERE a.name=%s AND e.bitid=%s)''',
(artist, li['bit_event_id'], artist, li['bit_event_id']))
conn.commit()
conn.close()
def get_stubhub_ids():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT sl.stubhubid FROM stubhub_listing sl
JOIN event e
ON e.id=sl.event_id
LEFT JOIN (SELECT DISTINCT sl_id FROM stubhub_point) as b
ON sl.id = b.sl_id
WHERE b.sl_id IS NULL
AND e.event_date > now()
UNION
SELECT sl.stubhubid FROM event e
JOIN stubhub_listing sl ON e.id=sl.event_id
JOIN
(SELECT MAX(update_time), sl_id FROM stubhub_point sp
GROUP BY sl_id
ORDER BY sl_id) as m
ON m.sl_id = sl.id
WHERE e.event_date >= now() AND
(e.onsale_date > (now() - interval '1 day')
OR now()-m.MAX > interval '24 hours'
OR e.event_date < (now() + interval '2 day'))
''')
stubhub_ids = [x[0] for x in cur.fetchall()]
conn.close()
print stubhub_ids
return stubhub_ids
def get_most_recent_13f(self):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
cur.execute('''SELECT MAX(filingDate) FROM form13flist
WHERE CIK = %s''', (self.cik,))
last_date = cur.fetchone()[0]
conn.close()
return last_date
def upload_prices(prices):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
for price in prices:
cur.execute('''INSERT INTO event_price (event_id, sz_id, min_price, max_price)
VALUES (%s,%s,%s,%s)''', (price[0],price[1],price[2],price[3]))
conn.commit()
conn.close()
def parse_entries(self):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
for entry in self.entry_elements:
cik, name = self.parse_entry(entry)
cur = self.add_to_database(cur, cik, name)
conn.commit()
conn.close()
def update_artist_db(id_pair):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''UPDATE artist SET spotifyid=%s, echonestid = %s
WHERE mbidid=%s''', (id_pair[1],id_pair[2],id_pair[0]))
conn.commit()
print 'Updated %s' %(id_pair[0])
conn.close()
def getCIKList():
conn = start_db_connection('local')
#conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT CIK FROM CIKList
WHERE importance<=3''')
cikList = [x[0] for x in cur.fetchall()]
conn.close()
return cikList
def get_most_recent_13f(self):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
cur.execute(
'''SELECT MAX(filingDate) FROM form13flist
WHERE CIK = %s''', (self.cik, ))
last_date = cur.fetchone()[0]
conn.close()
return last_date
def get_venues_to_update():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
#cur.execute('''SELECT name, state FROM venue WHERE skid IS NULL
cur.execute('''SELECT name, city FROM venue WHERE skid IS NULL
AND capacity IS NULL''')
venues = cur.fetchall()
conn.close()
return venues
def update_artist_db(id_pair):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute(
'''UPDATE artist SET spotifyid=%s, echonestid = %s
WHERE mbidid=%s''', (id_pair[1], id_pair[2], id_pair[0]))
conn.commit()
print 'Updated %s' % (id_pair[0])
conn.close()
def getCIKList():
#conn = start_db_connection('local')
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT CIK FROM CIKList
WHERE importance<=3''')
cikList = [x[0] for x in cur.fetchall()]
conn.close()
return cikList
def upload_venue_capacity(venues):
for venue, id_cap in venues.iteritems():
sk_venue_id, capacity = id_cap
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute(
'''UPDATE venue SET skid=%s, capacity=%s
WHERE name=%s''', (sk_venue_id, capacity, venue))
conn.commit()
conn.close()
def get_event_ids():
conn = start_db_connection()
with closing(conn.cursor()) as cur:
cur.execute('''SELECT e.bitid FROM event e
LEFT JOIN event_price ep ON e.id=ep.event_id
WHERE ep.event_id IS NULL''')
event_ids = [x[0] for x in cur.fetchall()]
conn.close()
return event_ids
def get_event_ids():
conn = start_db_connection()
with closing(conn.cursor()) as cur:
cur.execute('''SELECT e.bitid FROM event e
LEFT JOIN event_price ep ON e.id=ep.event_id
WHERE ep.event_id IS NULL''')
event_ids = [x[0] for x in cur.fetchall()]
conn.close()
return event_ids
def get_mbids():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT DISTINCT mbidid FROM artist WHERE mbidid IS NOT NULL
AND spotifyid IS NULL''')
#cur.execute('''SELECT DISTINCT mbid FROM artists WHERE mbid IS NOT NULL
# AND spotify_id IS NULL''')
data = [x[0] for x in cur.fetchall()]
conn.close()
return data
def get_unmatched_artists():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT id, name, mbidid FROM artist
WHERE skid IS NULL
ORDER BY id''')
artists = cur.fetchall()
conn.close()
return artists
def upload_prices(prices):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
for price in prices:
cur.execute(
'''INSERT INTO event_price (event_id, sz_id, min_price, max_price)
VALUES (%s,%s,%s,%s)''',
(price[0], price[1], price[2], price[3]))
conn.commit()
conn.close()
def update_database(artist_id, spotify_id):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''UPDATE artist
SET spotifyid = '{0}'
WHERE id = '{1}'
'''.format(spotify_id, artist_id))
conn.commit()
conn.close()
print 'Updated artist_id: ' + str(artist_id)
def get_mbids():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute(
'''SELECT DISTINCT mbidid FROM artist WHERE mbidid IS NOT NULL
AND spotifyid IS NULL''')
#cur.execute('''SELECT DISTINCT mbid FROM artists WHERE mbid IS NOT NULL
# AND spotify_id IS NULL''')
data = [x[0] for x in cur.fetchall()]
conn.close()
return data
def upload_artists_to_db(artist_data):
conn = start_db_connection()
with closing(conn.cursor()) as cur:
for ar in artist_data:
cur.execute(
'''INSERT INTO artist (name, mbidID, biturl)
SELECT %s,%s,%s WHERE NOT EXISTS (SELECT * FROM artist
WHERE name = %s)''',
(ar['name'], ar['mbid'], ar['url'], ar['name']))
conn.commit()
conn.close()
def get_price_data(self):
tickers = self.portfolio['ticker']
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
for ticker in tickers:
#price_data = self.get_db_price_data(cur, ticker)
price_data = self.request_price_data(cur, ticker)
if price_data is not None and price_data.any():
self.portfolio.loc[self.portfolio.ticker==ticker, 'startdate'] = price_data[0]
self.portfolio.loc[self.portfolio.ticker==ticker, 'enddate'] = price_data[-1]
conn.close()
def update_database(artist_id, skid, mbid=None):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
query = '''UPDATE artist SET skid = '{0}' '''.format(skid)
if mbid:
query += ", mbidid = '{0}' ".format(mbid)
query += "WHERE id = '{0}'".format(artist_id)
cur.execute(query)
conn.commit()
conn.close()
print 'Updated artist_id: ' + str(artist_id)
def get_price_data(self):
tickers = self.portfolio['ticker']
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
for ticker in tickers:
#price_data = self.get_db_price_data(cur, ticker)
price_data = self.request_price_data(cur, ticker)
if price_data is not None and price_data.any():
self.portfolio.loc[self.portfolio.ticker == ticker,
'startdate'] = price_data[0]
self.portfolio.loc[self.portfolio.ticker == ticker,
'enddate'] = price_data[-1]
conn.close()
def upload_venues_to_db(venue_data):
conn = start_db_connection()
with closing(conn.cursor()) as cur:
for vn in venue_data:
if vn['country'] != 'United States':
vn['region'] = None
cur.execute(
'''INSERT INTO venue (bitID, name, city, state,
latitude, longitude) SELECT %s,%s,%s,%s,%s,%s
WHERE NOT EXISTS (SELECT * FROM venue WHERE bitID = %s)''',
(vn['id'], vn['name'], vn['city'], vn['region'],
vn['latitude'], vn['longitude'], str(vn['id'])))
conn.commit()
conn.close()
def get_fund_tickers(self):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
cur.execute('''SELECT DISTINCT c.ticker
FROM form13flist b
INNER JOIN (SELECT DISTINCT accessionnunber, cusip
FROM form13fholdings) a
ON a.accessionnunber=b.accessionnunber
INNER JOIN cusiplist c
ON a.cusip=c.cusip
WHERE b.quarterdate = %s AND
b.cik =%s''', (self.quarter_date, self.cik))
tickers = [x[0] for x in cur.fetchall()]
conn.close()
return tickers
def get_portfolio_changes(self, start_date, end_date):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
portfolio_changes = []
for member_pair in self.members_list:
cik = member_pair[0]
cur.execute(
("SELECT filingdate FROM form13flist WHERE filingdate" "> %s AND filingdate<%s AND CIK=%s"),
(start_date, end_date, cik),
)
changes = cur.fetchall()
portfolio_changes += [x[0] for x in changes]
conn.close()
portfolio_changes = self.add_date_ends(portfolio_changes, start_date, end_date)
return portfolio_changes
def upload_stubhub_ids(event_ids):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
for event_id, stubhub_id in event_ids:
cur.execute('''INSERT INTO stubhub_listing (stubhubid, event_id)
SELECT %s, %s WHERE NOT EXISTS (SELECT * FROM stubhub_listing
WHERE stubhubid = %s OR event_id = %s)''', (stubhub_id,
event_id, str(stubhub_id), str(event_id)))
#cur.execute('''INSERT INTO stubhub_listing (stubhub_id, event_id)
# VALUES (%s, %s)''', (stubhub_id, event_id))
conn.commit()
print 'StubHub Artist: ' + str(stubhub_id)
conn.close()
def get_earnings_date_information(self):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
#Only grabs in 3m period after quarter end, important for new tickers
q_date_plus_3m = str(datetime.datetime.strptime(self.quarter_date,
'%Y-%m-%d') +
datetime.timedelta(days=92))
cur.execute('''SELECT ticker, earningsdate, time
FROM equityearningsdates WHERE
earningsdate > %s AND earningsdate < %s AND ticker=%s
ORDER BY earningsdate ASC''',
(self.quarter_date, q_date_plus_3m, self.ticker))
earnings_details = cur.fetchone()
conn.close()
return earnings_details
def upload_stubhub_ids(event_ids):
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
for event_id, stubhub_id in event_ids:
cur.execute(
'''INSERT INTO stubhub_listing (stubhubid, event_id)
SELECT %s, %s WHERE NOT EXISTS (SELECT * FROM stubhub_listing
WHERE stubhubid = %s OR event_id = %s)''',
(stubhub_id, event_id, str(stubhub_id), str(event_id)))
#cur.execute('''INSERT INTO stubhub_listing (stubhub_id, event_id)
# VALUES (%s, %s)''', (stubhub_id, event_id))
conn.commit()
print 'StubHub Artist: ' + str(stubhub_id)
conn.close()
def get_portfolio_changes(self, start_date, end_date):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
portfolio_changes = []
for member_pair in self.members_list:
cik = member_pair[0]
cur.execute(
("SELECT filingdate FROM form13flist WHERE filingdate"
"> %s AND filingdate<%s AND CIK=%s"),
(start_date, end_date, cik))
changes = cur.fetchall()
portfolio_changes += [x[0] for x in changes]
conn.close()
portfolio_changes = self.add_date_ends(portfolio_changes, start_date,
end_date)
return portfolio_changes
def upload_earnings_information(date, lines):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
cur.execute('''SELECT ticker FROM cusiplist''')
tickers = [x[0] for x in cur.fetchall()]
for line in lines:
name, ticker, time = line
if ticker in tickers:
try:
cur.execute('''INSERT INTO equityearningsdates
(ticker, earningsdate, time) VALUES
(%s,%s,%s)''',(ticker, date, time))
except psycopg2.IntegrityError:
conn.rollback()
conn.commit()
conn.close()
def most_common_holdings(self, quarter_date, limit=100):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
cur.execute(
'''SELECT c.ticker, c.longname, COUNT(a.cusip)
FROM form13flist b
INNER JOIN
(SELECT DISTINCT accessionnunber, cusip
FROM form13fholdings) a
ON a.accessionnunber=b.accessionnunber
INNER JOIN cusiplist c
ON a.cusip=c.cusip
WHERE b.quarterdate = %s
GROUP BY c.ticker, c.longname
ORDER BY count DESC
LIMIT 100''', (quarter_date, ))
print cur.fetchall()
conn.close()
def get_artists_to_update():
conn = start_db_connection()
with closing(conn.cursor()) as cur:
#SELECTS all artists that have either never been update or haven't been
#updated in greater than 6 days
cur.execute('''SELECT id, name, spotifyid
FROM
(SELECT DISTINCT ON (a.id) a.id, a.name, a.spotifyid,
MAX(pp.update_date)
FROM artist a LEFT JOIN popularity_point pp
on a.id=pp.artist_id
WHERE (a.spotifyid IS NOT NULL AND a.spotifyid <> 'no_match')
GROUP BY a.id, a.name, a.spotifyid) as foo
WHERE (MAX IS NULL OR max < now()-'7 days'::interval);''')
data = cur.fetchall()
conn.close()
return data
def get_artists_to_update():
conn = start_db_connection()
with closing(conn.cursor()) as cur:
#SELECTS all artists that have either never been update or haven't been
#updated in greater than 6 days
cur.execute('''SELECT id, name, spotifyid
FROM
(SELECT DISTINCT ON (a.id) a.id, a.name, a.spotifyid,
MAX(pp.update_date)
FROM artist a LEFT JOIN popularity_point pp
on a.id=pp.artist_id
WHERE (a.spotifyid IS NOT NULL AND a.spotifyid <> 'no_match')
GROUP BY a.id, a.name, a.spotifyid) as foo
WHERE (MAX IS NULL OR max < now()-'7 days'::interval);''')
data = cur.fetchall()
conn.close()
return data
def most_common_holdings(self, quarter_date, limit=100):
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
cur.execute(
"""SELECT c.ticker, c.longname, COUNT(a.cusip)
FROM form13flist b
INNER JOIN
(SELECT DISTINCT accessionnunber, cusip
FROM form13fholdings) a
ON a.accessionnunber=b.accessionnunber
INNER JOIN cusiplist c
ON a.cusip=c.cusip
WHERE b.quarterdate = %s
GROUP BY c.ticker, c.longname
ORDER BY count DESC
LIMIT 100""",
(quarter_date,),
)
print cur.fetchall()
conn.close()
def get_events_to_update():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT a.name, v.name, v.state, v.latitude, v.longitude,
e.event_date, e.onsale_date, AVG(pv.value), e.id
FROM artist a
JOIN event_artist ON a.id = event_artist.artist_id
JOIN event e ON e.id=event_artist.event_id
JOIN Venue v ON v.id = e.venue_id
LEFT JOIN stubhub_listing sl on e.id=sl.event_id
JOIN popularity_point pp ON a.id=pp.artist_id
JOIN popularity_value pv ON pp.id=pv.pp_id
JOIN popularity_type pt ON pt.id=pv.pt_id
WHERE sl.event_id IS NULL AND (e.onsale_date < (now()+'2 days'::interval))
AND (e.onsale_date > (now()-'14 days'::interval))
AND pp.id IN (SELECT id FROM (SELECT DISTINCT ON (artist_id) id, artist_id, update_date
FROM popularity_point ORDER BY artist_id, update_date DESC) as foo)
GROUP BY a.name, v.name, v.state, v.latitude, v.longitude, e.event_date,
e.onsale_date, e.id''')
event_list = cur.fetchall()
conn.close()
return event_list
def get_events_to_update():
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT a.name, v.name, v.state, v.latitude, v.longitude,
e.event_date, e.onsale_date, AVG(pv.value), e.id
FROM artist a
JOIN event_artist ON a.id = event_artist.artist_id
JOIN event e ON e.id=event_artist.event_id
JOIN Venue v ON v.id = e.venue_id
LEFT JOIN stubhub_listing sl on e.id=sl.event_id
JOIN popularity_point pp ON a.id=pp.artist_id
JOIN popularity_value pv ON pp.id=pv.pp_id
JOIN popularity_type pt ON pt.id=pv.pt_id
WHERE sl.event_id IS NULL AND (e.onsale_date < (now()+'2 days'::interval))
AND (e.onsale_date > (now()-'14 days'::interval))
AND pp.id IN (SELECT id FROM (SELECT DISTINCT ON (artist_id) id, artist_id, update_date
FROM popularity_point ORDER BY artist_id, update_date DESC) as foo)
GROUP BY a.name, v.name, v.state, v.latitude, v.longitude, e.event_date,
e.onsale_date, e.id''')
event_list = cur.fetchall()
conn.close()
return event_list
def upload_13f_holdings(self, info_tables, entry, quarter_date):
accession_nunber = entry['accession_nunber'].replace('-', '')
print self.cik, accession_nunber, quarter_date
conn = start_db_connection(DB_CONNECTION_TYPE)
with closing(conn.cursor()) as cur:
existing_entries = self.check_for_existing_form(
cur, accession_nunber)
if existing_entries:
cur = self.delete_existing_entries(cur, accession_nunber)
try:
for it in info_tables:
cur.execute(
'''INSERT INTO form13fholdings (accessionnunber,
nameofissuer, titleofclass, cusip, value, sshprnamt,
sshprnamttype, putcall, investmentdiscretion, sole,
shared, none) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
%s,%s)''',
(accession_nunber, it['nameOfIssuer'],
it['titleOfClass'], it['cusip'], it['value'],
it['sshPrnamt'], it['sshPrnamtType'], it['putCall'],
it['investmentDiscretion'], it['Sole'], it['Shared'],
it['None']))
cur = self.check_for_existing_cusip(
cur, it['cusip'], accession_nunber)
cur.execute(
'''INSERT INTO form13flist (cik, filingdate, accessionnunber,
quarterdate, updated, filingtype) VALUES (%s,%s,%s,%s,%s,%s)
''', (self.cik, entry['filing_date'],
accession_nunber, quarter_date,
entry['updated_time'], entry['filing_type']))
conn.commit()
except IntegrityError as e:
print 'FAILED: ' + self.cik + accession_nunber
#***For Unique Value Vio, will also catch Null Vio, which is prob
print e
conn.close()
def upload_popularity_data(popularity_data):
conn = start_db_connection()
with closing(conn.cursor()) as cur:
cur.execute('''SELECT name FROM popularity_type''')
popularity_types = [x[0] for x in cur.fetchall()]
current_date = datetime.datetime.now().date()
for pd in popularity_data:
cur.execute(
'''INSERT INTO popularity_point (artist_id, update_date)
SELECT artist.id, %s FROM artist WHERE name = %s''',
(current_date, pd['name']))
for pop_type in popularity_types:
if not pd[pop_type]:
continue
cur.execute(
'''INSERT INTO popularity_value (pp_id,
pt_id, value) SELECT pp.id, pt.id, %s
FROM popularity_point pp, popularity_type pt WHERE
pp.update_date = %s AND pp.artist_id = %s
AND pt.name = %s''',
(pd[pop_type], current_date, pd['id'], pop_type))
conn.commit()
conn.close()
from dbconnection import start_db_connection
from contextlib import closing
conn = start_db_connection('local')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT * FROM artist''')
artists = cur.fetchall()
cur.execute('''SELECT * FROM event''')
events = cur.fetchall()
cur.execute('''SELECT * FROM event_artist''')
ea = cur.fetchall()
cur.execute('''SELECT * FROM popularity_point''')
pp = cur.fetchall()
#cur.execute('''SELECT * FROM popularity_type''')
#pt = cur.fetchall()
cur.execute('''SELECT * FROM popularity_value''')
pv = cur.fetchall()
cur.execute('''SELECT * FROM stubhub_listing''')
sl = cur.fetchall()
cur.execute('''SELECT * FROM stubhub_point''')
sp = cur.fetchall()
cur.execute('''SELECT * FROM stubhub_price''')
from dbconnection import start_db_connection
from contextlib import closing
#conn = start_db_connection('local')
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
sql = '''CREATE TABLE artist (
id SERIAL PRIMARY KEY,
name VARCHAR(128) UNIQUE NOT NULL,
skID VARCHAR(10),
mbidID VARCHAR(40),
spotifyID VARCHAR(36),
echonestID VARCHAR(24),
bitURL text
)'''
cur.execute(sql)
sql = '''CREATE TABLE popularity_point (
id SERIAL PRIMARY KEY,
artist_id integer NOT NULL REFERENCES artist(id) ON DELETE CASCADE,
update_date date NOT NULL,
UNIQUE(artist_id, update_date)
)'''
cur.execute(sql)
sql = '''CREATE TABLE popularity_type (
id SERIAL PRIMARY KEY,
from dbconnection import start_db_connection
from contextlib import closing
#conn = start_db_connection('local')
conn = start_db_connection('AWS')
with closing(conn.cursor()) as cur:
sql = '''CREATE TABLE artist (
id SERIAL PRIMARY KEY,
name VARCHAR(128) UNIQUE NOT NULL,
skID VARCHAR(10),
mbidID VARCHAR(40),
spotifyID VARCHAR(36),
echonestID VARCHAR(24),
bitURL text
)'''
cur.execute(sql)
sql = '''CREATE TABLE popularity_point (
id SERIAL PRIMARY KEY,
artist_id integer NOT NULL REFERENCES artist(id) ON DELETE CASCADE,
update_date date NOT NULL,
UNIQUE(artist_id, update_date)
)'''
cur.execute(sql)
sql = '''CREATE TABLE popularity_type (
id SERIAL PRIMARY KEY,
name VARCHAR(32) UNIQUE NOT NULL
)'''
from dbconnection import start_db_connection
from contextlib import closing
conn = start_db_connection('local')
with closing(conn.cursor()) as cur:
cur.execute('''SELECT * FROM artist''')
artists = cur.fetchall()
cur.execute('''SELECT * FROM event''')
events = cur.fetchall()
cur.execute('''SELECT * FROM event_artist''')
ea = cur.fetchall()
cur.execute('''SELECT * FROM popularity_point''')
pp = cur.fetchall()
#cur.execute('''SELECT * FROM popularity_type''')
#pt = cur.fetchall()
cur.execute('''SELECT * FROM popularity_value''')
pv=cur.fetchall()
cur.execute('''SELECT * FROM stubhub_listing''')
sl =cur.fetchall()
cur.execute('''SELECT * FROM stubhub_point''')
sp=cur.fetchall()
cur.execute('''SELECT * FROM stubhub_price''')
