def get_locations_query(id=None, offset=0): ''' Takes in record id, offset and returns an Array. Parameters: id (int, Optional): id of specific record offset (int, Optional): offset for pagination Returns: Array: returns array of database result, count of records ''' connection = connect_to_db() if connection != "no connection": result = [] try: with connection.cursor() as cursor: if id is None: """ Get Count of Rows """ sql = "SELECT COUNT(*) \ FROM `locations`;" cursor.execute(sql) count = cursor.fetchone() count = count['COUNT(*)'] """ https://stackoverflow.com/questions/42050765/ sql-how-to-replace-foreign-key-column-with-data\ -from-referenced-table """ sql = "SELECT l.LocationID as id, l.name, l.population, s.name as system, l.desc \ FROM locations l \ JOIN systems s \ ON l.SystemID = s.SystemID \ LIMIT 25 \ OFFSET %s;" cursor.execute(sql, (int(offset))) result = cursor.fetchall() else: sql = "SELECT l.LocationID as id, l.name, l.population, s.name as system, l.desc \ FROM locations l \ JOIN systems s \ ON l.SystemID = s.SystemID \ WHERE l.LocationID = %s" cursor.execute(sql, (id)) result = cursor.fetchone() count = 1 if result is None: return "failed" except pymysql.Error: return "failed" finally: connection.close() else: return connection return [result, count]
def add_location_query(name, population, system, desc): ''' Takes in name, status, gender, desc and returns string. Parameters: name (str): name value population (str): population value system (str): system value desc (str): desc value Returns: str: returns string on success or failure ''' connection = connect_to_db() if connection != "no connection": try: # check if record exists first with connection.cursor() as cursor: sql = "SELECT * FROM `locations` WHERE `name` = %s;" cursor.execute(sql, (name)) result = cursor.fetchone() if result is None: # Get system id with connection.cursor() as cursor: sql = "SELECT systemid \ FROM `systems` \ WHERE `name` = %s;" cursor.execute(sql, (system)) result = cursor.fetchone() if result is not None: system_id = result['systemid'] else: return "system incorrect" with connection.cursor() as cursor: sql = "\ INSERT INTO `locations` \ (`name`, `population`, `systemid`, `desc`) \ VALUES(%s, %s, %s, %s);" cursor.execute(sql, (name, population, system_id, desc)) connection.commit() else: return "duplicate" except pymysql.Error: return "failed" finally: connection.close() else: return connection
def get_systems_query(id=None, offset=0): ''' Takes in record id, offset and returns an Array. Parameters: id (int, Optional): id of specific record offset (int, Optional): offset for pagination Returns: Array: returns array of database result, count of records ''' connection = connect_to_db() if connection != "no connection": result = [] try: with connection.cursor() as cursor: if id is None: """ Get Count of Rows """ sql = "SELECT COUNT(*) \ FROM `systems`;" cursor.execute(sql) count = cursor.fetchone() count = count['COUNT(*)'] sql = "SELECT systemid as id, `name`, `planets`, `desc` \ FROM `systems` \ LIMIT 25 \ OFFSET %s;" cursor.execute(sql, (int(offset))) result = cursor.fetchall() else: sql = "SELECT systemid as id, `name`, `planets`, `desc` \ FROM `systems` \ WHERE `systemid` = %s;" cursor.execute(sql, (id)) result = cursor.fetchone() count = 1 if result is None: return "failed" except pymysql.Error: return "failed" finally: connection.close() else: return connection return [result, count]
def edit_people_query(id, name="", status="", gender="", desc=""): ''' Takes in id, name, status, gender, desc and returns string. Parameters: id (int): id of record name (str, Optional): name value status (str, Optional): status value gender (str, Optional): gender value desc (str, Optional): desc value Returns: str: returns string on success or failure ''' connection = connect_to_db() if connection != "no connection": try: # check if record exists first with connection.cursor() as cursor: sql = "SELECT * \ FROM `people` \ WHERE `id` = %s;" cursor.execute(sql, (id)) result = cursor.fetchone() if result is not None: # set values if inputed value is empty if name == "" or name is None: name = result['name'] if status == "" or status is None: status = result['status'] if gender == "" or gender is None: gender = result['gender'] if desc == "" or desc is None: desc = result['desc'] with connection.cursor() as cursor: sql = "\ \ UPDATE `people` \ SET `name` = %s, `status` = %s, \ `gender` = %s, `desc` = %s \ WHERE `id` = %s;" cursor.execute(sql, (name, status, gender, desc, id)) connection.commit() else: return "no record" except pymysql.Error: return "failed" finally: connection.close() else: return connection
def get_location_query_filtered(filter, param): ''' Takes in filter, param and returns list of database result. Parameters: filter (str): filter name param (str): paramater to search for Returns: list: returns list of database results ''' connection = connect_to_db() if connection != "no connection": result = [] try: with connection.cursor() as cursor: if filter == "name": sql = "SELECT LocationID as id, name\ FROM `locations`\ WHERE `name` \ LIKE %s;" query = (param + '%') elif filter == "system": sql = "SELECT l.LocationID as id, l.name, s.name as system\ FROM `locations` l\ JOIN systems s \ ON l.SystemID = s.SystemID \ WHERE s.name \ LIKE %s;" query = param cursor.execute(sql, (query)) result = cursor.fetchall() if result is None: return "failed" except pymysql.Error: return "failed" finally: connection.close() else: return connection return result
def edit_system_query(id, name="", planets="", desc=""): ''' Takes in id, name, planets, desc and returns string. Parameters: id (int): id of record name (str, Optional): name value planets (str, Optional): planets value desc (str, Optional): desc value Returns: str: returns string on success or failure ''' connection = connect_to_db() if connection != "no connection": try: # check if record exists first with connection.cursor() as cursor: sql = "SELECT * \ FROM `systems` \ WHERE `systemid` = %s;" cursor.execute(sql, (id)) result = cursor.fetchone() if result is not None: # set values if inputed value is empty if name == "" or name is None: name = result['name'] if planets == "" or planets is None: planets = result['planets'] if desc == "" or desc is None: desc = result['desc'] with connection.cursor() as cursor: sql = "UPDATE `systems` \ SET `name` = %s, `planets` = %s, `desc` = %s \ WHERE `systemid` = %s;" cursor.execute(sql, (name, planets, desc, id)) connection.commit() else: return "no record" except pymysql.Error: return "failed" finally: connection.close() else: return connection
def get_people_query_filtered(filter, param): ''' Takes in filter, param and returns list of database result. Parameters: filter (str): filter name param (str): paramater to search for Returns: list: returns list of database results ''' connection = connect_to_db() if connection != "no connection": result = [] try: with connection.cursor() as cursor: if filter == "name": sql = "SELECT id, name \ FROM `people` \ WHERE `name` \ LIKE %s;" query = (param + '%') elif filter == "status": sql = "SELECT id, name, status \ FROM `people` \ WHERE `status` = %s;" query = param else: sql = "SELECT id, name, gender \ FROM `people` \ WHERE `gender` = %s;" query = param cursor.execute(sql, (query)) result = cursor.fetchall() if result is None: return "failed" except pymysql.Error: return "failed" finally: connection.close() else: return connection return result
def add_people_query(name, status, gender, desc): ''' Takes in name, status, gender, desc and returns string. Parameters: name (str): name value status (str): status value gender (str): gender value desc (str): desc value Returns: str: returns string on success or failure ''' connection = connect_to_db() if connection != "no connection": try: # check if record exists first with connection.cursor() as cursor: sql = "SELECT * \ FROM `people` \ WHERE `name` = %s;" cursor.execute(sql, (name)) result = cursor.fetchone() if result is None: with connection.cursor() as cursor: sql = "\ \ INSERT INTO `people` \ (`name`, `status`, `gender`, `desc`) \ VALUES(%s, %s, %s, %s);" cursor.execute(sql, (name, status, gender, desc)) connection.commit() else: return "duplicate" except pymysql.Error: return "failed" finally: connection.close() else: return connection
def delete_system_query(id): ''' Takes in id of a record and returns string. Parameters: id (int): id of record Returns: str: returns string on success or failure ''' connection = connect_to_db() if connection != "no connection": try: # check if record exists first with connection.cursor() as cursor: sql = "SELECT * \ FROM `systems` \ WHERE `systemid` = %s;" cursor.execute(sql, (id)) result = cursor.fetchone() if result is not None: with connection.cursor() as cursor: sql = "DELETE FROM `systems` \ WHERE `systemid` = %s;" cursor.execute(sql, (id)) connection.commit() else: return "no record" except pymysql.Error: return "failed" finally: connection.close() else: return connection
def edit_location_query(id, name="", population="", system="", desc=""): ''' Takes in id, name, status, gender, desc and returns string. Parameters: id (int): id of record name (str, Optional): name value population (str, Optional): population value system (str, Optional): system value desc (str, Optional): desc value Returns: str: returns string on success or failure ''' connection = connect_to_db() if connection != "no connection": try: # check if record exists first with connection.cursor() as cursor: sql = "SELECT * FROM `locations` WHERE `locationid` = %s;" cursor.execute(sql, (id)) result = cursor.fetchone() if result is not None: # set values if inputed value is empty if name == "" or name is None: name = result['name'] if population == "" or population is None: population = result['population'] if desc == "" or desc is None: desc = result['desc'] # Get system id if changed if system == "" or system is None: system_id = result['SystemID'] else: with connection.cursor() as cursor: sql = "SELECT systemid \ FROM `systems` \ WHERE `name` = %s;" cursor.execute(sql, (system)) result = cursor.fetchone() system_id = result['systemid'] # Added to DB with connection.cursor() as cursor: sql = "\ UPDATE `locations` \ SET `name` = %s, `population` = %s, \ `systemid` = %s, `desc` = %s \ WHERE `locationid` = %s;" cursor.execute(sql, (name, population, system_id, desc, id)) connection.commit() else: return "no record" except pymysql.Error: return "failed" finally: connection.close() else: return connection