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
   • 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
2. Using:
   • Select (with DISTINCT and TOP)
   • Update (with typed values)
   • Insert (multi-row VALUES)
   • Delete (with joins; refused without a WHERE unless asked)
   • From
   • InnerJoin, LeftJoin, RightJoin, FullJoin (on two fields or on any condition), CrossJoin
   • Where (with conditions)
   • 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

Not implemented (yet)

In planned order:

• 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.

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'

(Find it directly in the module example.py)

Compatible versions

Python 3.10 and above.

Dependencies

(None)

Changelog

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