def remove_vote(user_id, planet_name):
"""Remove vote from planet_votes table."""
sql = """DELETE FROM planet_votes WHERE account_id = %s AND planet_name = %s;"""
parameters = (user_id, planet_name)
fetch = None
data_manager.query(sql, parameters, fetch)
def add_vote(user_id, planet_name):
"""Add vote to planet_votes table."""
sub_time = create_timestamp()
sql = """INSERT INTO planet_votes (planet_name, account_id, sub_time)
VALUES (%s, %s, %s);"""
parameters = (planet_name, user_id, sub_time)
fetch = None
data_manager.query(sql, parameters, fetch)
def create_account(user_name, password):
"""Create account in accounts table."""
reg_date = create_timestamp()
hashed_pw = generate_password_hash(password,
method='pbkdf2:sha512:80000',
salt_length=8)
sql = """INSERT INTO accounts (user_name, password, reg_date)
VALUES (%s, %s, %s);"""
parameters = (user_name, hashed_pw, reg_date)
fetch = None
data_manager.query(sql, parameters, fetch)
def get_user_names():
"""Return all user names."""
sql = """SELECT user_name FROM accounts;"""
parameters = None
fetch = "col"
return data_manager.query(sql, parameters, fetch)
def get_user_password(user_name):
"""Get password for user_name in accounts table."""
sql = """SELECT password FROM accounts WHERE user_name = %s;"""
parameters = (user_name, )
fetch = 'cell'
return data_manager.query(sql, parameters, fetch)
def get_user_id(user_name):
"""Get id by user_name from accounts table."""
sql = """SELECT id FROM accounts WHERE user_name = %s;"""
parameters = (user_name, )
fetch = 'cell'
return data_manager.query(sql, parameters, fetch)
def get_vote_statistics():
"""Return vote statistics as a list of dictionaries."""
sql = """SELECT planet_name, COUNT(planet_name) as vote_count
FROM planet_votes
GROUP BY planet_name
ORDER BY vote_count DESC;"""
parameters = None
fetch = 'all'
return data_manager.query(sql, parameters, fetch)
def vote_exists(user_id, planet_name):
"""Check if certain user already voted on certain planet."""
sql = """SELECT id FROM planet_votes WHERE account_id = %s AND planet_name = %s;"""
parameters = (user_id, planet_name)
fetch = 'cell'
try:
return data_manager.query(sql, parameters, fetch)
except TypeError as err:
return None