How to write a generative update in SQLAlchemy

Question

I'm just using SQLAlchemy core, and cannot get the sql to allow me to add where clauses. I would like this very generic update code to work on all my tables. The intent is that this is part of a generic insert/update function that corresponds to every table. By doing it this way it allows for extremely brief test code and simple CLI utilities that can simply pass all args & options without the complexity of separate sub-commands for each table.

It'll take a few more tweaks to get it there, but should be doing the updates now just fine. However, while SQLAlchemy refers to generative queries it doesn't distinguish between selects & updates. I've reviewed SQLAlchemy documentation, Essential SQLAlchemy, stackoverflow, and several source code repositories, and have found nothing.

u      = self._table.update()
non_key_kw = {}
for column in self._table.c:
    if column.name in self._table.primary_key:
        u.where(self._table.c[column.name] == kw[column.name])
    else:
        col_name = column.name
        non_key_kw[column.name] = kw[column.name]

print u
result = u.execute(kw)

Which fails - it doesn't seem to recognize the where clause:

UPDATE struct SET year=?, month=?, day=?, distance=?, speed=?, slope=?, temp=?
FAIL

And I can't find any examples of building up an update in this way. Any recommendations?

Answer 1

the "where()" method is generative in that it returns a new Update() object. The old one is not modified:

u = u.where(...)