def db_config():
data = request.get_json()
username = data.get("username")
password = data.get("password")
url = data.get("url")
port = data.get("port")
db_name = data.get("db_name")
db_type = data.get("db_type")
# Bad request
if None in [username, password, url, port, db_name, db_type]:
return {"error": "All required values not specified"}, 400
# Possible errors
try:
db.establish_connection(username, password, url, port, db_name,
db_type)
except OperationalError as e:
return {
"error":
"Failed to establish connection with the DB, {0}".format(e.orig)
}, 500
# On successful connection, return list of tables in the DB
tables = db.get_tables_in_db()
return {"tables": tables}, 200
def delete_all_table_data():
data = request.get_json()
table = data.get("table")
if table not in db.get_tables_in_db():
return {"error": "Table does not exist"}, 400
current_table = Table(table, MetaData(), autoload_with=db.engine)
try:
db.session.execute(current_table.delete())
db.session.commit()
except (OperationalError, IntegrityError) as e:
return {"error": "Failed to delete table, {0}".format(e.orig)}, 400
return {"message": "Successfully Deleted All Rows"}, 200
def create_table_data():
data = request.get_json()
table = data.get("table")
if table not in db.get_tables_in_db():
return {"error": "Table does not exist"}, 400
row = data.get("row")
db.clean_data(row, table)
current_table = Table(table, MetaData(), autoload_with=db.engine)
try:
db.engine.execute(current_table.insert(), row)
db.session.commit()
return {"message": "Successfully Created"}, 200
except (OperationalError, DataError) as e:
return {"error": "Failed to create row, {0}".format(e.orig)}, 400
def update_table_data():
data = request.get_json()
table = data.get("table")
if table not in db.get_tables_in_db():
return {"error": "Table does not exist"}, 400
row = data.get("row")
old_row = data.get("old_row")
db.clean_data(row, table)
db.clean_data(old_row, table)
current_table = Table(table, MetaData(), autoload_with=db.engine)
try:
db.session.query(current_table).filter_by(**old_row).update(row)
db.session.commit()
return {"message": "Successfully Updated"}, 200
except (DataError, OperationalError, IntegrityError) as e:
return {"error": "Failed to update table, {0}".format(e.orig)}, 400
def delete_table_data():
data = request.get_json()
table = data.get("table")
rows = data.get("rows")
if table not in db.get_tables_in_db():
return {"error": "Table does not exist"}, 400
current_table = Table(table, MetaData(), autoload_with=db.engine)
column_names = current_table.c.keys()
for row in rows:
row_to_be_deleted = {}
for idx in range(len(column_names)):
row_to_be_deleted[column_names[idx]] = row[idx]
try:
db.session.query(current_table).filter_by(**row_to_be_deleted).delete()
db.session.commit()
except (OperationalError, IntegrityError) as e:
return {"error": "Failed to delete rows, {0}".format(e.orig)}, 400
return {"message": "Successfully Deleted"}, 200
def read_table_data():
data = request.get_json()
table = data.get("table")
per_page = 100
page_num = int(data.get("pageNum"))
page_num = (page_num - 1) * per_page
if table not in db.get_tables_in_db():
return {"error": "Table does not exist"}, 400
# Get all rows from table
current_table = Table(table, MetaData(), autoload_with=db.engine)
size = db.session.query(current_table).count()
if size % per_page == 0:
pages = size // per_page
else:
pages = size // per_page + 1
data = db.session.query(current_table).all()[page_num:page_num + per_page]
metadata = db.get_metadata(table)
result = []
for row in data:
# Convert everything to string to avoid JSON serialization issues
result.append([str(x) for x in list(row)])
return {"metadata": metadata, "rows": result, "pages": pages}, 200
def get_tables():
return {"tables": db.get_tables_in_db()}, 200
def get_table_metadata():
data = request.get_json()
table = data.get("table")
if table not in db.get_tables_in_db():
return {"error": "Table does not exist"}, 400
return {"metadata": db.get_metadata(table)}, 200