def load_from_db_by_screening_id(cls, id):
with CursorConnectionFromPool() as cursor:
cursor.execute('SELECT * FROM screenings WHERE id = %s', (id,))
screening_data = cursor.fetchone()
if screening_data:
return cls(movie_id=screening_data[1], theatre=screening_data[2], date=screening_data[3],
attending=screening_data[4], id=screening_data[0])
def createInstructorTable():
with CursorConnectionFromPool() as cursor:
function = "SELECT distinct lower(instructor) from project.classmetadata"
cursor.execute(function)
result = cursor.fetchall()
count = 0
for re in result:
r = re[0]
instructor2 = None
if ' & ' in r:
instructor1, instructor2 = r.split(' & ')
if instructor2 is None and ' and ' in r:
instructor1, instructor2 = r.split(' and ')
if instructor2 is not None:
insertQuery = "INSERT INTO project.instructor(id, name) VALUES ({},'{}')".format(
count, instructor1)
cursor.execute(insertQuery)
count += 1
insertQuery = "INSERT INTO project.instructor(id,name) VALUES ({},'{}')".format(
count, instructor2)
cursor.execute(insertQuery)
count += 1
print(insertQuery)
else:
insertQuery = "INSERT INTO project.instructor(id,name) VALUES ({}, '{}')".format(
count, r)
print(insertQuery)
cursor.execute(insertQuery)
count += 1
def load_from_db_by_name_or_address(cls, name, address,
response_parameters):
with CursorConnectionFromPool() as cursor:
#cursor.execute('SELECT * FROM users WHERE email=%s',(email,))
if (name and not address):
cursor.execute(
'SELECT transaction_id,name_orig FROM ssot.transaction')
user_dict = dict(cursor.fetchall())
name_list = list(user_dict.values())
matched_names_scores = process.extract(name,
name_list,
limit=3)
elif (address and not name):
cursor.execute(
'SELECT transaction_id,address1_orig FROM ssot.transaction'
)
user_dict = dict(cursor.fetchall())
address_list = list(user_dict.values())
matched_names_scores = process.extract(address,
address_list,
limit=3)
#print(matched_name)
cursor.execute(
'SELECT column_name FROM information_schema.columns WHERE table_schema = \'ssot\' AND table_name = \'transaction\''
)
temp_columns = list(cursor.fetchall())
response_list = []
response_columns = [name for (name, ) in temp_columns]
matched_names = [name for (name, score) in matched_names_scores]
matched_scores = [score for (name, score) in matched_names_scores]
matched_id = []
for id, name in user_dict.items():
if name in matched_names:
index_name = matched_names.index(name)
matched_names.remove(name)
#print(index_name)
cursor.execute(
'SELECT * FROM ssot.transaction WHERE transaction_id = %s',
(id, ))
response_data = cursor.fetchone()
print(response_data)
response_dict = dict(zip(response_columns, response_data))
#response_list.append(response_dict)
response_dict_filtered = {}
response_parameter_list = response_parameters.split(",")
if ',' in response_parameters:
for i in response_parameter_list:
response_dict_filtered[i] = response_dict[i]
elif (response_parameters):
response_dict_filtered[
response_parameters] = response_dict[
response_parameters]
response_dict_filtered['score'] = matched_scores[
index_name]
response_list.append(response_dict_filtered)
response_list = sorted(response_list,
key=lambda k: 100 - k['score'])
#from operator import itemgetter
#sortedresponse = sorted(response_list, key=itemgetter('score'))
return cls(response_list=response_list)
def load_from_db_by_id(cls, id):
with CursorConnectionFromPool() as cursor:
cursor.execute('SELECT * FROM movie WHERE id = %s', (id,))
movie_data = cursor.fetchone()
if movie_data:
return cls(name=movie_data[1], director=movie_data[2], synopsis=movie_data[3], photo_url=movie_data[4],
id=movie_data[0])
def load_screenings_by_movie_id(cls, id):
with CursorConnectionFromPool() as cursor:
cursor.execute('SELECT * FROM screenings WHERE movie_id= %s', (id,))
screenings = cursor.fetchall()
screening_list = [cls(movie_id=screenings[i][1], theatre=screenings[i][2], date=screenings[i][3],
attending=screenings[i][4], id=screenings[i][0])
for i in range(len(screenings))]
if screening_list:
return screening_list
def load_all(cls):
with CursorConnectionFromPool() as cursor:
cursor.execute('SELECT * FROM movie')
# gets all movies in the database
movies = cursor.fetchall()
# uses list comprehension to generate a list of movies, then returns that list
movie_list = [cls(name=movies[i][1], director=movies[i][2], synopsis=movies[i][3],
photo_url=movies[i][4], id=movies[i][0])
for i in range(len(movies))]
if movie_list:
return movie_list
def createCourseTable():
with CursorConnectionFromPool() as cursor:
function = "Select distinct course, title from project.classmetadata"
cursor.execute(function)
result = cursor.fetchall()
for index, re in enumerate(result):
course, title = re
insertQuery = "INSERT INTO project.course(id, course_number, title) VALUES ({},'{}','{}')".format(
index, course, title)
print(insertQuery)
cursor.execute(insertQuery)
def load_from_db_by_screen_name(cls, screen_name):
with CursorConnectionFromPool() as cursor:
# connection = connection_pool.getconn()
cursor.execute('SELECT * FROM users WHERE screen_name=%s',
(screen_name, ))
user_data = cursor.fetchone()
if user_data:
return cls(screen_name=user_data[1],
oauth_token=user_data[2],
oauth_token_secret=user_data[3],
id=user_data[0])
def load_from_db_by_name_address(cls, name, address, response_parameters):
with CursorConnectionFromPool() as cursor:
cursor.execute(
'SELECT transaction_id,name_orig FROM ssot.transaction')
dict_name = dict(cursor.fetchall())
cursor.execute(
'SELECT transaction_id,address1_orig FROM ssot.transaction')
dict_address = dict(cursor.fetchall())
ds = [dict_name, dict_address]
merged_dict = {}
for k in dict_name:
merged_dict[k] = " ".join([str(d[k]) for d in ds])
name_address_list = list(merged_dict.values())
matched_nameaddress = process.extract(name + address,
name_address_list,
limit=3)
print("matched_name", matched_nameaddress)
cursor.execute(
'SELECT column_name FROM information_schema.columns WHERE table_schema = \'ssot\' AND table_name = \'transaction\''
)
temp_columns = list((cursor.fetchall()))
response_columns = [name for (name, ) in temp_columns]
response_list = []
matched_names = [name for (name, score) in matched_nameaddress]
matched_scores = [score for (name, score) in matched_nameaddress]
matched_id = -99999999
for id, nameaddress in merged_dict.items():
if nameaddress in matched_names:
index_name = matched_names.index(nameaddress)
matched_names.remove(nameaddress)
cursor.execute(
'SELECT * FROM ssot.transaction WHERE transaction_id = %s',
(id, ))
response_data = cursor.fetchone()
print('response data', response_data)
response_dict = dict(zip(response_columns, response_data))
# response_list.append(response_dict)
response_dict_filtered = {}
response_parameter_list = response_parameters.split(",")
if ',' in response_parameters:
for i in response_parameter_list:
response_dict_filtered[i] = response_dict[i]
elif (response_parameters):
response_dict_filtered[
response_parameters] = response_dict[
response_parameters]
response_dict_filtered['score'] = matched_scores[
index_name]
response_list.append(response_dict_filtered)
response_list = sorted(response_list,
key=lambda k: 100 - k['score'])
return cls(response_list=response_list)
def createQuarterTable():
with CursorConnectionFromPool() as cursor:
function = "Select distinct term from project.classmetadata"
cursor.execute(function)
result = cursor.fetchall()
for index, re in enumerate(result):
content = re[0]
term, year = content.split(" ")
insertQuery = "INSERT INTO project.quarter(id, term, year) VALUES ({},'{}',{})".format(
index, term, int(year))
print(insertQuery)
cursor.execute(insertQuery)
def load_from_db_by_name(cls, name):
with CursorConnectionFromPool() as cursor:
# the replace function allows us to directly put the name in the url when requesting for that movie,
# replacing all of the spaces with a dashes
# the %s allows us to format the sql query with variables in python, replacing the %s with the variable
# next to it, in this case the variable 'name'
cursor.execute('SELECT * FROM movie WHERE name = %s', (name.replace("-", " "),))
movie_data = cursor.fetchone()
if movie_data:
# returns a movie object if it exists in the database
return cls(name=movie_data[1], director=movie_data[2], synopsis=movie_data[3], photo_url=movie_data[4],
id=movie_data[0])
def createScheduleTable():
with CursorConnectionFromPool() as cursor:
function = "Select distinct time from project.classmetadata"
cursor.execute(function)
result = cursor.fetchall()
count = 0
for re in result:
r = re[0]
insertQuery = "INSERT INTO project.schedule(id, day, time) VALUES "
try:
day, time = r.split(", ")
if " " in day:
d, t = day.split(" ")
# adding day time to table
data = "({}, '{}', '{}')".format(count, d, t)
insert = insertQuery + data
print(insert)
cursor.execute(insert)
count += 1
elif " " in time:
d, t = day.split(" ")
# adding day time to table
data = "({}, '{}', '{}')".format(count, d, t)
insert = insertQuery + data
print(insert)
cursor.execute(insert)
count += 1
else:
# adding day time to table
data = "({}, '{}', '{}')".format(count, day, time)
insert = insertQuery + data
print(insert)
cursor.execute(insert)
count += 1
except:
try:
day, time = r.split(" ")
# adding day time to table
# adding day time to table
data = "({}, '{}', '{}')".format(count, day, time)
insert = insertQuery + data
print(insert)
cursor.execute(insert)
count += 1
except:
print(r)
def createStudentTable():
first_name = np.array([
"Mary", "Geogre", "Lucy", "Tim", "Jennifer", "Jerry", "Wendy", "Larry"
"Candy", "Andrew"
])
last_name = np.array([
"Hamilton",
"Ng",
"Li",
"Le",
"Smith",
"Johnson",
"Davis",
"Rodriguez",
"Lopez",
"Nguyen",
"Chan",
])
genders = np.array(["male", "female", "other"])
day = np.arange(1, 32)
month = np.arange(1, 13)
birthday_year = np.arange(1989, 2003)
count = 0
for first in first_name:
for last in last_name:
bd = day[np.random.randint(0, len(day))]
bdm = month[np.random.randint(0, len(month))]
bdy = birthday_year[np.random.randint(0, len(birthday_year))]
ad = day[np.random.randint(0, len(day))]
am = month[np.random.randint(0, len(month))]
ay = bdy + 18
gender = genders[np.random.randint(0, len(genders))]
birthday = "{}/{}/{}".format(bdm, bd, bdy)
admisionDay = "{}/{}/{}".format(am, ad, ay)
# print(first, last, birthday, admisionDay)
with CursorConnectionFromPool() as cursor:
function = "INSERT INTO project.student(id, first_name, last_name, birthday, gender, admission_date) VALUES ({},'{}','{}','{}','{}','{}')".format(
count, first, last, birthday, gender, admisionDay)
print(function)
cursor.execute(function)
count += 1
def createStudentInClassTable():
classInstanceIDs = np.arange(993)
insertQuery = "INSERT INTO project.student_in_class(student_id, class_instance_id) VALUES"
with CursorConnectionFromPool() as cursor:
for classInstanceID in classInstanceIDs:
numberOfStudent = np.random.randint(10, 30)
studentIDs = np.random.choice(98, numberOfStudent, replace=False)
for studentID in studentIDs:
value = "(%d, %d)" % (studentID, classInstanceID)
insert = insertQuery + value
print(insert)
cursor.execute(insert)
# createInstructorTable()
# createScheduleTable()
# createCourseTable()
# createQuarterTable()
# createStudentTable()
# createClassInstance()
# createStudentInClassTable()
def save_to_db(self):
with CursorConnectionFromPool() as cursor:
# Running code
cursor.execute(
'INSERT INTO users (screen_name, oauth_token, oauth_token_secret) VALUES (%s, %s, %s)',
(self.screen_name, self.oauth_token, self.oauth_token_secret))
def load_from_db_by_email(cls, email):
with CursorConnectionFromPool() as cursor:
cursor.execute('select * from users where email=%s',(email,))
user_data = cursor.fetchone()
return cls(email=user_data[1],first_name=user_data[2],last_name=user_data[3],id=user_data[0])
def save_to_db(self):
with CursorConnectionFromPool() as cursor:
cursor.execute('insert into users(email,first_name,last_name) values (%s,%s,%s)',
(self.email,self.first_name,self.last_name))
def update_attending(count, id):
with CursorConnectionFromPool() as cursor:
cursor.execute('UPDATE screenings SET attending = attending + %s WHERE id = %s', (count, id,))