SQLAlchemy using wildcards or ILIKE for update statments

Question

I need to use ilike in an update statement but it returns this error when I try:

InvalidRequestError: Could not evaluate current criteria in Python. Specify 'fetch' or False for the synchronize_session parameter.

for this code:

meta.Session.query(i.mappedClass).filter(getattr(i.mappedClass, j).ilike("%"+userid+"%")).update({j:newUserId})

I could use something like regexp_replace but it's a bit overkill. I just want the update to accommodate case insensitivity and spaces at either end.

score 1 · Answer 1 · answered Oct 26 '11 at 10:47

1

Try this:

# test columns
userid = "dUmMy"
j = "name" # name of the column
mappedTable = i.mappedClass.__table__ # assuming use of Declarative. if not, mappedTable is the Table object mapped to i.mappedClass
_stmt = (mappedTable.update().where(getattr(i.mappedClass, j).ilike("%"+ userid +"%")).
            values({getattr(i.mappedClass, j): func.lower(getattr(i.mappedClass, j))})
        )
session.execute(_stmt)

produces SQL:

UPDATE person SET name=lower(person.name) WHERE lower(person.name) LIKE lower(?)

In fact you can update all records in the table by just removing the where clause:

_stmt = mappedTable.update().values({getattr(i.mappedClass, j): func.lower(getattr(i.mappedClass, j))})
session.execute(_stmt)

which produces SQL like this:

UPDATE person SET name=lower(person.name)

answered Oct 26 '11 at 10:47

van

74,297
13
168
171

ideally I wanted to stay away from session.execute, but Ill have a mess around with what youve given here and see what I can get – RonnyKnoxville Oct 26 '11 at 10:56
I do not see how `session.execute()` is at the end different from the `query(...).update()`? – van Oct 26 '11 at 10:57
Because the query is in a loop and could cause issues with opening multiple connections – RonnyKnoxville Oct 26 '11 at 11:03
... then it would look like you are even better off using `session.execute(...)` or? – van Oct 26 '11 at 11:17

score 1 · Accepted Answer · answered Oct 27 '11 at 08:22

1

Ok that was frustrating!

The simple workaround I found was this:

for i in model.dataTables:
for j in i.idColumn:
    rows = meta.Session.query(i.mappedClass).filter(getattr(i.mappedClass, j).ilike("%"+userid+"%")).all()
     for row in rows:
         setattr(row, j, newuserid)
meta.Session.commit()

answered Oct 27 '11 at 08:22

RonnyKnoxville

6,166
10
46
75

This is not just a workaround, but a different way of doing it. Your first approach would basically execute the `SQL UPDATE` statement directly on the SQL backend, whereas the workaround would load every row into memory as model object, update it in-memory and later commit the changes to the SQL backend. If this is a maintenance type of operation, I recon the first approach would still be better, especially if `newuserid` can be calculated from `userid` on the SQL backend. – van Oct 28 '11 at 06:09
I dont want to have to execute the statement on every loop. The second way works better for me as the user cannot interrupt the loop half way through an update. – RonnyKnoxville Oct 28 '11 at 14:29

SQLAlchemy using wildcards or ILIKE for update statments

2 Answers2

Linked