Python/MySQL: write to database successful ONLY one time

Question

Slowly building out my app (for CS50) and it appears to work, but only once will it write to the database. I can go to the site, make an order, and it will successfully display my order on the HTML page every time. But only one time will it write the order to the database AND display it. I can log out, but still only one order will ever be written to the database.

@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    if request.method == "POST":
        #http://stackoverflow.com/questions/32640090/python-flask-keeping-track-of-user-sessions-how-to-get-session-cookie-id
        id = session.get('user_id')

        url_start = 'http://download.finance.yahoo.com/d/quotes.csv?s='
        url_middle = request.form["symbol"]
        url_end = '&f=nsl1d1t1c1ohgv&e=.csv'
        full_url = url_start + url_middle + url_end

        # http://stackoverflow.com/questions/21351882/reading-data-from-a-csv-file-online-in-python-3
        response = urllib.request.urlopen(full_url)

        datareader = csv.reader(io.TextIOWrapper(response))
        quote_list = list(datareader)

        num_shares = request.form["num_shares"]

        name = quote_list[0][0]
        symbol = quote_list[0][1]
        price = float(quote_list[0][2])

        #http://stackoverflow.com/questions/12078571/jinja-templates-format-a-float-as-comma-separated-currency
        total_cost = round((float(price) * 100.0) * float(num_shares) / 100.0,2)

        username = db.execute("SELECT username FROM users WHERE id = :id", id=id)
        username = username[0]
        username = username.get('username')

        db.execute("INSERT INTO purchases (id, symbol, name, shares, price, total) VALUES(:id, :symbol, :name, :shares, :price, :total)",
            id=id, symbol=symbol, name=name, price=price, shares=num_shares, total=total_cost)

        return render_template("bought.html", username=username, id=id, name=name, symbol=symbol, price=price, num_shares=num_shares, total_cost=total_cost)
    else:
        return render_template("buy.html")

Answer 1

Simple answer: I had the purchases table set up in the same was as the user table, with id_num being unique in both tables. It should have been unique in the user table because users are unique. But it should not have been unique in the purchases table because the same user can make many purchases.

Answer 2

First of all, most of all problems can be solved by separating functions into parts and checking if each part works as expected, so let's refactor your code a bit.

# additional import for generating URL from parts,
# not by concatenating strings
import urllib.parse


@app.route("/buy", methods=["GET", "POST"])
@login_required
def buy():
    if request.method != "POST":
        return render_template("buy.html")

    # http://stackoverflow.com/questions/32640090/python-flask-keeping-track-of-user-sessions-how-to-get-session-cookie-id
    user_id = session.get('user_id')

    # what is "full_url"?
    # to which resource and what we get with it?
    full_url = generate_url(symbol=request.form["symbol"])

    # http://stackoverflow.com/questions/21351882/reading-data-from-a-csv-file-online-in-python-3
    response = urllib.request.urlopen(full_url)

    datareader = csv.reader(io.TextIOWrapper(response))
    quote_list = list(datareader)

    num_shares = request.form["num_shares"]

    # what first element means,
    # if it contains info about specific data
    # there should be name for it
    quote_list_first_element = quote_list[0]
    name = quote_list_first_element[0]
    # is this symbol different from one in "request.form"?
    symbol = quote_list_first_element[1]
    price = float(quote_list_first_element[2])

    # http://stackoverflow.com/questions/12078571/jinja-templates-format-a-float-as-comma-separated-currency
    total_cost = get_total_cost(price, num_shares)

    username = fetch_user_name(user_id)

    save_purchase(user_id=user_id,
                  symbol=symbol,
                  name=name,
                  price=price,
                  num_shares=num_shares,
                  total_cost=total_cost)

    return render_template("bought.html",
                           username=username,
                           id=user_id,
                           name=name,
                           symbol=symbol,
                           price=price,
                           num_shares=num_shares,
                           total_cost=total_cost)


def fetch_user_name(user_id):
    username = db.execute("SELECT username FROM users WHERE id = :user_id",
                          user_id=user_id)
    username = username[0]
    username = username.get('username')
    return username


def save_purchase(user_id, name, num_shares,
                  price, symbol, total_cost):
    db.execute(
        "INSERT INTO purchases (id, symbol, name, num_shares, price, total) "
        "VALUES (:user_id, :symbol, :name, :num_shares, :price, :total)",
        # FIXME: if "purchases" table's "id" column is a primary key 
        # here we are saving purchase by user id 
        # not by purchase id (which probably is auto-incremented 
        # and should not be specified in insert query at all), 
        # so for each user we will have only one purchase since primary key is unique
        user_id=user_id,
        symbol=symbol,
        name=name,
        price=price,
        # maybe it will be better to rename column into "num_shares"?
        shares=num_shares,
        # maybe it will be better to rename column into "total_cost"?
        total=total_cost)


def get_total_cost(price, num_shares):
    return round((float(price) * 100.0) * float(num_shares) / 100.0, 2)


def generate_url(symbol):
    scheme = 'http'
    netloc = 'download.finance.yahoo.com'
    path = '/d/quotes.csv'
    params = ''
    # what 's' query parameter means?
    # looks like it stands for "symbol", but which one?
    # is it product label or something else?
    query_dict = dict(s=symbol,
                      f='nsl1d1t1c1ohgv',
                      e='.csv')
    query_str = urllib.parse.urlencode(query_dict)
    fragment = ''
    components = [scheme, netloc, path, params, query_str, fragment]
    return urllib.parse.urlunparse(components)

now we can see that we save each purchase by user id, but it probably auto-incremented column or there are column for user id, not for purchase id, i don't know what your database schema is

If purchases table's id column is auto-incremented, we can remove user_id parameter

def save_purchase(name, num_shares,
                  price, symbol, total_cost):
    db.execute(
        "INSERT INTO purchases (symbol, name, num_shares, price, total) "
        "VALUES (:symbol, :name, :num_shares, :price, :total)",
        symbol=symbol,
        name=name,
        price=price,
        shares=num_shares,
        total=total_cost)

If purchases table has both id and user_id columns and we want to insert record with user_id specified it should be like

def save_purchase(user_id, name, num_shares,
                  price, symbol, total_cost):
    db.execute(
        "INSERT INTO purchases (user_id, symbol, name, num_shares, price, total) "
        "VALUES (:user_id, :symbol, :name, :num_shares, :price, :total)",
        user_id=user_id,
        symbol=symbol,
        name=name,
        price=price,
        shares=num_shares,
        total=total_cost)

Hope it helps.