SqlQueryBuilder

Wraps string building of MS SQL queries. It provides first level validation.

Current features

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

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

(Find it directly in the module example.py)

Compatible versions

Python 3.10 and above.

Dependencies

(None)

Changelog

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