CODEWITHSUNDEEP
pythonpostgresqlsqlitesqlalchemypartial-index
Nevermore
Nevermore

Reputation: 7389

Generic partial Index support for sqlalchemy

Along the lines of this question, SQLAlchemy - SQLite for testing and Postgresql for development - How to port?

I realize, that the (above) the consensus is don't test with a db not used in production.

I want to abstract partial index support for sqlalchemy such that I could use either Postgres or Sqlite.

I've seen that with PostgreSQL I can use 

    Index('only_one_active_invoice', 
          invoice_id, active,
          unique=True,
          postgresql_where=(active)
    ),

But I see that partial index is also supported in sqlite https://sqlite.org/partialindex.html

Is there some sort of generic partial index support for sqlalchemy with which my module could work for either postgres or sqlite databases?

Upvotes: 1

Views: 881

Answers (1)

Andrii Gerasymchuk
Andrii Gerasymchuk

Reputation: 36

There is no general way, according to doc

You should use for sqlite

idx = Index('test_idx1', tbl.c.data,
            sqlite_where=and_(tbl.c.data > 5, tbl.c.data < 10))

and for postgres:

idx = Index('my_index', my_table.c.id, 
            postgresql_where=my_table.c.value > 10)

in your case, you can check db engine and initialize index with kwargs

Upvotes: 1

Related Questions