queries

`queries` Package

The queries package is designed for loading and executing queries against databases. It provides functionality to load queries from files or strings and execute them asynchronously or synchronously. Additionally, it includes utilities for comparing dataframes.

queries.query.Query

This module is responsible for:

Loading queries from files, directories or strings.
Executing queries against databases.
Handling both synchronous and asynchronous query execution.

We recommend using the Queries class instead of the Query class, as it provides additional functionality for managing multiple queries.

queries.query.Queries

This module is responsible for:

Managing multiple queries.
Executing multiple queries asynchronously or synchronously.
Reordering queries based on a provided list of query names.
Indexing and slicing queries.
Loading queries from files or strings.
loading queries from directories.
Executing queries against databases.
Handling both synchronous and asynchronous query execution.
Loading multiple environments and executing them against databases.

To load queries from a file or from a string you need to follow the specific structure defined in queries.query.get_format

queries.compare.get_differences

This method is responsible for:

Comparing two dataframes to identify differences.

Installation

To install the queries package, add the following line to your pyproject.toml file (edit paths as needed):

[tool.poetry.dependencies]
python = "^3.12"
queries = {path = "<path to the queries package>", develop = true}

After you specify the package in your pyproject.toml file, you can install it by running:

poetry install

Clickhouse and Postgres connections need environment variables to be set.

Setting the environment variables can be done:

implicitly by loading environment variables into the shell or
explicitly by setting them in the code using the configuration file.

implicitly

The package expects the following environment variables:

for clickhouse connection clickhouse:
- CLICKHOUSE_HOST
- CLICKHOUSE_PORT
- CLICKHOUSE_USER
- CLICKHOUSE_PASSWORD
- CLICKHOUSE_DATABASE
for postgres connection postgres:
- POSTGRES_HOST
- POSTGRES_PORT
- POSTGRES_USER
- POSTGRES_PASSWORD
- POSTGRES_DATABASE

explicitly

You just need to follow the following structure <path_to_environments_file.json>:

{
    "<environment_name_for_clickhouse>": {
        "CLICKHOUSE_HOST": "<host>",
        "CLICKHOUSE_PORT": "<port>",
        "CLICKHOUSE_USER": "<username>",
        "CLICKHOUSE_PASSWORD": "<password>",
        "CLICKHOUSE_DATABASE": "<database>",
    },
    "<environment_name_for_postgres>": {
        "POSTGRES_HOST": "<host url>",
        "POSTGRES_PORT": "<port>",
        "POSTGRES_USER": "<username>",
        "POSTGRES_PASSWORD": "<password>",
        "POSTGRES_DATABASE": "<database>"
    }
}

After you save the file, you can load the environments using the Queries object:

from queries.query import Queries

queries = Queries(
    source='<path_to_the_sql_file>',
    env_config_file='<path_to_environments_file.json>'
)

# run on specific environment for example
queries.run_queries(env_name='<environment_name>')

Variables in the configuration files have higher priority than environment variables. If you set the same variable in both the configuration file and the environment, the configuration file will be used.

Don't forget that when calling a query, you need to specify the environment name if you have multiple environments loaded (if not set, environment variables will be used).

Comparing Dataframes

If you want to use methods for comparing dataframes, you can use the compare module. It provides a method for comparing two dataframes and identifying differences between them.

import pandas as pd
from queries.compare import get_differences

a = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8]})
b = pd.DataFrame({'a': [1, 2, 3, 5], 'b': [5, 6, 7, 8]})

print(get_differences(a, b))

View Source

  1"""
  2# `queries` Package
  3
  4The `queries` package is designed for loading and executing queries against
  5databases. It provides functionality to load queries from files or strings and
  6execute them asynchronously or synchronously. Additionally, it includes
  7utilities for comparing dataframes.
  8
  9## queries.query.Query
 10This module is responsible for:
 11- Loading queries from files, directories or strings.
 12- Executing queries against databases.
 13- Handling both **synchronous** and **asynchronous** query execution.
 14
 15> We recommend using the `Queries` class instead of the `Query` class, as it
 16> provides additional functionality for managing multiple queries.
 17
 18## queries.query.Queries
 19This module is responsible for:
 20- Managing multiple queries.
 21- Executing multiple queries asynchronously or synchronously.
 22- Reordering queries based on a provided list of query names.
 23- Indexing and slicing queries.
 24- Loading queries from files or strings.
 25- loading queries from directories.
 26- Executing queries against databases.
 27- Handling both **synchronous** and **asynchronous** query execution.
 28- Loading multiple environments and executing them against databases.
 29
 30> To load queries from a file or from a string you need to follow the
 31> specific structure defined in queries.query.get_format
 32
 33## queries.compare.get_differences
 34This method is responsible for:
 35- Comparing two dataframes to identify differences.
 36
 37## Installation
 38
 39To install the `queries` package, add the following line to your
 40`pyproject.toml` file (edit paths as needed):
 41```toml
 42[tool.poetry.dependencies]
 43python = "^3.12"
 44queries = {path = "<path to the queries package>", develop = true}
 45```
 46
 47After you specify the package in your `pyproject.toml` file, you can install it
 48by running:
 49```bash
 50poetry install
 51```
 52
 53---
 54
 55Clickhouse and Postgres connections need environment variables to be set.
 56
 57Setting the environment variables can be done:
 58+ **implicitly** by loading environment variables into the shell or
 59+ **explicitly** by setting them in the code using the configuration file.
 60
 61## **implicitly**
 62
 63The package expects the following environment variables:
 64- for clickhouse connection **clickhouse**:
 65    - `CLICKHOUSE_HOST`
 66    - `CLICKHOUSE_PORT`
 67    - `CLICKHOUSE_USER`
 68    - `CLICKHOUSE_PASSWORD`
 69    - `CLICKHOUSE_DATABASE`
 70- for postgres connection **postgres**:
 71    - `POSTGRES_HOST`
 72    - `POSTGRES_PORT`
 73    - `POSTGRES_USER`
 74    - `POSTGRES_PASSWORD`
 75    - `POSTGRES_DATABASE`
 76
 77## **explicitly**
 78
 79You just need to follow the following structure
 80`<path_to_environments_file.json>`:
 81```json
 82{
 83    "<environment_name_for_clickhouse>": {
 84        "CLICKHOUSE_HOST": "<host>",
 85        "CLICKHOUSE_PORT": "<port>",
 86        "CLICKHOUSE_USER": "<username>",
 87        "CLICKHOUSE_PASSWORD": "<password>",
 88        "CLICKHOUSE_DATABASE": "<database>",
 89    },
 90    "<environment_name_for_postgres>": {
 91        "POSTGRES_HOST": "<host url>",
 92        "POSTGRES_PORT": "<port>",
 93        "POSTGRES_USER": "<username>",
 94        "POSTGRES_PASSWORD": "<password>",
 95        "POSTGRES_DATABASE": "<database>"
 96    }
 97}
 98```
 99
100After you save the file, you can load the environments using the `Queries` object:
101```python
102from queries.query import Queries
103
104queries = Queries(
105    source='<path_to_the_sql_file>',
106    env_config_file='<path_to_environments_file.json>'
107)
108
109# run on specific environment for example
110queries.run_queries(env_name='<environment_name>')
111```
112
113Variables in the configuration files have higher priority than environment
114variables. If you set the same variable in both the configuration file and the
115environment, the configuration file will be used.
116
117> Don't forget that when calling a query, you need to specify the environment
118> name if you have multiple environments loaded (if not set, environment
119> variables will be used).
120
121## Comparing Dataframes
122
123If you want to use methods for comparing dataframes, you can use the `compare`
124module. It provides a method for comparing two dataframes and identifying
125differences between them.
126
127```python
128import pandas as pd
129from queries.compare import get_differences
130
131a = pd.DataFrame({'a': [1, 2, 3, 4], 'b': [5, 6, 7, 8]})
132b = pd.DataFrame({'a': [1, 2, 3, 5], 'b': [5, 6, 7, 8]})
133
134print(get_differences(a, b))
135```
136"""