altqq 0.0.2

Alternative Queries: Typed and Reusable Handcrafted SQL

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]