SQLAlchemy 使用通配符或 ILIKE 进行更新语句

发布于 2024-12-11 20:34:59 字数 391 浏览 0 评论 0原文

我需要在更新语句中使用 ilike 但当我尝试时它会返回此错误：

InvalidRequestError：无法评估 Python 中的当前条件。为synchronize_session 参数指定“fetch”或False。

对于这段代码：

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

我可以使用类似 regexp_replace 的东西，但这有点矫枉过正。我只是希望更新能够适应大小写不敏感和两端的空格。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

离鸿 2024-12-18 20:35:00

试试这个：

# 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)

产生 SQL：

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

事实上，您可以通过删除 where 子句来更新表中的所有记录：

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

它会产生如下 SQL：

UPDATE person SET name=lower(person.name)

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)

回复收藏 0 原文

盗心人 2024-12-18 20:35:00

好吧，这很令人沮丧！

我发现的简单解决方法是这样的：

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()

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()

回复收藏 0 原文

~没有更多了~