A Lucene query like filtering for SQLModel
Project description
sqlmodel-filters
A Lucene query like filter for SQLModel.
[!NOTE] This is an alpha level library. Everything is subject to change & there are some known limitations.
Installation
pip install sqlmodel-filters
How to Use
Let's say we have the following model & records:
import datetime
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine
class Headquarter(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
teams: list["Team"] = Relationship(back_populates="headquarter")
class Team(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
headquarter_id: int | None = Field(default=None, foreign_key="headquarter.id")
headquarter: Headquarter | None = Relationship(back_populates="teams")
heroes: list["Hero"] = Relationship(back_populates="team")
class Hero(SQLModel, table=True): # type: ignore
id: int | None = Field(default=None, primary_key=True)
name: str
secret_name: str
age: int | None = None
created_at: datetime.datetime = Field(default_factory=datetime.datetime.utcnow)
team_id: int | None = Field(default=None, foreign_key="team.id")
team: Team | None = Relationship(back_populates="heroes")
headquarter_1 = Headquarter(id=1, name="Sharp Tower")
headquarter_2 = Headquarter(id=2, name="Sister Margaret's Bar")
team_1 = Team(id=1, name="Preventers", headquarter_id=1)
team_2 = Team(id=2, name="Z-Force", headquarter_id=2)
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
engine = create_engine("sqlite://")
SQLModel.metadata.create_all(engine)
with Session(engine) as session:
for obj in [headquarter_1, headquarter_2, team_1, team_2, hero_1, hero_2, hero_3]:
session.add(obj)
session.commit()
And let's try querying with this library.
# this library relies on luqum (https://github.com/jurismarches/luqum) for parsing Lucene query
from luqum.thread import parse
from sqlmodel import Session
from sqlmodel_filters import SelectBuilder
# parse a Lucene query
parsed = parse('name:Spider')
# build SELECT statement for Hero based on the parsed query
builder = SelectBuilder(Hero)
statement = builder(parsed)
# the following is a compiled SQL query
statement.compile(compile_kwargs={"literal_binds": True})
The compiled SQL query is:
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.created_at
FROM hero
WHERE hero.name = '%Spider%'
And you can execute the query to get Hero objects.
>>> heros = session.exec(statement).all()
[Hero(name='Spider-Boy', id=2, team_id=1, age=None, secret_name='Pedro Parqueador', created_at=datetime.datetime(...))]
Specs
Type Casting
A value is automatically casted based on a field of a model.
Query | SQL (Where Clause) | Field |
---|---|---|
age:48 |
WHERE hero.age = 48 |
age: Optional[int] |
created_at:2020-01-01 |
WHERE hero.created_at = '2020-01-01 00:00:00' |
created_at: datetime.datetime |
Word
(Term
)
- Double quote a value if you want to use the equal operator.
- The
LIKE
operator is used when you don't double quote a value. - Use
?
(a single character wildcard) or*
(a multiple character wildcard) to control a LIKE operator pattern. *
is converted asIS NOT NULL
.
Query | SQL (Where Clause) |
---|---|
name:Spider-Boy" |
WHERE hero.name = 'Spider-Boy' |
name:Spider |
WHERE hero.name LIKE '%Spider%' |
name:Deadpond? |
WHERE hero.name LIKE 'Deadpond_' |
name:o* |
WHERE hero.name LIKE 'o%' |
name:* |
WHERE hero.name IS NOT NULL |
REGEX
Query | SQL (Where Clause) |
---|---|
name:/Spider?Boy/ |
WHERE hero.name <regexp> 'Spider?Boy' |
[!NOTE] Regex support works differently per backend. See SQLAlchemy docs for details.
FROM
& TO
Query | SQL (Where Clause) |
---|---|
age:>40 |
WHERE hero.age > 40 |
age:>=40 |
WHERE hero.age >= 40 |
age:<40 |
WHERE hero.age < 40 |
age:<=40 |
WHERE hero.age <= 40 |
RANGE
Query | SQL (Where Clause) |
---|---|
age:{48 TO 60} |
WHERE hero.age < 60 AND hero.age > 48 |
age:[48 TO 60] |
WHERE hero.age <= 60 AND hero.age => 48 |
AND
, OR
, NOT
and GROUP
(Grouping)
Query | SQL (Where Clause) |
---|---|
name:Rusty AND age:48 |
WHERE hero.name LIKE '%Rusty%' AND hero.age = 48 |
name:Rusty OR age:47 |
WHERE hero.name LIKE '%Rusty%' OR hero.age = 47 |
NOT name:Rusty |
WHERE hero.name NOT LIKE '%Rusty%' |
(name:Spider OR age:48) AND name:Rusty |
WHERE (hero.name LIKE '%Spider%' OR hero.age = 48) AND hero.name LIKE '%Rusty%' |
Note that the default conjunction is OR
.
Query | SQL (Where Clause) |
---|---|
name:Rusty age:48 |
WHERE hero.name LIKE '%Rusty%' OR hero.age = 48 |
Relationship
Set relationships
(key-to-model mapping) to do filtering on relationship(s).
>>> parsed = parse('name:Spider AND team.name:"Preventers" AND team.headquarter.name:Sharp')
>>> builder = SelectBuilder(Hero, relationships={"team": Team, "headquarter": Headquarter})
>>> statement = builder(parsed)
>>> statement.compile(compile_kwargs={"literal_binds": True})
SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.created_at, hero.team_id
FROM hero JOIN team ON team.id = hero.team_id JOIN headquarter ON headquarter.id = team.headquarter_id
WHERE hero.name LIKE '%Spider%' AND team.name = 'Preventers' AND headquarter.name LIKE '%Sharp%'
Many-to-Many Relationship
If you have a many-to-many relationship like the following:
class Tag(SQLModel, table=True):
__tablename__ = "tags" # type: ignore
id: int | None = Field(default=None, primary_key=True)
name: str = Field(...)
posts: list["Post"] = Relationship(back_populates="tags", sa_relationship_kwargs={"secondary": "taggings"})
class Post(SQLModel, table=True):
__tablename__ = "posts" # type: ignore
id: int | None = Field(default=None, primary_key=True)
tags: list["Tag"] = Relationship(back_populates="posts", sa_relationship_kwargs={"secondary": "taggings"})
class Tagging(SQLModel, table=True):
__tablename__ = "taggings" # type: ignore
tag_id: str = Field(foreign_key="tags.id", primary_key=True)
post_id: str = Field(foreign_key="posts.id", primary_key=True)
A value of the relationships
keyword argument should be a dict which has:
join
: a many-to-many relationship to join.isouter
: whether to generate LEFT OUTER join or not. Defaults toFalse
.full
: whether to generate FULL OUTER join or not. Defaults toFalse
.model
: a model of a many-to-many relationship.
For example:
>>> tree = parse("tags.name:foo")
>>> builder = SelectBuilder(Post, relationships={"tags": {"join": Post.tags, "model": Tag}}) # type: ignore
>>> statement = builder(tree)
>>> statement.compile(compile_kwargs={"literal_binds": True})
SELECT posts.id
FROM posts JOIN taggings AS taggings_1 ON posts.id = taggings_1.post_id JOIN tags ON tags.id = taggings_1.tag_id
WHERE tags.name LIKE '%foo%'
Entity
Set entities
to select specific columns.
>>> tree = parse("name:*")
>>> statement = builder(tree, entities=(Hero.id, Hero.name))
>>> session.exec(statement).all()
[(1, "Deadpond"), (2, "Spider-Boy"), (3, "Rusty-Man")]
You can also use a function like count
.
>>> tree = parse("name:*")
>>> statement = builder(tree, entities=func.count(Hero.id))
>>> session.scalar(statement)
3
Default Fields
Default fields, all of the fields in a model by default, are used when you don't set a field in a query.
Query | SQL (Where Clause) |
---|---|
Spider |
WHERE hero.name LIKE '%Spider%' OR hero.secret_name LIKE '%Spider%' |
You can override the default by setting default_fields
.
builder = SelectBuilder(Hero, default_fields={"name": Hero.model_fields["name"]})
Helper Function
q_to_select
function parses a query and builds a select statement on the fly.
from sqlmodel_filters import q_to_select
statement = q_to_select('name:"Spider-Boy"', Hero)
Known Limitations / Todos
- Field Grouping is not supported
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distribution
Hashes for sqlmodel_filters-0.0.11-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | fc3b901fc3eda475a50abffa1cbb43547cd33ce6f9c106e3b08f96eb64f871c8 |
|
MD5 | e0fd99f0b8e71d2dfe379ecd3a858a06 |
|
BLAKE2b-256 | 884a5afff2eb20059a9d9741b6853adaeb6fffee705c2e31845b12f2844c318b |