A Lucene query like fliltering 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 functools import partial
from typing import Optional
from sqlmodel import Field, SQLModel
class Hero(SQLModel, table=True):
id: Optional[int] = Field(default=None, primary_key=True)
name: str
secret_name: str
age: Optional[int] = None
created_at: datetime.datetime = Field(
default_factory=partial(datetime.datetime.now, datetime.UTC)
)
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:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
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 import parse
from sqlmodel import Session
from sqlmodel_filters import Builder
# parse a Lucene query
parsed = parse('name:Spider')
# build SELECT statement for Hero based on the parsed query
builder = Builder(Hero)
statement = builder(parsed)
# the following is a compiled SQL query
statement.compile(compile_kwargs={"literal_binds": True})
>>> SELECT hero.id, hero.name, hero.secret_name, hero.age, hero.created_at
>>> FROM hero
>>> WHERE hero.name = '%Spider%'
# you can use the statement like this
heros = session.exec(statement).all()
assert len(heros) == 1
assert heros[0].name == "Spider-Boy"
Note that a value is automatically casted based on a field definition.
# age: Optional[int]
"age:48"
>>> WHERE hero.age = 48
# created_at: datetime.datetime
"created_at:2020-01-01"
>>> WHERE hero.created_at = '2020-01-01 00:00:00'
Word
(Term
)
Double quote a value if you want to use the equal operator.
'name:"Spider-Boy"'
>>> WHERE hero.name = 'Spider-Boy'
The LIKE
operator is used when you don't double quote a value.
"name:Spider"
>>> WHERE hero.name LIKE '%Spider%'
Use ?
(a single character wildcard) or *
(a multiple character wildcard) to control a LIKE operator pattern.
"name:Deadpond?"
>>> WHERE hero.name LIKE 'Deadpond_'
"name:o*"
>>> WHERE hero.name LIKE 'o%'
FROM
& TO
"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
"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)
"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
"name:Rusty NOT age:47"
>>> WHERE hero.name LIKE '%Rusty%' AND hero.age != 47
"(name:Spider OR age:48) AND name:Rusty"
>>> WHERE (hero.name LIKE '%Spider%' OR hero.age = 48) AND hero.name LIKE '%Rusty%'
Known Limitations / Todos
- Relationship join is not supported
- Filed 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
sqlmodel_filters-0.0.1.tar.gz
(5.2 kB
view hashes)
Built Distribution
Close
Hashes for sqlmodel_filters-0.0.1-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 385c872fb02b3003d546668962128093df18c0c89e43874efa5dfee01d2066c9 |
|
MD5 | 04314bbd047b163514e09aaf50528d01 |
|
BLAKE2b-256 | 0be182ab4f6e1b466b67a0e99407ea791b0eac19df12a8b9d5e1a20f6c128c36 |