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
   • Update
   • From
   • InnerJoin
   • Where
   • OrderBy
3. Handling automatically:
   • Table aliases
   • Field aliases

Not implemented (yet)

• Insert
• Compound (sub-)queries
• Functions
• Proper Conditions

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 yopu 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 takes 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, "gt 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 gt 20000 AND Customers.customerId LIKE 'A1C%'
# > ORDER BY cid DESC

4. Manipulate temporary tables which 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.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