Alternative Queries: Typed and Reusable Handcrafted SQL
Project description
Alternative Queries
Alternative queries is a library created to help with handcrafted SQL queries.
It works by providing a class that represent the queries, with parameters type
checked by Pydantic
.
Installation
The library is available in PyPI
pip install altqq
Basic Usage
To use the library, you can define a class that represents a query. Then this
query can be converted to plain text or pyodbc
usable query.
import altqq
class SelectUserByFirstName(altqq.Query):
__query__ = """
SELECT * FROM "Users"
WHERE first_name = {first_name}
"""
first_name: str
q = altqq.to_pyodbc(SelectUserByFirstName(first_name="arietta"))
print(q.query)
print(q.parameters)
Running the code above should give the result below:
SELECT * FROM "Users"
WHERE first_name = ?
['arietta']
Templating Non-Parameters
By default, the class properties are treated as parameters. If there's a need
for more customization, they can be declared as altqq.NonParameter
.
import altqq
class SelectByFirstName(altqq.Query):
__query__ = """
SELECT * FROM "{table}"
WHERE first_name = {first_name}
"""
first_name: str
table: altqq.NonParameter[str]
q = altqq.to_pyodbc(SelectByFirstName(
first_name="arietta",
table="Users"
))
print(q.query)
print(q.parameters)
Running the code above should give the result below:
SELECT * FROM "Users"
WHERE first_name = ?
['arietta']
Nested Queries
Queries can also use other queries. When passed to the functions for conversion, other queries will also be converted.
import altqq
class SelectUserByFirstName(altqq.Query):
__query__ = """
SELECT * FROM "Users"
WHERE first_name = {first_name}
"""
first_name: str
class SelectSubqueryByAge(altqq.Query):
__query__ = """
SELECT * FROM ({subquery}) AS tbl
WHERE tbl.age = {age}
"""
age: int
subquery: altqq.Query
q = altqq.to_pyodbc(SelectSubqueryByAge(
age=20,
subquery=SelectUserByFirstName(
first_name="arietta"
)
))
print(q.query)
print(q.parameters)
Running the code above should give the result below:
SELECT * FROM (
SELECT * FROM "Users"
WHERE first_name = ?
) AS tbl
WHERE tbl.age = ?
['arietta', 20]
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.