在Python中用SQLAlchemy对Azure SQL执行CONTAINS全文检索遇阻

环境与前置配置 #

使用Azure SQL，搭配SQLAlchemy及mssql、aioodbc驱动，针对一张含3个TEXT列的表做全文检索，已通过以下语句完成全文目录、索引创建并开启自动变更跟踪：

CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

CREATE FULLTEXT INDEX ON myTable(
    col1,
    col2,
    col3
)
KEY INDEX PK__blabla_ans__3213E83F6A7DF9F1

ALTER FULLTEXT INDEX ON myTable SET CHANGE_TRACKING AUTO;

直接执行原生SQL可正常实现检索：

SELECT * FROM dbo.myTable
WHERE CONTAINS(col1, '"foo-bar"') OR CONTAINS(col2, '"foo-bar"') OR CONTAINS(col3, '"foo-bar"')

尝试的SQLAlchemy方法及问题 #

1. 使用.contains方法：

select(myTable).where(
  myTable.col1.contains('foo-bar')
)

该方法生成LIKE运算符，虽能运行但逻辑与CONTAINS不同，且查询速度极慢（DBEaver中原生查询耗时约0.03秒，此方法超1秒）。

2. 使用in运算符：

select(myTable).where(
  'foo-bar' in myTable.col1
)

预期调用CONTAINS，但报错：

NotImplementedError: Operator 'contains' is not supported on this expression.

3. 使用.match方法：

select(myTable).where(
  myTable.col1.match('foo-bar')
)

报错：

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The argument type "varchar(max)" is invalid for argument 2 of "CONTAINS". (4110) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)')
尝试将列转为nvarchar未成功，不确定是否可行，疑问是否必须使用原生SQL查询（违背ORM初衷）。

4. func.freetext可行，但func.contains不行：
   可行代码：

func.freetext(myTable.col1, text(f"'{filters.search_term}'"))

不可行代码：

func.contains(myTable.col1, text(f"'{filters.search_term}'"))

5. Azure SQL支持多列批量检索语法，但SQLAlchemy无法实现：

WHERE CONTAINS((myTable.col1, myTable.col2, myTable.col3), 'foo-bar')

目前只能通过OR连接多列查询，但速度较慢：

or_(
  func.freetext(myTable.col1, text(f"'{filters.search_term}'")),
  func.freetext(myTable.col2, text(f"'{filters.search_term}'"))
)

内容的提问来源于stack exchange，提问作者user26953316