sqlo 0.2.0

A modern, type-safe, and extensible SQL query builder for Python.

sqlo

A lightweight and powerful SQL query builder for Python. Build SQL queries with a clean, intuitive API while staying safe from SQL injection. Support for JSON fields, CTEs, batch updates, and more!

Why sqlo?

• 🪶 Lightweight: Zero dependencies, minimal footprint
• ✨ Simple: Intuitive fluent API, easy to learn
• 🛡️ Secure by Default: Built-in SQL injection protection
• 🐍 Pythonic: Fluent API design that feels natural to Python developers
• 🧩 Composable: Build complex queries from reusable parts
• 🚀 Extensible: Support for custom dialects and functions
• 🔍 Type-Safe: Designed with type hints for better IDE support
• ✅ Well-Tested: 99% code coverage with comprehensive security tests

Installation

pip install sqlo

Quick Start

from sqlo import Q

# SELECT query
query = Q.select("id", "name").from_("users").where("active", True)
sql, params = query.build()
# SQL: SELECT `id`, `name` FROM `users` WHERE `active` = %s
# Params: (True,)

# INSERT query
query = Q.insert_into("users").values([
    {"name": "Alice", "email": "[e-mail]"}
])
sql, params = query.build()

Debug Mode

Global Debug Mode

Enable debug mode to automatically print all queries:

from sqlo import Q
# Enable debug mode globally
Q.set_debug(True)
query = Q.select("*").from_("users").where("id", 1)
sql, params = query.build()
# Automatically prints:
# [sqlo DEBUG] SELECT * FROM `users` WHERE `id` = %s
# [sqlo DEBUG] Params: (1,)
# Disable debug mode
Q.set_debug(False)

Query Debug Mode

You can also enable debug mode for a specific query:

from sqlo import Q
query = Q.select("*").from_("users").where("id", 1).debug()
sql, params = query.build(
# Prints debug output for this query only
# [sqlo DEBUG] SELECT * FROM `users` WHERE `id` = %s
# [sqlo DEBUG] Params: (1,)

JSON Field Support

Query JSON columns with ease:

from sqlo import Q, JSON

# Extract JSON fields in SELECT
query = Q.select("id", JSON("data").extract("name").as_("name")).from_("users")
# SQL: SELECT `id`, `data`->>'$.name' AS `name` FROM `users`

# Filter by JSON fields
query = Q.select("*").from_("users").where(JSON("data").extract("age"), 18, ">")
# SQL: SELECT * FROM `users` WHERE `data`->>'$.age' > %s

Batch Updates

Efficiently update multiple rows with different values:

values = [
    {"id": 1, "name": "Alice", "status": "active"},
    {"id": 2, "name": "Bob", "status": "inactive"},
]
query = Q.update("users").batch_update(values, key="id")
# Generates optimized CASE WHEN SQL

Common Table Expressions (CTE)

Build complex queries with CTEs:

from sqlo import Q, func

# Define a CTE
cte = Q.select("user_id", func.count("*").as_("order_count")) \
    .from_("orders") \
    .group_by("user_id") \
    .as_("user_orders")

# Use it in main query
query = Q.select("u.name", "uo.order_count") \
    .with_(cte) \
    .from_("users", alias="u") \
    .join("user_orders uo", "u.id = uo.user_id")

Window Functions

Perform advanced analytics with window functions:

from sqlo import Q, Window, func

# Ranking within partitions
query = Q.select(
    "name",
    "department",
    "salary",
    func.row_number().over(
        Window.partition_by("department").and_order_by("-salary")
    ).as_("rank")
).from_("employees")
# SQL: SELECT `name`, `department`, `salary`,
#   ROW_NUMBER() OVER (PARTITION BY `department` ORDER BY `salary` DESC) AS `rank`
# FROM `employees`

# Running totals
query = Q.select(
    "date",
    "amount",
    func.sum("amount").over(
        Window.order_by("date").rows_between("UNBOUNDED PRECEDING", "CURRENT ROW")
    ).as_("running_total")
).from_("transactions")

# LAG and LEAD for time series
query = Q.select(
    "date",
    "value",
    func.lag("value", 1).over(Window.order_by("date")).as_("prev_value"),
    func.lead("value", 1).over(Window.order_by("date")).as_("next_value")
).from_("metrics")

Documentation

Full documentation is available at https://nan-guo.github.io/sqlo/.

You can also browse the markdown files on GitHub:

• Getting Started
• Security Guide
• SELECT Queries
• INSERT Queries
• UPDATE Queries
• DELETE Queries
• JOIN Operations
• Condition Objects
• Expressions & Functions

License

MIT License