Necesito crear un índice de búsqueda de texto completo de PostgreSQL en Python con SQLAlchemy. Esto es lo que quiero en SQL:
CREATE TABLE person ( id INTEGER PRIMARY KEY, name TEXT ); CREATE INDEX person_idx ON person USING GIN (to_tsvector('simple', name));Ahora, ¿cómo hago la segunda parte con SQLAlchemy cuando uso el ORM?
class Person(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String)Puede crear un índice usando Index en __table_args__ . También uso una función para crear ts_vector para hacerlo más ordenado y reutilizable si se requiere más de un campo. Algo como a continuación:
from sqlalchemy.dialects import postgresql def create_tsvector(*args): exp = args[0] for e in args[1:]: exp += ' ' + e return func.to_tsvector('english', exp) class Person(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String) __ts_vector__ = create_tsvector( cast(func.coalesce(name, ''), postgresql.TEXT) ) __table_args__ = ( Index( 'idx_person_fts', __ts_vector__, postgresql_using='gin' ) )Actualización: una consulta de muestra usando el índice (corregido en base a los comentarios):
people = Person.query.filter(Person.__ts_vector__.match(expressions, postgresql_regconfig='english')).all()La respuesta de @sharez es realmente útil (especialmente si necesita concatenar columnas en su índice). Para cualquiera que busque crear un índice GIN de tsvector en una sola columna, puede simplificar el enfoque de respuesta original con algo como:
from sqlalchemy import Column, Index, Integer, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.sql import func Base = declarative_base() class Example(Base): __tablename__ = 'examples' id = Column(Integer, primary_key=True) textsearch = Column(String) __table_args__ = ( Index( 'ix_examples_tsv', func.to_tsvector('english', textsearch), postgresql_using='gin' ), ) Tenga en cuenta que la coma que sigue a Index(...) en __table_args__ no es una elección de estilo, el valor de __table_args__ debe ser una tupla, un diccionario o None .
Si necesita crear un índice GIN de tsvector en varias columnas, aquí hay otra forma de llegar usando text() .
from sqlalchemy import Column, Index, Integer, String, text from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.sql import func Base = declarative_base() def to_tsvector_ix(*columns): s = " || ' ' || ".join(columns) return func.to_tsvector('english', text(s)) class Example(Base): __tablename__ = 'examples' id = Column(Integer, primary_key=True) atext = Column(String) btext = Column(String) __table_args__ = ( Index( 'ix_examples_tsv', to_tsvector_ix('atext', 'btext'), postgresql_using='gin' ), )Ya ha sido respondido por @sharez y @benvc. Sin embargo, necesitaba hacerlo funcionar con pesas. Así es como lo hice en base a sus respuestas:
from sqlalchemy import Column, func, Index, String from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.sql.operators import op CONFIG = 'english' Base = declarative_base() def create_tsvector(*args): field, weight = args[0] exp = func.setweight(func.to_tsvector(CONFIG, field), weight) for field, weight in args[1:]: exp = op(exp, '||', func.setweight(func.to_tsvector(CONFIG, field), weight)) return exp class Example(Base): __tablename__ = 'example' foo = Column(String) bar = Column(String) __ts_vector__ = create_tsvector( (foo, 'A'), (bar, 'B') ) __table_args__ = ( Index('my_index', __ts_vector__, postgresql_using='gin'), )