Flask-SQLAlchemy: How to conditionally insert or update a row

happygoat picture happygoat · Jul 2, 2012 · Viewed 30.8k times · Source

My application uses a combination of Flask, Flask-SQLAlchemy, Flask-WTF and Jinja2.

In its current incarnation, I have a settings table. The table will only have one record with one field. Initially the table contains zero records.

What I want to achieve is:

  • Given that no entries exist in db, then show empty form ready for user input
  • Given that an entry exist, show the entry, and
  • if the user changes the value, then update the rec in db.

Here is my code:

models.py

class Provider(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    rssfeed = db.Column(db.String(120), unique = True)

    def __init__(self, rssfeed):
        self.rssfeed = rssfeed

    def __repr__(self):
        return '<NZBMatrix feed url %r>' % self.rssfeed

forms.py

class SettingsForm(Form):
    rssfeed = TextField('rssfed', validators= [Required()])

views.py

    @app.route('/settings', methods=["GET","POST"])
    def settings():
    """ show settings """
        provider = Provider.query.get(1)
        form = SettingsForm(obj=provider)
        print provider

        if request.method == "POST" and form.validate():
            if Provider.query.get(1) is None:
                provider = Provider(rssfeed=form.rssfeed.data)
                form.populate_obj(provider)
                db.session.add(provider)
                db.session.commit()
                flash("Settings added")

        return render_template("settings.html", form=form)

As it stands this code creates a record if it doesn't exists but the rssfeed column is empty.

How can I change this code so that it will INSERT if the record doesn't exist and UPDATE if it does?

Answer

codegeek picture codegeek · Aug 2, 2012

Once your form is validated etc,

To add a new record:

new_provider = Provider(form.rssfeed.data)
db.session.add(new_provider)
db.session.commit()

To update an existing record:

existing_provider = Provider.query.get(1) # or whatever
# update the rssfeed column
existing_provider.rssfeed = form.rssfeed.data
db.session.commit()

The trick in updating is that you just have to change the particular field and do a commit. rest is taken care by the db session. I think you are using the merge function which is now deprecated in SQLAlchemy.