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
forbind parameter 'last_sync',inparameter 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?
the underlying call we use in this case is cursor.executemany():
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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
