def profile_history(username):
# get info for author card
card = prof_edit.get_card(username)
# list of watchlist tables and files
watchlists = []
# connect to database to get existing watchlist names
conn = db.get_db()
user_lists = conn.execute('''SELECT DISTINCT watchlist_name FROM IMDb_Watchlist WHERE username="******"'''.format(username=username)).fetchall()
for ent in user_lists:
watchlists.append(ent['watchlist_name'])
# list of recent videos; restrict to 4 titles
recents = []
# TESTING HARDCODE DUMMY
recents.extend([{'imdbid':'tt0105236','title':'Reservoir Dogs','url':''},
{'imdbid':'tt4975722','title':'Moonlight','url':''},
{'imdbid':'tt0475784','title':'Westworld','url':''},
{'imdbid':'tt3322314','title':'Luke Cage','url':''}])
for rec in recents:
rec['url'] = pi.get_poster_url_sql(rec['imdbid'],rec['title'])
# dictionary containing page content
page = {'watchlists': watchlists,
'recents': recents }
return render_template('profile/profile-history.html', profile=card, page=page, username=username)
def login():
if request.method == 'POST':
username = request.form['username']
password1 = request.form['password']
conn = db.get_db()
error = None
user = conn.execute('''SELECT * FROM all_user_data WHERE username="******"'''.format(username=username)).fetchone()
# grabbing all possible errors
if not username:
error = 'Username is required.'
elif not password1:
error = 'Password is required.'
elif user is None:
error = 'Incorrect username.'
elif user['password'] != password1:
error = 'Incorrect password.'
if error is None:
session.clear()
session['user_id'] = user['userID']
return redirect(url_for('profile_edit', username=username))
flash(error)
return render_template('bootstrap-login-login.html')
def imdb_database(table_name, imdbID, title, poster, year, certificate,
runtime, genres, IMDb_rating, intro, director, star):
# Connect to database
con = db_conn.get_db()
# Create a new table
create_table_query = '''CREATE TABLE IF NOT EXISTS {table} (imdbID varchar(255) PRIMARY KEY,
title varchar(255),
poster varchar(255),
year varchar(255),
certificate varchar(255),
runtime int,
genres varchar(255),
IMDb_rating real,
intro varchar(255),
director varchar(255),
star varchar(255))'''.format(table=table_name)
con.execute(create_table_query)
# Insert head into the table
insert_query = '''INSERT IGNORE INTO {table} (imdbID, title, poster, year, certificate, runtime, genres, IMDb_rating,
intro, director, star)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''.format(
table=table_name)
# Execute query
query_parameters = (imdbID, title, poster, year, certificate, runtime,
genres, IMDb_rating, intro, director, star)
con.execute(insert_query, query_parameters)
con.close()
# end of method
return
def get_poster_url(external_id):
# call API
TMDb_response = single_content_TMDb(external_id)
# technically could push bottom two into separate methods in case grab content type info out of downloaded watchlist's columns
# if content is movie
if len(TMDb_response['movie_results']) == 1:
if TMDb_response['movie_results'][0]['poster_path'] != None:
return POSTER_URL_HEAD + TMDb_response['movie_results'][0][
'poster_path']
# if content is tv show
if len(TMDb_response['tv_results']) == 1:
if TMDb_response['tv_results'][0]['poster_path'] != None:
return POSTER_URL_HEAD + TMDb_response['tv_results'][0][
'poster_path']
# couldn't find poster from TMDb
conn = db.get_db()
poster_query = conn.execute(
'''SELECT poster FROM IMDb_Catalog WHERE imdbID="{id}"'''.format(
id=external_id)).fetchone()
conn.close()
return poster_query['poster']
def load_logged_in_user():
user_id = session.get('user_id')
if user_id is None:
g.user = None
else:
g.user = db.get_db().execute('''SELECT * FROM all_user_data WHERE userID="{user_id}"'''.format(user_id=user_id)).fetchone()
def main():
con = dbc.get_db()
if test() == True:
# pop up message on sign up screen
message = "You already have an existing account. Please return to Login."
ret = render_template('bootstrap-login-signup.html', message=message)
else:
# NEED TO CATCH WHERE HAVE 'NONE' IN ALL FIELDS
username = str(request.args.get('username'))
password = str(request.args.get('pass'))
fname = str(request.args.get('first'))
lname = str(request.args.get('last'))
email = str(request.args.get('email'))
join_date = get_time()
query_parameters = (username, password, fname, lname, email, join_date)
message = "Success! Thank you for joining StreamLine."
query_template = '''INSERT IGNORE INTO {db}.{table}(username,
password,
fname,
lname,
email,
join_date)
VALUES (%s, %s, %s, %s, %s, %s)'''.format(
db='streamline', table='all_user_data')
# con.execute(query_template, query_parameters)
ret = render_template('bootstrap-login-signup.html',
page_title="Success!",
message=message)
con.close()
return ret
def ranked_genre(username):
# connect to database
conn = db.get_db()
# fetch watchlist content
# CURRENTLY HARDCODE, NEED TO SETUP VARIABLE LATER
# "IMDb_Watchlist_username"?
genres = conn.execute('SELECT Genres FROM IMDb_Watchlist_Jenny').fetchall()
# parse genres in watchlist into dictionary
# key -> genre
# value -> incrementing
genre_dict = {}
for title in genres:
tgenres = parse_genres(title[0])
for ent in tgenres:
if ent not in genre_dict.keys():
genre_dict[ent] = 1
else:
genre_dict[ent] += 1
ranked = {
k: v
for k, v in sorted(
genre_dict.items(), key=lambda item: item[1], reverse=True)
}
conn.close()
return ranked
def __init__(self, ticker):
self.ticker = ticker.upper()
self.db = db_connect.get_db()
result = self.db.companies.find({'ticker': self.ticker})
if result.count():
self.stats = result[0]
else:
self.stats = self.initStats()
def main():
con = db_conn.get_db()
ret = render_template('bootstrap_login-login.html')
return ret
# Need function to get data user submits and compare it to SQL database
# If okay, direct to profile page
def ensureCompanies(name):
t = db_connect.get_db()[name].aggregate([
{'$group': {'_id': '_id',
'tickers': {'$addToSet':'$details.ticker'}}}
])
if t['result']:
for ticker in t['result'][0]['tickers']:
Company(ticker)
def __init__(self, name="main"):
"""Select an optional save file name.
save: optional string specifying a portfolio name. Defaults to 'store'
"""
self._name = name
self._db = db_connect.get_db()
self._update()
company.ensureCompanies(self._name)
def update_sql_bio(username, bio):
# connect to database
conn = db.get_db()
# write new bio content into database
conn.execute(
'UPDATE all_user_data SET user_bio = %s WHERE username = %s',
(bio, username),
)
# conn.commit()
conn.close()
return
def __init__(self):
self.db = db_connect.get_db()
events = []
test = Portfolio('test') # Comment me out
self.portfolios.append(test) # Comment me out
# main = Portfolio() # Uncomment Me
# self.portfolios.append(main) # Uncomment Me
for p in self.portfolios:
self.tickers.extend(p.getTickers())
if 'current' not in self.db.collection_names():
fetch.fetch(self.tickers)
def get_bio(username):
# connect to database
conn = db.get_db()
# fetch bio content from all user table
bio = conn.execute(
'SELECT user_bio FROM all_user_data WHERE username = %s',
(username, ),
).fetchone()
conn.close()
# check if bio is empty
if bio[0] is None:
return ""
return bio[0]
def get_card(username):
# connect to database
conn = db.get_db()
# create to return dictionary
card = {'first': '', 'last': '', 'month': '', 'year': ''}
# fetch content from all user table
user_info = conn.execute(
'''SELECT * FROM all_user_data WHERE username="******"'''.format(
username=username)).fetchone()
conn.close()
# fill information
card['fname'] = user_info['fname']
card['lname'] = user_info['lname']
card['join_month'] = user_info['join_date'].strftime("%B")
card['join_year'] = user_info['join_date'].year
return card
def test():
# connect to database
con = dbc.get_db()
# get user provided username and password
username = str(request.args.get('username'))
password = str(request.args.get('pass'))
# query check if user already exists
test_query = '''SELECT EXISTS(SELECT * FROM all_user_data WHERE username="******")'''.format(
username=username)
# BELOW CAUSING ERROR
# test_query = '''SELECT username FROM all_user_data WHERE EXISTS (SELECT username FROM all_user_data WHERE username = {username}'''.format(username=username)
test_check = con.execute(test_query)
con.close()
# parse through returned object
for ent in test_check:
if ent[0] == 1:
return True
return False
def get_poster_url_sql(external_id, title):
# connect to database
con = db.get_db()
# does entry already exist?
exist_query = '''SELECT EXISTS(SELECT * FROM posters WHERE imdbID="{id}")'''.format(
id=external_id)
exist_check = con.execute(exist_query)
for ent in exist_check:
if ent[0] == 1: # does already exist
# check if doesn't have poster
retrieve_query = '''SELECT * FROM posters WHERE imdbID="{id}"'''.format(
id=external_id)
retrieve_url = con.execute(retrieve_query)
# THERE HAS TO BE A BETTER WAY TO DO THIS
image_url = ''
for ret in retrieve_url:
image_url = ret[2]
if image_url == NO_POSTER_URL:
replace_url = get_poster_url(external_id)
insert_query = '''UPDATE posters SET url="{url}" WHERE imdbID="{id}"'''.format(
url=replace_url, id=external_id)
con.execute(insert_query)
con.close()
return replace_url
else:
con.close()
return image_url
else: # does not already exist
replace_url = get_poster_url(external_id)
insert_query = '''INSERT INTO posters (imdbID, title, url) VALUES ("{id}","{title}","{url}")'''.format(
id=external_id, title=title, url=replace_url)
con.execute(insert_query)
con.close()
return replace_url
return NO_POSTER_URL
def profile_security(username):
# get info for author card
card = prof_edit.get_card(username)
# connect to db
conn = db.get_db()
# dictionary for items to put into input boxes of page
form_input = conn.execute('''SELECT username, password, email FROM all_user_data WHERE username="******"'''.format(username=username)).fetchone()
form_dic = {'username':form_input['username'],'password':form_input['password'],'email':form_input['email']}
# update database from submitting change to form
if request.method == 'POST':
# OH WHOOPS NEED TO ADDRESS DUPLICATES
# update_username = conn.execute('''UPDATE all_user_data SET username="******" WHERE ''')
update_password = conn.execute('''UPDATE all_user_data SET password="******" WHERE username="******"'''.format(password=request.form['password'], username=username))
update_email = conn.execute('''UPDATE all_user_data SET email="{email}" WHERE username="******"'''.format(email=request.form['email'],username=username))
return redirect(url_for('profile_security', username=username))
conn.close()
return render_template('profile/profile-security.html', profile=card, username=username, form_dic=form_dic)
def sign_up():
# print("Enter 0")
if request.method == 'POST':
username = request.form['username'].strip()
password = request.form['password'].strip()
fname = request.form['first'].strip()
lname = request.form['last'].strip()
email = request.form['email'].strip()
join_date = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
conn = db.get_db()
error = None
# grabbing all possible errors
if not username:
error = 'Username is required.'
elif not password:
error = 'Password is required.'
elif not fname:
error = 'First name is required.'
elif not lname:
error = 'Last name is required.'
elif not email:
error = 'Email is required.'
elif conn.execute('''SELECT EXISTS(SELECT * FROM all_user_data WHERE username="******")'''.format(username=username)).fetchone()[0] == 1:
error = 'User {} is already registered.'.format(username)
# print("Enter 9")
if error is None:
# print("Enter 10")
conn.execute('''INSERT INTO all_user_data (userID, username, password, fname, lname, email, join_date, user_bio, linked_amazon, linked_netflix, linked_hbo, linked_hulu) VALUES (default, "{username}", "{password}", "{fname}", "{lname}", "{email}", "{join_date}","", FALSE, FALSE, FALSE, FALSE)'''.format(username=username,password=password,fname=fname,lname=lname,email=email,join_date=join_date))
return redirect(url_for('login'))
flash(error)
# print("Enter 3")
# page = signup.main() # replaced by above
return render_template('bootstrap-login-signup.html')
def convert_to_sql(parsed_loc, username, watchlist_name):
# Get the parsed watchlist
# parsed_loc = sr.watchlist_parse(watchlist)
# Connect to database
con = db_conn.get_db()
# # REMOVED BY JESSICA 05.01
# # Drop the old table if exists
# drop_table_query = '''DROP table IF EXISTS {table}'''.format(table=table_name)
# con.execute(drop_table_query)
# # Create a new table
# create_table_query = '''CREATE TABLE IF NOT EXISTS {table} (position int,
# imdbID text,
# title varchar(255),
# google_rent real,
# google_buy real,
# itunes_rent real,
# itunes_buy real,
# amazon_prime bool,
# netflix bool,
# hbo bool,
# hulu bool,
# nowhere bool,
# PRIMARY KEY(position, title))'''.format(table=table_name)
# con.execute(create_table_query)
# Query set up to insert into Parsed_Watchlist table
insert_query = '''INSERT IGNORE INTO Parsed_Watchlist (position, username, watchlist_name, imdbID, title, google_rent, google_buy, google_url, itunes_rent, itunes_buy, itunes_url, amazon_prime, netflix, hbo, hulu, nowhere)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'''
# creating set of all items - Jessica
all_set = set()
all_set.update(parsed_loc['individual']['google']['buy'].keys())
all_set.update(parsed_loc['individual']['itunes']['buy'].keys())
all_set.update(parsed_loc['subscription']['amazon prime'])
all_set.update(parsed_loc['subscription']['netflix'])
all_set.update(parsed_loc['subscription']['hbo'])
all_set.update(parsed_loc['subscription']['hulu'])
all_set.update(parsed_loc['nowhere'])
# slice the dictionary
for index, title in enumerate(all_set):
# position
position = index + 1
imdbID = parsed_loc['ids'][title]
# individual
# MODIFIED by Jessica to handle cases where no ind option
google_rent = None
google_buy = None
google_url = ""
itunes_rent = None
itunes_buy = None
itunes_url = ""
if title in parsed_loc['individual']['google']['rent']:
google_rent = parsed_loc['individual']['google']['rent'][title]
if title in parsed_loc['individual']['google']['buy']:
google_buy = parsed_loc['individual']['google']['buy'][title]
if title in parsed_loc['individual']['itunes']['rent']:
itunes_rent = parsed_loc['individual']['itunes']['rent'][title]
if title in parsed_loc['individual']['itunes']['buy']:
itunes_buy = parsed_loc['individual']['itunes']['buy'][title]
# subscription
amazon_prime = True if title in parsed_loc['subscription'][
'amazon prime'] else False
netflix = True if title in parsed_loc['subscription'][
'netflix'] else False
hbo = True if title in parsed_loc['subscription']['hbo'] else False
hulu = True if title in parsed_loc['subscription']['hulu'] else False
nowhere = True if title in parsed_loc['nowhere'] else False
# execute query
query_parameters = (position, username, watchlist_name, imdbID, title,
google_rent, google_buy, google_url, itunes_rent,
itunes_buy, itunes_url, amazon_prime, netflix, hbo,
hulu, nowhere)
con.execute(insert_query, query_parameters)
con.close()
import db_connect
import company
__author__ = "Chadwick Puterbaugh"
__copyright__ = "2014"
__credits__ = "Chadwick Puterbaugh"
__license__ = "GPL"
__version__ = "0.3.3"
__maintainer__ = "Chadwick Puterbaugh"
__email__ = "*****@*****.**"
__status__ = "Prototype"
today = datetime.date.today()
db = db_connect.get_db()
def getTickers():
""" Retrieves tickers from a given portfolio
returns: list of tickers
"""
tickerList = []
t = db.companies.aggregate([
{'$group': {'_id': '_id',
'tickers': {'$push':
{'ticker':'$ticker', 'market': '$market.exchange'}}}}
])
def retrieve_from_sql(username):
# Connect to database
con = db_conn.get_db()
# TO DO: proper try catch error handling
query = '''SELECT DISTINCT imdbID, title, google_rent, google_buy, itunes_rent, itunes_buy, amazon_prime, netflix, hbo, hulu, nowhere FROM Parsed_Watchlist WHERE username="******"'''.format(
username=username)
query_ret = con.execute(query)
# Constructing dictionary that will be returned
parsed_loc = {
'individual': {
'google': {
'rent': dict(),
'buy': dict()
},
'itunes': {
'rent': dict(),
'buy': dict()
},
'amazon instant': {
'rent': dict(),
'buy': dict()
}
},
'subscription': {
'amazon prime': [],
'netflix': [],
'hbo': [],
'hulu': []
},
'nowhere': []
}
# headers: index, title, google_buy, google_rent, itunes_buy,
# itunes_rent, amazon_prime, netflix, hbo, hulu, nowhere
# Going through each line of table
# NOTE: there is definitely a better way to do this
for item in query_ret:
# check and fill in individual
# GOOGLE
if item['google_rent'] is not None:
parsed_loc['individual']['google']['rent'][
item['title']] = item['google_rent']
if item['google_buy'] is not None:
parsed_loc['individual']['google']['buy'][
item['title']] = item['google_buy']
# ITUNES
if item['itunes_rent'] is not None:
parsed_loc['individual']['itunes']['rent'][
item['title']] = item['itunes_rent']
if item['itunes_buy'] is not None:
parsed_loc['individual']['itunes']['buy'][
item['title']] = item['itunes_buy']
# check and fill in subscriptions
# AMAZON
if item['amazon_prime'] == 1:
parsed_loc['subscription']['amazon prime'].append({
'title':
item['title'],
'imdbID':
item['imdbID']
})
# NETFLIX
if item['netflix'] == 1:
parsed_loc['subscription']['netflix'].append({
'title':
item['title'],
'imdbID':
item['imdbID']
})
# HBO
if item['hbo'] == 1:
parsed_loc['subscription']['hbo'].append({
'title': item['title'],
'imdbID': item['imdbID']
})
# HULU
if item['hulu'] == 1:
parsed_loc['subscription']['hulu'].append({
'title': item['title'],
'imdbID': item['imdbID']
})
# check if nowhere
if item['nowhere'] == 1:
parsed_loc['nowhere'].append({
'title': item['title'],
'imdbID': item['imdbID']
})
con.close()
return parsed_loc
