def post(self): '''create login for pilot''' connection = get_connection("database_server.db") cursor = connection.cursor() payload = api.payload username = payload['username'] password = payload['password'] select_stmt = cursor.execute( 'SELECT Passwort FROM Pilot WHERE Nutzername LIKE ?', [username]) try: # wenn der pilot schon ein passwort hat, return 512 if select_stmt.fetchone()[0] is not None: return {}, 512 # falls es keine ergebnisse gibt -> pilot existiert nicht except TypeError: return {}, 512 cursor.execute('UPDATE Pilot SET Passwort = ? WHERE Nutzername LIKE ?', [password, username]) connection.commit() connection.close() return {}, 200
def get(self): '''get pilots''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): return {}, 401 args = pilots_parser.parse_args() p_id = args['id'] is_active = args['is_active'] return_dict = { 'pilots': [] } # /pilots?is_active=true if p_id is None: if is_active: select_stmt = cursor.execute( 'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin ' 'FROM Pilot WHERE RFID_Code IS NOT NULL ' ) else: select_stmt = cursor.execute( 'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin ' 'FROM Pilot WHERE RFID_Code IS NULL ' ) # /pilots?id=1 elif is_active is None: select_stmt = cursor.execute( 'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin ' 'FROM Pilot WHERE PilotID = ?', [p_id] ) # /pilots?id=1&is_active=true else: if is_active: select_stmt = cursor.execute( 'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin ' 'FROM Pilot WHERE PilotID = ? AND RFID_Code IS NOT NULL', [p_id] ) else: select_stmt = cursor.execute( 'SELECT PilotID, Vorname, Nachname, Eintrittsdatum, RFID_Code, Nutzername, Ist_Admin ' 'FROM Pilot WHERE PilotID = ? AND RFID_Code IS NULL', [p_id] ) for row in select_stmt: pilot = { 'pilot_id': row[0], 'pilot_name': row[1], 'pilot_surname': row[2], 'entry_date': row[3], 'rfid': 'null' if row[4] is None else hex(row[4]), 'pilot_username': row[5], 'is_admin': bool(row[6]) } return_dict['pilots'].append(pilot) connection.close() return return_dict
def sync_rfids(): if not is_online(): print("Terminal offline") return connection = get_connection("database_server.db") cursor = connection.cursor() unsynced_rfid_list = cursor.execute( 'SELECT RFID_Code FROM RFID_Ausweis WHERE Synced = FALSE' ).fetchall() for rfid in unsynced_rfid_list: payload = { 'rfid_code': rfid[0] } try: response = requests.post(url=RASPI_URL + '/rfid', data=payload, timeout=2) except: print('Post failed') return if not response.ok: continue set_synced_rfid(rfid[0], True) connection.close() return
def sync_pilots(): if not is_online(): print("Terminal is offline") return connection = get_connection("database_server.db") cursor = connection.cursor() unsynced_pilot_list = cursor.execute( 'SELECT PilotID, RFID_Code, Token FROM Pilot WHERE Synced = FALSE' ).fetchall() for pilot in unsynced_pilot_list: payload = { 'pilot_id': pilot[0], 'rfid_code': pilot[1], 'token': pilot[2] } try: response = requests.post(url=RASPI_URL + '/pilot', json=payload, timeout=2) except: print('Post failed') return if not response.ok: continue set_synced_pilot(pilot[0], True) connection.close() return
def get(self): '''all active sessions''' connection = get_connection("database_server.db") cursor = connection.cursor() p_id = is_pilot(cursor) if p_id == -1: return {}, 401 return_dict = {'sessions': []} select_stmt = cursor.execute( 'SELECT P.PilotID, P.Vorname, P.Nachname, time(F.Startzeit), F.Ist_Flugleiter ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'WHERE F.Endzeit IS NULL') for row in select_stmt: session = { 'pilot_id': row[0], 'pilot_name': row[1] + " " + row[2], 'start_time': row[3], 'session_leader': row[4] } return_dict['sessions'].append(session) connection.close() return return_dict
def put(self): '''add guest name and info to session''' connection = get_connection("database_server.db") cursor = connection.cursor() payload = api.payload args = put_parser.parse_args() p_id = cursor.execute( 'SELECT PilotID FROM Flugsession WHERE SessionID = ?', [args['id']]).fetchone()[0] # wenn der pilot kein admin ist und die p_id nicht übereinstimmt, return 401 if p_id != is_pilot(cursor) and not is_admin(cursor): connection.close() return {}, 401 # wenn die endzeit mitgeschickt wurde, darf diese nur geändert werden, wenn die vorherige NULL war. if 'end_time' in payload.keys(): end_time = cursor.execute( 'SELECT Endzeit FROM Flugsession WHERE SessionID = ?', [args['id']]).fetchone()[0] if end_time is None: cursor.execute( 'UPDATE Flugsession SET Endzeit = ? WHERE SessionID = ?', [payload['end_time'], args['id']]) else: connection.close() return {}, 401 if 'guest_name' in payload.keys(): if 'guest_info' not in payload.keys(): guest_info = None else: guest_info = payload['guest_info'] cursor.execute('INSERT INTO Gast(Gastname, Freitext) VALUES (?,?)', [payload['guest_name'], guest_info]) guest_row_nr = cursor.lastrowid guest_id = cursor.execute( 'SELECT GastID FROM Gast WHERE ROWID = ?', [guest_row_nr]).fetchone()[0] cursor.execute( 'UPDATE Flugsession SET GastID = ? WHERE SessionID = ?', [guest_id, args['id']]) if 'is_leader' in payload.keys(): cursor.execute( 'UPDATE Flugsession SET Ist_Flugleiter = ? WHERE SessionID = ?', [payload['is_leader'], args['id']]) connection.commit() connection.close() return {}
def set_synced_rfid(rfid_code, is_synced): connection = get_connection("database_server.db") cursor = connection.cursor() cursor.execute( 'UPDATE RFID_Ausweis SET Synced = ? WHERE RFID_Code = ?', [is_synced, rfid_code] ) connection.commit() connection.close() return
def set_synced_pilot(pilot_id, is_synced): connection = get_connection("database_server.db") cursor = connection.cursor() cursor.execute( 'UPDATE Pilot SET Synced = ? WHERE PilotID = ?', [is_synced, pilot_id] ) connection.commit() connection.close() return
def post(self): '''log off all pilots''' connection = get_connection("database_server.db") cursor = connection.cursor() cursor.execute( 'UPDATE Flugsession SET Endzeit = datetime() WHERE Endzeit is NULL' ) connection.commit() connection.close() return {}
def get(self): '''get settings''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): connection.close() return {}, 401 settings = {} with open('settings.json') as settings_file: settings['settings'] = json.load(settings_file) return settings
def post(self): '''Post finished and unfinished sessions from terminal to backend''' connection = get_connection("database_server.db") cursor = connection.cursor() payload = api.payload start_time = datetime.combine( date.fromisoformat(payload['session_date']), datetime.strptime(payload['start_time'], "%H:%M").time()) # erst wird geschaut, ob der datensatz bereits existiert (ret > 0) ret = cursor.execute( 'SELECT PilotID FROM Flugsession ' 'WHERE PilotID = ? AND Startzeit = ?', [payload['pilot_id'], start_time]).fetchall() # case: neue session if 'end_time' not in payload.keys(): cursor.execute( 'INSERT INTO Flugsession(PilotID, Startzeit, Ist_Flugleiter) VALUES (?,?,?)', [payload['pilot_id'], start_time, payload['is_leader']]) # vorhandene session updaten # case: session ohne endzeit vorhanden # ret ist None, wenn es keine Sessions (mit PilotID, und Startzeit) gibt elif ret: end_time = datetime.combine( date.fromisoformat(payload['session_date']), datetime.strptime(payload['end_time'], "%H:%M").time()) cursor.execute( 'UPDATE Flugsession SET Endzeit = ?, Ist_Flugleiter = ? WHERE PilotID = ? AND Startzeit = ?', [ end_time, payload['is_leader'], payload['pilot_id'], start_time ]) # case: session wird jetzt zum ersten mal synchronisiert (mit endzeit) else: end_time = datetime.combine( date.fromisoformat(payload['session_date']), datetime.strptime(payload['end_time'], "%H:%M").time()) cursor.execute( 'INSERT INTO Flugsession(PilotID, Startzeit, Endzeit, Ist_Flugleiter) VALUES (?,?,?,?)', [ payload['pilot_id'], start_time, end_time, payload['is_leader'] ]) connection.commit() connection.close() return {}
def sync_settings(): if not is_online(): print("Terminal offline") return connection = get_connection("database_server.db") cursor = connection.cursor() with open('settings.json') as settings_file: settings = json.load(settings_file) try: response = requests.post(url=RASPI_URL + '/settings', json=settings, timeout=2) except: print('Post failed') return
def put(self): '''update pilot''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): return {}, 401 payload = api.payload p_id = payload['pilot_id'] if 'pilot_name' in payload.keys(): new_first_name = payload['pilot_name'] cursor.execute('UPDATE Pilot SET Vorname = ? WHERE PilotID = ?', [new_first_name, p_id]) if 'pilot_surname' in payload.keys(): new_last_name = payload['pilot_surname'] cursor.execute('UPDATE Pilot SET Nachname = ? WHERE PilotID = ?', [new_last_name, p_id]) if 'rfid' in payload.keys(): if not payload['rfid']: new_rfid = None else: new_rfid = int(payload['rfid'], 16) cursor.execute('UPDATE Pilot SET RFID_Code = ?, Synced = FALSE WHERE PilotID = ?', [new_rfid, p_id]) if 'pilot_username' in payload.keys(): new_user_name = payload['pilot_username'] cursor.execute('UPDATE Pilot SET Nutzername = ? WHERE PilotID = ?', [new_user_name, p_id]) if 'is_admin' in payload.keys(): new_admin = payload['is_admin'] cursor.execute('UPDATE Pilot SET Ist_Admin = ? WHERE PilotID = ?', [new_admin, p_id]) if 'reset_password' in payload.keys(): new_password = payload['reset_password'] if new_password: cursor.execute('UPDATE Pilot SET Passwort = NULL WHERE PilotID = ?', [p_id]) connection.commit() connection.close() # piloten mit terminal synchronisieren sync_pilots() return {}
def post(self): '''add session''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): return {}, 401 payload = api.payload start_time = datetime.combine( date.fromisoformat(payload['session_date']), datetime.strptime(payload['start_time'], "%H:%M").time()) try: end_time = datetime.combine( date.fromisoformat(payload['session_date']), datetime.strptime(payload['end_time'], "%H:%M").time()) except KeyError: end_time = None try: guest_name = payload['guest_name'] guest_info = payload['guest_info'] # gast einfügen cursor.execute('INSERT INTO Gast(Gastname, Freitext) VALUES (?,?)', [guest_name, guest_info]) guest_row_nr = cursor.lastrowid guest_id = cursor.execute( 'SELECT GastID FROM Gast WHERE ROWID = ?', [guest_row_nr]).fetchone()[0] except KeyError: guest_id = None # session einfügen cursor.execute( 'INSERT INTO Flugsession(PilotID, GastID, Startzeit, Endzeit, Ist_Flugleiter) VALUES (?,?,?,?,?)', [ payload['pilot_id'], guest_id, start_time, end_time, payload['is_leader'] ]) connection.commit() connection.close() return {}
def post(self): '''login pilot''' connection = get_connection("database_server.db") cursor = connection.cursor() payload = api.payload username = payload['username'] password = payload['password'] select_stmt = cursor.execute( 'SELECT Passwort FROM Pilot WHERE Nutzername LIKE ?', [username] ) try: # wenn der pilot kein passwort hat, return 403 pwd_db = select_stmt.fetchone()[0] if pwd_db is None: connection.close() return {}, 403 # falls es keine ergebnisse gibt -> pilot existiert nicht, return 403 except TypeError: connection.close() return {}, 403 user_stmt = cursor.execute( 'SELECT PilotID, Vorname, Nachname, Nutzername, Ist_Admin, Token FROM Pilot WHERE Nutzername LIKE ?', [username] ) # wenn das passwort stimmt, return daten und token, sonst 403 if checkpw(password.encode('utf8'), pwd_db.encode('utf8')): user = user_stmt.fetchone() connection.close() return { 'token': user[5], 'user': { 'id': user[0], 'name': user[1] + ' ' + user[2], 'username': user[3], 'is_admin': user[4] } } else: connection.close() return {}, 403
def post(self): '''post settings to server''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): connection.close() return {}, 401 file = open('settings.json', 'w') file.write(json.dumps(api.payload)) file.close() sync_settings() # todo: prüfen, ob erfolgreich synchronisiert, ggf. neu synchronisieren return {}
def post(self): '''add RFID Tags to DB''' connection = get_connection("database_server.db") cursor = connection.cursor() payload = api.payload if not is_admin(cursor): return {}, 401 cursor.execute( 'INSERT INTO RFID_Ausweis (RFID_Code, Synced) VALUES (?, FALSE)', [int(payload['rfid'], 16)] ) connection.commit() connection.close() sync_rfids() return {}
def get(self): '''get id, and name of every pilot''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): return {}, 401 return_dict = { 'pilots': [] } for row in cursor.execute('SELECT PilotID, Vorname, Nachname, Nutzername FROM Pilot'): pilot = { 'pilot_id': row[0], 'pilot_name': row[1] + " " + row[2], 'pilot_username': row[3] } return_dict['pilots'].append(pilot) connection.close() return return_dict
def get(self): '''assigned RFID tags''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): return {}, 401 return_dict = {'rfid_list': []} for row in cursor.execute( 'SELECT PilotID, RFID_Code, Vorname, Nachname ' 'FROM Pilot WHERE RFID_CODE IS NOT NULL'): list_item = { 'pilot_id': row[0], 'pilot_name': row[2] + ' ' + row[3], 'rfid': hex(row[1]) } return_dict['rfid_list'].append(list_item) connection.close() return return_dict
def post(self): '''create pilot''' connection = get_connection("database_server.db") cursor = connection.cursor() if not is_admin(cursor): return {}, 401 payload = api.payload first_name = payload['pilot_name'] last_name = payload['pilot_surname'] rfid = int(payload['rfid'], 16) username = payload['pilot_username'] admin = bool(payload['is_admin']) # erzeuge token token = abs(hash(username)) select_stmt = cursor.execute( 'SELECT Token FROM Pilot' ).fetchall() all_tokens = [t[0] for t in select_stmt] # wenn token vergeben, inkrementiere und prüfe erneut while token in all_tokens: token += 1 cursor.execute( 'INSERT INTO Pilot(RFID_Code, Nachname, Vorname, Eintrittsdatum, Nutzername, Passwort, Ist_Admin, Token, Synced) ' 'VALUES(?, ?, ?, date(), ?, NULL, ?, ?, FALSE)', [rfid, last_name, first_name, username, admin, str(token)] ) connection.commit() connection.close() # piloten mit terminal synchronisieren sync_pilots() return {}
def get(self): '''get Session info''' select_stmt = None connection = get_connection("database_server.db") cursor = connection.cursor() p_id = is_pilot(cursor) if p_id == -1: return {}, 404 args = get_parser.parse_args() from_ = args['from'] to = args['to'] # entferne alle argumente mit value 'None' (NULL) for k, v in list(args.items()): if v is None: args.pop(k) # GET sessions?name&start_date&end_date if 'name' in args.keys() and 'start_date' in args.keys( ) and 'end_date' in args.keys(): name_list = args['name'].split() # z.B. 'Mustermann' -> in Vor- und Nachname suchen if len(name_list) == 1: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)' 'AND date(F.Startzeit) BETWEEN ? AND ?', [ name_list[0], name_list[0], args['start_date'], args['end_date'] ]) # z.B. 'Max Mustermann' -> vor(Max), nach(Mustermann) oder vor(Mustermann) nach(Max) if len(name_list) == 2: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) ' 'OR ' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)' ')' 'AND date(F.Startzeit) BETWEEN ? AND ?', [ name_list[0], name_list[1], name_list[1], name_list[0], args['start_date'], args['end_date'] ]) # GET sessions?name&start_date elif 'name' in args.keys() and 'start_date' in args.keys(): name_list = args['name'].split() if len(name_list) == 1: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)' 'AND date(F.Startzeit) > ?', [name_list[0], name_list[0], args['start_date']]) if len(name_list) == 2: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) ' 'OR ' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)' ')' 'AND date(F.Startzeit) BETWEEN ? AND ?' 'AND date(F.Startzeit) > ?', [ name_list[0], name_list[1], name_list[1], name_list[0], args['start_date'] ]) elif 'name' in args.keys() and 'end_date' in args.keys(): name_list = args['name'].split() if len(name_list) == 1: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)' 'AND date(F.Startzeit) < ?', [name_list[0], name_list[0], args['end_date']]) if len(name_list) == 2: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) ' 'OR ' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)' ')' 'AND date(F.Startzeit) BETWEEN ? AND ?' 'AND date(F.Startzeit) < ?', [ name_list[0], name_list[1], name_list[1], name_list[0], args['end_date'] ]) # GET sessions?start_date&end_date elif 'start_date' in args.keys() and 'end_date' in args.keys(): select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE date(F.Startzeit) BETWEEN ? AND ?', [args['start_date'], args['end_date']]) # /sessions?name elif 'name' in args.keys(): name_list = args['name'].split() if len(name_list) == 1: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (instr(lower(P.Vorname), lower(?)) > 0 OR instr(lower(P.Nachname), lower(?)) > 0)', [name_list[0], name_list[0]]) if len(name_list) == 2: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE (' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0) ' 'OR ' '(instr(lower(P.Vorname), lower(?)) > 0 AND instr(lower(P.Nachname), lower(?)) > 0)' ')' 'AND date(F.Startzeit) BETWEEN ? AND ?', [name_list[0], name_list[1], name_list[1], name_list[0]]) elif 'start_date' in args.keys(): select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE date(F.Startzeit) > ?', [args['start_date']]) elif 'end_date' in args.keys(): select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ' 'WHERE date(F.Startzeit) < ?', [args['end_date']]) else: select_stmt = cursor.execute( 'SELECT F.SessionID, P.PilotID, P.Vorname, P.Nachname, date(F.Startzeit), time(F.Startzeit), ' 'time(F.Endzeit), F.Ist_Flugleiter, G.Gastname, G.Freitext ' 'FROM Flugsession F ' 'JOIN Pilot P on P.PilotID = F.PilotID ' 'LEFT JOIN Gast G on G.GastID = F.GastID ') return_dict = {'sessions': [], 'session_count': 0} sessions_count = 0 # alle ergebnisse von 'from' bis 'to' for row in select_stmt.fetchall(): sessions_count += 1 if from_ <= sessions_count <= to: session = { 'session_id': row[0], 'pilot_id': row[1], 'pilot_name': row[2] + " " + row[3], 'date': row[4], 'start_time': row[5], 'end_time': row[6], 'session_leader': row[7], 'guest': { 'name': row[8], 'text': row[9] } } return_dict['sessions'].append(session) return_dict['session_count'] = sessions_count connection.close() return return_dict