Update a field dynamically by specifying column name

Question

How can I dynamically update a field programmatically by specifying the column name in Flask-SQLAlchemy? I have a class PTc. It will only ever have one long row so I can pull all the info 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 :

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

...

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

Documentation only specifies insert, delete and select with minimal detail. The class :

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)

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

Murchie85 · Accepted Answer · 2020-07-11 23:07:21Z

1

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.

answered Jul 11, 2020 at 23:07

Murchie85

8552 gold badges15 silver badges28 bronze badges

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

1 Comment

Ryan Over a year ago

how did you commit this to the DB? Specifically, how did you use the setattr() call?

vladinstein · Accepted Answer · 2022-10-03 20:00:15Z

1

That was extremely helpful! Thanks, @Gord Thompson and @Murchie85!

target = PTc.query.order_by(PTc.id).first()
setattr(target, column_name, data_to_insert)
db.session.commit()

This is the way to use it. Basically, this way you can use dynamic value for the column_name.

edited Oct 3, 2022 at 20:00

answered Oct 2, 2022 at 6:24

vladinstein

112 bronze badges

Collectives™ on Stack Overflow

Update a field dynamically by specifying column name

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related