def read_all_records():
connection = mysql.connector.connect(**connection_config)
print("[INFO] Connected to MySQL Server")
cursor = connection.cursor(dictionary=True)
query = "SELECT * FROM " + main_table_name
cursor.execute(query)
rows = cursor.fetchall()
cursor.close()
connection.close()
print("[INFO] MySQL connection closed")
return create_response(rows)
def read_record(person_id):
connection = mysql.connector.connect(**connection_config)
print("[INFO] Connected to MySQL Server")
cursor = connection.cursor(dictionary=True)
query = "SELECT * FROM " + main_table_name + " WHERE id = %s"
cursor.execute(query, (person_id, ))
row = cursor.fetchone()
cursor.close()
connection.close()
print("[INFO] MySQL connection closed")
if row is None:
return create_error("person_not_found")
return create_response(row)
def create_record(request):
if "first_name" not in request:
return create_error("first_name_required")
if "last_name" not in request:
return create_error("last_name_required")
first_name = request["first_name"]
last_name = request["last_name"]
connection = mysql.connector.connect(**connection_config)
print("[INFO] Connected to MySQL Server")
cursor = connection.cursor()
query = "INSERT INTO " + main_table_name + \
" (first_name, last_name) VALUES (%s, %s)"
values = (first_name, last_name)
cursor.execute(query, values)
connection.commit()
cursor.close()
connection.close()
print(first_name + " " + last_name + " added to database!")
print("[INFO] MySQL connection closed")
return create_response(None)
def update_record(request):
person_id = None
first_name = None
last_name = None
if "id" in request:
person_id = request["id"]
else:
return create_error("person_id_required")
if "first_name" in request:
first_name = request["first_name"]
else:
return create_error("first_name_required")
if "last_name" in request:
last_name = request["last_name"]
else:
return create_error("last_name_required")
connection = mysql.connector.connect(**connection_config)
print("[INFO] Connected to MySQL Server")
cursor = connection.cursor()
query = "SELECT EXISTS(SELECT * FROM " + \
main_table_name + " WHERE id = %s)"
cursor.execute(query, (person_id, ))
(is_exist, ) = cursor.fetchone()
if is_exist == 0:
cursor.close()
connection.close()
return create_error("person_id_not_found")
query = "UPDATE " + main_table_name + \
" SET first_name = %s, last_name = %s WHERE id = %s"
values = (first_name, last_name, person_id)
cursor.execute(query, values)
connection.commit()
print("Record updated!")
cursor.close()
connection.close()
print("[INFO] MySQL connection closed")
return create_response(None)
def delete_record(person_id):
connection = mysql.connector.connect(**connection_config)
print("[INFO] Connected to MySQL Server")
cursor = connection.cursor()
select_query = "SELECT EXISTS(SELECT * FROM " + \
main_table_name + " WHERE id = %s)"
cursor.execute(select_query, (person_id, ))
(is_exist, ) = cursor.fetchone()
if is_exist == 0:
cursor.close()
connection.close()
return create_error("person_id_not_found")
delete_query = "DELETE FROM " + main_table_name + " WHERE id = %s"
cursor.execute(delete_query, (person_id, ))
connection.commit()
print("Record removed!")
cursor.close()
connection.close()
print("[INFO] MySQL connection closed")
return create_response(None)