Flask application for buying stocks

Question

I wrote three Flask views below.

First view

1. input stock code

Second view

2. get the stock details (name, price) of the stock by using 3rd party api
3. get the balance cash of DB and show to the view after calculating available number of stock by computing stock price and cash balance
4. and user input positive number of stock that would buy for this price

Third view

5. after receive the number of stock send the query to reduce the cash balance from first table called users, and then sent another query to update purchasing record to second table called history.

I believe there would certainly be better way than this one. As a newbie in programming and python, it was for me very difficult to send and receive information from html form and python by flask. especially this is three step showing but not simple one step so i use flag. But not sure this is a good way.

Another point is using global variable. since I seperate case by using if statement and flag, the variable used in other if statement could only be accessed by using global variable. However, I would do another way since i worry it would affect for the further fuction as sell later.

def dbexe(sqlquery, str):
    db = sqlite3.connect("finance.db")
    cur = db.cursor()
    if str == "regi" or str == "updat":
        cur.execute(sqlquery)
        db.commit()
    elif str == "selec":
        rows = cur.execute(sqlquery)
        tupdata = rows.fetchone()
        return tupdata
    db.close()



@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    global flag, balance, sharePrice, username, symbol, resultcom, comname
    username=session["user_id"]

    if request.method == "GET":
        flag=1
        return render_template("buy.html")
    if request.method == "POST" and flag==1:
        symbol = request.form.get("symbol")
        resultcom = lookup(symbol)
        if not resultcom:
            return apology("No such Stock exist!")
        sharePrice = resultcom["price"]
        balance = int(dbexe(f"select cash from users where username='{username}'", "selec")[0])
        maxshareNum = balance//sharePrice
        flag=2
        return render_template("buy.html", htmlflag=1, result=resultcom, maxNum=maxshareNum)

    if request.method == "POST" and flag==2:
        sharenum = int(request.form.get("shares"))
        newbal = balance-(sharePrice*sharenum)
        dbexe(f"update users SET cash={newbal} where username='{username}'", "updat")
        comname=resultcom["name"]
        dbexe(f"INSERT INTO history('usernameid','date','symbol','comname','sharenum','balatthetime') VALUES('{username}',datetime('now'),'{symbol}','{comname}',{sharenum},{newbal})","regi")
        return render_template("buy.html", htmlflag=2)

HTML

{% extends "layout.html" %}

{% block title %}
Buy stock
{% endblock %}

{% block main %}



{% if htmlflag==1 %}
<form action="/buy" method="POST" class="needs-validation" novalidate>
    <div class="form-group">
        <input type="text" autocomplete="off" autofocus class="form-control" name="symbol"
            placeholder="{{result["name"]}}" value="{{result["symbol"]}}" disabled>
    </div>

    {{result["name"]}} 's current pirce is <B>{{result["price"]| usd}}</B><br>
    You can purchase {{result["name"]}} up to <b>{{maxNum}}</b> shares.<br>
    How many will you buy? <br>

    <div class="form-group">
        <input type="number" min="1" max="{{maxNum}}" autocomplete="off" autofocus class="form-control" name="shares"
            placeholder="# of Share" required>
    </div>
    <button class="btn btn-primary" type="submit">Buy</button>
</form>

{% elif htmlflag==2 %}
Successfully bought!

{% else%}
<form action="/buy" method="POST" class="needs-validation" novalidate>
    <div class="form-group">
        <input autocomplete="off" autofocus class="form-control" name="symbol" placeholder="Stock Symbol" type="text"
            required>
    </div>
    <button class="btn btn-primary" type="submit">Search</button>
</form>
{% endif %}


<script>
    (function () {
        'use strict';
        window.addEventListener('load', function () {
            // Fetch all the forms we want to apply custom Bootstrap validation styles to
            var forms = document.getElementsByClassName('needs-validation');
            // Loop over them and prevent submission
            var validation = Array.prototype.filter.call(forms, function (form) {
                form.addEventListener('submit', function (event) {
                    if (form.checkValidity() === false) {
                        event.preventDefault();
                        event.stopPropagation();
                    }
                    form.classList.add('was-validated');
                }, false);
            });
        }, false);
    })();
</script>
{% endblock %}

Answer 1

Unclosed Connection

Your dbexe method has a bug. If the elif statement is executed, tupdata is returned without closing the connection to the database. A simple fix is to close the connection the line before returning data.

Operator Spacing

There should be one space on either side of operators and assignments, like so

flag==1: -> flag == 1:
username=session["user_id"] -> username = session["user_id"]
flag=2 -> flag = 2

Variable Names

Variable and function names should be in snake_case.

Type Hints

Use type hints to display what types of parameters are accepted, and the type of data that is returned by the function

From this

def dbexe(sqlquery, str):

to this

def dbexe(sqlquery: str, string: str):

Reserved Names

It's not recommended to use str as a parameter/variable name, as it's a reserved keyword in python.