altqq 0.0.1a1

Alternative Queries: Typed and Composable Tool for Handwritten 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.

The library is currently still in development and has an alpha release.

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]

Road Map

Below is the list of things planned for the library

• Documentation Page
• Tests
• Expansion of Supported Version
• Support for other Python Database Tooling