Flask-sqlalchemy update a field dynamically by specifying column name

Question

How can I dynamically update a field programatically by specifying the column name in Flask-SQLAlchemy

I have a class called PTc It will will only ever have one long row so I can pull all the info back with the following:

Target = PTc.query.order_by(PTc.id).first()

I can manually update a field e.g. Target.price = 2 then commit.

How can I update dynamically? i.e. When the program runs, it may want to update the price, amount or availability depending on the condition.

If I save column name to a variable, columnToUpdate = 'amount' then try Target.columnToUpdate = 10 it will of course fail because it thinks that is a column.

Otherwise I am forced to check condition of each which could get cumbersome given the large number of columns i.e.

if columnToUpdate == 'price'
   Target.price = 20
elif columnToUpdate =='amount':
     Target.amount = 20

...

elif columnToUpdate =='XYZ':
     Target.amount = ABC

I have been looking through the documentation which only specifies, insert, delete and select with minimal detail.

Addendum - the class looks like the following.

class PTc(db.Model):
    # id column     
    id = db.Column(db.Integer, primary_key=True) 
    goldPrice       = db.Column(db.Integer, nullable=False)
    goldStock       = db.Column(db.Integer, nullable=False)
    goldPriceChange = db.Column(db.String(50), nullable=False)
    goldHistory     = db.Column(db.String(100), nullable=False)
    goldAverage     = db.Column(db.Integer, nullable=False)

    rmPrice         = db.Column(db.Integer, nullable=False)
    rmStock         = db.Column(db.Integer, nullable=False)
    rmPriceChange   = db.Column(db.String(50), nullable=False)
    rmHistory       = db.Column(db.String(100), nullable=False)
    rmAverage       = db.Column(db.Integer, nullable=False)

    gemsPrice         = db.Column(db.Integer, nullable=False)
    gemsStock         = db.Column(db.Integer, nullable=False)
    gemsPriceChange   = db.Column(db.String(50), nullable=False)
    gemsHistory       = db.Column(db.String(100), nullable=False)
    gemsAverage       = db.Column(db.Integer, nullable=False)

    oilPrice         = db.Column(db.Integer, nullable=False)
    oilStock         = db.Column(db.Integer, nullable=False)
    oilPriceChange   = db.Column(db.String(50), nullable=False)
    oilHistory       = db.Column(db.String(100), nullable=False)
    oilAverage       = db.Column(db.Integer, nullable=False)

Many thanks

Afraid no joy with that: AttributeError: type object 'PTc' has no attribute 'setattr' — Murchie85
Updated class in description, it's fairly similar to the examples given in their tutorial flask-sqlalchemy.palletsprojects.com/en/2.x — Murchie85
@Murchie85 - setattr works for me. Note that it is a built-in function, not a class method. — Gord Thompson

Murchie85 Murchie85 · Accepted Answer · 2020-07-11T23:07:21

The setattr(PTc, "goldPrice", 200) is what I was looking for, thanks to Gord for the solution.

It would be interested to see if there was another SQLAlchemy specific method given the documented class.field = x convention prevents the use of a variable as the field and would seem remarkable if they hadn't accounted for that.

Flask-sqlalchemy update a field dynamically by specifying column name

1 Answers