SQLAlchemy - Bulk update using sqlalchemy core table.update() expects all columns in the values data
Rajesh Rolo
I'm trying to do a bulk update using core SQLAlchemy to a postgres database. bulk_update_mappings does not work (reports StaleDataError). So I'm trying to use core functions to do a bulk update. This works fine when the update data passed to the values have all the columns in the db but fails to work when we update only a certain columns. In my application, during periodic syncs between the server and the client only a few of the columns will get updated most of the times.
The code snippet I have for update is :
conn = session.connection()
table = table_dict[table_key].__table__
stmt=table.update().where(and_(table.c.user_id==bindparam('uid'),
tbl_pk[table_key]==bindparam('pkey'))).values()
conn.execute(stmt, update_list)Since I update multiple tables on every sync, table names and primary keys are indexed through an array. For the example below table_dict[table_key] would translate to the table 'nwork' and tbl_pk[table_key] would translate to 'table.c.nwid' which would be 'nwork.nwid'.
The update_list is a list of records (that need to get updated) as a python dictionary. When the record has values for all the columns it works fine and when only some of the columns is getting updated it's throwing the following error:
StatementError: (sqlalchemy.exc.InvalidRequestError) A value is required for bind parameter 'last_sync', in parameter group 1
[SQL: u'UPDATE nwork SET type=%(type)s, name=%(name)s, last_sync=%(last_sync)s,
update_time=%(update_time)s, status=%(status)s, total_contacts=%(total_contacts)s,
import_type=%(import_type)s, cur_index=%(cur_index)s WHERE
nwork.user_id = %(uid)s AND nwork.nwid = %(pkey)s']In this case the error was happening for a record where the column 'last_sync' was not getting updated.
What's the way of doing a bulk update where the records may not have all the columns (the same set of them) getting updated?
I'm running SQLAlchemy 1.0.14.
Seth P
Mike Bayer
On 09/28/2016 04:29 AM, Rajesh Rolo wrote:
> I'm trying to do a bulk update using core SQLAlchemy to a postgres
> database. bulk_update_mappings does not work (reports StaleDataError).
> So I'm trying to use core functions to do a bulk update. This works fine
> when the update data passed to the values have all the columns in the db
> but fails to work when we update only a certain columns. In my
> application, during periodic syncs between the server and the client
> only a few of the columns will get updated most of the times.
>
> The code snippet I have for update is :
>
> |conn =session.connection()table =table_dict[table_key].__table__
> stmt=table.update().where(and_(table.c.user_id==bindparam('uid'),tbl_pk[table_key]==bindparam('pkey'))).values()conn.execute(stmt,update_list)|
>
> Since I update multiple tables on every sync, table names and primary
> keys are indexed through an array. For the example below
> table_dict[table_key] would translate to the table 'nwork' and
> tbl_pk[table_key] would translate to 'table.c.nwid' which would be
> 'nwork.nwid'.
>
> The update_list is a list of records (that need to get updated) as a
> python dictionary. When the record has values for all the columns it
> works fine and when only some of the columns is getting updated it's
> throwing the following error:
>
> |StatementError:(sqlalchemy.exc.InvalidRequestError)A value isrequired
> update_time=%(update_time)s, status=%(status)s,
> total_contacts=%(total_contacts)s, import_type=%(import_type)s,
> cur_index=%(cur_index)s WHERE nwork.user_id = %(uid)s AND nwork.nwid =
> %(pkey)s']|
>
> In this case the error was happening for a record where the column
> 'last_sync' was not getting updated.
>
> What's the way of doing a bulk update where the records may not have all
> the columns (the same set of them) getting updated?
https://www.python.org/dev/peps/pep-0249/#executemany
that is, the SQL is just once, and then lots of parameters. Such as,
INSERT INTO table (x, y, z) VALUES (%s, %s, %s)
Then you'd send a list of 3-tuples. So it should be clear that *every*
tuple needs to have three values. SQLAlchemy does not assume what
value you'd want to put in your dictionary if values are missing.
The three ways this could be dealt with are:
1. provide an onupdate value for the Column that's missing (described at
http://docs.sqlalchemy.org/en/latest/core/defaults.html). This is not
likely to be what you want.
2. Fill in those missing values for your dictionaries. But since this
is an UPDATE, I can tell maybe that's not what you want here. Which
leaves us with
3. Break up your collection of dictionaries based on which keys are
present, then use multiple calls to conn.execute() for each batch. I
like to use itertools.groupby() for this sort of thing.
>
>
> I'm running SQLAlchemy 1.0.14.
>
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
Seth P
Rajesh Rolo
Thanx for your reply. I'll try out the 3rd option. It would still be better than updating record by record at object level.