Get the data from UI and generate a Mysql query from the mysql using Flask

Question

I have 2 tables in the database.

Orders table:

OrderID     CustomerID  EmployeeID  OrderDate   
-----------------------------------------------
10308         2           1         1996-09-18  
10309        37           3         1996-09-19  
10310        77           6         1996-09-20  

Customers table:

CustomerID  CustomerName Country
--------------------------------- 
    1       Alfreds      Germany
    2       Ana          Mexico
    3       Antoni       Mexico

My UI :

• You can select first Table(Orders) From orders there is a drop-down to select columns
• You can select first Table(Customer) From Customers there is a drop-down to select columns
• Middle scroll down You can select type of join(inner,outer,left,right)

I am able to get the table names and column values from the UI, below is requirement I need to display (response) dynamically generated mysql query?

MySql query example:

SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; 

Answer 1

Here's the python code that you can use:

@app.route('/generating_customized_query/', methods=['POST'])
def get_generating_customized_query():

    details = {}
    details['orders_table_column'] = flask.request.json['orders_table_column']
    details['customers_table_column'] = flask.request.json['customers_table_column']
    details['join'] = flask.request.json['join']

    base_sql = """
       SELECT Orders.%(orders_table_column)s, Customers.%(customers_table_column)s 
       FROM Orders 
       %(join)s JOIN Customers 
       ON Orders.%(customers_table_column)s = Customers.%(customers_table_column)s 
    """

    data = [dict(k) for k in db.session.execute((base_sql) % details).fetchall()]
    return flask.jsonify(data=data)

The variables which we are using:

`orders_table_column` = Column which we are fetching from `Orders` Table
`customers_table_column` = Column which we are fetching from `Customers` Table
`join` = Join which we are using in the base query