How To Create A Pg_trgm Index Using Sqlalchemy For Scrapy?

I am using Scrapy to scrape data from a web forum. I am storing this data in a PostgreSQL database using SQLAlchemy. The table and columns create fine, however, I am not able to ha

Solution 1:

The proper way to reference an Operator Class in SQLAlchemy (such as gin_trgm_ops) is to use the postgresql_ops parameter. This will also allow tools like alembic to understand how use it when auto-generating migrations.

Index('description_idx',
      'description', postgresql_using='gin',
      postgresql_ops={
          'description': 'gin_trgm_ops',
      })

Solution 2:

Since the Index definition uses text expression it has no references to the Table "table", which has been implicitly created by the declarative class forumDB. Compare that to using a Column as expression, or some derivative of it, like this:

Index('some_index_idx', forumDB.title)

In the above definition the index will know about the table and the other way around.

What this means in your case is that the Table "table" has no idea that such an index exists. Adding it as an attribute of the declarative class is the wrong way to do it. It should be passed to the implicitly created Table instance. The attribute __table_args__ is just for that:

classforumDB(DeclarativeBase):

    __tablename__ = "table"# Note: This used to use `text('description gin_trgm_ops')` instead of the# `postgresql_ops` parameter, which should be used.
    __table_args__ = (
        Index('description_idx', "description",
              postgresql_ops={"description": "gin_trgm_ops"},
              postgresql_using='gin'),
    )

    id = Column(Integer, primary_key=True)
    title = Column('title', String)
    desc = Column('description', String, nullable=True)

With the modification in place, a call to create_forum_table(engine) resulted in:

> \d "table"
                                 Table "public.table"
   Column|       Type        |                     Modifiers                      
-------------+-------------------+----------------------------------------------------
 id          |integer|notnulldefault nextval('table_id_seq'::regclass)
 title       |charactervarying| 
 description |charactervarying| 
Indexes:
    "table_pkey" PRIMARY KEY, btree (id)
    "description_idx" gin (description gin_trgm_ops)