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 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)
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()]