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
2. Using:
   • Select (with DISTINCT and TOP)
   • Update
   • From
   • InnerJoin, LeftJoin, RightJoin, FullJoin, CrossJoin
   • Where
   • OrderBy (with OFFSET/FETCH paging)
3. Handling automatically:
   • Table aliases
   • Field aliases

Not implemented (yet)

In planned order:

• Proper conditions: typed values, AND/OR/NOT, IN, BETWEEN, LIKE, IS NULL
• Insert and Delete
• Functions (aggregates and others), GROUP BY, HAVING, CASE
• Compound queries (UNION, INTERSECT, EXCEPT) and sub-queries
• Parameterized 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
where = Where(accounts_table.fields.accountId, "> 20000 AND", 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

(Find it directly in the module example.py)

Compatible versions

Python 3.10 and above.

Dependencies

(None)

Changelog

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