0

I'm trying to update a record (row) in SQLAlchemy. I've been following along with the explanation in this question: How to update SQLAlchemy row entry? and it seems pretty straightforward, but just is not working for me.
Following this question: Update an object after session.commit() in SQLAlchemy, I also used expire_on_commit=False, but to no avail.

This question addresses the issue of adding a record instead of modifying a record: A Flask-SQLAlchemy update is creating a new record in MySQL. This question addresses moving records from one table to another: Replace existing records in SQLAlchemy Core. Neither of those two questions address my problem.

First I pull the record from the database:

>>>first_record = db.session.query(LPRRank).get(1)

Then I check it in my session:

>>>first_record
M. Misty || None || 1

All looks good, so I enter a value for the missing field:

>>>first_record.riding = 'South Riding'

Check the record again:

>>>first_record
M. Misty || South Riding || 1

I like what I see, so I commit it to the database:

>>>db.session.commit()

Apparently, I am missing something because now I lose the value for the riding:

>>>first_record
M. Misty || None || 1

I lose the value I entered even when I do another query:

>>>first_record = db.session.query(LPRRank).get(1)
>>>first_record
M. Misty || None || 1

Still not working even when I use expire_on_commit=False:

>>>first_record = db.session.query(LPRRank).get(1)
>>>first_record
M. Misty || None || 1 
>>>first_record.riding = 'South Riding'
>>>first_record
M. Misty || South Riding || 1 
>>>expire_on_commit=False       <--------- right here
>>>db.session.commit()
>>>first_record = db.session.query(LPRRank).get(1)
>>>first_record
M. Misty || None || 1

So my question: What do I need to do to make a permanent change to the database?

Here's the model I'm working with:

class LPRRank(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    candid = db.Column(db.String(40), index=True, unique=False)
    riding = db.Column(db.String(50), index=True, unique=False)
    rank = db.Column(db.Integer, index=True, unique=False)

def __repr__(self):
    return '{} || {} || {}'.format(self.candid, self.riding, self.rank)

And how I construct my session from my main module:

from app import db
from app.models import LPRRank
def make_shell_context():
    return {'db': db, 'LPRRank': LPRRank}

and my __init__.py file:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate

app = Flask(__name__)
db = SQLAlchemy(app)
migrate = Migrate(app, db)

from app import routes, models
David Collins
  • 848
  • 3
  • 10
  • 28
  • It seems like your approach should work, perhaps try querying by "LPRank.query.order_by(LPRank.id).first()". get(1) would return the object by its primary key, which you might or might not want – Tobias Apr 12 '18 at 11:18
  • For what it's worth, `>>>expire_on_commit=False <--------- right here` simply creates a new variable called `expire_on_commit`. It doesn't change a thing. – Ilja Everilä Apr 12 '18 at 11:42
  • @Tobias, thanks. Your suggestion returns the same empty field for the 'riding' column. – David Collins Apr 12 '18 at 11:58
  • @IljaEverilä, thanks. I saw that in the other questions, but as you pointed out, it does nothing. – David Collins Apr 12 '18 at 11:59
  • 1
    You're not adding the change to the `commit` call. Add `db.session.add(first_record)` before `db.session.commit()` and then check the record. – Brian Mar 12 '19 at 19:03

0 Answers0