Metadata-Version: 2.4
Name: sql_querystring_builder
Version: 0.3.0
Summary: Wraps string building of MS SQL queries
Author-email: Wael GRIBAA <g.wael@outlook.fr>
License-Expression: GPL-3.0-only
Keywords: sql,query,string
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
Wraps string building of MS SQL queries.
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] 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
2. Using:
    - [x] Select (with DISTINCT and TOP)
    - [x] Update (with typed values)
    - [x] From
    - [x] InnerJoin, LeftJoin, RightJoin, FullJoin (on two fields or on any condition), CrossJoin
    - [x] Where (with conditions)
    - [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) and returns the values

## Not implemented (yet)
In planned order:
- [ ] Insert and Delete
- [ ] Functions (aggregates and others), GROUP BY, HAVING, CASE
- [ ] Compound queries (UNION, INTERSECT, EXCEPT) and sub-queries

## 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.
```

(Find it directly in the module example.py)

## Compatible versions
Python 3.10 and above.

## Dependencies
(None)

## Changelog
### 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
