Framework for testing SQL queries
Project description
sql-test-kit
This is a framework for testing SQL queries. It works by directly running the queries against the targeted engine, thus being robust to any change in the corresponding SQL dialect. Moreover, it is currently focused on interpolating test data directly inside the SQL queries, making the test much quicker than if it were creating temporary tables.
Application example
Using the Table and Column class, you can generate variablized SQL queries such as this :
sales_amount_col = "SALES_AMOUNT"
sales_date_col = "SALES_DATE"
sales_table = BigqueryTable(
project="project",
dataset="dataset",
table="table",
columns=[
Column(sales_amount_col, "FLOAT64"),
Column(sales_date_col, "STRING"),
],
)
current_year_sales_by_day_query = f"""
SELECT {sales_date_col}, SUM({sales_amount_col}) as {sales_amount_col}
FROM {sales_table}
WHERE {sales_date_col} >= "2023-01-01"
GROUP BY {sales_date_col}
"""
You can then test it this way :
def test_current_year_sales_by_day_query():
# Given
sales_data = pd.DataFrame(
{
"SALES_ID": [1, 2, 3, 4],
sales_date_col: ["2022-12-31", "2023-01-01", "2023-01-01", "2023-01-02"],
sales_amount_col: [10, 20, 30, 40],
}
)
# When
interpolated_query = QueryInterpolator() \
.add_input_table(sales_table, sales_data) \
.interpolate_query(current_year_sales_by_day_query)
current_year_sales_by_day_data = Client().query(interpolated_query).to_dataframe()
# Then
expected_current_year_sales_by_day_data = pd.DataFrame(
{
sales_date_col: ["2023-01-01", "2023-01-02"],
sales_amount_col: [50, 40],
}
)
pd.testing.assert_frame_equal(
current_year_sales_by_day_data,
expected_current_year_sales_by_day_data,
check_dtype=False,
)
Project details
Release history Release notifications | RSS feed
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
sql_test_kit-0.3.1.tar.gz
(8.0 kB
view hashes)
Built Distribution
Close
Hashes for sql_test_kit-0.3.1-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 1ffb895b559b9354cc299d20e2e821d32c20da366f652f3dc13cbde59a95040e |
|
MD5 | e8ee01c5742507332c889c99df9d0020 |
|
BLAKE2b-256 | 3128cc89ebf3f7d0bc3411c4f73b154c37b483e295b976e795b8b9a8fdeaf39c |