Create a Full Text Search index with SQLAlchemy on PostgreSQL

sqlalchemy postgres
sqlalchemy jsonb
postgres full text search
sqlalchemy postgres upsert
sqlalchemy dialects postgresql array
postgresql fuzzy full text search
sqlalchemy full text search sqlite
postgres full text search partial words

I need to create a PostgreSQL Full Text Search index in Python with SQLAlchemy. Here's what I want in SQL:

CREATE TABLE person ( id INTEGER PRIMARY KEY, name TEXT );
CREATE INDEX person_idx ON person USING GIN (to_tsvector('simple', name));

Now how do I do the second part with SQLAlchemy when using the ORM:

class Person(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)

You could create index using Index in __table_args__. Also I use a function to create ts_vector to make it more tidy and reusable if more than one field is required. Something like below:

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

Update: A sample query using index:

query = Person.__ts_vector__.match(expressions, postgresql_regconfig='english')
people = query.all()

SQLAlchemy and full text searching in postgresql, SQLAlchemy and full text searching in postgresql. Introduction. Postgresql has support for full text search. The table we want. Scaffold. Creating the type. Creating the tables. Add a few helper functions. Adding an index for this column. Automatically updating the column using a trigger. PostgreSQL supports sequences, and SQLAlchemy uses these as the default means of creating new primary key values for integer-based primary key columns. When creating tables, SQLAlchemy will issue the SERIAL datatype for integer-based primary key columns, which generates a sequence and server side default corresponding to the column.

The answer from @sharez is really useful (especially if you need to concatenate columns in your index). For anyone looking to create a tsvector GIN index on a single column, you can simplify the original answer approach with something like:

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'
            ),
        )

Note that the comma following Index(...) in __table_args__ is not a style choice, the value of __table_args__ must be a tuple, dictionary, or None.

If you do need to create a tsvector GIN index on multiple columns, here is another way to get there using 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'
            ),
        )

Postgres Full Text Search in SQLAlchemy, Postgres Full Text Search in SQLAlchemy We will be adding the trigram index on a text column for quicker searches. """alter books trgm title Revision ID: some_revision_id Revises: some_down_revision_id Create Date:  After reading Postgres full-text search is Good Enough!, and Implementing Multi-Table Full Text Search with Postgres in Rails, I figured that I would try implementing Postgres full-text search. I ended using a very similar approach to the one outlined in the Rails post.

It has been answered already by @sharez and @benvc. I needed to make it work with weights though. This is how I did it based on their answers :

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'),
    )

PostgreSQL, Full Text Searches in PostgreSQL are influenced by a combination of: the that take full advantage of any indexes you may have created for full text search. Can I get an example of how to do full text search with Postgres with the SQLAlchemy ORM? The documentation here does not show an example with the ORM. Simple example code I have: class Person(db.M

Multi-Table Full Text Search with Postgres, Flask, and Sqlalchemy , Multi-Table Full Text Search with Postgres, Flask, and Sqlalchemy create remaining indices on the tsv columns for index in index_set:  Submit correction. If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Postgres full-text search, Postgres has full-text search built into it (and.. as with anything, there are pros and from sqlalchemy import ForeignKey, or_, and_ from sqlalchemy import some changes had to be made so it could compute the full-text index data. CREATE TRIGGER tsv_odesk_job_snippet_trigger AFTER INSERT OR  Offering FullText Search of MySQL in SQLAlchemy. Contribute to mengzhuo/sqlalchemy-fulltext-search development by creating an account on GitHub.

[PDF] SQLAlchemy-Searchable Documentation, SQLAlchemy-Searchable provides full text search capabilities for SQLAlchemy models. Currently it only content to be fulltext indexed, hence we put them inside the definition of TSVectorType. Now lets create the tables and some dummy data. readable search query into PostgreSQL tsquery format. When performing a Full Text Search against a column that has a GIN or GiST index that is already pre-computed (which is common on full text searches) one may need to explicitly pass in a particular PostgresSQL regconfig value to ensure the query-planner utilizes the index and does not re-compute the column on demand.

Comments
  • Could you give an example on how to query the vector? Thanks.
  • @sharez with latest version, query.all() throws error: Neither 'BinaryExpression' object nor 'Comparator' object has an attribute 'all'. could you provide alternative to it ?
  • @apaleja match is an operator, hence it should be within a filter method, as such: Person.query.filter(Person.__ts_vector__.match(expressions, postgresql_regconfig='english')).all()
  • Could you give an example on how to query the vector? Thanks.
  • @apaleja - see example query at the bottom of this answer: stackoverflow.com/questions/13837111/…