CODEWITHSUNDEEP
pythonflaskflask-sqlalchemy
cs_stackX
cs_stackX

Reputation: 1527

Flask-SQL Alchemy iterate over query and update values

I have a python list of values. I would like to update a subset of one of my sqllite database tables with the values from this list. Note that the values in the list vary, but the database subset length and the list length are equal.

I can access the subset with the following query:

query = db.session.query(Sensor).filter(Sensor.experiment_id == pandas_id) # returns n rows where n > 1

But how can I loop over the returned values and update them according to my list?

I've tried:

my_list = [1, 2, 3, 4, 5]
s = db.session.query(Sensor).filter(Sensor.experiment_id == pandas_id)

for i in my_list:
    s.value = i
    db.session.add(s)

db.session.commit() # does not work

I'm now considering either deleting the subset and recreating it instead of doing an update (seems very wasteful), or exporting everything to csv and then re-entering. Both of these options are poor.

Any help would be appreciated.

Upvotes: 0

Views: 1882

Answers (1)

Yaroslav Admin
Yaroslav Admin

Reputation: 14535

The problem is that you're trying to set value on query object, but you need to set it on every object separately.

You can use izip() to iterate over two sequences at the same time and update each object separately. Something like:

my_list = [1, 2, 3, 4, 5]
query = db.session.query(Sensor).filter(Sensor.experiment_id == pandas_id)

for obj, new_value in itertools.izip(query, my_list):
    obj.value = new_value
    db.session.add(obj)  # I think this line can be removed

db.session.commit()

Upvotes: 2

Related Questions