update a database table based on a python list using sqlalchemy

Question

I have a list of absent_studentID_list

absent_studentID_list = ['sid000001','sid000006']

I want to update a column status inside student table which matches the studentID with absent_studentID_list

currently I'm looping over the list and using update

for sid in absent_studentID_list:
        u = update(studentTable).values({"status": "Absent"}).where(studentTable.c.studentid == str(sid))
        studentTableSession.execute(u)
        studentTableSession.commit()

Is there a better way to update than this?

How about u = studentTable.update().values(status="Absent").where(studentTable.c.studentid.in_(absent_studentID_list)) ? You should only need to execute it once instead of doing it in a loop. — Gord Thompson
– Gord Thompson, Commented Nov 28, 2019 at 11:25
@GordThompson can you post this answer so that i can accept your answer — Santhosh Dhaipule Chandrakanth
– Santhosh Dhaipule Chandrakanth, Commented Nov 28, 2019 at 13:32

Gord Thompson · Accepted Answer · 2019-11-28 13:45:03Z

3

You can use

u = studentTable.update()\
    .values(status="Absent")\
    .where(studentTable.c.studentid.in_(absent_studentID_list))

By using in_ you will only need to execute it once instead of doing it in a loop.

answered Nov 28, 2019 at 13:45

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

Collectives™ on Stack Overflow

update a database table based on a python list using sqlalchemy

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related