Unable to update record with Flask, MySQL

I'm working on a music database app with Flask, and I have a page where I can insert a record into the database that works how it should. Yesterday, I built a page where you can edit the values of the record. For the route, I copied the code from another, more simple app I made and re-wrote it for this app. When I visit the edit page, it fills the text boxes with the current values for the record...but when I change any of the items and submit it, nothing happens. It renders the page that I specified in the route after submit, but when I query the table nothing has changed.

Here's the route:

@app.route('/edit_album/<string:catno>/', methods=['GET', 'POST'])
def edit_album(catno):
cur = mysql.connection.cursor()

# Get article by catno
result = cur.execute("SELECT * FROM albums WHERE catno = %s", [catno])

album = cur.fetchone()

form = AlbumForm()

form.artist.data = album['artist']
form.title.data = album['title']
form.year.data = album['year']
form.rlabel.data = album['label']
form.genre.data = album['genre']

if request.method == 'POST':
# album art
#cover =
catno = album['catno']
artist = form.artist.data
title = form.title.data
year = form.year.data
rlabel = form.rlabel.data
genre = form.genre.data
# format (lp or tape)

# Create Cursor
cur = mysql.connection.cursor()

# Execute cursor
cur.execute("UPDATE albums SET artist=%s, title=%s, year=%s, label=%s, genre=%s WHERE catno=%s", (artist, title, year, rlabel, genre, catno))

# Commit to DB
mysql.connection.commit()

# Close DB connection
cur.close()

return redirect(url_for('view_album', catno=catno))

return render_template('edit_album.html', album=album, form=form)


And here's the actual edit page:

{% extends 'layout.html' %}

{% block body %}


{{album.artist}} :: {{album.title}}

{% if album.albumArt == None %}

Upload Cover
{% endif %}

Artist: {{album.artist}}

Album: {{album.title}}

Catalog No: {{album.catno}}

Record Label: {{album.label}}

Year Released: {{album.year}}

Genre: {{album.genre}}

</div>

EDIT ALBUM

<form method="POST" action="{{ url_for('edit_album', catno=album.catno) }}" class="card-footer text-center">

{{ form.csrf_token}}

{{ form.artist.label }}
{{ form.artist }}

{{ form.title.label }}
{{ form.title }}

{{ form.year.label }}
{{ form.year }}

</div>

{{ form.rlabel.label }}
{{ form.rlabel }}

{{ form.genre.label }}
{{ form.genre }}

</div>
<p><input class="btn btn-primary mt-3" type="submit" value="Submit">
</form>
</div>

</div>
{% endblock %}

The only thing I really got from searching last night, is that I may have two connections to the DB open, but I don't since I just have the one connection at the beginning of the script. It wouldn't be an issue with too many cursors, would it?

Otherwise, this is the first app I've used the Flask-WTF module for the forms, so could it be something wrong I'm doing with that? Here's that class if there's any questions:

# Form for adding record to database
class AlbumForm(FlaskForm):
# Album Art - figure out image uploads
cover = FileField('Upload Cover Art')
catno = StringField('Catalog Number')
artist = StringField('Artist')
title = StringField("Album Title")
year = StringField('Year Released')
rlabel = StringField('Record Label')
genre = StringField('Genre')


The app doesn't throw any errors, so I'm not sure what's going on, or if I'm just overlooking something.

2 Answers
2

Seems like you are overwriting your form on post, because on both get and post you are fetching an album entry and filing a form with it's data. It should work if you structure it like this:

def edit_album(catno):
cur = mysql.connection.cursor()
if request.method == 'POST':
form = AlbumForm() # WTForms will extract data from the request's body by itself
# other code from a if request.method == 'POST' block
elif request.method == 'GET':
cur.execute("...")
album = cur.fetchone()

form = AlbumForm()

form.artist.data = album['artist']
form.title.data = album['title']
form.year.data = album['year']
form.rlabel.data = album['label']
form.genre.data = album['genre']

return render_template...


P.S. A good but too high-level and magic-y (and hard to understand as a result) example is given in the WTForms docs: wtform has formdata and obj arguments; each time wtform instance is created (form = AlbumForm) it tries to extract data from a request to populate it's fields. If it fails (and it would on a get-request because no form-data exist) it will get data from a second source -- the obj argument, which has your current db-entry value. But on post wtform successfully retrieves data from a post-request-formdata which then populates db-entry which is then saved.

formdata

obj

form = AlbumForm

obj

So basically, since the form's action is to use the function defined for the page, as soon as I submit it's basically repopulating the fields with existing row data and resubmitting that?
– Beaver Jones
yesterday

The solution was this:

The value of the variables that are used to update the database were supposed to be like this:

catno = album['catno']
artist = request.form['artist']
title = request.form['title']
year = request.form['year']
rlabel = request.form['rlabel']
genre = request.form['genre']


And not:

catno = album['catno']
artist = form.artist.data
title = form.title.data
year = form.year.data
rlabel = form.rlabel.data
genre = form.genre.data


Because in the latter method, I was just passing the same data into the variables that was loaded into the form when the page was opened, instead of the updated values in the text boxes.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.