Menu
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) 
522
In PostgreSQL, you use two functions to perform Full Text Search. They are to_tsvector() and to_tsquery(). Let's see how they work and to use them first. to_tsvector() function breaks up the input string and creates tokens out of it, which is then used to perform Full Text Search using the to_tsquery() function.
Yes, You Can Keep Full-Text Search in Postgres You can get even deeper and make your Postgres full-text search even more robust, by implementing features such as highlighting results, or writing your own custom dictionaries or functions.
Go to any cluster and select the “Search” tab to do so. From there, you can click on “Create Search Index” to launch the process. Once the index is created, you can use the $search operator to perform full-text searches.
This SQLAlchemy engine is a global object which can be created and configured once and use the same engine object multiple times for different operations. The first step in establishing a connection with the PostgreSQL database is creating an engine object using the create_engine() function of SQLAlchemy.
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 (corrected based on comments):
people = Person.query.filter(Person.__ts_vector__.match(expressions, postgresql_regconfig='english')).all() If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
