def combine_database_files(databases):
"""
Combine all the database files into one database, return name of main database
"""
sorted(databases)
conn = get_connection(db_name=databases[0])
cursor = conn.cursor()
for db in databases[1:]:
# get current db count
cursor.execute('''SELECT MAX(sentence_no) from words''')
sentences_so_far = cursor.fetchone()[0]
# update sentence count in current db
temp_conn = get_connection(db_name=db)
temp_cursor = temp_conn.cursor()
temp_cursor.execute(
'''UPDATE words SET sentence_no = sentence_no + ?''',
[sentences_so_far])
temp_conn.commit()
temp_conn.close()
# attach next db to main db
db2_name = 'db' + str(uuid4()).replace('-', '')[:6]
cursor.execute('''ATTACH ? AS ?;''', [db, db2_name])
cursor.execute(
'INSERT INTO main.words SELECT * FROM {}.words;'.format(db2_name))
conn.commit()
conn.close()
return databases[0]
def get_games() -> Union[str, Tuple[str, int]]:
week_id, error = try_get_param("weekId", int)
if week_id == None:
return error, 400
c = get_connection()
result = c.execute(
"""
select
t1.original_city team1_original_city,
t1.original_mascot team1_original_mascot,
t1.city team1_city,
t1.mascot team1_mascot,
t1.team_id team1_id,
t2.original_city team2_original_city,
t2.original_mascot team2_original_mascot,
t2.city team2_city,
t2.mascot team2_mascot,
t2.team_id team2_id,
g.game_id
from game g
inner join team t1 on t1.team_id = g.team1_id
inner join team t2 on t2.team_id = g.team2_id
where g.week_id = ?
""",
(week_id, ),
).fetchall()
fields = ["originalCity", "originalMascot", "city", "mascot", "id"]
response = get_team_structure(result, fields)
return json.dumps(response)
def connect(userid, requesterid):
""" Route for sending a connection request to a user """
# Checks if connection between users exist
connection = get_connection(conn, int(requesterid), int(userid))
# If connection does not exist yet, create connection
if connection == None:
create_connection(conn, int(requesterid), int(userid))
flash("Connection request sent!", "success")
return redirect("/profile/" + userid)
def get_weeks() -> Union[str, Tuple[str, int]]:
year, error = try_get_param("year", int)
if year == None:
return error, 400
c = get_connection()
result = c.execute(
"""select week_id, week_name from week where year = ? order by week_order""",
(year, ),
).fetchall()
return json.dumps(result)
def create_words_tables(databases):
"""Create the table to store the words"""
for db_name in databases:
connection = get_connection(db_name=db_name)
cursor = connection.cursor()
cursor.execute(
'''CREATE TABLE IF NOT EXISTS words( word TEXT, sentence_no INTEGER, file_no INTEGER);'''
)
cursor.execute('''DELETE FROM words''')
cursor.execute('''DROP INDEX IF EXISTS word_index''')
connection.commit()
connection.close()
return True
def add_data_to_db(filename, file_no):
"""
Read data from file and populate words table - returns number of sentences analyzed
"""
connection = get_connection(file_no=file_no)
with open(filename, 'r') as f:
# final sentence may not be a complete sentence, save and prepend to next chunk
leftovers = ''
sentence_no = 0
# store batch word inserts here
word_rows = []
for chunk in read_in_chunks(f): # lazy way of reading our file in case it's large
# prepend leftovers to chunk
chunk = leftovers + chunk
# run nlp
chunk_doc = nlp(chunk)
# tokenized sentences from chunk
sents = [sent.string.strip() for sent in chunk_doc.sents]
# save for next chunk
leftovers = sents[-1]
for s in sents[:-1]:
sentence_no += 1
# add sentence to db
add_sentence_to_word_rows(s, word_rows, sentence_no, file_no)
if len(word_rows) >= BULK_INSERT_SIZE:
insert_word_rows(connection, word_rows)
del word_rows[:] # saves us the pain of reassigning and waiting for a garbage collector
if word_rows: # insert leftovers into word rows, then left over word rows into db
sentence_no += 1
add_sentence_to_word_rows(leftovers, word_rows, sentence_no, file_no)
insert_word_rows(connection, word_rows)
insert_word_rows(connection, [['z'*25, 0, file_no]])
connection.commit()
connection.close()
return sentence_no
def get_scores() -> Union[str, Tuple[str, int]]:
week_id, error = try_get_param("weekId", int)
if week_id == None:
return error, 400
c = get_connection()
result = c.execute(
"""
with max_score as
(
select max(s.score_order), s.score_id
from score s
inner join game g on g.game_id = s.game_id
where g.week_id = $1
group by g.game_id, quarter
)
select
g.game_id,
s.quarter,
s.time,
s.scoring_team_id,
s.detail,
s.team1_game_score,
s.team2_game_score,
s.team1_misery_index,
s.team2_misery_index,
s.score_order,
case when ms.score_id is null then false else true end is_last_of_quarter
from score s
inner join game g on g.game_id = s.game_id
left outer join max_score ms on ms.score_id = s.score_id
where g.week_id = $1
order by g.game_id, score_order
""",
(week_id, ),
).fetchall()
fields = ["gameScore", "miseryIndex"]
response = get_team_structure(result, fields)
return json.dumps(response)
def accept(otheruserid):
""" Handles connection acceptance """
# Queries database for connection
connection = get_connection(conn, session["user_id"], int(otheruserid))
# Checks for existing connection
if connection == None:
flash("Connection does not exist.", "danger")
return redirect(url_for('connections'))
# Checks permission for acceptance
if session["user_id"] != connection["receiver_id"]:
flash("You don't have permission to do that.", "danger")
return redirect(url_for('connections'))
# If everything is ok, accept connection and redirect
accept_connection(conn, connection["id"])
flash("Connection request accepted!", "success")
return redirect(url_for('connections'))
def profile(userid):
""" Render profile page """
# Get user profile
user = get_user_profile(conn, userid)
# Get connection between profile user and logged user
connection = get_connection(conn, session["user_id"], int(userid))
# Get location
location = get_location(conn, userid)
# Get user occupations
occupations = get_all_occupations(conn, userid)
# Get instruments
instruments = get_all_instruments(conn, userid)
# Get pages
pages = get_all_pages(conn, userid)
return render_template("profile.html", user=user, connection=connection, location=location, occupations=occupations, instruments=instruments, pages=pages)
q = channel.queue_declare(queue='completed')
while completed < len(filenames):
method_frame, header_frame, body = channel.basic_get('completed')
if method_frame:
completed += 1
sentences.append(int(body))
channel.basic_ack(method_frame.delivery_tag)
else:
pass # no message returned
time.sleep(2)
logging.info('Combining database files...')
main_db_name = combine_database_files(database_names)
# combine databases
logging.info('Indexing database...')
conn = get_connection(db_name=main_db_name)
cursor = conn.cursor()
add_index_to_db(cursor)
conn.commit() # final commit
if outputfile:
print_db(cursor, outputfile)
else:
print_db(cursor)
conn.close() # close db connection
total_time = round((datetime.now() - start_time).total_seconds(), 2)
print('success!!\n\nFinished in {} seconds.'.format(total_time))
def get_years() -> str:
c = get_connection()
db_response = c.execute(
"""select distinct year from week order by year desc""").fetchall()
result = sql_result_values(db_response)
return json.dumps(result)