请提供实现指定俄文全文检索SQL的SQLAlchemy请求

数据老陈

2026-6-20

使用SQLAlchemy实现PostgreSQL全文检索查询

以下是对应你提供的PostgreSQL全文检索SQL的SQLAlchemy实现，涵盖Core和ORM两种常用场景：

SQLAlchemy Core 写法

from sqlalchemy import create_engine, text, select, func

# 初始化数据库连接引擎
engine = create_engine("postgresql://用户名:密码@主机地址/数据库名")

# 构建全文检索表达式
ts_vector = func.to_tsvector(text("'russian'"), text("txt"))
ts_query = func.to_tsquery(text("'russian'"), text("'большой & вес'")) | func.to_tsquery(text("'russian'"), text("'запрос'"))

# 组装查询语句
query = select(
    ts_vector.op("@@")(ts_query).label("匹配结果"),
    text("txt")
).select_from(text("test1"))

# 执行并遍历结果
with engine.connect() as conn:
    result_set = conn.execute(query)
    for row in result_set:
        print(row.匹配结果, row.txt)

SQLAlchemy ORM 写法

先定义对应表的ORM模型（需匹配实际表结构）：

from sqlalchemy import Column, Integer, Text, func, text
from sqlalchemy.orm import declarative_base, sessionmaker

Base = declarative_base()

class Test1(Base):
    __tablename__ = "test1"
    id = Column(Integer, primary_key=True)  # 示例主键，根据实际表调整
    txt = Column(Text)

# 创建会话
engine = create_engine("postgresql://用户名:密码@主机地址/数据库名")
Session = sessionmaker(bind=engine)
db_session = Session()

# 构建查询
ts_vector = func.to_tsvector(text("'russian'"), Test1.txt)
ts_query = func.to_tsquery(text("'russian'"), text("'большой & вес'")) | func.to_tsquery(text("'russian'"), text("'запрос'"))

query = db_session.query(
    ts_vector.op("@@")(ts_query).label("匹配结果"),
    Test1.txt
)

# 获取并处理结果
for row in query.all():
    print(row.匹配结果, row.txt)