updating¶

This module provide utility functions for update operation.

sqlalchemy_mate.crud.updating.update_all(engine: sqlalchemy.engine.base.Engine, table: sqlalchemy.sql.schema.Table, data: Union[Dict[str, Any], List[Dict[str, Any]]], upsert=False) → Tuple[int, int][source]¶: Update data by its primary_key column values. By default upsert is False.

sqlalchemy_mate.crud.updating.upsert_all(engine: sqlalchemy.engine.base.Engine, table: sqlalchemy.sql.schema.Table, data: Union[Dict[str, Any], List[Dict[str, Any]]]) → Tuple[int, int][source]¶

Update data by primary key columns. If not able to update, do insert.

Example:

# define data model
t_user = Table(
    "users", metadata,
    Column("id", Integer, primary_key=True),
    Column("name", String),
)

# suppose in database we already have {"id": 1, "name": "Alice"}
data = [
    {"id": 1, "name": "Bob"}, # this will be updated
    {"id": 2, "name": "Cathy"}, # this will be added
]
update_count, insert_count = upsert_all(engine, t_user, data)
print(update_count) # number of row updated counter
print(insert_count) # number of row inserted counter

# will return: [{"id": 1, "name": "Bob"}, {"id": 2, "name": "Cathy"}]
with engine.connect() as connection:
    print(connection.execute(select([table_user])).all())

中文文档

批量更新文档. 如果该表格定义了Primary Key, 则用Primary Key约束where语句. 对于 where语句无法找到的行, 自动进行批量 bulk insert.