current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 1 down vote favorite

I've been trying to update a randomly selected row in my Sqlite database using Flask and the Flask-Sqlalchemy. I have just a few rows in the database with columns called "word", "yes", and "no", where word is a string and yes and no are integers. There are two buttons on the "vote" view, yes and no. When a button is pressed, the appropriate code executes, should increment the yes or no column, and the view is updated with a new random word from the Word table. 

@app.route("/vote", methods=["GET", "POST"])
def vote():
    #Get random row from database
    query = db.session.query(Word)
    rowCount = int(query.count())
    row = query.offset(int(rowCount*random.random())).first()

    #POST

    # If "yes" button is pressed, increment yes column in database
    if request.method == "POST":
        if request.form.get("yes"):
            row.yes += 1
            db.session.commit()
            return render_template("vote.html", row=row)
    # otherwise increment no column
        elif request.form.get("no"):
            row.no += 1
            db.session.commit()
            return redirect(url_for("vote"))

    #GET

    # on get request, render vote.html
    return render_template("vote.html", row=row)

This code is working, but the yes and no columns are only updated when the view comes back around to the random word the next time. If I close the browser right after clicking a button, the database is not incremented. I think this has something to do with db.session.commit(), or something about the session. It seems like: 

row.yes += 1

is saved in the session object, but only committed when that database row is queried the next time. This code DID work when I replaced the query at the top of the method with:

row = Word.query.get(4)

which returns the row with id of 4. With this query, the yes or no column are updated immediately.

Any thoughts?

Thanks

share|improve this question
    
Your increments are vulnerable to race conditions. You should let the database handle the increment for you. row.yes = Word.yes + 1 – dirn Nov 24 at 4:34
up vote 1 down vote accepted

Thanks all. I figured out the problem. The database incrementing was actually working fine, but I wasn't incrementing the correct rows. The problem was that I generated a random row from the database on each call of the vote() method, which meant that I got a random value for the GET request, and a different random value for the POST request, and ended up incrementing that different random value in the POST request.

I separated the logic out into two methods for the "/vote" route, getWord() and vote(), and created a randRow() method for the row generation. I needed to store the random row that gets generated when getWord() is called, so I used session variables so I could access the random row from the vote() method. It's a bit verbose, but seems to work.

Anyone have a better idea about how to achieve this?

@app.route('/vote', methods=["GET"])
def getWord():
    wordObj = randRow()
    session['word'] = wordObj.word
    session['yesVotes'] = wordObj.yes
    session['noVotes'] = wordObj.no
    return render_template("vote.html", word=session['word'], yesVotes=session['yesVotes'], noVotes=session['noVotes'])

@app.route('/vote', methods=["POST"])
def vote():
    # store session 'word' in word variable
    # look up word in database and store object in wordObj
    word = session['word']
    wordObj = Word.query.filter_by(word=word).first()
    # check button press on vote view, increment yes or no column
    # depending on which button was pressed
    if request.form.get("yes"):
        wordObj.yes = wordObj.yes + 1
    elif request.form.get("no"):
        wordObj.no = wordObj.no + 1
    db.session.commit()
    return redirect(url_for("getWord"))

###### HELPERS ######

# returns a random row from the database
def randRow():
    rowId = Word.query.order_by(func.random()).first().id
    row = Word.query.get(rowId)
    return row
share|improve this answer
up vote 0 down vote

I think you need to add the update into the session before the commit, using code like this:

[...]
    row.yes += 1
    db.session.add(row)
    db.session.commit()
[...]

That's the pattern that I use for a basic update in Flask-SQLAlchemy.

share|improve this answer
    
You don't need to add things to the session if they were loaded through it. Model.query does that. – dirn Nov 24 at 4:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.