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

Question

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?

Just FYI - form.validate should be form.validate() - a function is always truthy, but your form might not pass validation. — Sean Vieira
– Sean Vieira, Commented Jul 4, 2012 at 14:32

codegeek · Accepted Answer · 2013-11-01 18:52:14Z

32

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.

edited Nov 1, 2013 at 18:52

answered Aug 2, 2012 at 21:50

codegeek

33.5k12 gold badges65 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adi Elisha Over a year ago

what about race condition in the adding of a new record?

Burhan Khalid · Accepted Answer · 2012-08-02 22:17:56Z

5

I have managed to solve the problem doing these changes to the view.py file:

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

    if request.method == "POST" and form.validate():
        if provider:
            provider.rssfeed = form.rssfeed.data
            db.session.merge(provider)
            db.session.commit()
            flash("Settings changed")
            return redirect(url_for("index"))
        else:
            provider = Provider(form.rssfeed.data)
            db.session.add(provider)
            db.session.commit()
            flash("Settings added")
            return redirect(url_for("index"))
    return render_template("settings.html", form=form)

edited Aug 2, 2012 at 22:17

Burhan Khalid

175k20 gold badges254 silver badges291 bronze badges

answered Jul 3, 2012 at 8:27

happygoat

7912 gold badges7 silver badges17 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related