Metadata-Version: 2.4
Name: sql_querystring_builder
Version: 0.7.0
Summary: Builds SQL query strings for SQL Server, PostgreSQL and MySQL
Author-email: Wael GRIBAA <g.wael@outlook.fr>
License-Expression: GPL-3.0-only
Keywords: sql,query,string,tsql,sql server,postgresql,mysql
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Operating System :: OS Independent
Classifier: Topic :: Database
Classifier: Typing :: Typed
Requires-Python: >=3.10
Description-Content-Type: text/markdown
License-File: LICENSE
Dynamic: license-file

# SqlQueryBuilder
Builds SQL query strings for SQL Server (T-SQL, the default), PostgreSQL and MySQL.
It provides first level validation.

## Current features
1. Provides a string building facility for:
    - [x] Table
    - [x] Temporary Table
    - [x] Field
    - [x] Function on field
    - [x] String
    - [x] Functions: Count, Sum, Avg, Min, Max, Coalesce, Cast, Upper, Lower, Len, GetDate, and any other
    - [x] CASE expressions
    - [x] Conditions: eq, ne, lt, le, gt, ge, like, in_, between, is_null, is_not_null, combined with & | ~
    - [x] Values: text, numbers, booleans, None, dates and times, bytes, UUIDs, and Raw SQL
    - [x] Sub-queries, derived tables and CTEs
2. Using:
    - [x] Select (with DISTINCT and TOP)
    - [x] Update (with typed values)
    - [x] Insert (multi-row VALUES, or from a SELECT)
    - [x] Delete (with joins; refused without a WHERE unless asked)
    - [x] With (CTEs)
    - [x] Union, UnionAll, Intersect, Except
    - [x] From
    - [x] InnerJoin, LeftJoin, RightJoin, FullJoin (on two fields or on any condition), CrossJoin
    - [x] Where (with conditions)
    - [x] GroupBy and Having
    - [x] OrderBy (with OFFSET/FETCH paging)
3. Handling automatically:
    - [x] Table aliases
    - [x] Field aliases
    - [x] Escaped values: 'O''Brien', N'café'
    - [x] Parameterized queries: build_with_params() writes ? (pyodbc) or %s (pymssql, psycopg, MySQL drivers) and returns the values
    - [x] Sub-queries indented by their depth
    - [x] Dialects: build(dialect=PostgreSql()) or MySql(); T-SQL by default; a Dialect subclass adds another engine

## Not implemented (yet)
Planned for 1.0, with its breaking changes batched:
- [ ] Field names and aliases validated, and reserved words bracketed automatically
- [ ] A review of the legacy APIs: FunctionOnField, raw strings in Where, plain strings as raw SQL in Update

## What it does not do
    Connect to a database server and run the queries by itself.
    It gives you the query as a string, it's then up to you to pass it to a SQL engine.

## Example
1. Define the elements of your query:
```
# Defines a Table for customers
customers_table = Table("Customers").add("id", "customerId", "mainPhone")
# Defines a Table for accounts
accounts_table = Table("Accounts", "acc").add("id", "customerId", "accountId")
# Selects the fields
select = Select(customers_table, accounts_table.fields.accountId)
# Sets an alias for an existing field
customers_table.fields.customerId.set_alias("cid")
# Specifies the table to take as a FROM
from_ = From(customers_table)
# Specifies a temporary table for the select
atemptable = select.to_temptable("a_temp_table")
# Defines the InnerJoin
innerjoin = InnerJoin(accounts_table, accounts_table.fields.customerId, customers_table)
# Defines an Order By a defined field
orderby = OrderBy(customers_table.fields.customerId, descending=True)
# Adds a Where filter: conditions are methods of fields, combined with & (and_), | (or_) and ~ (not_)
where = Where(accounts_table.fields.accountId.gt(20000) & customers_table.fields.customerId.like("A1C%"))
```
2. Inspect what those elements are made of:
```
# See what the object Select looks like
print(select)
# > Select(fields=[Field(name='id', alias='None', table='Customers'),
# ...    Field(name='customerId', alias='cid', table='Customers'),
# ...    Field(name='mainPhone', alias='None', table='Customers'),
# ...    Field(name='accountId', alias='None', table='Accounts')])

# Initializes the QueryBuilder
query_builder = QueryBuilder(select, where, orderby, innerjoin, from_)
```
3. Build your query:
```
# Builds the query
query_str: str = query_builder.build()
print(query_str)
# > SELECT Customers.id, Customers.customerId AS cid, Customers.mainPhone, acc.accountId
# > INTO #a_temp_table
# > FROM Customers
# > INNER JOIN Accounts acc on acc.customerId = Customers.customerId
# > WHERE acc.accountId > 20000 AND Customers.customerId LIKE 'A1C%'
# > ORDER BY cid DESC
```
4. Manipulate temporary tables whose components are inferred:
```
# Is able to infer fields from temporary tables and use it as with a regular table.
temp_select = Select(atemptable)
print(temp_select)
# > Select(fields=[Field(name='id', alias='None', table='#a_temp_table'),
# ...    Field(name='cid', alias='None', table='#a_temp_table'),
# ...    Field(name='mainPhone', alias='None', table='#a_temp_table'),
# ...    Field(name='accountId', alias='None', table='#a_temp_table')])

# Builds the query from the temporary table
print(QueryBuilder(
        temp_select,
        From(atemptable)
    ).build())
# > SELECT #a_temp_table.id, #a_temp_table.cid, #a_temp_table.mainPhone, #a_temp_table.accountId
# > FROM #a_temp_table
```
5. Send the values as parameters, so that they never enter the SQL text:
```
# Builds the same query with a placeholder for each value, and returns the values in order
sql, params = query_builder.build_with_params()
print(sql)
# > SELECT Customers.id, Customers.customerId AS cid, Customers.mainPhone, acc.accountId
# > INTO #a_temp_table
# > FROM Customers
# > INNER JOIN Accounts acc on acc.customerId = Customers.customerId
# > WHERE acc.accountId > ? AND Customers.customerId LIKE ?
# > ORDER BY cid DESC
print(params)
# > (20000, 'A1C%')
# With pyodbc: cursor.execute(sql, params). For pymssql, build_with_params("format") writes %s instead.
```
6. Insert and delete rows:
```
# Inserts rows: one tuple per row, with the values in column order
insert = Insert(customers_table.fields.customerId, customers_table.fields.mainPhone).values(
    ("A1C42", "555-0100"), ("B7", None)
)
print(QueryBuilder(insert).build())
# > INSERT INTO Customers (customerId, mainPhone)
# > VALUES ('A1C42', '555-0100'), ('B7', NULL)

# Deletes the accounts of customer B7; a DELETE needs a WHERE, unless built with where_required=False
delete = QueryBuilder(Delete(accounts_table), From(accounts_table), Where(accounts_table.fields.customerId.eq("B7")))
print(delete.build())
# > DELETE acc
# > FROM Accounts acc
# > WHERE acc.customerId = 'B7'
```
7. Aggregate rows with functions, GROUP BY and HAVING:
```
# Counts the accounts of each customer, keeping the customers that have more than 2
accounts_count = Count(alias="accounts")
per_customer = QueryBuilder(
    Select(customers_table.fields.id, accounts_count),
    From(customers_table),
    InnerJoin(accounts_table, accounts_table.fields.customerId, customers_table),
    GroupBy(customers_table.fields.id),
    Having(accounts_count.gt(2)),
    OrderBy(accounts_count, descending=True),
)
print(per_customer.build())
# > SELECT Customers.id, COUNT(*) AS accounts
# > FROM Customers
# > INNER JOIN Accounts acc on acc.customerId = Customers.customerId
# > GROUP BY Customers.id
# > HAVING COUNT(*) > 2
# > ORDER BY accounts DESC
# With build_with_params(), write constants inside grouped expressions with Raw: Coalesce(f, Raw("0"))
```
8. Nest queries: sub-queries, CTEs, derived tables and UNION:
```
# Finds the customers with an account above 20000, through a sub-query
big_accounts = QueryBuilder(
    Select(accounts_table.fields.customerId), From(accounts_table), Where(accounts_table.fields.accountId.gt(20000))
)
print(QueryBuilder(
    Select(customers_table.fields.id), From(customers_table), Where(customers_table.fields.customerId.in_(big_accounts))
).build())
# > SELECT Customers.id
# > FROM Customers
# > WHERE Customers.customerId IN (
# >     SELECT acc.customerId
# >     FROM Accounts acc
# >     WHERE acc.accountId > 20000
# > )

# Counts the accounts again, as a CTE: query.as_cte() gives a table, declared by With
counts = QueryBuilder(
    Select(accounts_table.fields.customerId, Count(alias="accounts")),
    From(accounts_table),
    GroupBy(accounts_table.fields.customerId),
).as_cte("counts")
print(QueryBuilder(With(counts), Select(counts), From(counts), Where(counts.fields.accounts.gt(2))).build())
# > WITH counts AS (
# >     SELECT acc.customerId, COUNT(*) AS accounts
# >     FROM Accounts acc
# >     GROUP BY acc.customerId
# > )
# > SELECT counts.customerId, counts.accounts
# > FROM counts
# > WHERE counts.accounts > 2
# query.as_table("t") gives a derived table instead; Union, UnionAll, Intersect and Except combine queries
```
9. Build the same query for PostgreSQL or MySQL:
```
# The dialect is an argument of build(); T-SQL is the default, as in every step above
top_customers = QueryBuilder(
    Select(customers_table.fields.id, top=3),
    From(customers_table),
    Where(customers_table.fields.customerId.like("A1C%")),
    OrderBy(customers_table.fields.id),
)
print(top_customers.build(dialect=PostgreSql()))
# > SELECT Customers.id
# > FROM Customers
# > WHERE Customers.customerId LIKE 'A1C%'
# > ORDER BY Customers.id
# > LIMIT 3
sql, params = top_customers.build_with_params(dialect=MySql())  # the MySQL drivers take %s placeholders
print(sql)
# > SELECT Customers.id
# > FROM Customers
# > WHERE Customers.customerId LIKE %s
# > ORDER BY Customers.id
# > LIMIT 3
print(params)
# > ('A1C%',)
# QueryBuilder(..., dialect=MySql()) remembers its dialect; a Dialect subclass adds another engine
```

(Find it directly in the module example.py)

## Compatible versions
Python 3.10 and above.

## Dependencies
(None)

## Changelog
### 0.7.0
Queries built with 0.6.0 produce the same SQL; see "Breaking" for the one change.
- Dialects: `build(dialect=PostgreSql())`, `build_with_params(dialect=MySql())`, or
  `QueryBuilder(..., dialect=...)` for a query that remembers its dialect. T-SQL stays the default.
  - PostgreSQL writes `LIMIT` and `OFFSET`, `"quoted"` names for bracketed parts, `TRUE` and `FALSE`,
    `'2026-10-11'` dates, `'\x01ff'::bytea`, `NOW()`, `CHAR_LENGTH`, `INTO TEMP`, and UPDATE … FROM or
    DELETE … USING with the join conditions in WHERE (inner joins only).
  - MySQL writes `` `quoted` `` names, doubles backslashes in strings, `X'01FF'`,
    `CREATE TEMPORARY TABLE … AS`, and the joins between UPDATE and SET.
  - Plain names stay as written in every dialect; PostgreSQL folds them to lower case, so write
    `[Customers]` for a case-sensitive name there.
- `build_with_params()` defaults to the dialect's placeholder style: `?` for T-SQL, `%s` for PostgreSQL and
  MySQL. `style=` still overrides it.
- `Dialect`, the base class: a subclass that overrides what differs adds another engine.
- `DialectException` for what a dialect cannot write: `FullJoin` in MySQL, `.offset()` without `fetch()`
  in MySQL, an outer join in a PostgreSQL UPDATE or DELETE, a `From` naming another table than the
  statement's, and two dialects in one query.
- New top-level names: `Dialect`, `TSql`, `PostgreSql`, `MySql` and `DialectException`.

Breaking:
- The INSERT row limit is a dialect rule (1000 rows for T-SQL, none for PostgreSQL and MySQL), checked when
  the query builds: `Insert.values()` no longer raises `ValueError` at the 1001st row, and `build()` raises
  `DialectException` for T-SQL instead.
- Modules moved, by responsibility. Importing every name from the package top level,
  `from sql_querystring_builder import ...`, is unaffected; the submodule paths changed as follows:
  - every exception lives in `exceptions`; `NonExistingAliasException`, never raised, is removed;
  - `Field`, `Aliasable`, `FunctionOnField` and `AliasableString` live in `fields`, not `tables`;
  - `validate_name` lives in `names`, not `tables`;
  - `clauses` is a package: `sql_querystring_builder.clauses` still exports every clause, and `Clause` lives
    in `clauses.base` (`abstract_clause` is gone);
  - `Query` lives in `query` (`abstract_query` is gone), and `Union`, `UnionAll`, `Intersect` and `Except`
    in `compound`, not `query_builder`;
  - `parameterize` lives in `parameters`, not `values`.

### 0.6.0
Queries built with 0.5.0 produce the same SQL; see "Breaking" for the two kinds now refused.
- Sub-queries: `f.in_(query)`, `Exists(query)` (and `~Exists(query)` for NOT EXISTS), comparisons with a
  query (`f.gt(query)`), queries as `Update` values and function arguments, and
  `SubQuery(query, alias="n")` in a SELECT. A nested query is written in parentheses, one clause per line,
  indented four spaces per level of nesting. Correlated sub-queries work as written.
- Queries as tables: `query.as_table("t")` gives a derived table, and `query.as_cte("t")` a CTE declared
  by `With(...)`. Their fields are named after the query's SELECT list.
- `Union`, `UnionAll`, `Intersect` and `Except`. To sort one, select from it as a derived table.
- `Insert(...).from_select(query)` for `INSERT … SELECT`. An Insert may share its query with a `With`.
- Checks when a query builds: ORDER BY in a sub-query needs TOP or OFFSET; a nested query cannot hold
  INTO or WITH; a sub-query in `in_()` or a comparison selects one column; the queries of a compound
  select as many columns each; a CTE needs its `With`.
- New top-level names: `Query` (the base of `QueryBuilder` and the compound queries), `SubQuery`,
  `Exists`, `DerivedTable`, `Cte`, `With`, `Union`, `UnionAll`, `Intersect` and `Except`.

Breaking:
- A query whose SELECT lists fields but that has no FROM, and a query with a join but no FROM, raise
  `IncompatibleClausesException` instead of building SQL that SQL Server refuses. Inside a sub-query, a
  SELECT without FROM stays allowed, as T-SQL allows it there.

### 0.5.0
Queries built with 0.4.0 produce the same SQL; see "Breaking" for the one change.
- Functions: `Count()` (`COUNT(*)`), `Count(f, distinct=True)`, `Sum`, `Avg`, `Min`, `Max`, `Coalesce`,
  `Cast(f, "DECIMAL(10, 2)")`, `Upper`, `Lower`, `Len` and `GetDate()`, and `Function("ROUND", f, 2)` for
  any other. Each takes `alias=` and goes wherever a field goes: SELECT, ORDER BY, conditions, UPDATE
  values, other functions' arguments. Arguments are escaped like condition values. Function names are
  validated like table names, and `Cast` types must look like types.
- `Case(alias="size").when(condition, value).else_(value)` renders `CASE WHEN … THEN … ELSE … END`.
- `GroupBy(*expressions)` and `Having(condition)`. GROUP BY writes qualified names, never aliases, which
  T-SQL refuses there. With `build_with_params()`, write constants inside grouped expressions with `Raw`:
  SQL Server cannot match two parameters as the same expression.
- An Insert row accepts functions that read no column, such as `GetDate()`.
- A temporary table made from a function or CASE without alias raises `ValueError`: `SELECT … INTO`
  needs a name for every column.

Breaking:
- `OrderBy(...).place` is 7 instead of 5, to make room for GROUP BY (5) and HAVING (6). Only code that
  reads `.place`, or orders its own clauses by these numbers, is affected.

### 0.4.0
Queries built with 0.3.0 produce the same SQL.
- `Insert(*columns).values(*rows)` renders `INSERT INTO Customers (id, name)` and
  `VALUES (1, 'Ann'), (2, 'O''Brien')`. The columns are fields of one table, or a whole table. Each row is a
  tuple of values in column order, with at most 1000 rows per INSERT, as in T-SQL. Values are escaped as in
  conditions, plain strings included, and `Raw("DEFAULT")` writes SQL. An Insert stands alone in its query.
- `Delete(table)` renders `DELETE` and the table's alias, or its name, and works with `From`, joins and
  `Where`: `DELETE acc FROM Accounts acc INNER JOIN ...`. A query with a DELETE but no WHERE raises
  `IncompatibleClausesException`, unless written `Delete(table, where_required=False)`; so does a DELETE with
  ORDER BY.
- Both work with `build_with_params()`. SQL Server accepts about 2,100 parameters per query, so split big
  parameterized inserts.

### 0.3.0
Queries built with 0.2.0 produce the same SQL, except for the fixed `Update` values below.
- Conditions are methods of fields, functions on fields and strings: `eq`, `ne`, `lt`, `le`, `gt`, `ge`,
  `like`, `in_`, `between`, `is_null` and `is_not_null`; `eq(None)` renders `IS NULL`. Combine them with
  `&`, `|` and `~`, or with `and_()`, `or_()` and `not_()`; parentheses are added where T-SQL needs them.
  Python's `and`, `or` and `not` raise `TypeError`, since `c1 and c2` would silently keep only `c2`.
- Values are escaped: text becomes `'O''Brien'`, or `N'café'` when it is not ASCII (`NStr("...")` forces
  `N'...'`). Numbers, `True`/`False` (1/0), `None` (`NULL`), dates (`'20261011'`), datetimes and times
  (ISO), bytes (`0x...`) and UUIDs are supported; `Raw("GETDATE()")` is written verbatim.
- `Where(condition)`, also next to raw strings, and `on=condition` on every join, for multi-column joins.
- `QueryBuilder.build_with_params()` returns the SQL with `?` placeholders and the values, for
  `cursor.execute(sql, params)` with pyodbc; `build_with_params("format")` writes `%s` for pymssql.
- `Update` values: a plain string is still raw SQL (`{f: "'x'"}`), `Str("O'Brien")` is an escaped string,
  and other values render as in conditions.
- New top-level names: `Condition`, `Raw`, `Str` and `NStr`.

Fixed:
- `Update` values `None`, `True`/`False`, dates and fields rendered invalid or wrong SQL: `f=None`,
  `f=True`, `f=2026-10-11` (a subtraction) or the field's description. They now render `NULL`, `1`/`0`,
  `'20261011'` and the field's qualified name. A `Decimal` with an exponent is written in full (`1000`, not
  `1E+3`, which T-SQL reads as a float).

Breaking:
- `Update` values of an unsupported type raise `TypeError` instead of being written with `str()`.

### 0.2.0
Queries built with 0.1.x produce the same SQL; see "Breaking" for the few behavior changes.
- `LeftJoin`, `RightJoin` and `FullJoin` (same arguments as `InnerJoin`), and `CrossJoin(table)`.
- `Select(..., distinct=True, top=10)` renders `SELECT DISTINCT TOP (10) ...`.
- `OrderBy(...).offset(20).fetch(10)` adds `OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY`. Combining paging with
  TOP raises `IncompatibleClausesException`.
- Every exception derives from `SqlQueryBuilderError` and can be imported from `sql_querystring_builder`.
- `table.fields["name"]`, `"name" in table.fields`, iteration and `len()`. `table.fields["add"]` also
  reaches fields whose names clash with `Fields` methods.
- Table names accept bracketed parts anywhere: `[Order]`, `dbo.[My Table]` (`]]` stands for `]`).
- The README example uses `>` instead of `gt`, which is not a T-SQL operator.

Breaking:
- `TempTable(3)` raises `TypeError` instead of a bare `Exception` (same message).
- Unknown keyword arguments to `Table`, such as `fields=[...]`, raise `TypeError` instead of being ignored.
- Malformed bracketed names such as `dbo.Order]` or `dbo.[Order` are rejected.
- An empty `table.fields` is now falsy.

### 0.1.1
No change to the generated SQL.
- The package ships a `py.typed` marker, so type checkers such as mypy now use its type hints.
- PyPI classifiers for the supported Python versions (3.10 to 3.14), typing and topic.
- The "Not implemented (yet)" list shows the planned features in order; typo fixes.

### 0.1.0
This release changes the generated SQL: check your queries when upgrading.
- WHERE conditions and the right side of `INNER JOIN ... on` use the field's qualified name (`t1.KeyId`)
  instead of its SELECT alias, which SQL Server only accepts in ORDER BY. ORDER BY still uses the alias.
- `UPDATE ... SET` no longer writes `AS alias` after aliased fields (`SET acc.a=1`).
- Temporary tables made with `Select.to_temptable()` name their columns after the field aliases, as
  `SELECT ... INTO` does.
- Adding the same field name twice in one call (`Table("t").add("a", "a")`), including duplicate
  temporary-table columns, raises `AlreadyExistingFieldException`.
- An `Update` mixing two tables with the same name but different aliases raises `IncorrectUpdateException`.
- `NonExistingFieldException` is now an `AttributeError`: `hasattr()` and `getattr(..., default)` work on
  `table.fields`, and tables can be copied and pickled.
- Requires Python 3.10 or later (was 3.7).
- The license metadata uses the SPDX expression `GPL-3.0-only`.

## Authors
Wael GRIBAA
Contact: g.wael@outlook.fr
Made in September 2024
