CODEWITHSUNDEEP
pythonsqlalchemy
J.K.
J.K.

Reputation: 1616

Constructing an UPDATE .. SET .. WHERE .. statement with SQLAalchemy

I have two lists of column names: setcols and wherecols. I want to build a query with SQLAlchemy methods that is along the lines of 

UPDATE tablename SET setcols[0]= ?, ... WHERE wherecols[0]= ?, ...

What I tried is

t = sa.table('tablename', *[sa.column(name) for name in setcols+wherecols])

query = t.update(). \
        where(t.c[cn] == sa.bindparam(cn) for cn in wherecols).\
        values({cn: sa.bindparam(cn) for cn in setcols})

and this

query = t.update({cn: sa.bindparam(cn) for cn in setcols}). \
        where(t.c[cn] == sa.bindparam(cn) for cn in wherecols)

but neither of these worked and I got the following error:

sqlalchemy.exc.ArgumentError: SQL expression object or string expected, got object of type <class 'generator'> instead

I've read the manual and thought that the values to be set can be provided as a dictionary, but I see that I'm wrong...

Could anyone help?

I'm using sqlalchemy-1.1.13.

Upvotes: 1

Views: 602

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 52929

The problem lies in the way you call where(). It expects a whereclause, not a generator object. Combine those generated predicates using for example and_ or or_:

query = t.update(). \
        where(and_(*[t.c[cn] == sa.bindparam(cn) for cn in wherecols])).\
        values({cn: sa.bindparam(cn) for cn in setcols})

Upvotes: 2

Related Questions