sqlalchemy update using list of tuples

user_3327034 0
Viewed 83

Is there an efficient way to update rows based on list of tuples in sqlalchemy? If its a single row, then I can simply do:

session.query(table).filter(table.id == 10).update({'values': 'x'})
session.commit

however, the data i'm getting is a list of tuples

[(10, 'x'),(20,'y'),(30,'z'),(40,'p')]

table has IDs 10,20,30,40 etc.

is there efficient way to update instead of multiple individual updates?

sqlalchemy
1 Answers

You can convert the list of tuples to a list of dicts and then use update() with bindparam() as illustrated in the tutorial:

from pprint import pprint

import sqlalchemy as sa

engine = sa.create_engine("sqlite://")

tbl = sa.Table(
    "tbl",
    sa.MetaData(),
    sa.Column("id", sa.Integer, primary_key=True, autoincrement=False),
    sa.Column("value", sa.String(50)),
)
tbl.create(engine)

with engine.begin() as conn:
    conn.execute(
        tbl.insert(),
        [
            {"id": 10, "value": "old_10"},
            {"id": 20, "value": "old_20"},
            {"id": 30, "value": "old_30"},
        ],
    )

with engine.begin() as conn:
    # initial state
    print(conn.execute(sa.select(tbl)).all())
    # [(10, 'old_10'), (20, 'old_20'), (30, 'old_30')]

    new_data = [(10, "x"), (20, "y"), (30, "z")]

    params = [dict(tbl_id=a, new_value=b) for (a, b) in new_data]
    pprint(params, sort_dicts=False)
    """
    [{'tbl_id': 10, 'new_value': 'x'},
     {'tbl_id': 20, 'new_value': 'y'},
     {'tbl_id': 30, 'new_value': 'z'}]
    """

    upd = (
        sa.update(tbl)
        .values(value=sa.bindparam("new_value"))
        .where(tbl.c.id == sa.bindparam("tbl_id"))
    )
    print(upd)
    # UPDATE tbl SET value=:new_value WHERE tbl.id = :tbl_id

    conn.execute(upd, params)

    # check results
    print(conn.execute(sa.select(tbl)).all())
    # [(10, 'x'), (20, 'y'), (30, 'z')]
user_21443900
Related