def create_account(email, password):
account_id = generate_id()
hashed_password = hash_password(password)
params = (account_id, email, hashed_password)
sql = '''INSERT INTO accounts
(id, email, hashed_password, date_created)
VALUES
(?, ?, ?, DATETIME('now'))'''
db.cursor().execute(sql, params)
return account_id
def create_session(account_id):
session_id = generate_session_id()
hashed_session_id = hash_session_id(session_id)
params = (account_id, hashed_session_id)
sql = '''INSERT INTO sessions
(account_id, hashed_session_id, date_created, date_accessed)
VALUES
(?, ?, DATETIME('now'), DATETIME('now'))'''
db.cursor().execute(sql, params)
return session_id
def getLocationAssets():
with db.conn(settings['database']) as conn:
with db.cursor(conn) as c:
c.execute(queries.read_all([
'uri',
]))
return [asset[0] for asset in c.fetchall()]
def doesStudentExist(email):
c = db.cursor()
print("SELECT id FROM students WHERE email = \'%s\'" % email)
rowsCount = c.execute("SELECT id FROM students WHERE email = \'%s\'" %
email)
c.close()
return rowsCount != 0
def doesStudentExist(email):
c = db.cursor()
print("SELECT id FROM students WHERE email = \'%s\'" % email)
rowsCount1 = c.execute("SELECT id FROM students WHERE email = \'%s\'" %
email)
rowsCount2 = c.execute(
"SELECT studentID FROM non_nhs WHERE email = \'%s\'" % email)
c.close()
return (rowsCount1 + rowsCount2) != 0
def main():
# Make sure the asset folder exist. If not, create it
if not path.isdir(settings['assetdir']):
mkdir(settings['assetdir'])
# Create config dir if it doesn't exist
if not path.isdir(settings.get_configdir()):
makedirs(settings.get_configdir())
with db.conn(settings['database']) as conn:
with db.cursor(conn) as cursor:
cursor.execute(queries.exists_table)
if cursor.fetchone() is None:
cursor.execute(assets_helper.create_assets_table)
def get_account_with_password(email, password):
params = (email, )
sql = '''SELECT * FROM accounts WHERE
email = ?'''
account = db.cursor().execute(sql, params).fetchone()
if account is None:
raise AuthError
if not argon2.verify(password, account['hashed_password']):
raise AuthError
return account
def studentHasPassword(email):
c = db.cursor()
rowsCount = c.execute(
"SELECT id FROM students WHERE email = \'%s\' AND password IS NOT NULL"
% email)
if rowsCount > 0:
c.close()
return "Password Set"
else:
c.execute("SELECT code FROM students WHERE email = \'%s\'" % email)
(code) = c.fetchone()
c.close()
return code[0]
def insertStudent(row):
c = db.cursor()
studentID = row[0]
email = row[1]
code = makeid(12)
print(email)
print(code)
# Salt First Last Code email
print(
"INSERT INTO non_nhs (studentID, email, password, score, code, verified, timeCode) VALUES(\'{0}\', \'{1}\', NULL, 0, \'{2}\',0, CURRENT_TIMESTAMP)"
.format(studentID, email, code))
c.execute(
"INSERT INTO non_nhs (studentID, email, password, score, code, verified, timeCode) VALUES(\'{0}\', \'{1}\', NULL, 0, \'{2}\',0, CURRENT_TIMESTAMP)"
.format(studentID, email, code))
sendEmailCreateAccount(email, code)
c.connection.commit()
def check_current_session():
account_id, session_id = get_session_cookies()
if not account_id or not session_id:
raise NoSessionError
hashed_session_id = hash_session_id(session_id)
params = (account_id, hashed_session_id)
sql = '''SELECT * FROM sessions WHERE
account_id = ? AND hashed_session_id = ?'''
session = db.cursor().execute(sql, params).fetchone()
if session is None:
raise NoSessionError
return session['account_id']
def insertStudent(row):
print("yeezy")
c = db.cursor()
salt = makeid(12)
code = makeid(7)
print(salt)
print(code)
# Salt First Last Code email
print(
"INSERT INTO students (salt, first, last, year, coordinator, code, email, password, sid, login) VALUES(\'{0}\', \'{1}\', \'{2}\', 11, 0, \'{3}\', \'{4}\', NULL, NULL, CURRENT_TIMESTAMP)"
.format(salt, row[0], row[1], code, row[2]))
c.execute(
"INSERT INTO students (salt, first, last, year, coordinator, code, email, password, sid, login) VALUES(\'{0}\', \'{1}\', \'{2}\', 11, 0, \'{3}\', \'{4}\', NULL, NULL, CURRENT_TIMESTAMP)"
.format(salt, row[0], row[1], code, row[2]))
sendEmailCreateAccount(row, code)
c.connection.commit()
def init_kenban():
# Create images and templates folder if they don't exist
if not os.path.isdir(k_settings['images_folder']):
os.mkdir(k_settings['images_folder'])
# Create config dir if it doesn't exist
if not os.path.isdir(k_settings['templates_folder']):
os.mkdir(k_settings['templates_folder'])
# Create database tables if they don't exist
with db.conn(s_settings['database']) as conn:
with db.cursor(conn) as cursor:
cursor.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name='schedule'"
)
if cursor.fetchone() is None:
cursor.execute(create_schedule_table)
cursor.execute(
"SELECT name FROM sqlite_master WHERE type='table' AND name='events'"
)
if cursor.fetchone() is None:
cursor.execute(create_event_table)
def test_execute_custom_cursor(self):
db = self.db()
db.connect()
cur = db.cursor()
db.execute("select 1", cur=cur)
self.assertEqual("select 1", cur.execute_kwargs[0][0])
def test_execute_custom_cursor(self):
db = self.db()
db.connect()
cur = db.cursor()
db.execute("select 1", cur=cur)
self.assertEqual("select 1", cur.execute_kwargs[0][0])
def init_db():
with closing(connect_db()) as db:
with app.open_resource('schema.sql', mode='r') as f:
db.cursor().executescript(f.read())
db.commit()
def test_cursor(self):
db = self.db()
db.connect()
self.assertTrue(db.cursor() is not None)
self.assertTrue(db._conn.cursor_kwargs is not None)
@auth_basic
def static_with_mime(path):
mimetype = request.args['mime'] if 'mime' in request.args else 'auto'
return send_from_directory(directory='static', filename=path, mimetype=mimetype)
if __name__ == "__main__":
# Make sure the asset folder exist. If not, create it
if not path.isdir(settings['assetdir']):
mkdir(settings['assetdir'])
# Create config dir if it doesn't exist
if not path.isdir(settings.get_configdir()):
makedirs(settings.get_configdir())
with db.conn(settings['database']) as conn:
with db.cursor(conn) as cursor:
cursor.execute(queries.exists_table)
if cursor.fetchone() is None:
cursor.execute(assets_helper.create_assets_table)
config = {
'bind': '{}:{}'.format(LISTEN, PORT),
'threads': 2,
'timeout': 20
}
class GunicornApplication(Application):
def init(self, parser, opts, args):
return config
def load(self):
################################
@route('/static/:path#.+#', name='static')
def static(path):
return static_file(path, root='static')
if __name__ == "__main__":
# Make sure the asset folder exist. If not, create it
if not path.isdir(settings['assetdir']):
mkdir(settings['assetdir'])
# Create config dir if it doesn't exist
if not path.isdir(settings.get_configdir()):
makedirs(settings.get_configdir())
with db.conn(settings['database']) as conn:
global db_conn
db_conn = conn
with db.cursor(db_conn) as c:
c.execute(queries.exists_table)
if c.fetchone() is None:
c.execute(assets_helper.create_assets_table)
run(
host=settings.get_listen_ip(),
port=settings.get_listen_port(),
server='gunicorn',
timeout=240,
)
# Static
################################
@route('/static/:path#.+#', name='static')
def static(path):
return static_file(path, root='static')
if __name__ == "__main__":
# Make sure the asset folder exist. If not, create it
if not path.isdir(settings['assetdir']):
mkdir(settings['assetdir'])
# Create config dir if it doesn't exist
if not path.isdir(settings.get_configdir()):
makedirs(settings.get_configdir())
with db.conn(settings['database']) as conn:
global db_conn
db_conn = conn
with db.cursor(db_conn) as c:
c.execute(queries.exists_table)
if c.fetchone() is None:
c.execute(assets_helper.create_assets_table)
run(
host=settings.get_listen_ip(),
port=settings.get_listen_port(),
server='gunicorn',
timeout=240,
)
def build_assets_table():
""" Use the kenban schedule and event table to build the assets table used by screenly"""
with db.conn(s_settings['database']) as conn:
conn.row_factory = dict_factory
with db.cursor(conn) as c:
c.execute('select * from schedule')
schedule_slots = c.fetchall()
c.execute('select * from event')
events = c.fetchall()
c.execute('delete from assets')
# Parse the times
for slot in schedule_slots:
# This gets the start time in UTC to save in the database. It's messy but when I made it shorter it broke
slot_start_local = datetime.datetime.strptime(slot["start_time"],
"%H:%M:%S")
as_full_datetime = datetime.datetime.now(tz=tzlocal())\
.replace(hour=slot_start_local.hour, minute=0, second=0, microsecond=0)
utc_start = as_full_datetime.astimezone(pytz.utc)
slot["start_time"] = utc_start.time()
for event in events:
try:
event["event_start"] = datetime.datetime.strptime(
event["event_start"], "%Y-%m-%dT%H:%M:%S+00:00")
event["event_end"] = datetime.datetime.strptime(
event["event_end"], "%Y-%m-%dT%H:%M:%S+00:00")
except ValueError:
logging.error("Failed converting event datetime")
# if we ever convert to python3, use datetime.isoformat() instead of this mess
# The current method assumes utc which it should always be but ya never know
# Put slots in a dict according to the weekday
day_slots = {}
for day in WEEKDAYS:
day_slots[day] = []
for slot in schedule_slots:
if slot["weekday"] == day:
day_slots[day].append(slot)
# Sort the slots according to start time
day_slots[day].sort(key=lambda x: x["start_time"])
for x in range(0, len(day_slots[day]) - 1):
day_slots[day][x]["end_time"] = day_slots[day][x + 1]["start_time"]
# The last slot of the day finishes at 23:59
if day_slots[day]:
# Create 23:59 in local time and convert it to UTC
end = datetime.datetime.now(tz=tzlocal()).replace(hour=23,
minute=59,
second=59,
microsecond=9999)
day_slots[day][-1]["end_time"] = end.astimezone(
pytz.utc).replace(tzinfo=None).time()
# Loop through the days of the upcoming week and apply the schedule
# Create 00:00 in local time and convert to UTC
midnight = datetime.datetime.now(tz=tzlocal()).replace(hour=0,
minute=0,
second=0,
microsecond=0)
utc_midnight_in_local_time = midnight.astimezone(pytz.utc)
for x in range(0, 7):
day_start = utc_midnight_in_local_time + datetime.timedelta(days=x)
day_end = utc_midnight_in_local_time + datetime.timedelta(days=x + 1)
# Loop through every slot on this day and create an asset
day_name = day_start.strftime("%A")
for slot in day_slots[day_name]:
asset_start = datetime.datetime.combine(day_start.date(),
slot["start_time"])
asset_end = datetime.datetime.combine(day_end.date(),
slot["end_time"])
# Check if any events are during this period
for event in events:
if day_start <= event["event_start"] <= day_end:
create_asset_from_event(event, slot)
create_asset_from_schedule_slot(schedule_slot=slot,
start=asset_start,
end=asset_end)
def test_cursor(self):
db = self.db()
db.connect()
self.assertTrue(db.cursor() is not None)
self.assertTrue(db._conn.cursor_kwargs is not None)
mimetype = request.args['mime'] if 'mime' in request.args else 'auto'
return send_from_directory(directory='static',
filename=path,
mimetype=mimetype)
if __name__ == "__main__":
# Make sure the asset folder exist. If not, create it
if not path.isdir(settings['assetdir']):
mkdir(settings['assetdir'])
# Create config dir if it doesn't exist
if not path.isdir(settings.get_configdir()):
makedirs(settings.get_configdir())
with db.conn(settings['database']) as conn:
with db.cursor(conn) as cursor:
cursor.execute(queries.exists_table)
if cursor.fetchone() is None:
cursor.execute(assets_helper.create_assets_table)
config = {
'bind': '{}:{}'.format(LISTEN, PORT),
'threads': 2,
'timeout': 20
}
class GunicornApplication(Application):
def init(self, parser, opts, args):
return config
def load(self):
def get_event_uuids():
with db.conn(s_settings['database']) as conn:
with db.cursor(conn) as c:
c.execute('select uuid from event')
return [uuid[0] for uuid in c.fetchall()]
