Auto Generating SQL Scripts based on User Input using Flask, python

Problem: There is an arduous update script we run for work. It takes a lot of time to update because of security issues. We want to make sure we have edited the update script correctly/want to avoid all permission issues.

Solution: Using Flask as a front end gui to eliminate the possibility for adding the wrong value. The Flask app will guarantee correct placement of inputs into the update script by auto generating the script, so we can just copy and paste into SSMS.

The current problem I am having is aesthetics. I was able to generate a flask app using user inputs and exports a SQL script. The problem is, the sql looks horrible, here is an example of the output:

Ideally the output would be multi-line, like all current SQL scripts:

UPDATE table_name
SET ordercomplete = 1
WHERE UserName = 'testuser' and OrderID = 12345

​x
 
UPDATE table_nameSET ordercomplete = 1WHERE UserName = 'testuser' and OrderID = 12345​​

Below are my files:

#templates  home.html
<!doctype html>

<head>

    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>SQL Auto Generate</title>


    <link rel="stylesheet" href="https://unpkg.com/purecss@0.6.2/build/pure-min.css" integrity="sha384-UQiGfs9ICog+LwheBSRCt1o5cbyKIHbwjWscjemyBMT9YCUMZffs6UqUTd0hObXD" crossorigin="anonymous">

    <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='style.css') }}">

</head>

<h1>SQL Auto Generate</h1>

<h2>Add the following inputs, select 'Submit' to generate a script for SQL</h2>
<body>

<div class="main">
    <form class="pure-form" method="POST" action="/">
    UserName:<br>
    <input type="text" name="UserName"><br>
    OrderID:<br>
    <input type="number" name="OrderID"><br>
    <button type="submit" class="pure-button pure-button-primary" value="Submit">Submit</button>
    </form>
</div>

<br>

<div class="main">
    {% if UserName %}
        <p>
        {% print('''
        UPDATE table_name
          SET ordercomplete = 1
        WHERE
          UserName = {} and OrderID = {}
      '''.format(UserName, OrderID)) %}
        </p>
    {% endif %}
</div>

</body>

 
#templates  home.html<!doctype html>​<head>​    <meta name="viewport" content="width=device-width, initial-scale=1">​    <title>SQL Auto Generate</title>​​    <link rel="stylesheet" href="https://unpkg.com/purecss@0.6.2/build/pure-min.css" integrity="sha384-UQiGfs9ICog+LwheBSRCt1o5cbyKIHbwjWscjemyBMT9YCUMZffs6UqUTd0hObXD" crossorigin="anonymous">​    <link rel="stylesheet" type="text/css" href="{{ url_for('static', filename='style.css') }}">​</head>​<h1>SQL Auto Generate</h1>​<h2>Add the following inputs, select 'Submit' to generate a script for SQL</h2><body>​<div class="main">    <form class="pure-form" method="POST" action="/">    UserName:<br>    <input type="text" name="UserName"><br>    OrderID:<br>    <input type="number" name="OrderID"><br>    <button type="submit" class="pure-button pure-button-primary" value="Submit">Submit</button>    </form></div>​<br>​<div class="main">    {% if UserName %}        <p>        {% print('''        UPDATE table_name          SET ordercomplete = 1        WHERE          UserName = {} and OrderID = {}      '''.format(UserName, OrderID)) %}        </p>    {% endif %}</div>​</body>​

# app.py

from flask import Flask, render_template, request

app = Flask(__name__)

@app.route('/', methods=['GET', 'POST'])
def index():
    UserName = ''
    OrderID = ''
    if request.method == 'POST' and 'UserName' in request.form:
        UserName = request.form.get('UserName')
        OrderID = int(request.form.get('OrderID'))

    return render_template("home.html", UserName=UserName, OrderID = OrderID)

if __name__ == '__main__':
    app.run(debug=True)

 
# app.py​from flask import Flask, render_template, request​app = Flask(__name__)​@app.route('/', methods=['GET', 'POST'])def index():    UserName = ''    OrderID = ''    if request.method == 'POST' and 'UserName' in request.form:        UserName = request.form.get('UserName')        OrderID = int(request.form.get('OrderID'))​    return render_template("home.html", UserName=UserName, OrderID = OrderID)​if __name__ == '__main__':    app.run(debug=True)​​

I also uploaded to github: https://github.com/jpf5046/sqlflask

Ask: Is there a better way to create a text output with multi-line via flask?

Answer

By default, HTML ignores extra space, such as line endings. If you want to display the query exactly like you generate it, use <pre>:

<pre>
    UPDATE table_name
      SET ordercomplete = 1
    WHERE
      UserName = {{UserName}} and OrderID = {{OrderId}}
</pre>

 
<pre>    UPDATE table_name      SET ordercomplete = 1    WHERE      UserName = {{UserName}} and OrderID = {{OrderId}}</pre>​

Advertisement

Answer