sqlym 0.3.0

SQL-first database access library for Python

sqlym

日本語

A SQL template engine for Python. Inspired by Java's Clione-SQL / Doma2, it provides a 2-way SQL parser and row-to-object mapping.

• SQL-first — Write SQL directly, not through an ORM. sqlym never auto-generates SQL
• 2-way SQL — SQL files remain directly executable by DB tools
• Zero dependencies — Core runs on the Python standard library only (Pydantic is optional)
• Flexible mapping — Auto-mapping for dataclass / Pydantic, or bring your own function

Quick Start

pip install sqlym

1. Define an Entity

from dataclasses import dataclass
from typing import Annotated
from sqlym import Column

@dataclass
class Employee:
    id: int
    name: Annotated[str, Column("EMP_NAME")]  # when column name differs
    dept_id: int | None = None

2. Write a SQL File

sql/employee/find_by_dept.sql:

SELECT
    id,
    EMP_NAME,
    dept_id
FROM
    employee
WHERE
    id = /* $id */0
    AND dept_id = /* $dept_id */1
    AND status = /* $status */'active'

3. Query with Sqlym

import sqlite3
from sqlym import Sqlym

# Connect to database
conn = sqlite3.connect("example.db")

# Create Sqlym instance
db = Sqlym(conn, sql_dir="sql")

# Query with parameters (lines with None are automatically removed)
employees = db.query(Employee, "employee/find_by_dept.sql", {
    "id": 100,
    "dept_id": None,  # this line is removed
    "status": "active",
})

for emp in employees:
    print(emp.name)

# Get a single record
employee = db.query_one(Employee, "employee/find_by_id.sql", {"id": 100})

# Execute INSERT/UPDATE/DELETE
affected = db.execute("employee/update.sql", {"id": 100, "status": "inactive"})
conn.commit()

For the full SQL syntax reference, see SQL Syntax.

Features

2-way SQL (Clione-SQL Style)

Parameters are written as SQL comments. The SQL file can be executed directly by DB tools.

-- None removes the line ($ prefix)
WHERE name = /* $name */'default'

-- None binds as NULL (no $ prefix)
WHERE name = /* name */'default'

Indent-based Parent-Child Relationships

When all children are removed, the parent is also removed.

WHERE
    id = /* $id */0
    AND (
        status = /* $status1 */'a'
        OR status = /* $status2 */'b'
    )
-- If both status1 and status2 are None, the entire parenthesized block is
-- removed

Automatic IN Clause Expansion

WHERE dept_id IN /* $dept_ids */(1, 2, 3)
-- dept_ids=[10,20,30] → WHERE dept_id IN (?, ?, ?)

Error Message Settings

Errors raised during SQL parsing include the line number by default. If you want to hide the SQL snippet from error messages, disable it via config:

from sqlym.config import ERROR_INCLUDE_SQL, ERROR_MESSAGE_LANGUAGE
import sqlym.config as config

config.ERROR_INCLUDE_SQL = False
config.ERROR_MESSAGE_LANGUAGE = "en"

Set ERROR_MESSAGE_LANGUAGE to ja or en.

Mappers

# Auto-mapping (dataclass / Pydantic)
mapper = create_mapper(Employee)

# Manual mapping (when column names differ significantly)
mapper = create_mapper(Employee, mapper=lambda row: Employee(
    id=row['EMP_ID'],
    name=row['EMP_NM'],
    dept_id=row['DEPT_CODE'],
))

Column Name Mapping

from typing import Annotated
from sqlym import Column, entity

@dataclass
class Employee:
    # Per-field mapping
    id: Annotated[int, Column("EMP_ID")]
    name: Annotated[str, Column("EMP_NAME")]

    # No mapping — uses field name as-is
    email: str

# Or apply a naming convention
@entity(naming="snake_to_camel")  # dept_id → deptId
@dataclass
class Employee:
    dept_id: int  # → deptId

RDBMS Support

Supports SQLite, PostgreSQL, MySQL, and Oracle.

RDBMS	Driver	Placeholder	Extras
SQLite	sqlite3 (stdlib)	?	—
PostgreSQL	psycopg 3.1+	%s	sqlym[postgresql]
MySQL	PyMySQL 1.1+	%s	sqlym[mysql]
Oracle	python-oracledb 3.0+	:name	sqlym[oracle]

For RDBMS other than SQLite, install with extras. The driver will be installed automatically.

pip install sqlym[postgresql]

Feature	Description
LIKE escaping	Handles LIKE escape differences across databases
IN clause limit	Splits when exceeding Oracle's 1000-element limit
RDBMS-specific SQL file loading	Fallback: find.oracle.sql → find.sql

When SQL syntax differs across databases, you can provide database-specific SQL files:

sql/employee/
├── find.sql              # Common SQL
├── find.oracle.sql       # Oracle-specific (loaded preferentially)
└── find.postgresql.sql   # PostgreSQL-specific (loaded preferentially)

What sqlym Does Not Provide

sqlym is a SQL template engine. The following features are out of scope. Write SQL directly or combine with other libraries.

• SQL generation (INSERT/UPDATE/DELETE/UPSERT, etc.)
• Pagination SQL generation (LIMIT/OFFSET, ROWNUM, etc.)
• DDL management / migrations
• Connection management / connection pooling
• Transaction management

Acknowledgments

sqlym's 2-way SQL parser is based on the design of Clione-SQL by tauty. The four rules for line-based SQL processing, indent-driven parent-child relationships, and parameter comment syntax all originate from Clione-SQL.

The dialect design and RDBMS-specific behavior handling draw from Doma2 by the Doma Framework team.

We are grateful to both projects for their pioneering work in 2-way SQL.

License

MIT