queries.query

View Source

   1import os
   2import re
   3import copy
   4import asyncio
   5import datetime
   6import warnings
   7import inspect
   8import json
   9
  10import pprint
  11import pandas as pd
  12from dotenv import load_dotenv
  13load_dotenv()
  14
  15from clickhouse_connector.connector import ClickHouseConnector
  16from postgres_connector.connector import PostgresConnector
  17
  18
  19information_to_gather = {
  20    'name': {
  21        'regex': {
  22            'regex': r'\*\s=+\n\s\*\sSQL\sQuery:\s*(.*)\n\s\*\s=+',
  23            'group_index': 0
  24        },
  25        'required': True,
  26        'format': ' * ============\n * SQL Query: <name>\n * '
  27                  '============',
  28        'documentation_name': 'SQL Query',
  29    },
  30    'author': {
  31        'regex': {
  32            'regex': r'\s\*\sAuthor:(.*)',
  33            'group_index': 0
  34        },
  35        'required': True,
  36        'format': ' * Author: <author>',
  37        'documentation_name': 'Author',
  38    },
  39    'date': {
  40        'regex': {
  41            'regex': r'\s\*\sDate:(.*)',
  42            'group_index': 0
  43        },
  44        'required': True,
  45        'format': ' * Date: <date>',
  46        'documentation_name': 'Date'
  47    },
  48    'input_parameters': {
  49        'regex': {
  50            'regex': r'(\s\*\sInput\sParameters:\n\s\*\s-+\n(('
  51                     r'\s\*\s-\s:\w+:\s.*\n?)+))',
  52            'group_index': 1
  53        },
  54        'required': False,
  55        'format': ' * Input parameters: <parameters>',
  56        'documentation_name': 'Input Parameters'
  57    },
  58    'database': {
  59        'regex': {
  60            'regex': r'\s\*\sDatabase:(.*)',
  61            'group_index': 0
  62        },
  63        'required': True,
  64        'format': ' * Database: <database>',
  65        'possible_values': [
  66            'clickhouse',
  67            'postgres'
  68        ],
  69        'documentation_name': 'Database'
  70    },
  71    'output': {
  72        'regex': {
  73            'regex': r'(\s\*\sOutput:\n\s\*\s-+\n(('
  74                     r'\s\*\s-\s\w+:\s.*\n?)+))',
  75            'group_index': 1
  76        },
  77        'required': True,
  78        'documentation_name': 'Output'
  79    },
  80    'depends': {
  81        'regex': {
  82            'regex': r'(\s\*\sDepends:\n\s\*\s-+\n(('
  83                     r'\s\*\s-\s.+:\s.*\n?)+))',
  84            'group_index': 1
  85        },
  86        'required': False,
  87        'documentation_name': 'Depends'
  88    },
  89    # 'group_on': {
  90    #     'regex': {
  91    #         'regex': r'(\s\*\sGroup on:\n\s\*\s-+\n(('
  92    #                  r'\s\*\s-\s.+:\s.*\n?)+))',
  93    #         'group_index': 1
  94    #     },
  95    #     'required': False,
  96    #     'documentation_name': 'Group On'
  97    # },
  98    # other must be the last entry in this dictionary
  99    # because if it is anywhere other than the last entry
 100    # it will match the other entries, and other entires won't be found
 101    'other': {
 102        'regex': {
 103            'regex': r'(\s\* (.+?):\n\s\*\s-+\n((?:(?!^\s*\*\s*\n)\s\*\s.*\n)+))',
 104            'group_index': 2
 105        },
 106        'required': False,
 107        'documentation_name': 'Other'
 108    }
 109}
 110"""
 111This dictionary contains information about the query that can be
 112extracted from the query itself.
 113The dictionary contains the following
 114keys:
 115- `name`: name of the query
 116- `author`: author of the query
 117- `date`: date when the query was created
 118- `input_parameters`: input parameters of the query
 119- `database`: database on which the query is run
 120- `output`: output of the query
 121- `depends`: dependencies of the query
 122- `other`: other information about the query
 123
 124Each key contains a dictionary with the following keys (not all keys are
 125present in all dictionaries):
 126- `regex`: dictionary with the following
 127  keys:
 128  - `regex`: regular expression to extract the information
 129  - `group_index`: index of the group in the regular expression
 130- `required`: boolean value if the parameter is required
 131- `format`: format of the parameter
 132- `possible_values`: list of possible values for the parameter
 133- `documentation_name`: name of the parameter in the documentation
 134"""
 135
 136
 137ignore_types_in_prepare_query = [
 138    'FROM',
 139    'TABLE',
 140    'INTO'
 141]
 142"""
 143List of keywords that need to be before the parameter in the query
 144to ignore the type of the parameter.
 145For example when the parameter is a string, the query should look like:
 146`SELECT * FROM table WHERE column = '<parameter_value>'`
 147If the parameter is a string but it is after one of the keywords in the list,
 148the query should look like:
 149- `SELECT * FROM <parameter_value>` (*without `''`*)
 150- `INSERT INTO <parameter_value>` (*without `''`*)
 151- `CREATE TABLE <parameter_value>` (*without `''`*)
 152
 153**You must use capital letters on these keywords in order for this library 
 154to work.**
 155"""
 156
 157
 158class Query:
 159    def __init__(
 160            self,
 161            query: str,
 162            name: str,
 163            author: str,
 164            database: str,
 165            date: str,
 166            output: dict = None,
 167            input_parameters: dict = None,
 168            depends: dict = None,
 169            other: dict = None
 170            ):
 171        """
 172        This class represents a query that can be run on a database.
 173        The query can be run on ClickHouse or Postgres database.
 174
 175        This class is written for fast execution of queries because of use
 176        of asyncio and threading.
 177
 178        The main idea is that the query is **prepared only once** and then
 179        it is
 180        run on the database. The query can be run synchronously or
 181        asynchronously.
 182
 183        The query can have **input parameters** that are replaced in the query
 184        before running the query.
 185
 186        The query can have **output** that is checked if it matches the
 187        columns in the results.
 188
 189        Other information that can be used in the child classes and use cases
 190        can be set using the `other` parameter.
 191
 192        The query can have **dependencies** on other queries. The
 193        dependencies are
 194        used for other libraries and are not used in this library.
 195
 196        Args:
 197            name (str): name of the query
 198            author (str): author of the query
 199            database (str): database on which the query is run
 200            date (str): date when the query was created
 201            output (dict): output of the query in format: {'<name>': '<description>'}
 202            input_parameters (dict): input parameters of the query (default: None), for example: {'<name>': '<description>'}
 203            depends (dict): dependencies of the query (default: None) in format: {'<name>': <description>}
 204            other (dict): other information about the query (default: None)
 205        """
 206        self._query: str = query
 207        self._original_query: str = query
 208        self._name: str = name
 209        self._author: str = author
 210        self._database: str = database
 211        self._output: dict = output
 212        self._other: dict = other
 213
 214        if self._output is None:
 215            self._output = dict()
 216
 217        self._date: datetime.datetime = datetime.datetime.strptime(
 218            date,
 219            '%Y-%m-%d'
 220        )
 221        self._input_parameters: dict = input_parameters
 222        self._depends: dict = depends
 223
 224        self._flag_prepare_query = False
 225        self._result: pd.DataFrame = None
 226        self._lock = asyncio.Lock()
 227
 228        if self._database not in information_to_gather['database'][
 229            'possible_values']:
 230            raise ValueError(
 231                f'Database has to be one of the following values: '
 232                f'{", ".join(information_to_gather["database"]["possible_values"])},'
 233                f' Got: {self._database}'
 234                )
 235
 236    def number_of_dependent(self) -> int:
 237        """
 238        This method returns the number of dependent queries.
 239
 240        Returns:
 241            int: Number of dependent queries.
 242        """
 243        if self._depends is None:
 244            return 0
 245
 246        return len(self._depends)
 247
 248    def get_dependent(self) -> list[str]:
 249        """
 250        This method returns the dependent queries names.
 251
 252        Will return an empty list if there are no dependent queries.
 253
 254        Returns:
 255            list[str]: List of names of dependent queries.
 256        """
 257        if self._depends is None:
 258            return []
 259
 260        return list(self._depends.keys())
 261
 262    def get_output(self) -> list[str]:
 263        """
 264        This method returns the list of output column names.
 265
 266        Returns:
 267            list[str]: List of output column names .
 268        """
 269        return list(self._output.keys())
 270
 271    def __repr__(self) -> str:
 272        """
 273        This method returns the string representation of the Query object.
 274
 275        Returns:
 276            str: String representation of the Query object.
 277        """
 278        return str(self)
 279
 280    def __str__(self) -> str:
 281        """
 282        This method returns the string representation of the Query object.
 283        Each parameter is represented in the following format:
 284        ```
 285        {
 286            'name': <name>,
 287            'query': <query>,
 288            'prepared_query': <prepared_query>,
 289            'original_query': <original_query>,
 290            'author': <author>,
 291            'database': <database>,
 292            'output': <output>,
 293            'date': <date>,
 294            'input_parameters': <input_parameters>,
 295            'depends': <depends>,
 296            'results': <results>
 297        }
 298        ```
 299
 300        Returns:
 301            str: String representation of the Query object
 302        """
 303        prepared_string_representation = {
 304            'name': self._name,
 305            'query': self._query,
 306            'prepared_query': self._query,
 307            'original_query': self._original_query,
 308            'author': self._author,
 309            'database': self._database,
 310            'output': self._output,
 311            'date': self._date.strftime('%Y-%m-%d'),
 312            'input_parameters': self._input_parameters,
 313            'depends': self._depends,
 314            'results': str(self._result),
 315            'other': self._other
 316        }
 317        return pprint.pformat(prepared_string_representation)
 318
 319    def get_documentation_other(self) -> dict:
 320        """
 321        This method returns the other information about the query from
 322        documentation.
 323
 324        Returns:
 325            dict: Other information about the query
 326        """
 327        return self._other
 328
 329    def _prepare_query(self, **kwargs) -> None:
 330        """
 331        This method prepares the query for running on the database.
 332        The input parameters are replaced in the query through the kwargs.
 333
 334        > Be careful, **each parameter in kwargs must exactly match to the
 335        name used in SQL query in format**: `:<parameter_name>`
 336
 337        This method is called only once before running the query on the
 338        database.
 339        This method raises a ValueError if the input parameters are not
 340        provided.
 341        This method warns if the input parameter is not defined in the query.
 342
 343        This method transforms the input parameters to the correct format
 344        before inserting them into the query.
 345
 346        Args:
 347            **kwargs: input parameters for the query
 348        Returns:
 349            None
 350        """
 351        # Check if the query is already prepared
 352        if self._flag_prepare_query:
 353            return
 354
 355        # Check if the input parameters are provided
 356        # If the input parameters are not provided, the query is already
 357        # prepared
 358        if self._input_parameters is None:
 359            self._flag_prepare_query = True
 360            return
 361
 362        # Check if the input parameters are in the query
 363        for input_name in self._input_parameters.keys():
 364            # Warn if the input parameter is not in the query
 365            if input_name not in self._query:
 366                warnings.warn(f'Parameter {input_name} is not in the query.',
 367                              UserWarning)
 368
 369            # Replace `:` in input parameter names
 370            input_name = input_name.replace(':', '')
 371            # if the input parameter is not in the kwargs, raise ValueError
 372            if input_name not in kwargs:
 373                raise ValueError(f'Parameter {input_name} is required.')
 374            to_insert = kwargs[input_name]
 375
 376            # Check if the input parameter is a string
 377            if isinstance(to_insert, str):
 378                for pre_keyword in ignore_types_in_prepare_query:
 379                    pattern = re.compile(rf'{pre_keyword}\s+:{input_name}')
 380                    if pattern.search(self._query):
 381                        self._query = self._query.replace(
 382                            f':{input_name}',
 383                            to_insert
 384                        )
 385                        continue
 386
 387                to_insert = f"'{to_insert}'"
 388
 389            # Check if the input parameter is a datetime
 390            if isinstance(to_insert, datetime.datetime):
 391                to_insert = f"'{to_insert.strftime('%Y-%m-%d %H:%M:%S')}'"
 392
 393            # check if the input parameter is a list
 394            if isinstance(to_insert, list):
 395                to_insert = str(to_insert)
 396
 397            # replace the input parameter in the query
 398            self._query = self._query.replace(f':{input_name}', to_insert)
 399
 400        # Set the flag that the query is prepared
 401        self._flag_prepare_query = True
 402
 403    def _run_clickhouse_query(self,
 404                              query: str,
 405                              host: str = None,
 406                              port: str = None,
 407                              user: str = None,
 408                              password: str = None,
 409                              database: str = None) -> pd.DataFrame:
 410        """
 411        This method creates a one connection to the clickhouse database,
 412        runs the query and fetches the results.
 413
 414        If the output is not defined, the method will return an empty DataFrame.
 415        If output is empty but the results are fetched, the method will raise a
 416        UserWarning.
 417
 418        Args:
 419            query (str): Query to run on the database.
 420            host (str): Host of the database.
 421            port (str): Port of the database.
 422            user (str): User of the database.
 423            password (str): Password of the database.
 424            database (str): Database name.
 425
 426        Returns:
 427            pd.DataFrame: Results of the query.
 428        """
 429        connector = ClickHouseConnector(
 430            host=host,
 431            port=port,
 432            user=user,
 433            password=password,
 434            database=database
 435        )
 436        connector.connect()
 437
 438        # if length of output is None, run the query and return empty DataFrame
 439        if len(self._output) == 0:
 440            # check if there is a data present as an output
 441            results = connector.run_query(query)
 442
 443            if len(results) != 0:
 444                warnings.warn(
 445                    'Output is not defined, but there is a result.',
 446                    UserWarning
 447                    )
 448            connector.disconnect()
 449            return pd.DataFrame()
 450
 451        results = connector.fetch_dataframe(query)
 452        connector.disconnect()
 453        return results
 454
 455    def _run_postgres_query(self,
 456                            query: str,
 457                            host: str = None,
 458                            port: str = None,
 459                            user: str = None,
 460                            password: str = None,
 461                            database: str = None) -> pd.DataFrame:
 462        """
 463        This method creates a one connection to the postgres database,
 464        runs the query and fetches the results.
 465
 466        Args:
 467            query (str): Query to run on the database.
 468            host (str): Host of the database.
 469            port (str): Port of the database.
 470            user (str): User of the database.
 471            password (str): Password of the database.
 472            database (str): Database name.
 473
 474        Returns:
 475            pd.DataFrame: Results of the query.
 476        """
 477        connector = PostgresConnector(
 478            host=host,
 479            port=port,
 480            user=user,
 481            password=password,
 482            database=database
 483        )
 484        connector.connect()
 485
 486        # if length of output is None, run the query and return empty DataFrame
 487        if len(self._output) == 0:
 488            # check if there is a data present as an output
 489            results = connector.run_query(query)
 490
 491            if len(results) != 0:
 492                warnings.warn('Output is not defined, '
 493                              'but there is a result.',
 494                              UserWarning)
 495
 496            connector.disconnect()
 497            return pd.DataFrame()
 498
 499        results = connector.fetch_dataframe(query)
 500        connector.disconnect()
 501        return results
 502
 503    def _run_on_database(self,
 504                         host: str = None,
 505                         port: str = None,
 506                         user: str = None,
 507                         password: str = None,
 508                         database: str = None) -> pd.DataFrame:
 509        """
 510        This method runs the query on the database.
 511        The method checks on which database the query is run and then runs
 512        the query on the database.
 513
 514        This method raises a ValueError if the database is not one of the
 515        possible values (clickhouse or postgres).
 516
 517        Args:
 518            host (str): Host of the database.
 519            port (str): Port of the database.
 520            user (str): User of the database.
 521            password (str): Password of the database.
 522            database (str): Database name.
 523
 524        Returns:
 525            pd.DataFrame: Results of the query (can be empty if no results were fetched).
 526        """
 527        # Check if the database is ClickHouse
 528        if self._database == 'clickhouse':
 529            return self._run_clickhouse_query(
 530                self._query,
 531                host=host,
 532                port=port,
 533                user=user,
 534                password=password,
 535                database=database
 536            )
 537        # Check if the database is Postgres
 538        elif self._database == 'postgres':
 539            return self._run_postgres_query(
 540                self._query,
 541                host=host,
 542                port=port,
 543                user=user,
 544                password=password,
 545                database=database
 546            )
 547        else:
 548            raise ValueError(
 549                f'Database has to be one of the following values: '
 550                f'{", ".join(information_to_gather["database"]["possible_values"])},'
 551                f' Got: {self._database}'
 552                )
 553
 554    async def _run_on_database_async(self,
 555                                     host: str = None,
 556                                     port: str = None,
 557                                     user: str = None,
 558                                     password: str = None,
 559                                     database: str = None) -> pd.DataFrame:
 560        """
 561        This method runs the query on the database asynchronously.
 562        The method checks on which database the query is run and then runs
 563        the query on the database.
 564
 565        This method raises a ValueError if the database is not one of the
 566        possible values (clickhouse or postgres).
 567
 568        Args:
 569            host (str): Host of the database.
 570            port (str): Port of the database.
 571            user (str): User of the database.
 572            password (str): Password of the database.
 573            database (str): Database name.
 574
 575        Returns:
 576            pd.DataFrame: Results of the query (can be empty if no results were fetched).
 577        """
 578        if self._database == 'clickhouse':
 579            return await asyncio.to_thread(
 580                self._run_clickhouse_query,
 581                self._query,
 582                host,
 583                port,
 584                user,
 585                password,
 586                database
 587                )
 588        elif self._database == 'postgres':
 589            return await asyncio.to_thread(
 590                self._run_postgres_query,
 591                self._query,
 592                host,
 593                port,
 594                user,
 595                password,
 596                database
 597                )
 598        else:
 599            raise ValueError(
 600                f'Database has to be one of the following values: '
 601                f'{", ".join(information_to_gather["database"]["possible_values"])},'
 602                f' Got: {self._database}'
 603                )
 604
 605    def _check_output_and_result_column_match(self,
 606                                             results: pd.DataFrame) -> None:
 607        """
 608        This method checks if the output columns match with the columns in
 609        the results.
 610
 611        Args:
 612            results (pd.DataFrame): Results of the query.
 613        """
 614        if results.columns.tolist() != list(self._output.keys()):
 615            raise ValueError(
 616                f'Output columns do not match. [Query name: {self._name}]'
 617                f'Expected: {list(self._output.keys())}, '
 618                f'Got: {results.columns.tolist()}'
 619                )
 620
 621    def run_query(self,
 622                  host: str = None,
 623                  port: str = None,
 624                  user: str = None,
 625                  password: str = None,
 626                  database: str = None,
 627                  **kwargs) -> pd.DataFrame:
 628        """
 629        This method runs the query on the database.
 630        The method prepares the query and then runs the query on the database.
 631
 632        The method raises a ValueError if the output columns do not match with
 633        the columns in the results.
 634
 635        If the output is not defined, the method will return an empty
 636        DataFrame.
 637        If output is empty but the results are fetched, the method will raise a
 638        UserWarning.
 639
 640        Args:
 641            host (str): Host of the database.
 642            port (str): Port of the database.
 643            user (str): User of the database.
 644            password (str): Password of the database.
 645            database (str): Database name.
 646            **kwargs: input parameters for the query
 647
 648        Returns:
 649            pd.DataFrame: Results of the query (can be empty if no results were fetched).
 650        """
 651        # prepare the query
 652        self._prepare_query(**kwargs)
 653        # run the query on the database
 654        res = self._run_on_database(
 655            host=host,
 656            port=port,
 657            user=user,
 658            password=password,
 659            database=database
 660        )
 661        if len(self._output) == 0:
 662            self._result = res
 663            return self._result
 664
 665        # check if the output columns match with the columns in the results
 666        self._check_output_and_result_column_match(res)
 667
 668        self._result = res
 669        return self._result
 670
 671    async def run_query_async(self,
 672                              semaphore: asyncio.Semaphore = None,
 673                              host: str = None,
 674                              port: str = None,
 675                              user: str = None,
 676                              password: str = None,
 677                              database: str = None,
 678                              **kwargs) -> pd.DataFrame:
 679        """
 680        This method runs the query on the database asynchronously.
 681        The method prepares the query and then runs the query on the database.
 682
 683        The method raises a ValueError if the output columns do not match with
 684        the columns in the results.
 685
 686        > **It is important to have in mind that each call of this method
 687        creates a separate connection to the database. Too much of open
 688        connections to the database can result in Exceptions. So the use of
 689        `Semaphores` is recommended.**
 690
 691        > **The use of `Semaphores` is recommended when running multiple
 692        queries asynchronously.**
 693
 694        Args:
 695            semaphore (asyncio.Semaphore): Semaphore for running the query asynchronously.
 696            host (str): Host of the database.
 697            port (str): Port of the database.
 698            user (str): User of the database.
 699            password (str): Password of the database.
 700            database (str): Database name.
 701            **kwargs: input parameters for the query
 702
 703        Returns:
 704            pd.DataFrame: Results of the query (can be empty if no results were fetched).
 705        """
 706        # prepare the query
 707        self._prepare_query(**kwargs)
 708
 709        # check if semaphore is provided
 710        if semaphore is not None:
 711            # run the query on the database asynchronously
 712            async with semaphore:
 713                results = await self._run_on_database_async(
 714                    host=host,
 715                    port=port,
 716                    user=user,
 717                    password=password,
 718                    database=database
 719                )
 720        else:
 721            results = await self._run_on_database_async(
 722                host=host,
 723                port=port,
 724                user=user,
 725                password=password,
 726                database=database
 727            )
 728
 729        # check if lock is free in order to save results
 730        async with self._lock:
 731            self._result = results
 732
 733        if len(self._output) == 0:
 734            return self._result
 735
 736        # check if the output columns match with the columns in the results
 737        self._check_output_and_result_column_match(results)
 738
 739        return self._result
 740
 741    def get_results(self) -> pd.DataFrame:
 742        """
 743        Returns the results of the query.
 744
 745        Returns:
 746            pd.DataFrame: Results of the query (can be empty if no results were fetched).
 747        """
 748        # return the results
 749        return self._result
 750
 751    async def get_results_async(self) -> pd.DataFrame:
 752        """
 753        Returns the results of the query asynchronously.
 754
 755        Returns:
 756            pd.DataFrame: Results of the query (can be empty if no results were fetched).
 757        """
 758        # return the results, be careful to use the lock
 759        async with self._lock:
 760            return self._result
 761
 762    def get_name(self) -> str:
 763        """
 764        Returns the name of the query.
 765
 766        Returns:
 767            str: Name of the query.
 768        """
 769        return self._name
 770
 771    def get_database(self) -> str:
 772        """
 773        Returns the database on which the query is run.
 774
 775        Returns:
 776            str: Database on which the query is run.
 777        """
 778        return self._database
 779
 780    def get_input_parameters(self) -> dict[str, str] | None:
 781        """
 782        Returns the input parameters of the query.
 783        This method returns the input parameters in the following format:
 784        ```
 785        {
 786            <input_name>: <input_description>
 787        }
 788        ```
 789
 790        Input parameters can be empty, so method can return None.
 791
 792        Returns:
 793            dict | None: Input parameters of the query.
 794        """
 795        return self._input_parameters
 796
 797    def get_date(self) -> datetime.datetime:
 798        """
 799        Returns the date when the query was created.
 800
 801        Returns:
 802            datetime.datetime: Date when the query was created.
 803        """
 804        return self._date
 805
 806    def __hash__(self) -> int:
 807        """
 808        This method returns the hash of the Query object.
 809
 810        Returns:
 811            int: Hash of the Query object.
 812        """
 813        return hash(str(self))
 814
 815    def __eq__(self, other) -> bool:
 816        """
 817        This method checks if the Query object is equal to the other Query
 818        object.
 819
 820        Args:
 821            other: Query or other object to compare with.
 822
 823        Returns:
 824            bool: True if the Query objects are equal, False otherwise.
 825        """
 826        if not isinstance(other, Query):
 827            return False
 828
 829        return str(self) == str(other)
 830
 831    def __ne__(self, other) -> bool:
 832        """
 833        This method checks if the Query object is not equal to the other Query
 834        object.
 835
 836        Args:
 837            other: Query or other object to compare with.
 838
 839        Returns:
 840            bool: True if the Query objects are not equal, False otherwise.
 841        """
 842        return not self.__eq__(other)
 843
 844    def get_query(self) -> str:
 845        """
 846        Returns the query.
 847
 848        Returns:
 849            str: Query.
 850        """
 851        return self._query
 852
 853
 854def get_format(query) -> Query:
 855    """
 856    This function extracts the information from the query and returns
 857    the Query object.
 858
 859    The following rules must apply to the query:
 860    - The query must be in the following format:
 861    ```
 862    /* <parameters> */
 863    <query>
 864    ```
 865    - The query must contain the following parameters:
 866        - name - name of the query
 867        - author - author of the query
 868        - date - date when the query was created
 869        - database - database on which the query is run (clickhouse or
 870        postgres)
 871        - output - output of the query
 872    - The query can contain the following parameters:
 873        - input_parameters - input parameters of the query
 874        - depends - dependencies of the query (implementation used in other
 875        libraries)
 876        - other - contains the list of entires in the following
 877        format:
 878        ```
 879        <env_name>:
 880        -----------
 881        - <anything>
 882        - <anything>
 883        ```
 884
 885    Template for the query:
 886    ```sql
 887    /*
 888     * ============================================================================
 889     * SQL Query: <query name>
 890     * ============================================================================
 891     *
 892     * Author: <query author>
 893     * Date: <date in format %Y-%m-%d>
 894     * Database: <database name, clickhouse or postgres>
 895     *
 896     *
 897     * Input Parameters:
 898     * -----------------
 899     * - :<input name>: <input description>
 900     *
 901     * Output:
 902     * -------
 903     * - <output column>: <description of output column>
 904     *
 905     * Depends:
 906     * -------
 907     * - <table depend on name>: <table depends on description>
 908     *
 909     *
 910     *
 911     * <any name other then specified in this example>:
 912     * -------
 913     * - <value>
 914     * - <value>
 915     *
 916     * ============================================================================
 917     */
 918    SELECT *
 919    FROM :<input name>;
 920    ```
 921
 922    ## BE CAREFUL!
 923
 924    > If you have anywhere **input parameter prefix in a query**, that prefix
 925    > will be replaced with input variable **value**.
 926    > To be specific, every part of query that has the following format:
 927    > `:<input_variable_name>...` will be replaced with:
 928    > `<input_variable_value>...`.
 929
 930    > Dynamic querying of a source database is **only possible in clickhouse**,
 931    > because Postgres must reference one database on initialization,
 932    > we can’t choose the database after we initialized the object.
 933    > That means that you can use the following format in a query only
 934    > if you are using clickhouse:
 935
 936    ```sql
 937    /* ... */
 938    SELECT *
 939    FROM :database.table1
 940    ```
 941
 942    > It is good practice to have SQL keywords in capital letters. This
 943    > library requires you to have all capital letters on keywords defined
 944    > in `ignore_types_in_prepare_query`.
 945
 946
 947
 948    Args:
 949        query (str): Query string.
 950
 951    Returns:
 952        **Query**: Query object.
 953    """
 954    # Check if the query is in the correct BASE format
 955    if '/*' not in query or '*/' not in query:
 956        raise ValueError(
 957            f'Query is not properly formatted. '
 958            f'Please use the following format: '
 959            f'/* <parameters> */ query'
 960            )
 961
 962    # Extract the information from the query
 963    description_and_parameters = query[query.find('/*'):query.find('*/') + 2]
 964    query = query[query.find('*/') + 2:]
 965
 966    # Check if the query is empty
 967    if len(query) == 0:
 968        raise ValueError('Query is empty.')
 969
 970    # Check if the query has only one command
 971    if query.count(';') > 1:
 972        raise ValueError('Query can have only one command.')
 973
 974    constructor_information = dict()
 975    # Extract the information from the query
 976    for parameter_name, config in information_to_gather.items():
 977        # Check if the parameter is in the query
 978        tmp_pattern = re.compile(config['regex']['regex'], flags=re.MULTILINE)
 979        got = tmp_pattern.findall(
 980            string=description_and_parameters,
 981        )
 982
 983        # Check if the parameter is required
 984        if ((len(got) == 0) and (config['required'])):
 985            #  Check if the parameter is output and is empty
 986            if parameter_name == 'output':
 987                tmp_pattern = re.compile(
 988                    r'\s\*\sOutput:\n\s\*\s-+\n'
 989                )
 990                g = tmp_pattern.search(
 991                    string=description_and_parameters
 992                )
 993                # warn that parameter output is empty
 994                if g is not None:
 995                    warnings.warn(f"Output is empty. [QUERY: ({description_and_parameters[:300].replace('\n', ' ')})]")
 996                    continue
 997
 998            # raise error that parameter is required
 999            raise ValueError(f'Parameter {parameter_name} is required.')
1000
1001        # Check if the parameter is empty (and not required), just skip it
1002        if len(got) == 0:
1003            continue
1004
1005        if parameter_name == 'other':
1006            documentation_names = set([information_to_gather[key]['documentation_name'] for
1007                                       key in
1008                                       constructor_information.keys()])
1009            other_information = dict()
1010            for section in got:
1011                if section[1] in documentation_names:
1012                    continue
1013
1014                other_information[section[1]] = list(
1015                    filter(
1016                        lambda y: len(y) > 0,
1017                        map(lambda x: x.strip(), section[config['regex']['group_index']].split(' *'))
1018                    )
1019                )
1020            constructor_information[parameter_name] = other_information
1021            continue
1022
1023        # check if the parameter is in tuple
1024        if isinstance(got[0], tuple):
1025            # select the first element
1026            got = got[0]
1027
1028        # select the element defined in config
1029        got = got[config['regex']['group_index']]
1030
1031        # check if the parameter is empty, but required
1032        if len(got.strip()) == 0 and config['required']:
1033            raise ValueError(f'Parameter {parameter_name} is empty and'
1034                             f' required.')
1035        # check if the parameter is empty, but not required
1036        elif len(got.strip()) == 0:
1037            raise ValueError(f'Parameter {parameter_name} is empty.')
1038
1039        # check if input represents a list
1040        if ' * - ' in got:
1041            # isolate list to dictionary
1042            got = list(
1043                filter(
1044                    lambda y: len(y) > 0,
1045                    map(lambda x: x.strip(), got.split(' * - '))
1046                    )
1047                )
1048            got = dict(map(lambda x: x.split(': '), got))
1049        elif ' *' in got:
1050            got = got.replace(' *', '').strip()
1051
1052        # check if the parameter is a string
1053        # and remove the `\s` from the string beginning and the end
1054        if isinstance(got, str):
1055            got = got.strip()
1056
1057        # check if the parameter has to be one of the possible values
1058        if 'possible_values' in config:
1059            if got not in config['possible_values']:
1060                raise ValueError(
1061                    f'Parameter {parameter_name} '
1062                    f'has to be one of the following values: '
1063                    f'{", ".join(config["possible_values"])},'
1064                    f' Got: {got}'
1065                    )
1066
1067        constructor_information[parameter_name] = got
1068
1069    # return the Query object
1070    return Query(query=query, **constructor_information)
1071
1072
1073class Queries:
1074    """
1075    This class represents a collection of queries.
1076    The queries can be run synchronously or asynchronously.
1077
1078    You can select `Query` or subset of `Query` in `Queries` by using
1079    `__getitem__` method.
1080
1081    If the given ite, is an integer, the method returns the `Query` object (
1082    based on position in the collection).
1083    If the item is a slice, the method returns the `Queries` object with
1084    the subset of queries.
1085    If the item is a string, the method returns the `Query` object with
1086    the name of the query.
1087
1088    You can also provide slices with integers or datetime.datetime objects.
1089
1090    > In the case of slices, you will get the subset of queries based on
1091    > the position in the collection or based on the date of the query.
1092    > Resulting `Queries` object will have the same order of queries as in
1093    > the original collection also it will be a copy of the original
1094
1095    For example:
1096    ```python
1097    from queries.query import Queries
1098
1099    queries = Queries('path_to_file')
1100    query = queries[0]
1101    subset = queries[1:3]
1102    query = queries['query_name']
1103    query = queries[datetime.datetime(2021, 1, 1):datetime.datetime(2021, 1, 2)]
1104    ```
1105
1106    Another good thing about this library is that you can use addition to
1107    combine multiple Queries objects and get one.
1108
1109    ```python
1110    from queries.query import Queries
1111
1112    queries1 = Queries('path_to_file1')
1113    queries2 = Queries('path_to_file2')
1114    queries = queries1 + queries2
1115    ```
1116
1117    As you can see, you can use the `+` operator to combine multiple
1118    `Queries` objects, but you can also use the `+=` operator to add
1119    multiple `Queries` objects to the original `Queries` object.
1120
1121    ```python
1122    from queries.query import Queries
1123
1124    queries = Queries('path_to_file1')
1125    queries += Queries('path_to_file2')
1126    ```
1127
1128    **The order of the output `Queries` is not specified,** and after
1129    calling this method path is set to `None`. For more information look
1130    at `__add__` method.
1131
1132    ---
1133
1134    You can also subtract `Queries` objects from the original `Queries`
1135    object. The result is the `Queries` object with the queries that are
1136    in the original `Queries` object but not in the subtracted `Queries`
1137    object.
1138
1139    ```python
1140    from queries.query import Queries
1141
1142    queries = Queries('path_to_file1')
1143    queries -= Queries('path_to_file2')
1144    ```
1145
1146    You can also use the `-` to subtract `Query` objects from the original
1147    `Queries` object. The result is the `Queries` object with the queries
1148    that are in the original `Queries` object but not in the subtracted
1149    `Query` object.
1150
1151    ```python
1152    from queries.query import Queries
1153
1154    queries = Queries('path_to_file1')
1155    queries -= queries[0]
1156    ```
1157
1158    For more information look at `__sub__` method.
1159    """
1160    def __init__(self,
1161                 source: str | list[str] | Query | list[Query],
1162                 n_workers: int = None,
1163                 env_config_file: str = None):
1164        """
1165        This class represents a collection of queries.
1166        The queries can be run synchronously or asynchronously.
1167
1168        Constructor recieves the path to the file or directory with queries.
1169        The queries are loaded from the file or directory and stored in the
1170        dictionary.
1171
1172        If the path is a directory, the class will load all `.sql` files from
1173        the directory.
1174
1175        If path is not existing, the class will raise a `ValueError`.
1176        If the file is not a SQL file (doesn't end with `.sql`), object
1177        raises a `ValueError`.
1178
1179        If the file is empty, the method raises a `ValueError`.
1180        If there are multiple queries with the same name detected, the method
1181        raises a `ValueError`.
1182
1183        You can pass a path to the config file with different environment
1184        variables. The config file has to be a JSON file.
1185
1186        > Later you can define running environment from config file based on
1187        the parameter you send when calling a method that runs a query.
1188
1189        Args:
1190            source (str | list[str] | Query | list[Query]): path to the file or directory with queries or list of paths to the files or directories or Query object or list of Query objects
1191            n_workers (int): number of workers for running queries in parallel
1192            env_config_file (str): path to the config file with different environment variables
1193        """
1194        self._queries: dict[str, Query] = dict()
1195        self.path = None
1196        """Path to the file or directory with queries."""
1197
1198        self.env_config_file = env_config_file
1199        """Path to the config file with different environment variables."""
1200        self.env_configuration = None
1201        """Configuration from the env config file."""
1202
1203        # check if the config file exists
1204        if self.env_config_file is not None:
1205            if not self.env_config_file.endswith('.json'):
1206                raise ValueError('Config file has to be a JSON file.')
1207
1208            if not os.path.exists(self.env_config_file):
1209                raise ValueError(f'Config file {self.env_config_file} does not exist.')
1210
1211            with open(self.env_config_file, 'r') as file:
1212                self.env_configuration = json.load(file)
1213
1214        # if the source is path to the file or directory
1215        if isinstance(source, str):
1216            self._handle_one_path(source)
1217
1218        if isinstance(source, Query):
1219            self._queries[source.get_name()] = source
1220
1221        # if the source is list
1222        if isinstance(source, list):
1223            # of paths to the files or directories
1224            if all(isinstance(item, str) for item in source):
1225                for path in source:
1226                    self._handle_one_path(path)
1227            # of Query objects
1228            elif all(isinstance(item, Query) for item in source):
1229                names = [q.get_name() for q in source]
1230                if len(set(names)) != len(list(names)):
1231                    raise ValueError('There are multiple queries with the '
1232                                     'same name.')
1233                self._queries = {q.get_name(): q for q in source}
1234
1235        # check if the number of workers is provided
1236        self._semaphore = None
1237        if n_workers is not None:
1238            # create a semaphore for running queries in parallel
1239            self._semaphore = asyncio.Semaphore(n_workers)
1240            self._n_workers = n_workers
1241
1242    def _handle_one_path(self, path: str) -> None:
1243        """
1244        This method handles the path to the file or directory with queries.
1245        The method loads the queries from the file or directory.
1246
1247        If path is not existing, the method raises a ValueError.
1248
1249        Args:
1250            path (str): path to the file or directory with queries
1251        """
1252        # check if the path exists
1253        if not os.path.exists(path):
1254            raise ValueError(f'Path {path} does not exist.')
1255        self.path = path
1256
1257        # load the queries from the file or directory
1258        if os.path.isdir(path):
1259            for file in self._walk():
1260                self._create_queries(file)
1261        else:
1262            self._create_queries(path)
1263
1264    def _create_queries(self, path: str) -> None:
1265        """
1266        This method creates the queries from the file.
1267        If the file is not a SQL file (doesn't end with `.sql`), the method
1268        raises a ValueError.
1269
1270        If the file is empty, the method raises a ValueError.
1271        If there are multiple queries with the same name detected, the method
1272        raises a ValueError.
1273
1274        Args:
1275            path (str): path to the file with queries
1276        """
1277        if not path.endswith('.sql'):
1278            raise ValueError(f'File {path} is not a SQL file.')
1279
1280        with open(path, 'r') as file:
1281            file_text = file.read()
1282
1283            if len(file_text) == 0:
1284                raise ValueError(f'File {path} is empty.')
1285
1286            if len(file_text.strip()) == 0:
1287                raise ValueError(f'File {path} is empty.')
1288
1289            queries = list(
1290                filter(
1291                    lambda x: len(x) > 0,
1292                    re.split(
1293                        r'\n\s*\n',
1294                        file_text
1295                    )
1296                )
1297            )
1298
1299            for query in queries:
1300                try:
1301                    query_obj = get_format(query)
1302
1303                    if query_obj.get_name() in self._queries:
1304                        raise ValueError('There is already a query with the '
1305                                         'same name.')
1306
1307                    self._queries[query_obj.get_name()] = query_obj
1308                except ValueError as e:
1309                    raise ValueError(f'\n\n'
1310                                     f'[ERROR]: {e}\n[FILE]: {path}\n'
1311                                     f'[QUERY]: '
1312                                     f'{(query[:300].replace("\n", " "))}')
1313
1314    def _walk(self) -> str:
1315        """
1316        This method walks through the directory and yields the path to the
1317        `.sql` files.
1318
1319        Returns:
1320            str: Path to the `.sql` file.
1321        """
1322        for root, dirs, files in os.walk(self.path):
1323            for file in files:
1324                if file.endswith('.sql'):
1325                    yield os.path.join(root, file)
1326
1327    def _prepare_for_run_with_env(self,
1328                                  query: Query,
1329                                  env_name: str = None) -> dict[str, str | None]:
1330        """
1331        This method prepares the queries for running with the environment.
1332        The method checks if the environment name is in the configuration.
1333
1334        If the environment name is not in the configuration, the method raises
1335        a ValueError.
1336
1337        If env_name is None, the method returns dictionary of parmaeters for
1338        database connections that are `None`.
1339
1340        This method returns dictionary of parmaeters for database connection:
1341        + host
1342        + port
1343        + user
1344        + password
1345        + database
1346
1347        Args:
1348            query (Query): Query object
1349            env_name (str): name of the environment from the config file
1350        """
1351        if env_name is None:
1352            return {
1353                'host': None,
1354                'port': None,
1355                'user': None,
1356                'password': None,
1357                'database': None
1358            }
1359
1360        if env_name not in self.env_configuration:
1361            raise ValueError(f'Environment {env_name} is '
1362                             f'not in the configuration.')
1363
1364        if query.get_database() == 'clickhouse':
1365            return {
1366                'host': self.env_configuration[env_name]['CLICKHOUSE_HOST'],
1367                'port': self.env_configuration[env_name]['CLICKHOUSE_PORT'],
1368                'user': self.env_configuration[env_name]['CLICKHOUSE_USER'],
1369                'password': self.env_configuration[env_name]['CLICKHOUSE_PASSWORD'],
1370                'database': self.env_configuration[env_name]['CLICKHOUSE_DATABASE']
1371            }
1372
1373        if query.get_database() == 'postgres':
1374            return {
1375                'host': self.env_configuration[env_name]['POSTGRES_HOST'],
1376                'port': self.env_configuration[env_name]['POSTGRES_PORT'],
1377                'user': self.env_configuration[env_name]['POSTGRES_USER'],
1378                'password': self.env_configuration[env_name]['POSTGRES_PASSWORD'],
1379                'database': self.env_configuration[env_name][
1380                    'POSTGRES_DATABASE']
1381            }
1382
1383        raise ValueError(f'Database has to be one of the following values: '
1384                            f'clickhouse or postgres. Got: {query.get_database()}')
1385
1386    def run_queries(self,
1387                    env_name: str = None,
1388                    **kwargs) -> dict[str, pd.DataFrame]:
1389        """
1390        This method runs all queries in the collection, synchronously.
1391
1392        If there is an error in the query, the method raises a ValueError.
1393
1394        Example of usage:
1395        ```python
1396        from queries.query import Queries
1397
1398        queries = Queries('path_to_file')
1399        results = queries.run_queries(input_name='value')
1400        ```
1401
1402        Args:
1403            env_name (str): name of the environment from the config file
1404            **kwargs: input parameters for the queries
1405
1406        Returns:
1407            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1408        """
1409        results = dict()
1410        query_name = None
1411        try:
1412            for query_name, query in self._queries.items():
1413                results[query_name] = query.run_query(
1414                    **self._prepare_for_run_with_env(query, env_name),
1415                    **kwargs
1416                )
1417            return results
1418        except ValueError as e:
1419            raise ValueError(f'\n\n'
1420                             f'[ERROR]: {e}\n[QUERY NAME]: {query_name}')
1421
1422    async def run_queries_async(self,
1423                                env_name: str = None,
1424                                **kwargs) -> dict[str, pd.DataFrame]:
1425        """
1426        This method runs all queries in the collection, asynchronously.
1427
1428        If there is an error in the query, the method raises a ValueError.
1429
1430        Example of usage:
1431        ```python
1432        import asyncio
1433        from queries.query import Queries
1434
1435        queries = Queries('path_to_file')
1436        results = asyncio.run(queries.run_queries_async(input_name='value'))
1437        ```
1438
1439        Args:
1440            env_name (str): name of the environment from the config file
1441            **kwargs: input parameters for the queries
1442
1443        Returns:
1444            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1445        """
1446        try:
1447            res = await asyncio.gather(
1448                *[
1449                    query.run_query_async(
1450                        semaphore=self._semaphore,
1451                        **self._prepare_for_run_with_env(
1452                            query,
1453                            env_name
1454                        ),
1455                        **kwargs
1456                        )
1457                    for query in self._queries.values()
1458                ]
1459            )
1460            return {
1461                key: self._queries[key].get_results()
1462                for key in self._queries.keys()
1463            }
1464        except ValueError as e:
1465            raise ValueError(f'\n\n'
1466                             f'[ERROR]: {e}')
1467
1468    def run_query_specific(self,
1469                           query_name: str,
1470                           env_name: str = None,
1471                           **kwargs) -> pd.DataFrame:
1472        """
1473        This method runs a specific query from the collection.
1474        You need to provide the name of the query.
1475
1476        If provided query name does not exist in the collection, the method
1477        raises a ValueError.
1478
1479        Args:
1480            query_name (str): name of the query
1481            env_name (str): name of the environment from the config file
1482            **kwargs: input parameters for the query
1483
1484        Returns:
1485            pd.DataFrame: Results of the query.
1486        """
1487        if query_name not in self._queries:
1488            raise ValueError(f'Query with name {query_name} does not exist.')
1489
1490        return self._queries[query_name].run_query(
1491            **self._prepare_for_run_with_env(self._queries[query_name],
1492                                             env_name),
1493            **kwargs
1494        )
1495
1496    async def run_query_specific_async(self,
1497                                       query_name: str,
1498                                       env_name: str = None,
1499                                       **kwargs) -> pd.DataFrame:
1500        """
1501        This method runs a specific query from the collection asynchronously.
1502        You need to provide the name of the query.
1503
1504        If provided query name does not exist in the collection, the method
1505        raises a ValueError.
1506
1507        This method uses the semaphore for running the query asynchronously.
1508
1509        Args:
1510            query_name (str): name of the query
1511            env_name (str): name of the environment from the config file
1512            **kwargs: input parameters for the query
1513
1514        Returns:
1515            pd.DataFrame: Results of the query.
1516        """
1517        if query_name not in self._queries:
1518            raise ValueError(f'Query with name {query_name} does not exist.')
1519
1520        if self._semaphore is not None:
1521            async with self._semaphore:
1522                return await self._queries[query_name].run_query_async(
1523                    **self._prepare_for_run_with_env(
1524                        self._queries[query_name],
1525                        env_name
1526                    ),
1527                    **kwargs
1528                )
1529
1530        return await self._queries[query_name].run_query_async(**kwargs)
1531
1532    def run_subset_queries(self,
1533                           query_names: list[str],
1534                           env_name: str = None,
1535                           **kwargs) -> dict[str, pd.DataFrame]:
1536        """
1537        This method runs a subset of queries from the collection.
1538        You need to provide the list of query names.
1539
1540        If provided query names do not exist in the collection, the method
1541        raises a ValueError.
1542
1543        Args:
1544            query_names (list): list of query names
1545            env_name (str): name of the environment from the config file
1546            **kwargs: input parameters for the queries
1547
1548        Returns:
1549            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1550        """
1551        if not set(query_names).issubset(set(self._queries.keys())):
1552            raise ValueError(f'Query names are not subset of queries.')
1553
1554        to_ret = dict()
1555        try:
1556            for query_name in query_names:
1557                to_ret[query_name] = self.run_query_specific(
1558                    query_name,
1559                    env_name,
1560                    **kwargs
1561                )
1562            return to_ret
1563        except ValueError as e:
1564            raise ValueError(
1565                f'\n\n'
1566                f'[ERROR]: {e}\n[QUERY NAMES]: {query_names}'
1567                )
1568
1569    async def run_subset_queries_async(self,
1570                                       query_names: list[str],
1571                                       env_name: str = None,
1572                                       **kwargs) -> dict[str, pd.DataFrame]:
1573        """
1574        This method runs a subset of queries from the collection asynchronously.
1575        You need to provide the list of query names.
1576
1577        If provided query names do not exist in the collection, the method
1578        raises a ValueError.
1579
1580        Args:
1581            query_names (list): list of query names
1582            env_name (str): name of the environment from the config file
1583            **kwargs: input parameters for the queries
1584
1585        Returns:
1586            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1587        """
1588        if not set(query_names).issubset(set(self._queries.keys())):
1589            raise ValueError(f'Query names are not subset of queries.')
1590
1591        try:
1592            res = await asyncio.gather(
1593                *[
1594                    self.run_query_specific_async(
1595                        query_name,
1596                        env_name,
1597                        **kwargs
1598                    )
1599                    for query_name in query_names
1600                ]
1601            )
1602            to_return = {
1603                query_name: self._queries[query_name].get_results()
1604                for query_name in query_names
1605            }
1606            return to_return
1607        except ValueError as e:
1608            raise ValueError(
1609                f'\n\n'
1610                f'[ERROR]: {e}\n[QUERY NAMES]: {query_names}'
1611                )
1612
1613    def __getitem__(self, item: int | slice | str) -> 'Query | Queries':
1614        """
1615        This method returns the `Query` or `Queries` from the collection.
1616
1617        If the item is an integer, the method returns the `Query` object (
1618        based on position in the collection).
1619        If the item is a slice, the method returns the `Queries` object with
1620        the subset of queries.
1621        If the item is a string, the method returns the `Query` object with
1622        the name of the query.
1623
1624        You can also provide slices with integers or datetime.datetime objects.
1625
1626        > In the case of slices, you will get the subset of queries based on
1627        > the position in the collection or based on the date of the query.
1628        > Resulting `Queries` object will have the same order of queries as in
1629        > the original collection also it will be a copy of the original
1630
1631        Args:
1632            item: int, slice(int | datetime.datetime) or str
1633
1634        Returns:
1635            Query | Queries: Query or Queries object
1636        """
1637        if isinstance(item, int):
1638            return list(self._queries.values())[item]
1639
1640        if isinstance(item, slice):
1641            start = item.start
1642            end = item.stop
1643            query_subset_copy = None
1644
1645            if isinstance(start, int) or isinstance(end, int):
1646                if start is None:
1647                    start = 0
1648                if end is None:
1649                    end = len(self._queries)
1650
1651                if not isinstance(start, int) or not isinstance(end, int):
1652                    raise ValueError(
1653                        'Slice indices have to be integers. '
1654                        'Got {start} and {end}.'
1655                        )
1656
1657                query_subset_copy = copy.deepcopy(self)
1658                query_subset_copy.path = None
1659                query_subset_copy._queries = {
1660                    k: copy.deepcopy(self._queries[k]) for k in
1661                    self._queries.keys() if k in
1662                                            list(self._queries.keys())[
1663                                            start:end]
1664                }
1665
1666            if isinstance(start, datetime.datetime) or isinstance(
1667                    end,
1668                    datetime.datetime
1669                    ):
1670                if start is None:
1671                    sorted_by_datetime = sorted(
1672                        self._queries.values(),
1673                        key=lambda x: x.get_date()
1674                        )
1675                    start = sorted_by_datetime[0].get_date()
1676                if end is None:
1677                    sorted_by_datetime = sorted(
1678                        self._queries.values(),
1679                        key=lambda x: x.get_date()
1680                        )
1681                    end = sorted_by_datetime[-1].get_date()
1682
1683                if not isinstance(start, datetime.datetime) or not isinstance(end, datetime.datetime):
1684                    raise ValueError(
1685                        f'Slice indices have to be '
1686                        f'datetime.datetime. '
1687                        f'Got {start} and {end}.'
1688                    )
1689
1690                query_subset_copy = copy.deepcopy(self)
1691                query_subset_copy.path = None
1692                query_subset_copy._queries = {k: copy.deepcopy(
1693                    self._queries[k]
1694                ) for k in
1695                    self._queries.keys() if start <=
1696                                            self._queries[k].get_date() <= end
1697                }
1698
1699            return query_subset_copy
1700
1701        return self._queries[item]
1702
1703    def add(self, query: Query) -> None:
1704        """
1705        This method adds a query to the collection.
1706
1707        If the query with the same name already exists in the collection,
1708        the method raises a ValueError.
1709
1710        Args:
1711            query (Query): Query object to add to the collection.
1712        """
1713        if query.get_name() in self._queries:
1714            raise ValueError(f'Query with name {query.get_name()} already '
1715                             'exists in the collection.')
1716
1717        self.path = None
1718        self._queries[query.get_name()] = query
1719
1720    def pop(self, query: str | list[str]) -> Query | list[Query]:
1721        """
1722        This method removes the query from the collection and returns it.
1723
1724        If the query with the name does not exist in the collection, the
1725        method raises a ValueError.
1726
1727        You can provide a list of query names to remove multiple queries.
1728
1729        Args:
1730            query (str | list[str]): name of the query, or list of names
1731
1732        Returns:
1733            Query | list[Query]: Query object or list of Query objects
1734        """
1735        if not isinstance(query, list) and not isinstance(query, str):
1736            raise ValueError(f'You can provide only list or string.'
1737                             f'Got: {query}')
1738
1739        if isinstance(query, str):
1740            if query not in self._queries:
1741                raise ValueError(f'Query with name {query} does not exist.')
1742
1743            self.path = None
1744            return self._queries.pop(query)
1745
1746        queries = []
1747        for query_name in query:
1748            if query_name not in self._queries:
1749                raise ValueError(f'Query with name {query_name} does not exist.')
1750            queries.append(self._queries.pop(query_name))
1751        self.path = None
1752        return queries
1753
1754    def __setitem__(self, key, value):
1755        raise KeyError('You cannot set values in this object.')
1756
1757    def __contains__(self, item: str) -> bool:
1758        """
1759        This method checks if the query with the name exists in the collection.
1760
1761        Args:
1762            item (str): name of the query
1763
1764        Returns:
1765            bool: True if the query exists in the collection, False otherwise
1766        """
1767        return item in self._queries.keys()
1768
1769    def __str__(self) -> str:
1770        """
1771        This method returns the string representation of the Queries object.
1772        Each query is represented in the following format:
1773        ```
1774        {
1775            <query_name>: <Query object>
1776        }
1777        ```
1778
1779        Returns:
1780            str: String representation of the Queries object
1781        """
1782        return pprint.pformat(self._queries)
1783
1784    def __repr__(self):
1785        """
1786        This method returns the string representation of the Queries object.
1787        Each query is represented in the following format:
1788        ```
1789        {
1790            <query_name>: <Query object>
1791        }
1792        ```
1793
1794        Returns:
1795            str: String representation of the Queries object
1796        """
1797        return str(self)
1798
1799    def __len__(self) -> int:
1800        """
1801        This method returns the number of queries in the collection.
1802
1803        Returns:
1804            int: Number of queries in the collection.
1805        """
1806        return len(self._queries)
1807
1808    def get_queries(self) -> dict[str, Query]:
1809        """
1810        This method returns the queries in dictionary format.
1811
1812        Returns:
1813            dict[str, Query]: Dictionary with queries.
1814        """
1815        return self._queries
1816
1817    def keys(self) -> list[str]:
1818        """
1819        This method returns the names of the queries.
1820
1821        Returns:
1822            list[str]: List of names of the queries.
1823        """
1824        return list(self._queries.keys())
1825
1826    def reorder_queries(self, query_names: list[str]) -> None:
1827        """
1828        This method reorders the queries in the collection.
1829        You need to provide the list of query names in the new order.
1830
1831        Args:
1832            query_names (list): List of query names in the new order.
1833
1834        """
1835        queries = dict()
1836        for query_name in query_names:
1837            if query_name not in self._queries:
1838                raise ValueError(f'Query with name {query_name} does not exist.')
1839            queries[query_name] = self._queries[query_name]
1840        self._queries = queries
1841
1842    def __eq__(self, other):
1843        """
1844        This method checks if the Queries object is equal to the other Queries
1845        object.
1846        """
1847        if not isinstance(other, Queries):
1848            return False
1849
1850        return hash(str(self)) == hash(str(other))
1851
1852    def __hash__(self) -> int:
1853        """
1854        This method returns the hash of the Queries object.
1855
1856        Returns:
1857            int: Hash of the Queries object.
1858        """
1859        return hash(str(self))
1860
1861    def __ne__(self, other) -> bool:
1862        """
1863        This method checks if the Queries object is not equal to the other
1864        Queries object.
1865
1866        Returns:
1867            bool: True if the Queries objects are not equal, False otherwise.
1868        """
1869        return not self.__eq__(other)
1870
1871    def set_number_of_workers(self, n_workers: int) -> None:
1872        """
1873        This method sets the number of workers for running queries in parallel.
1874
1875        Args:
1876            n_workers (int): Number of workers for running queries in parallel.
1877        """
1878        if n_workers is not None:
1879            self._semaphore = asyncio.Semaphore(n_workers)
1880            self._n_workers = n_workers
1881
1882    def get_number_of_workers(self) -> int:
1883        """
1884        This method returns the number of workers for running queries
1885        in parallel.
1886
1887        Returns:
1888            int: Number of workers for running queries in parallel.
1889        """
1890        return self._n_workers
1891
1892    def __add__(self, other: 'Queries | Query') -> 'Queries':
1893        """
1894        This method adds two Queries objects together or adds a Query object
1895
1896        If the other object is not a `Queries` or `Query` object, the method
1897        raises a ValueError.
1898
1899        If there is a query with the same name in both collections:
1900        - if the query is the same, the query is added to destination
1901        collection from the source collection with a warning
1902        - if the query is different, the method raises a ValueError
1903
1904        **The order of the output `Queries` is not specified.** (*but if you
1905        add Query object to Queries object, the Query object will be added to
1906        the end of the collection*).
1907
1908        The number of workers is the same as in the source collection (the
1909        left one, but you can define it with a method:
1910        `set_number_of_workers`).
1911        The path is set to None.
1912
1913        Args:
1914            other (Queries | Query): Queries object to add to the current Queries object
1915
1916        Returns:
1917            Queries: New Queries object with queries from both collections, or if the other object is a Query object it returns Queries object with added Query object.
1918        """
1919        if not isinstance(other, Queries) and not isinstance(other, Query):
1920            raise ValueError('You can add only Queries objects.')
1921
1922        if isinstance(other, Query):
1923            queries = copy.deepcopy(self)
1924            queries.path = None
1925
1926            if other.get_name() in queries._queries:
1927                warnings.warn(f'Query {other.get_name()} already exists in '
1928                              'the collection. Query will be replaced.',
1929                              UserWarning)
1930
1931            queries._queries[other.get_name()] = other
1932            return queries
1933
1934        this_queries_names = set(self._queries.keys())
1935        other_queries_names = set(other.get_queries().keys())
1936
1937        for query_name in this_queries_names.intersection(other_queries_names):
1938            if self._queries[query_name] == other.get_queries()[query_name]:
1939                warnings.warn(f'Query {query_name} is the same in both '
1940                              'collections. It will be added from the source '
1941                              'collection to the destination collection.',
1942                              UserWarning)
1943                continue
1944            raise KeyError(f'Query {query_name} is different in both '
1945                           f'collections.')
1946
1947        queries = copy.deepcopy(self)
1948        queries.path = None
1949        queries._queries.update(other.get_queries())
1950        return queries
1951
1952    def __sub__(self, other: 'Queries | Query') -> 'Queries':
1953        """
1954        This method subtracts two Queries objects or Query object from Queries.
1955
1956        If the other object is not a `Queries` or `Query` object, the method
1957        raises a ValueError.
1958
1959        If the other object is a Query object, the method removes the query
1960        from the collection.
1961
1962        If the other object is a Queries object, the method removes queries
1963        from the collection.
1964
1965        If there is a query in the current collection that does not exist in
1966        the other collection, the method warns UserWarning (same goes when
1967        subtracting Query object).
1968
1969
1970        Args:
1971            other (Queries | Query): Queries object to subtract from the current Queries object or Query object to subtract from the current Queries object
1972
1973        Returns:
1974            Queries: New Queries object with queries from the current collection without queries from the other collection or without the Query object
1975        """
1976        if not isinstance(other, Queries) and not isinstance(other, Query):
1977            raise ValueError('You can subtract only Queries or Query object.')
1978
1979        if isinstance(other, Query):
1980            queries = copy.deepcopy(self)
1981            queries.path = None
1982
1983            if other.get_name() not in queries._queries:
1984                warnings.warn(f'Query {other.get_name()} does not exist in '
1985                              'the collection. Nothing to subtract.',
1986                              UserWarning)
1987                return queries
1988
1989            queries._queries.pop(other.get_name())
1990            return queries
1991
1992        this_queries_names = set(self._queries.keys())
1993        other_queries_names = set(other.get_queries().keys())
1994
1995        self.path = None
1996        if not this_queries_names.issuperset(other_queries_names):
1997            warnings.warn(f'Queries to subtract are not subset of '
1998                          f'queries. Differences: '
1999                          f'{other_queries_names.difference(this_queries_names)}',
2000                          UserWarning)
2001            return copy.deepcopy(self)
2002
2003        return Queries([self._queries[query] for query in
2004                        this_queries_names.difference(other_queries_names)])

information_to_gather = {'name': {'regex': {'regex': '\\*\\s=+\\n\\s\\*\\sSQL\\sQuery:\\s*(.*)\\n\\s\\*\\s=+', 'group_index': 0}, 'required': True, 'format': ' * ============\n * SQL Query: <name>\n * ============', 'documentation_name': 'SQL Query'}, 'author': {'regex': {'regex': '\\s\\*\\sAuthor:(.*)', 'group_index': 0}, 'required': True, 'format': ' * Author: <author>', 'documentation_name': 'Author'}, 'date': {'regex': {'regex': '\\s\\*\\sDate:(.*)', 'group_index': 0}, 'required': True, 'format': ' * Date: <date>', 'documentation_name': 'Date'}, 'input_parameters': {'regex': {'regex': '(\\s\\*\\sInput\\sParameters:\\n\\s\\*\\s-+\\n((\\s\\*\\s-\\s:\\w+:\\s.*\\n?)+))', 'group_index': 1}, 'required': False, 'format': ' * Input parameters: <parameters>', 'documentation_name': 'Input Parameters'}, 'database': {'regex': {'regex': '\\s\\*\\sDatabase:(.*)', 'group_index': 0}, 'required': True, 'format': ' * Database: <database>', 'possible_values': ['clickhouse', 'postgres'], 'documentation_name': 'Database'}, 'output': {'regex': {'regex': '(\\s\\*\\sOutput:\\n\\s\\*\\s-+\\n((\\s\\*\\s-\\s\\w+:\\s.*\\n?)+))', 'group_index': 1}, 'required': True, 'documentation_name': 'Output'}, 'depends': {'regex': {'regex': '(\\s\\*\\sDepends:\\n\\s\\*\\s-+\\n((\\s\\*\\s-\\s.+:\\s.*\\n?)+))', 'group_index': 1}, 'required': False, 'documentation_name': 'Depends'}, 'other': {'regex': {'regex': '(\\s\\* (.+?):\\n\\s\\*\\s-+\\n((?:(?!^\\s*\\*\\s*\\n)\\s\\*\\s.*\\n)+))', 'group_index': 2}, 'required': False, 'documentation_name': 'Other'}}

This dictionary contains information about the query that can be extracted from the query itself. The dictionary contains the following keys:

name: name of the query
author: author of the query
date: date when the query was created
input_parameters: input parameters of the query
database: database on which the query is run
output: output of the query
depends: dependencies of the query
other: other information about the query

Each key contains a dictionary with the following keys (not all keys are present in all dictionaries):

regex: dictionary with the following keys:
- regex: regular expression to extract the information
- group_index: index of the group in the regular expression
required: boolean value if the parameter is required
format: format of the parameter
possible_values: list of possible values for the parameter
documentation_name: name of the parameter in the documentation

ignore_types_in_prepare_query = ['FROM', 'TABLE', 'INTO']

List of keywords that need to be before the parameter in the query to ignore the type of the parameter. For example when the parameter is a string, the query should look like: SELECT * FROM table WHERE column = '<parameter_value>' If the parameter is a string but it is after one of the keywords in the list, the query should look like:

SELECT * FROM <parameter_value> (without '')
INSERT INTO <parameter_value> (without '')
CREATE TABLE <parameter_value> (without '')

You must use capital letters on these keywords in order for this library to work.

class Query: View Source

159class Query:
160    def __init__(
161            self,
162            query: str,
163            name: str,
164            author: str,
165            database: str,
166            date: str,
167            output: dict = None,
168            input_parameters: dict = None,
169            depends: dict = None,
170            other: dict = None
171            ):
172        """
173        This class represents a query that can be run on a database.
174        The query can be run on ClickHouse or Postgres database.
175
176        This class is written for fast execution of queries because of use
177        of asyncio and threading.
178
179        The main idea is that the query is **prepared only once** and then
180        it is
181        run on the database. The query can be run synchronously or
182        asynchronously.
183
184        The query can have **input parameters** that are replaced in the query
185        before running the query.
186
187        The query can have **output** that is checked if it matches the
188        columns in the results.
189
190        Other information that can be used in the child classes and use cases
191        can be set using the `other` parameter.
192
193        The query can have **dependencies** on other queries. The
194        dependencies are
195        used for other libraries and are not used in this library.
196
197        Args:
198            name (str): name of the query
199            author (str): author of the query
200            database (str): database on which the query is run
201            date (str): date when the query was created
202            output (dict): output of the query in format: {'<name>': '<description>'}
203            input_parameters (dict): input parameters of the query (default: None), for example: {'<name>': '<description>'}
204            depends (dict): dependencies of the query (default: None) in format: {'<name>': <description>}
205            other (dict): other information about the query (default: None)
206        """
207        self._query: str = query
208        self._original_query: str = query
209        self._name: str = name
210        self._author: str = author
211        self._database: str = database
212        self._output: dict = output
213        self._other: dict = other
214
215        if self._output is None:
216            self._output = dict()
217
218        self._date: datetime.datetime = datetime.datetime.strptime(
219            date,
220            '%Y-%m-%d'
221        )
222        self._input_parameters: dict = input_parameters
223        self._depends: dict = depends
224
225        self._flag_prepare_query = False
226        self._result: pd.DataFrame = None
227        self._lock = asyncio.Lock()
228
229        if self._database not in information_to_gather['database'][
230            'possible_values']:
231            raise ValueError(
232                f'Database has to be one of the following values: '
233                f'{", ".join(information_to_gather["database"]["possible_values"])},'
234                f' Got: {self._database}'
235                )
236
237    def number_of_dependent(self) -> int:
238        """
239        This method returns the number of dependent queries.
240
241        Returns:
242            int: Number of dependent queries.
243        """
244        if self._depends is None:
245            return 0
246
247        return len(self._depends)
248
249    def get_dependent(self) -> list[str]:
250        """
251        This method returns the dependent queries names.
252
253        Will return an empty list if there are no dependent queries.
254
255        Returns:
256            list[str]: List of names of dependent queries.
257        """
258        if self._depends is None:
259            return []
260
261        return list(self._depends.keys())
262
263    def get_output(self) -> list[str]:
264        """
265        This method returns the list of output column names.
266
267        Returns:
268            list[str]: List of output column names .
269        """
270        return list(self._output.keys())
271
272    def __repr__(self) -> str:
273        """
274        This method returns the string representation of the Query object.
275
276        Returns:
277            str: String representation of the Query object.
278        """
279        return str(self)
280
281    def __str__(self) -> str:
282        """
283        This method returns the string representation of the Query object.
284        Each parameter is represented in the following format:
285        ```
286        {
287            'name': <name>,
288            'query': <query>,
289            'prepared_query': <prepared_query>,
290            'original_query': <original_query>,
291            'author': <author>,
292            'database': <database>,
293            'output': <output>,
294            'date': <date>,
295            'input_parameters': <input_parameters>,
296            'depends': <depends>,
297            'results': <results>
298        }
299        ```
300
301        Returns:
302            str: String representation of the Query object
303        """
304        prepared_string_representation = {
305            'name': self._name,
306            'query': self._query,
307            'prepared_query': self._query,
308            'original_query': self._original_query,
309            'author': self._author,
310            'database': self._database,
311            'output': self._output,
312            'date': self._date.strftime('%Y-%m-%d'),
313            'input_parameters': self._input_parameters,
314            'depends': self._depends,
315            'results': str(self._result),
316            'other': self._other
317        }
318        return pprint.pformat(prepared_string_representation)
319
320    def get_documentation_other(self) -> dict:
321        """
322        This method returns the other information about the query from
323        documentation.
324
325        Returns:
326            dict: Other information about the query
327        """
328        return self._other
329
330    def _prepare_query(self, **kwargs) -> None:
331        """
332        This method prepares the query for running on the database.
333        The input parameters are replaced in the query through the kwargs.
334
335        > Be careful, **each parameter in kwargs must exactly match to the
336        name used in SQL query in format**: `:<parameter_name>`
337
338        This method is called only once before running the query on the
339        database.
340        This method raises a ValueError if the input parameters are not
341        provided.
342        This method warns if the input parameter is not defined in the query.
343
344        This method transforms the input parameters to the correct format
345        before inserting them into the query.
346
347        Args:
348            **kwargs: input parameters for the query
349        Returns:
350            None
351        """
352        # Check if the query is already prepared
353        if self._flag_prepare_query:
354            return
355
356        # Check if the input parameters are provided
357        # If the input parameters are not provided, the query is already
358        # prepared
359        if self._input_parameters is None:
360            self._flag_prepare_query = True
361            return
362
363        # Check if the input parameters are in the query
364        for input_name in self._input_parameters.keys():
365            # Warn if the input parameter is not in the query
366            if input_name not in self._query:
367                warnings.warn(f'Parameter {input_name} is not in the query.',
368                              UserWarning)
369
370            # Replace `:` in input parameter names
371            input_name = input_name.replace(':', '')
372            # if the input parameter is not in the kwargs, raise ValueError
373            if input_name not in kwargs:
374                raise ValueError(f'Parameter {input_name} is required.')
375            to_insert = kwargs[input_name]
376
377            # Check if the input parameter is a string
378            if isinstance(to_insert, str):
379                for pre_keyword in ignore_types_in_prepare_query:
380                    pattern = re.compile(rf'{pre_keyword}\s+:{input_name}')
381                    if pattern.search(self._query):
382                        self._query = self._query.replace(
383                            f':{input_name}',
384                            to_insert
385                        )
386                        continue
387
388                to_insert = f"'{to_insert}'"
389
390            # Check if the input parameter is a datetime
391            if isinstance(to_insert, datetime.datetime):
392                to_insert = f"'{to_insert.strftime('%Y-%m-%d %H:%M:%S')}'"
393
394            # check if the input parameter is a list
395            if isinstance(to_insert, list):
396                to_insert = str(to_insert)
397
398            # replace the input parameter in the query
399            self._query = self._query.replace(f':{input_name}', to_insert)
400
401        # Set the flag that the query is prepared
402        self._flag_prepare_query = True
403
404    def _run_clickhouse_query(self,
405                              query: str,
406                              host: str = None,
407                              port: str = None,
408                              user: str = None,
409                              password: str = None,
410                              database: str = None) -> pd.DataFrame:
411        """
412        This method creates a one connection to the clickhouse database,
413        runs the query and fetches the results.
414
415        If the output is not defined, the method will return an empty DataFrame.
416        If output is empty but the results are fetched, the method will raise a
417        UserWarning.
418
419        Args:
420            query (str): Query to run on the database.
421            host (str): Host of the database.
422            port (str): Port of the database.
423            user (str): User of the database.
424            password (str): Password of the database.
425            database (str): Database name.
426
427        Returns:
428            pd.DataFrame: Results of the query.
429        """
430        connector = ClickHouseConnector(
431            host=host,
432            port=port,
433            user=user,
434            password=password,
435            database=database
436        )
437        connector.connect()
438
439        # if length of output is None, run the query and return empty DataFrame
440        if len(self._output) == 0:
441            # check if there is a data present as an output
442            results = connector.run_query(query)
443
444            if len(results) != 0:
445                warnings.warn(
446                    'Output is not defined, but there is a result.',
447                    UserWarning
448                    )
449            connector.disconnect()
450            return pd.DataFrame()
451
452        results = connector.fetch_dataframe(query)
453        connector.disconnect()
454        return results
455
456    def _run_postgres_query(self,
457                            query: str,
458                            host: str = None,
459                            port: str = None,
460                            user: str = None,
461                            password: str = None,
462                            database: str = None) -> pd.DataFrame:
463        """
464        This method creates a one connection to the postgres database,
465        runs the query and fetches the results.
466
467        Args:
468            query (str): Query to run on the database.
469            host (str): Host of the database.
470            port (str): Port of the database.
471            user (str): User of the database.
472            password (str): Password of the database.
473            database (str): Database name.
474
475        Returns:
476            pd.DataFrame: Results of the query.
477        """
478        connector = PostgresConnector(
479            host=host,
480            port=port,
481            user=user,
482            password=password,
483            database=database
484        )
485        connector.connect()
486
487        # if length of output is None, run the query and return empty DataFrame
488        if len(self._output) == 0:
489            # check if there is a data present as an output
490            results = connector.run_query(query)
491
492            if len(results) != 0:
493                warnings.warn('Output is not defined, '
494                              'but there is a result.',
495                              UserWarning)
496
497            connector.disconnect()
498            return pd.DataFrame()
499
500        results = connector.fetch_dataframe(query)
501        connector.disconnect()
502        return results
503
504    def _run_on_database(self,
505                         host: str = None,
506                         port: str = None,
507                         user: str = None,
508                         password: str = None,
509                         database: str = None) -> pd.DataFrame:
510        """
511        This method runs the query on the database.
512        The method checks on which database the query is run and then runs
513        the query on the database.
514
515        This method raises a ValueError if the database is not one of the
516        possible values (clickhouse or postgres).
517
518        Args:
519            host (str): Host of the database.
520            port (str): Port of the database.
521            user (str): User of the database.
522            password (str): Password of the database.
523            database (str): Database name.
524
525        Returns:
526            pd.DataFrame: Results of the query (can be empty if no results were fetched).
527        """
528        # Check if the database is ClickHouse
529        if self._database == 'clickhouse':
530            return self._run_clickhouse_query(
531                self._query,
532                host=host,
533                port=port,
534                user=user,
535                password=password,
536                database=database
537            )
538        # Check if the database is Postgres
539        elif self._database == 'postgres':
540            return self._run_postgres_query(
541                self._query,
542                host=host,
543                port=port,
544                user=user,
545                password=password,
546                database=database
547            )
548        else:
549            raise ValueError(
550                f'Database has to be one of the following values: '
551                f'{", ".join(information_to_gather["database"]["possible_values"])},'
552                f' Got: {self._database}'
553                )
554
555    async def _run_on_database_async(self,
556                                     host: str = None,
557                                     port: str = None,
558                                     user: str = None,
559                                     password: str = None,
560                                     database: str = None) -> pd.DataFrame:
561        """
562        This method runs the query on the database asynchronously.
563        The method checks on which database the query is run and then runs
564        the query on the database.
565
566        This method raises a ValueError if the database is not one of the
567        possible values (clickhouse or postgres).
568
569        Args:
570            host (str): Host of the database.
571            port (str): Port of the database.
572            user (str): User of the database.
573            password (str): Password of the database.
574            database (str): Database name.
575
576        Returns:
577            pd.DataFrame: Results of the query (can be empty if no results were fetched).
578        """
579        if self._database == 'clickhouse':
580            return await asyncio.to_thread(
581                self._run_clickhouse_query,
582                self._query,
583                host,
584                port,
585                user,
586                password,
587                database
588                )
589        elif self._database == 'postgres':
590            return await asyncio.to_thread(
591                self._run_postgres_query,
592                self._query,
593                host,
594                port,
595                user,
596                password,
597                database
598                )
599        else:
600            raise ValueError(
601                f'Database has to be one of the following values: '
602                f'{", ".join(information_to_gather["database"]["possible_values"])},'
603                f' Got: {self._database}'
604                )
605
606    def _check_output_and_result_column_match(self,
607                                             results: pd.DataFrame) -> None:
608        """
609        This method checks if the output columns match with the columns in
610        the results.
611
612        Args:
613            results (pd.DataFrame): Results of the query.
614        """
615        if results.columns.tolist() != list(self._output.keys()):
616            raise ValueError(
617                f'Output columns do not match. [Query name: {self._name}]'
618                f'Expected: {list(self._output.keys())}, '
619                f'Got: {results.columns.tolist()}'
620                )
621
622    def run_query(self,
623                  host: str = None,
624                  port: str = None,
625                  user: str = None,
626                  password: str = None,
627                  database: str = None,
628                  **kwargs) -> pd.DataFrame:
629        """
630        This method runs the query on the database.
631        The method prepares the query and then runs the query on the database.
632
633        The method raises a ValueError if the output columns do not match with
634        the columns in the results.
635
636        If the output is not defined, the method will return an empty
637        DataFrame.
638        If output is empty but the results are fetched, the method will raise a
639        UserWarning.
640
641        Args:
642            host (str): Host of the database.
643            port (str): Port of the database.
644            user (str): User of the database.
645            password (str): Password of the database.
646            database (str): Database name.
647            **kwargs: input parameters for the query
648
649        Returns:
650            pd.DataFrame: Results of the query (can be empty if no results were fetched).
651        """
652        # prepare the query
653        self._prepare_query(**kwargs)
654        # run the query on the database
655        res = self._run_on_database(
656            host=host,
657            port=port,
658            user=user,
659            password=password,
660            database=database
661        )
662        if len(self._output) == 0:
663            self._result = res
664            return self._result
665
666        # check if the output columns match with the columns in the results
667        self._check_output_and_result_column_match(res)
668
669        self._result = res
670        return self._result
671
672    async def run_query_async(self,
673                              semaphore: asyncio.Semaphore = None,
674                              host: str = None,
675                              port: str = None,
676                              user: str = None,
677                              password: str = None,
678                              database: str = None,
679                              **kwargs) -> pd.DataFrame:
680        """
681        This method runs the query on the database asynchronously.
682        The method prepares the query and then runs the query on the database.
683
684        The method raises a ValueError if the output columns do not match with
685        the columns in the results.
686
687        > **It is important to have in mind that each call of this method
688        creates a separate connection to the database. Too much of open
689        connections to the database can result in Exceptions. So the use of
690        `Semaphores` is recommended.**
691
692        > **The use of `Semaphores` is recommended when running multiple
693        queries asynchronously.**
694
695        Args:
696            semaphore (asyncio.Semaphore): Semaphore for running the query asynchronously.
697            host (str): Host of the database.
698            port (str): Port of the database.
699            user (str): User of the database.
700            password (str): Password of the database.
701            database (str): Database name.
702            **kwargs: input parameters for the query
703
704        Returns:
705            pd.DataFrame: Results of the query (can be empty if no results were fetched).
706        """
707        # prepare the query
708        self._prepare_query(**kwargs)
709
710        # check if semaphore is provided
711        if semaphore is not None:
712            # run the query on the database asynchronously
713            async with semaphore:
714                results = await self._run_on_database_async(
715                    host=host,
716                    port=port,
717                    user=user,
718                    password=password,
719                    database=database
720                )
721        else:
722            results = await self._run_on_database_async(
723                host=host,
724                port=port,
725                user=user,
726                password=password,
727                database=database
728            )
729
730        # check if lock is free in order to save results
731        async with self._lock:
732            self._result = results
733
734        if len(self._output) == 0:
735            return self._result
736
737        # check if the output columns match with the columns in the results
738        self._check_output_and_result_column_match(results)
739
740        return self._result
741
742    def get_results(self) -> pd.DataFrame:
743        """
744        Returns the results of the query.
745
746        Returns:
747            pd.DataFrame: Results of the query (can be empty if no results were fetched).
748        """
749        # return the results
750        return self._result
751
752    async def get_results_async(self) -> pd.DataFrame:
753        """
754        Returns the results of the query asynchronously.
755
756        Returns:
757            pd.DataFrame: Results of the query (can be empty if no results were fetched).
758        """
759        # return the results, be careful to use the lock
760        async with self._lock:
761            return self._result
762
763    def get_name(self) -> str:
764        """
765        Returns the name of the query.
766
767        Returns:
768            str: Name of the query.
769        """
770        return self._name
771
772    def get_database(self) -> str:
773        """
774        Returns the database on which the query is run.
775
776        Returns:
777            str: Database on which the query is run.
778        """
779        return self._database
780
781    def get_input_parameters(self) -> dict[str, str] | None:
782        """
783        Returns the input parameters of the query.
784        This method returns the input parameters in the following format:
785        ```
786        {
787            <input_name>: <input_description>
788        }
789        ```
790
791        Input parameters can be empty, so method can return None.
792
793        Returns:
794            dict | None: Input parameters of the query.
795        """
796        return self._input_parameters
797
798    def get_date(self) -> datetime.datetime:
799        """
800        Returns the date when the query was created.
801
802        Returns:
803            datetime.datetime: Date when the query was created.
804        """
805        return self._date
806
807    def __hash__(self) -> int:
808        """
809        This method returns the hash of the Query object.
810
811        Returns:
812            int: Hash of the Query object.
813        """
814        return hash(str(self))
815
816    def __eq__(self, other) -> bool:
817        """
818        This method checks if the Query object is equal to the other Query
819        object.
820
821        Args:
822            other: Query or other object to compare with.
823
824        Returns:
825            bool: True if the Query objects are equal, False otherwise.
826        """
827        if not isinstance(other, Query):
828            return False
829
830        return str(self) == str(other)
831
832    def __ne__(self, other) -> bool:
833        """
834        This method checks if the Query object is not equal to the other Query
835        object.
836
837        Args:
838            other: Query or other object to compare with.
839
840        Returns:
841            bool: True if the Query objects are not equal, False otherwise.
842        """
843        return not self.__eq__(other)
844
845    def get_query(self) -> str:
846        """
847        Returns the query.
848
849        Returns:
850            str: Query.
851        """
852        return self._query

Query( query: str, name: str, author: str, database: str, date: str, output: dict = None, input_parameters: dict = None, depends: dict = None, other: dict = None) View Source

160    def __init__(
161            self,
162            query: str,
163            name: str,
164            author: str,
165            database: str,
166            date: str,
167            output: dict = None,
168            input_parameters: dict = None,
169            depends: dict = None,
170            other: dict = None
171            ):
172        """
173        This class represents a query that can be run on a database.
174        The query can be run on ClickHouse or Postgres database.
175
176        This class is written for fast execution of queries because of use
177        of asyncio and threading.
178
179        The main idea is that the query is **prepared only once** and then
180        it is
181        run on the database. The query can be run synchronously or
182        asynchronously.
183
184        The query can have **input parameters** that are replaced in the query
185        before running the query.
186
187        The query can have **output** that is checked if it matches the
188        columns in the results.
189
190        Other information that can be used in the child classes and use cases
191        can be set using the `other` parameter.
192
193        The query can have **dependencies** on other queries. The
194        dependencies are
195        used for other libraries and are not used in this library.
196
197        Args:
198            name (str): name of the query
199            author (str): author of the query
200            database (str): database on which the query is run
201            date (str): date when the query was created
202            output (dict): output of the query in format: {'<name>': '<description>'}
203            input_parameters (dict): input parameters of the query (default: None), for example: {'<name>': '<description>'}
204            depends (dict): dependencies of the query (default: None) in format: {'<name>': <description>}
205            other (dict): other information about the query (default: None)
206        """
207        self._query: str = query
208        self._original_query: str = query
209        self._name: str = name
210        self._author: str = author
211        self._database: str = database
212        self._output: dict = output
213        self._other: dict = other
214
215        if self._output is None:
216            self._output = dict()
217
218        self._date: datetime.datetime = datetime.datetime.strptime(
219            date,
220            '%Y-%m-%d'
221        )
222        self._input_parameters: dict = input_parameters
223        self._depends: dict = depends
224
225        self._flag_prepare_query = False
226        self._result: pd.DataFrame = None
227        self._lock = asyncio.Lock()
228
229        if self._database not in information_to_gather['database'][
230            'possible_values']:
231            raise ValueError(
232                f'Database has to be one of the following values: '
233                f'{", ".join(information_to_gather["database"]["possible_values"])},'
234                f' Got: {self._database}'
235                )

This class represents a query that can be run on a database. The query can be run on ClickHouse or Postgres database.

This class is written for fast execution of queries because of use of asyncio and threading.

The main idea is that the query is prepared only once and then it is run on the database. The query can be run synchronously or asynchronously.

The query can have input parameters that are replaced in the query before running the query.

The query can have output that is checked if it matches the columns in the results.

Other information that can be used in the child classes and use cases can be set using the other parameter.

The query can have dependencies on other queries. The dependencies are used for other libraries and are not used in this library.

Arguments:

name (str): name of the query
author (str): author of the query
database (str): database on which the query is run
date (str): date when the query was created
output (dict): output of the query in format: {'': ''}
input_parameters (dict): input parameters of the query (default: None), for example: {'': ''}
depends (dict): dependencies of the query (default: None) in format: {'': }
other (dict): other information about the query (default: None)

def number_of_dependent(self) -> int: View Source

237    def number_of_dependent(self) -> int:
238        """
239        This method returns the number of dependent queries.
240
241        Returns:
242            int: Number of dependent queries.
243        """
244        if self._depends is None:
245            return 0
246
247        return len(self._depends)

This method returns the number of dependent queries.

Returns:

int: Number of dependent queries.

def get_dependent(self) -> list[str]: View Source

249    def get_dependent(self) -> list[str]:
250        """
251        This method returns the dependent queries names.
252
253        Will return an empty list if there are no dependent queries.
254
255        Returns:
256            list[str]: List of names of dependent queries.
257        """
258        if self._depends is None:
259            return []
260
261        return list(self._depends.keys())

This method returns the dependent queries names.

Will return an empty list if there are no dependent queries.

Returns:

list[str]: List of names of dependent queries.

def get_output(self) -> list[str]: View Source

263    def get_output(self) -> list[str]:
264        """
265        This method returns the list of output column names.
266
267        Returns:
268            list[str]: List of output column names .
269        """
270        return list(self._output.keys())

This method returns the list of output column names.

Returns:

list[str]: List of output column names .

def get_documentation_other(self) -> dict: View Source

320    def get_documentation_other(self) -> dict:
321        """
322        This method returns the other information about the query from
323        documentation.
324
325        Returns:
326            dict: Other information about the query
327        """
328        return self._other

This method returns the other information about the query from documentation.

Returns:

dict: Other information about the query

def run_query( self, host: str = None, port: str = None, user: str = None, password: str = None, database: str = None, **kwargs) -> pandas.core.frame.DataFrame: View Source

622    def run_query(self,
623                  host: str = None,
624                  port: str = None,
625                  user: str = None,
626                  password: str = None,
627                  database: str = None,
628                  **kwargs) -> pd.DataFrame:
629        """
630        This method runs the query on the database.
631        The method prepares the query and then runs the query on the database.
632
633        The method raises a ValueError if the output columns do not match with
634        the columns in the results.
635
636        If the output is not defined, the method will return an empty
637        DataFrame.
638        If output is empty but the results are fetched, the method will raise a
639        UserWarning.
640
641        Args:
642            host (str): Host of the database.
643            port (str): Port of the database.
644            user (str): User of the database.
645            password (str): Password of the database.
646            database (str): Database name.
647            **kwargs: input parameters for the query
648
649        Returns:
650            pd.DataFrame: Results of the query (can be empty if no results were fetched).
651        """
652        # prepare the query
653        self._prepare_query(**kwargs)
654        # run the query on the database
655        res = self._run_on_database(
656            host=host,
657            port=port,
658            user=user,
659            password=password,
660            database=database
661        )
662        if len(self._output) == 0:
663            self._result = res
664            return self._result
665
666        # check if the output columns match with the columns in the results
667        self._check_output_and_result_column_match(res)
668
669        self._result = res
670        return self._result

This method runs the query on the database. The method prepares the query and then runs the query on the database.

The method raises a ValueError if the output columns do not match with the columns in the results.

If the output is not defined, the method will return an empty DataFrame. If output is empty but the results are fetched, the method will raise a UserWarning.

Arguments:

host (str): Host of the database.
port (str): Port of the database.
user (str): User of the database.
password (str): Password of the database.
database (str): Database name.
**kwargs: input parameters for the query

Returns:

pd.DataFrame: Results of the query (can be empty if no results were fetched).

async def run_query_async( self, semaphore: asyncio.locks.Semaphore = None, host: str = None, port: str = None, user: str = None, password: str = None, database: str = None, **kwargs) -> pandas.core.frame.DataFrame: View Source

672    async def run_query_async(self,
673                              semaphore: asyncio.Semaphore = None,
674                              host: str = None,
675                              port: str = None,
676                              user: str = None,
677                              password: str = None,
678                              database: str = None,
679                              **kwargs) -> pd.DataFrame:
680        """
681        This method runs the query on the database asynchronously.
682        The method prepares the query and then runs the query on the database.
683
684        The method raises a ValueError if the output columns do not match with
685        the columns in the results.
686
687        > **It is important to have in mind that each call of this method
688        creates a separate connection to the database. Too much of open
689        connections to the database can result in Exceptions. So the use of
690        `Semaphores` is recommended.**
691
692        > **The use of `Semaphores` is recommended when running multiple
693        queries asynchronously.**
694
695        Args:
696            semaphore (asyncio.Semaphore): Semaphore for running the query asynchronously.
697            host (str): Host of the database.
698            port (str): Port of the database.
699            user (str): User of the database.
700            password (str): Password of the database.
701            database (str): Database name.
702            **kwargs: input parameters for the query
703
704        Returns:
705            pd.DataFrame: Results of the query (can be empty if no results were fetched).
706        """
707        # prepare the query
708        self._prepare_query(**kwargs)
709
710        # check if semaphore is provided
711        if semaphore is not None:
712            # run the query on the database asynchronously
713            async with semaphore:
714                results = await self._run_on_database_async(
715                    host=host,
716                    port=port,
717                    user=user,
718                    password=password,
719                    database=database
720                )
721        else:
722            results = await self._run_on_database_async(
723                host=host,
724                port=port,
725                user=user,
726                password=password,
727                database=database
728            )
729
730        # check if lock is free in order to save results
731        async with self._lock:
732            self._result = results
733
734        if len(self._output) == 0:
735            return self._result
736
737        # check if the output columns match with the columns in the results
738        self._check_output_and_result_column_match(results)
739
740        return self._result

This method runs the query on the database asynchronously. The method prepares the query and then runs the query on the database.

The method raises a ValueError if the output columns do not match with the columns in the results.

It is important to have in mind that each call of this method creates a separate connection to the database. Too much of open connections to the database can result in Exceptions. So the use of Semaphores is recommended.

The use of Semaphores is recommended when running multiple queries asynchronously.

Arguments:

semaphore (asyncio.Semaphore): Semaphore for running the query asynchronously.
host (str): Host of the database.
port (str): Port of the database.
user (str): User of the database.
password (str): Password of the database.
database (str): Database name.
**kwargs: input parameters for the query

Returns:

pd.DataFrame: Results of the query (can be empty if no results were fetched).

def get_results(self) -> pandas.core.frame.DataFrame: View Source

742    def get_results(self) -> pd.DataFrame:
743        """
744        Returns the results of the query.
745
746        Returns:
747            pd.DataFrame: Results of the query (can be empty if no results were fetched).
748        """
749        # return the results
750        return self._result

Returns the results of the query.

Returns:

pd.DataFrame: Results of the query (can be empty if no results were fetched).

async def get_results_async(self) -> pandas.core.frame.DataFrame: View Source

752    async def get_results_async(self) -> pd.DataFrame:
753        """
754        Returns the results of the query asynchronously.
755
756        Returns:
757            pd.DataFrame: Results of the query (can be empty if no results were fetched).
758        """
759        # return the results, be careful to use the lock
760        async with self._lock:
761            return self._result

Returns the results of the query asynchronously.

Returns:

pd.DataFrame: Results of the query (can be empty if no results were fetched).

def get_name(self) -> str: View Source

763    def get_name(self) -> str:
764        """
765        Returns the name of the query.
766
767        Returns:
768            str: Name of the query.
769        """
770        return self._name

Returns the name of the query.

Returns:

str: Name of the query.

def get_database(self) -> str: View Source

772    def get_database(self) -> str:
773        """
774        Returns the database on which the query is run.
775
776        Returns:
777            str: Database on which the query is run.
778        """
779        return self._database

Returns the database on which the query is run.

Returns:

str: Database on which the query is run.

def get_input_parameters(self) -> dict[str, str] | None: View Source

781    def get_input_parameters(self) -> dict[str, str] | None:
782        """
783        Returns the input parameters of the query.
784        This method returns the input parameters in the following format:
785        ```
786        {
787            <input_name>: <input_description>
788        }
789        ```
790
791        Input parameters can be empty, so method can return None.
792
793        Returns:
794            dict | None: Input parameters of the query.
795        """
796        return self._input_parameters

Returns the input parameters of the query. This method returns the input parameters in the following format:

{
    <input_name>: <input_description>
}

Input parameters can be empty, so method can return None.

Returns:

dict | None: Input parameters of the query.

def get_date(self) -> datetime.datetime: View Source

798    def get_date(self) -> datetime.datetime:
799        """
800        Returns the date when the query was created.
801
802        Returns:
803            datetime.datetime: Date when the query was created.
804        """
805        return self._date

Returns the date when the query was created.

Returns:

datetime.datetime: Date when the query was created.

def get_query(self) -> str: View Source

845    def get_query(self) -> str:
846        """
847        Returns the query.
848
849        Returns:
850            str: Query.
851        """
852        return self._query

Returns the query.

Returns:

str: Query.

def get_format(query) -> Query: View Source

 855def get_format(query) -> Query:
 856    """
 857    This function extracts the information from the query and returns
 858    the Query object.
 859
 860    The following rules must apply to the query:
 861    - The query must be in the following format:
 862    ```
 863    /* <parameters> */
 864    <query>
 865    ```
 866    - The query must contain the following parameters:
 867        - name - name of the query
 868        - author - author of the query
 869        - date - date when the query was created
 870        - database - database on which the query is run (clickhouse or
 871        postgres)
 872        - output - output of the query
 873    - The query can contain the following parameters:
 874        - input_parameters - input parameters of the query
 875        - depends - dependencies of the query (implementation used in other
 876        libraries)
 877        - other - contains the list of entires in the following
 878        format:
 879        ```
 880        <env_name>:
 881        -----------
 882        - <anything>
 883        - <anything>
 884        ```
 885
 886    Template for the query:
 887    ```sql
 888    /*
 889     * ============================================================================
 890     * SQL Query: <query name>
 891     * ============================================================================
 892     *
 893     * Author: <query author>
 894     * Date: <date in format %Y-%m-%d>
 895     * Database: <database name, clickhouse or postgres>
 896     *
 897     *
 898     * Input Parameters:
 899     * -----------------
 900     * - :<input name>: <input description>
 901     *
 902     * Output:
 903     * -------
 904     * - <output column>: <description of output column>
 905     *
 906     * Depends:
 907     * -------
 908     * - <table depend on name>: <table depends on description>
 909     *
 910     *
 911     *
 912     * <any name other then specified in this example>:
 913     * -------
 914     * - <value>
 915     * - <value>
 916     *
 917     * ============================================================================
 918     */
 919    SELECT *
 920    FROM :<input name>;
 921    ```
 922
 923    ## BE CAREFUL!
 924
 925    > If you have anywhere **input parameter prefix in a query**, that prefix
 926    > will be replaced with input variable **value**.
 927    > To be specific, every part of query that has the following format:
 928    > `:<input_variable_name>...` will be replaced with:
 929    > `<input_variable_value>...`.
 930
 931    > Dynamic querying of a source database is **only possible in clickhouse**,
 932    > because Postgres must reference one database on initialization,
 933    > we can’t choose the database after we initialized the object.
 934    > That means that you can use the following format in a query only
 935    > if you are using clickhouse:
 936
 937    ```sql
 938    /* ... */
 939    SELECT *
 940    FROM :database.table1
 941    ```
 942
 943    > It is good practice to have SQL keywords in capital letters. This
 944    > library requires you to have all capital letters on keywords defined
 945    > in `ignore_types_in_prepare_query`.
 946
 947
 948
 949    Args:
 950        query (str): Query string.
 951
 952    Returns:
 953        **Query**: Query object.
 954    """
 955    # Check if the query is in the correct BASE format
 956    if '/*' not in query or '*/' not in query:
 957        raise ValueError(
 958            f'Query is not properly formatted. '
 959            f'Please use the following format: '
 960            f'/* <parameters> */ query'
 961            )
 962
 963    # Extract the information from the query
 964    description_and_parameters = query[query.find('/*'):query.find('*/') + 2]
 965    query = query[query.find('*/') + 2:]
 966
 967    # Check if the query is empty
 968    if len(query) == 0:
 969        raise ValueError('Query is empty.')
 970
 971    # Check if the query has only one command
 972    if query.count(';') > 1:
 973        raise ValueError('Query can have only one command.')
 974
 975    constructor_information = dict()
 976    # Extract the information from the query
 977    for parameter_name, config in information_to_gather.items():
 978        # Check if the parameter is in the query
 979        tmp_pattern = re.compile(config['regex']['regex'], flags=re.MULTILINE)
 980        got = tmp_pattern.findall(
 981            string=description_and_parameters,
 982        )
 983
 984        # Check if the parameter is required
 985        if ((len(got) == 0) and (config['required'])):
 986            #  Check if the parameter is output and is empty
 987            if parameter_name == 'output':
 988                tmp_pattern = re.compile(
 989                    r'\s\*\sOutput:\n\s\*\s-+\n'
 990                )
 991                g = tmp_pattern.search(
 992                    string=description_and_parameters
 993                )
 994                # warn that parameter output is empty
 995                if g is not None:
 996                    warnings.warn(f"Output is empty. [QUERY: ({description_and_parameters[:300].replace('\n', ' ')})]")
 997                    continue
 998
 999            # raise error that parameter is required
1000            raise ValueError(f'Parameter {parameter_name} is required.')
1001
1002        # Check if the parameter is empty (and not required), just skip it
1003        if len(got) == 0:
1004            continue
1005
1006        if parameter_name == 'other':
1007            documentation_names = set([information_to_gather[key]['documentation_name'] for
1008                                       key in
1009                                       constructor_information.keys()])
1010            other_information = dict()
1011            for section in got:
1012                if section[1] in documentation_names:
1013                    continue
1014
1015                other_information[section[1]] = list(
1016                    filter(
1017                        lambda y: len(y) > 0,
1018                        map(lambda x: x.strip(), section[config['regex']['group_index']].split(' *'))
1019                    )
1020                )
1021            constructor_information[parameter_name] = other_information
1022            continue
1023
1024        # check if the parameter is in tuple
1025        if isinstance(got[0], tuple):
1026            # select the first element
1027            got = got[0]
1028
1029        # select the element defined in config
1030        got = got[config['regex']['group_index']]
1031
1032        # check if the parameter is empty, but required
1033        if len(got.strip()) == 0 and config['required']:
1034            raise ValueError(f'Parameter {parameter_name} is empty and'
1035                             f' required.')
1036        # check if the parameter is empty, but not required
1037        elif len(got.strip()) == 0:
1038            raise ValueError(f'Parameter {parameter_name} is empty.')
1039
1040        # check if input represents a list
1041        if ' * - ' in got:
1042            # isolate list to dictionary
1043            got = list(
1044                filter(
1045                    lambda y: len(y) > 0,
1046                    map(lambda x: x.strip(), got.split(' * - '))
1047                    )
1048                )
1049            got = dict(map(lambda x: x.split(': '), got))
1050        elif ' *' in got:
1051            got = got.replace(' *', '').strip()
1052
1053        # check if the parameter is a string
1054        # and remove the `\s` from the string beginning and the end
1055        if isinstance(got, str):
1056            got = got.strip()
1057
1058        # check if the parameter has to be one of the possible values
1059        if 'possible_values' in config:
1060            if got not in config['possible_values']:
1061                raise ValueError(
1062                    f'Parameter {parameter_name} '
1063                    f'has to be one of the following values: '
1064                    f'{", ".join(config["possible_values"])},'
1065                    f' Got: {got}'
1066                    )
1067
1068        constructor_information[parameter_name] = got
1069
1070    # return the Query object
1071    return Query(query=query, **constructor_information)

This function extracts the information from the query and returns the Query object.

The following rules must apply to the query:

The query must be in the following format:

/* <parameters> */
<query>

The query must contain the following parameters:
- name - name of the query
- author - author of the query
- date - date when the query was created
- database - database on which the query is run (clickhouse or postgres)
- output - output of the query
The query can contain the following parameters:
- input_parameters - input parameters of the query
- depends - dependencies of the query (implementation used in other libraries)
- other - contains the list of entires in the following format:
```
<env_name>:
-----------
- <anything>
- <anything>
```

Template for the query:

/*
 * ============================================================================
 * SQL Query: <query name>
 * ============================================================================
 *
 * Author: <query author>
 * Date: <date in format %Y-%m-%d>
 * Database: <database name, clickhouse or postgres>
 *
 *
 * Input Parameters:
 * -----------------
 * - :<input name>: <input description>
 *
 * Output:
 * -------
 * - <output column>: <description of output column>
 *
 * Depends:
 * -------
 * - <table depend on name>: <table depends on description>
 *
 *
 *
 * <any name other then specified in this example>:
 * -------
 * - <value>
 * - <value>
 *
 * ============================================================================
 */
SELECT *
FROM :<input name>;

BE CAREFUL!

If you have anywhere input parameter prefix in a query, that prefix will be replaced with input variable value. To be specific, every part of query that has the following format: :<input_variable_name>... will be replaced with: <input_variable_value>....

Dynamic querying of a source database is only possible in clickhouse, because Postgres must reference one database on initialization, we can’t choose the database after we initialized the object. That means that you can use the following format in a query only if you are using clickhouse:

/* ... */
SELECT *
FROM :database.table1

It is good practice to have SQL keywords in capital letters. This library requires you to have all capital letters on keywords defined in ignore_types_in_prepare_query.

Arguments:

query (str): Query string.

Returns:

Query: Query object.

class Queries: View Source

1074class Queries:
1075    """
1076    This class represents a collection of queries.
1077    The queries can be run synchronously or asynchronously.
1078
1079    You can select `Query` or subset of `Query` in `Queries` by using
1080    `__getitem__` method.
1081
1082    If the given ite, is an integer, the method returns the `Query` object (
1083    based on position in the collection).
1084    If the item is a slice, the method returns the `Queries` object with
1085    the subset of queries.
1086    If the item is a string, the method returns the `Query` object with
1087    the name of the query.
1088
1089    You can also provide slices with integers or datetime.datetime objects.
1090
1091    > In the case of slices, you will get the subset of queries based on
1092    > the position in the collection or based on the date of the query.
1093    > Resulting `Queries` object will have the same order of queries as in
1094    > the original collection also it will be a copy of the original
1095
1096    For example:
1097    ```python
1098    from queries.query import Queries
1099
1100    queries = Queries('path_to_file')
1101    query = queries[0]
1102    subset = queries[1:3]
1103    query = queries['query_name']
1104    query = queries[datetime.datetime(2021, 1, 1):datetime.datetime(2021, 1, 2)]
1105    ```
1106
1107    Another good thing about this library is that you can use addition to
1108    combine multiple Queries objects and get one.
1109
1110    ```python
1111    from queries.query import Queries
1112
1113    queries1 = Queries('path_to_file1')
1114    queries2 = Queries('path_to_file2')
1115    queries = queries1 + queries2
1116    ```
1117
1118    As you can see, you can use the `+` operator to combine multiple
1119    `Queries` objects, but you can also use the `+=` operator to add
1120    multiple `Queries` objects to the original `Queries` object.
1121
1122    ```python
1123    from queries.query import Queries
1124
1125    queries = Queries('path_to_file1')
1126    queries += Queries('path_to_file2')
1127    ```
1128
1129    **The order of the output `Queries` is not specified,** and after
1130    calling this method path is set to `None`. For more information look
1131    at `__add__` method.
1132
1133    ---
1134
1135    You can also subtract `Queries` objects from the original `Queries`
1136    object. The result is the `Queries` object with the queries that are
1137    in the original `Queries` object but not in the subtracted `Queries`
1138    object.
1139
1140    ```python
1141    from queries.query import Queries
1142
1143    queries = Queries('path_to_file1')
1144    queries -= Queries('path_to_file2')
1145    ```
1146
1147    You can also use the `-` to subtract `Query` objects from the original
1148    `Queries` object. The result is the `Queries` object with the queries
1149    that are in the original `Queries` object but not in the subtracted
1150    `Query` object.
1151
1152    ```python
1153    from queries.query import Queries
1154
1155    queries = Queries('path_to_file1')
1156    queries -= queries[0]
1157    ```
1158
1159    For more information look at `__sub__` method.
1160    """
1161    def __init__(self,
1162                 source: str | list[str] | Query | list[Query],
1163                 n_workers: int = None,
1164                 env_config_file: str = None):
1165        """
1166        This class represents a collection of queries.
1167        The queries can be run synchronously or asynchronously.
1168
1169        Constructor recieves the path to the file or directory with queries.
1170        The queries are loaded from the file or directory and stored in the
1171        dictionary.
1172
1173        If the path is a directory, the class will load all `.sql` files from
1174        the directory.
1175
1176        If path is not existing, the class will raise a `ValueError`.
1177        If the file is not a SQL file (doesn't end with `.sql`), object
1178        raises a `ValueError`.
1179
1180        If the file is empty, the method raises a `ValueError`.
1181        If there are multiple queries with the same name detected, the method
1182        raises a `ValueError`.
1183
1184        You can pass a path to the config file with different environment
1185        variables. The config file has to be a JSON file.
1186
1187        > Later you can define running environment from config file based on
1188        the parameter you send when calling a method that runs a query.
1189
1190        Args:
1191            source (str | list[str] | Query | list[Query]): path to the file or directory with queries or list of paths to the files or directories or Query object or list of Query objects
1192            n_workers (int): number of workers for running queries in parallel
1193            env_config_file (str): path to the config file with different environment variables
1194        """
1195        self._queries: dict[str, Query] = dict()
1196        self.path = None
1197        """Path to the file or directory with queries."""
1198
1199        self.env_config_file = env_config_file
1200        """Path to the config file with different environment variables."""
1201        self.env_configuration = None
1202        """Configuration from the env config file."""
1203
1204        # check if the config file exists
1205        if self.env_config_file is not None:
1206            if not self.env_config_file.endswith('.json'):
1207                raise ValueError('Config file has to be a JSON file.')
1208
1209            if not os.path.exists(self.env_config_file):
1210                raise ValueError(f'Config file {self.env_config_file} does not exist.')
1211
1212            with open(self.env_config_file, 'r') as file:
1213                self.env_configuration = json.load(file)
1214
1215        # if the source is path to the file or directory
1216        if isinstance(source, str):
1217            self._handle_one_path(source)
1218
1219        if isinstance(source, Query):
1220            self._queries[source.get_name()] = source
1221
1222        # if the source is list
1223        if isinstance(source, list):
1224            # of paths to the files or directories
1225            if all(isinstance(item, str) for item in source):
1226                for path in source:
1227                    self._handle_one_path(path)
1228            # of Query objects
1229            elif all(isinstance(item, Query) for item in source):
1230                names = [q.get_name() for q in source]
1231                if len(set(names)) != len(list(names)):
1232                    raise ValueError('There are multiple queries with the '
1233                                     'same name.')
1234                self._queries = {q.get_name(): q for q in source}
1235
1236        # check if the number of workers is provided
1237        self._semaphore = None
1238        if n_workers is not None:
1239            # create a semaphore for running queries in parallel
1240            self._semaphore = asyncio.Semaphore(n_workers)
1241            self._n_workers = n_workers
1242
1243    def _handle_one_path(self, path: str) -> None:
1244        """
1245        This method handles the path to the file or directory with queries.
1246        The method loads the queries from the file or directory.
1247
1248        If path is not existing, the method raises a ValueError.
1249
1250        Args:
1251            path (str): path to the file or directory with queries
1252        """
1253        # check if the path exists
1254        if not os.path.exists(path):
1255            raise ValueError(f'Path {path} does not exist.')
1256        self.path = path
1257
1258        # load the queries from the file or directory
1259        if os.path.isdir(path):
1260            for file in self._walk():
1261                self._create_queries(file)
1262        else:
1263            self._create_queries(path)
1264
1265    def _create_queries(self, path: str) -> None:
1266        """
1267        This method creates the queries from the file.
1268        If the file is not a SQL file (doesn't end with `.sql`), the method
1269        raises a ValueError.
1270
1271        If the file is empty, the method raises a ValueError.
1272        If there are multiple queries with the same name detected, the method
1273        raises a ValueError.
1274
1275        Args:
1276            path (str): path to the file with queries
1277        """
1278        if not path.endswith('.sql'):
1279            raise ValueError(f'File {path} is not a SQL file.')
1280
1281        with open(path, 'r') as file:
1282            file_text = file.read()
1283
1284            if len(file_text) == 0:
1285                raise ValueError(f'File {path} is empty.')
1286
1287            if len(file_text.strip()) == 0:
1288                raise ValueError(f'File {path} is empty.')
1289
1290            queries = list(
1291                filter(
1292                    lambda x: len(x) > 0,
1293                    re.split(
1294                        r'\n\s*\n',
1295                        file_text
1296                    )
1297                )
1298            )
1299
1300            for query in queries:
1301                try:
1302                    query_obj = get_format(query)
1303
1304                    if query_obj.get_name() in self._queries:
1305                        raise ValueError('There is already a query with the '
1306                                         'same name.')
1307
1308                    self._queries[query_obj.get_name()] = query_obj
1309                except ValueError as e:
1310                    raise ValueError(f'\n\n'
1311                                     f'[ERROR]: {e}\n[FILE]: {path}\n'
1312                                     f'[QUERY]: '
1313                                     f'{(query[:300].replace("\n", " "))}')
1314
1315    def _walk(self) -> str:
1316        """
1317        This method walks through the directory and yields the path to the
1318        `.sql` files.
1319
1320        Returns:
1321            str: Path to the `.sql` file.
1322        """
1323        for root, dirs, files in os.walk(self.path):
1324            for file in files:
1325                if file.endswith('.sql'):
1326                    yield os.path.join(root, file)
1327
1328    def _prepare_for_run_with_env(self,
1329                                  query: Query,
1330                                  env_name: str = None) -> dict[str, str | None]:
1331        """
1332        This method prepares the queries for running with the environment.
1333        The method checks if the environment name is in the configuration.
1334
1335        If the environment name is not in the configuration, the method raises
1336        a ValueError.
1337
1338        If env_name is None, the method returns dictionary of parmaeters for
1339        database connections that are `None`.
1340
1341        This method returns dictionary of parmaeters for database connection:
1342        + host
1343        + port
1344        + user
1345        + password
1346        + database
1347
1348        Args:
1349            query (Query): Query object
1350            env_name (str): name of the environment from the config file
1351        """
1352        if env_name is None:
1353            return {
1354                'host': None,
1355                'port': None,
1356                'user': None,
1357                'password': None,
1358                'database': None
1359            }
1360
1361        if env_name not in self.env_configuration:
1362            raise ValueError(f'Environment {env_name} is '
1363                             f'not in the configuration.')
1364
1365        if query.get_database() == 'clickhouse':
1366            return {
1367                'host': self.env_configuration[env_name]['CLICKHOUSE_HOST'],
1368                'port': self.env_configuration[env_name]['CLICKHOUSE_PORT'],
1369                'user': self.env_configuration[env_name]['CLICKHOUSE_USER'],
1370                'password': self.env_configuration[env_name]['CLICKHOUSE_PASSWORD'],
1371                'database': self.env_configuration[env_name]['CLICKHOUSE_DATABASE']
1372            }
1373
1374        if query.get_database() == 'postgres':
1375            return {
1376                'host': self.env_configuration[env_name]['POSTGRES_HOST'],
1377                'port': self.env_configuration[env_name]['POSTGRES_PORT'],
1378                'user': self.env_configuration[env_name]['POSTGRES_USER'],
1379                'password': self.env_configuration[env_name]['POSTGRES_PASSWORD'],
1380                'database': self.env_configuration[env_name][
1381                    'POSTGRES_DATABASE']
1382            }
1383
1384        raise ValueError(f'Database has to be one of the following values: '
1385                            f'clickhouse or postgres. Got: {query.get_database()}')
1386
1387    def run_queries(self,
1388                    env_name: str = None,
1389                    **kwargs) -> dict[str, pd.DataFrame]:
1390        """
1391        This method runs all queries in the collection, synchronously.
1392
1393        If there is an error in the query, the method raises a ValueError.
1394
1395        Example of usage:
1396        ```python
1397        from queries.query import Queries
1398
1399        queries = Queries('path_to_file')
1400        results = queries.run_queries(input_name='value')
1401        ```
1402
1403        Args:
1404            env_name (str): name of the environment from the config file
1405            **kwargs: input parameters for the queries
1406
1407        Returns:
1408            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1409        """
1410        results = dict()
1411        query_name = None
1412        try:
1413            for query_name, query in self._queries.items():
1414                results[query_name] = query.run_query(
1415                    **self._prepare_for_run_with_env(query, env_name),
1416                    **kwargs
1417                )
1418            return results
1419        except ValueError as e:
1420            raise ValueError(f'\n\n'
1421                             f'[ERROR]: {e}\n[QUERY NAME]: {query_name}')
1422
1423    async def run_queries_async(self,
1424                                env_name: str = None,
1425                                **kwargs) -> dict[str, pd.DataFrame]:
1426        """
1427        This method runs all queries in the collection, asynchronously.
1428
1429        If there is an error in the query, the method raises a ValueError.
1430
1431        Example of usage:
1432        ```python
1433        import asyncio
1434        from queries.query import Queries
1435
1436        queries = Queries('path_to_file')
1437        results = asyncio.run(queries.run_queries_async(input_name='value'))
1438        ```
1439
1440        Args:
1441            env_name (str): name of the environment from the config file
1442            **kwargs: input parameters for the queries
1443
1444        Returns:
1445            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1446        """
1447        try:
1448            res = await asyncio.gather(
1449                *[
1450                    query.run_query_async(
1451                        semaphore=self._semaphore,
1452                        **self._prepare_for_run_with_env(
1453                            query,
1454                            env_name
1455                        ),
1456                        **kwargs
1457                        )
1458                    for query in self._queries.values()
1459                ]
1460            )
1461            return {
1462                key: self._queries[key].get_results()
1463                for key in self._queries.keys()
1464            }
1465        except ValueError as e:
1466            raise ValueError(f'\n\n'
1467                             f'[ERROR]: {e}')
1468
1469    def run_query_specific(self,
1470                           query_name: str,
1471                           env_name: str = None,
1472                           **kwargs) -> pd.DataFrame:
1473        """
1474        This method runs a specific query from the collection.
1475        You need to provide the name of the query.
1476
1477        If provided query name does not exist in the collection, the method
1478        raises a ValueError.
1479
1480        Args:
1481            query_name (str): name of the query
1482            env_name (str): name of the environment from the config file
1483            **kwargs: input parameters for the query
1484
1485        Returns:
1486            pd.DataFrame: Results of the query.
1487        """
1488        if query_name not in self._queries:
1489            raise ValueError(f'Query with name {query_name} does not exist.')
1490
1491        return self._queries[query_name].run_query(
1492            **self._prepare_for_run_with_env(self._queries[query_name],
1493                                             env_name),
1494            **kwargs
1495        )
1496
1497    async def run_query_specific_async(self,
1498                                       query_name: str,
1499                                       env_name: str = None,
1500                                       **kwargs) -> pd.DataFrame:
1501        """
1502        This method runs a specific query from the collection asynchronously.
1503        You need to provide the name of the query.
1504
1505        If provided query name does not exist in the collection, the method
1506        raises a ValueError.
1507
1508        This method uses the semaphore for running the query asynchronously.
1509
1510        Args:
1511            query_name (str): name of the query
1512            env_name (str): name of the environment from the config file
1513            **kwargs: input parameters for the query
1514
1515        Returns:
1516            pd.DataFrame: Results of the query.
1517        """
1518        if query_name not in self._queries:
1519            raise ValueError(f'Query with name {query_name} does not exist.')
1520
1521        if self._semaphore is not None:
1522            async with self._semaphore:
1523                return await self._queries[query_name].run_query_async(
1524                    **self._prepare_for_run_with_env(
1525                        self._queries[query_name],
1526                        env_name
1527                    ),
1528                    **kwargs
1529                )
1530
1531        return await self._queries[query_name].run_query_async(**kwargs)
1532
1533    def run_subset_queries(self,
1534                           query_names: list[str],
1535                           env_name: str = None,
1536                           **kwargs) -> dict[str, pd.DataFrame]:
1537        """
1538        This method runs a subset of queries from the collection.
1539        You need to provide the list of query names.
1540
1541        If provided query names do not exist in the collection, the method
1542        raises a ValueError.
1543
1544        Args:
1545            query_names (list): list of query names
1546            env_name (str): name of the environment from the config file
1547            **kwargs: input parameters for the queries
1548
1549        Returns:
1550            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1551        """
1552        if not set(query_names).issubset(set(self._queries.keys())):
1553            raise ValueError(f'Query names are not subset of queries.')
1554
1555        to_ret = dict()
1556        try:
1557            for query_name in query_names:
1558                to_ret[query_name] = self.run_query_specific(
1559                    query_name,
1560                    env_name,
1561                    **kwargs
1562                )
1563            return to_ret
1564        except ValueError as e:
1565            raise ValueError(
1566                f'\n\n'
1567                f'[ERROR]: {e}\n[QUERY NAMES]: {query_names}'
1568                )
1569
1570    async def run_subset_queries_async(self,
1571                                       query_names: list[str],
1572                                       env_name: str = None,
1573                                       **kwargs) -> dict[str, pd.DataFrame]:
1574        """
1575        This method runs a subset of queries from the collection asynchronously.
1576        You need to provide the list of query names.
1577
1578        If provided query names do not exist in the collection, the method
1579        raises a ValueError.
1580
1581        Args:
1582            query_names (list): list of query names
1583            env_name (str): name of the environment from the config file
1584            **kwargs: input parameters for the queries
1585
1586        Returns:
1587            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1588        """
1589        if not set(query_names).issubset(set(self._queries.keys())):
1590            raise ValueError(f'Query names are not subset of queries.')
1591
1592        try:
1593            res = await asyncio.gather(
1594                *[
1595                    self.run_query_specific_async(
1596                        query_name,
1597                        env_name,
1598                        **kwargs
1599                    )
1600                    for query_name in query_names
1601                ]
1602            )
1603            to_return = {
1604                query_name: self._queries[query_name].get_results()
1605                for query_name in query_names
1606            }
1607            return to_return
1608        except ValueError as e:
1609            raise ValueError(
1610                f'\n\n'
1611                f'[ERROR]: {e}\n[QUERY NAMES]: {query_names}'
1612                )
1613
1614    def __getitem__(self, item: int | slice | str) -> 'Query | Queries':
1615        """
1616        This method returns the `Query` or `Queries` from the collection.
1617
1618        If the item is an integer, the method returns the `Query` object (
1619        based on position in the collection).
1620        If the item is a slice, the method returns the `Queries` object with
1621        the subset of queries.
1622        If the item is a string, the method returns the `Query` object with
1623        the name of the query.
1624
1625        You can also provide slices with integers or datetime.datetime objects.
1626
1627        > In the case of slices, you will get the subset of queries based on
1628        > the position in the collection or based on the date of the query.
1629        > Resulting `Queries` object will have the same order of queries as in
1630        > the original collection also it will be a copy of the original
1631
1632        Args:
1633            item: int, slice(int | datetime.datetime) or str
1634
1635        Returns:
1636            Query | Queries: Query or Queries object
1637        """
1638        if isinstance(item, int):
1639            return list(self._queries.values())[item]
1640
1641        if isinstance(item, slice):
1642            start = item.start
1643            end = item.stop
1644            query_subset_copy = None
1645
1646            if isinstance(start, int) or isinstance(end, int):
1647                if start is None:
1648                    start = 0
1649                if end is None:
1650                    end = len(self._queries)
1651
1652                if not isinstance(start, int) or not isinstance(end, int):
1653                    raise ValueError(
1654                        'Slice indices have to be integers. '
1655                        'Got {start} and {end}.'
1656                        )
1657
1658                query_subset_copy = copy.deepcopy(self)
1659                query_subset_copy.path = None
1660                query_subset_copy._queries = {
1661                    k: copy.deepcopy(self._queries[k]) for k in
1662                    self._queries.keys() if k in
1663                                            list(self._queries.keys())[
1664                                            start:end]
1665                }
1666
1667            if isinstance(start, datetime.datetime) or isinstance(
1668                    end,
1669                    datetime.datetime
1670                    ):
1671                if start is None:
1672                    sorted_by_datetime = sorted(
1673                        self._queries.values(),
1674                        key=lambda x: x.get_date()
1675                        )
1676                    start = sorted_by_datetime[0].get_date()
1677                if end is None:
1678                    sorted_by_datetime = sorted(
1679                        self._queries.values(),
1680                        key=lambda x: x.get_date()
1681                        )
1682                    end = sorted_by_datetime[-1].get_date()
1683
1684                if not isinstance(start, datetime.datetime) or not isinstance(end, datetime.datetime):
1685                    raise ValueError(
1686                        f'Slice indices have to be '
1687                        f'datetime.datetime. '
1688                        f'Got {start} and {end}.'
1689                    )
1690
1691                query_subset_copy = copy.deepcopy(self)
1692                query_subset_copy.path = None
1693                query_subset_copy._queries = {k: copy.deepcopy(
1694                    self._queries[k]
1695                ) for k in
1696                    self._queries.keys() if start <=
1697                                            self._queries[k].get_date() <= end
1698                }
1699
1700            return query_subset_copy
1701
1702        return self._queries[item]
1703
1704    def add(self, query: Query) -> None:
1705        """
1706        This method adds a query to the collection.
1707
1708        If the query with the same name already exists in the collection,
1709        the method raises a ValueError.
1710
1711        Args:
1712            query (Query): Query object to add to the collection.
1713        """
1714        if query.get_name() in self._queries:
1715            raise ValueError(f'Query with name {query.get_name()} already '
1716                             'exists in the collection.')
1717
1718        self.path = None
1719        self._queries[query.get_name()] = query
1720
1721    def pop(self, query: str | list[str]) -> Query | list[Query]:
1722        """
1723        This method removes the query from the collection and returns it.
1724
1725        If the query with the name does not exist in the collection, the
1726        method raises a ValueError.
1727
1728        You can provide a list of query names to remove multiple queries.
1729
1730        Args:
1731            query (str | list[str]): name of the query, or list of names
1732
1733        Returns:
1734            Query | list[Query]: Query object or list of Query objects
1735        """
1736        if not isinstance(query, list) and not isinstance(query, str):
1737            raise ValueError(f'You can provide only list or string.'
1738                             f'Got: {query}')
1739
1740        if isinstance(query, str):
1741            if query not in self._queries:
1742                raise ValueError(f'Query with name {query} does not exist.')
1743
1744            self.path = None
1745            return self._queries.pop(query)
1746
1747        queries = []
1748        for query_name in query:
1749            if query_name not in self._queries:
1750                raise ValueError(f'Query with name {query_name} does not exist.')
1751            queries.append(self._queries.pop(query_name))
1752        self.path = None
1753        return queries
1754
1755    def __setitem__(self, key, value):
1756        raise KeyError('You cannot set values in this object.')
1757
1758    def __contains__(self, item: str) -> bool:
1759        """
1760        This method checks if the query with the name exists in the collection.
1761
1762        Args:
1763            item (str): name of the query
1764
1765        Returns:
1766            bool: True if the query exists in the collection, False otherwise
1767        """
1768        return item in self._queries.keys()
1769
1770    def __str__(self) -> str:
1771        """
1772        This method returns the string representation of the Queries object.
1773        Each query is represented in the following format:
1774        ```
1775        {
1776            <query_name>: <Query object>
1777        }
1778        ```
1779
1780        Returns:
1781            str: String representation of the Queries object
1782        """
1783        return pprint.pformat(self._queries)
1784
1785    def __repr__(self):
1786        """
1787        This method returns the string representation of the Queries object.
1788        Each query is represented in the following format:
1789        ```
1790        {
1791            <query_name>: <Query object>
1792        }
1793        ```
1794
1795        Returns:
1796            str: String representation of the Queries object
1797        """
1798        return str(self)
1799
1800    def __len__(self) -> int:
1801        """
1802        This method returns the number of queries in the collection.
1803
1804        Returns:
1805            int: Number of queries in the collection.
1806        """
1807        return len(self._queries)
1808
1809    def get_queries(self) -> dict[str, Query]:
1810        """
1811        This method returns the queries in dictionary format.
1812
1813        Returns:
1814            dict[str, Query]: Dictionary with queries.
1815        """
1816        return self._queries
1817
1818    def keys(self) -> list[str]:
1819        """
1820        This method returns the names of the queries.
1821
1822        Returns:
1823            list[str]: List of names of the queries.
1824        """
1825        return list(self._queries.keys())
1826
1827    def reorder_queries(self, query_names: list[str]) -> None:
1828        """
1829        This method reorders the queries in the collection.
1830        You need to provide the list of query names in the new order.
1831
1832        Args:
1833            query_names (list): List of query names in the new order.
1834
1835        """
1836        queries = dict()
1837        for query_name in query_names:
1838            if query_name not in self._queries:
1839                raise ValueError(f'Query with name {query_name} does not exist.')
1840            queries[query_name] = self._queries[query_name]
1841        self._queries = queries
1842
1843    def __eq__(self, other):
1844        """
1845        This method checks if the Queries object is equal to the other Queries
1846        object.
1847        """
1848        if not isinstance(other, Queries):
1849            return False
1850
1851        return hash(str(self)) == hash(str(other))
1852
1853    def __hash__(self) -> int:
1854        """
1855        This method returns the hash of the Queries object.
1856
1857        Returns:
1858            int: Hash of the Queries object.
1859        """
1860        return hash(str(self))
1861
1862    def __ne__(self, other) -> bool:
1863        """
1864        This method checks if the Queries object is not equal to the other
1865        Queries object.
1866
1867        Returns:
1868            bool: True if the Queries objects are not equal, False otherwise.
1869        """
1870        return not self.__eq__(other)
1871
1872    def set_number_of_workers(self, n_workers: int) -> None:
1873        """
1874        This method sets the number of workers for running queries in parallel.
1875
1876        Args:
1877            n_workers (int): Number of workers for running queries in parallel.
1878        """
1879        if n_workers is not None:
1880            self._semaphore = asyncio.Semaphore(n_workers)
1881            self._n_workers = n_workers
1882
1883    def get_number_of_workers(self) -> int:
1884        """
1885        This method returns the number of workers for running queries
1886        in parallel.
1887
1888        Returns:
1889            int: Number of workers for running queries in parallel.
1890        """
1891        return self._n_workers
1892
1893    def __add__(self, other: 'Queries | Query') -> 'Queries':
1894        """
1895        This method adds two Queries objects together or adds a Query object
1896
1897        If the other object is not a `Queries` or `Query` object, the method
1898        raises a ValueError.
1899
1900        If there is a query with the same name in both collections:
1901        - if the query is the same, the query is added to destination
1902        collection from the source collection with a warning
1903        - if the query is different, the method raises a ValueError
1904
1905        **The order of the output `Queries` is not specified.** (*but if you
1906        add Query object to Queries object, the Query object will be added to
1907        the end of the collection*).
1908
1909        The number of workers is the same as in the source collection (the
1910        left one, but you can define it with a method:
1911        `set_number_of_workers`).
1912        The path is set to None.
1913
1914        Args:
1915            other (Queries | Query): Queries object to add to the current Queries object
1916
1917        Returns:
1918            Queries: New Queries object with queries from both collections, or if the other object is a Query object it returns Queries object with added Query object.
1919        """
1920        if not isinstance(other, Queries) and not isinstance(other, Query):
1921            raise ValueError('You can add only Queries objects.')
1922
1923        if isinstance(other, Query):
1924            queries = copy.deepcopy(self)
1925            queries.path = None
1926
1927            if other.get_name() in queries._queries:
1928                warnings.warn(f'Query {other.get_name()} already exists in '
1929                              'the collection. Query will be replaced.',
1930                              UserWarning)
1931
1932            queries._queries[other.get_name()] = other
1933            return queries
1934
1935        this_queries_names = set(self._queries.keys())
1936        other_queries_names = set(other.get_queries().keys())
1937
1938        for query_name in this_queries_names.intersection(other_queries_names):
1939            if self._queries[query_name] == other.get_queries()[query_name]:
1940                warnings.warn(f'Query {query_name} is the same in both '
1941                              'collections. It will be added from the source '
1942                              'collection to the destination collection.',
1943                              UserWarning)
1944                continue
1945            raise KeyError(f'Query {query_name} is different in both '
1946                           f'collections.')
1947
1948        queries = copy.deepcopy(self)
1949        queries.path = None
1950        queries._queries.update(other.get_queries())
1951        return queries
1952
1953    def __sub__(self, other: 'Queries | Query') -> 'Queries':
1954        """
1955        This method subtracts two Queries objects or Query object from Queries.
1956
1957        If the other object is not a `Queries` or `Query` object, the method
1958        raises a ValueError.
1959
1960        If the other object is a Query object, the method removes the query
1961        from the collection.
1962
1963        If the other object is a Queries object, the method removes queries
1964        from the collection.
1965
1966        If there is a query in the current collection that does not exist in
1967        the other collection, the method warns UserWarning (same goes when
1968        subtracting Query object).
1969
1970
1971        Args:
1972            other (Queries | Query): Queries object to subtract from the current Queries object or Query object to subtract from the current Queries object
1973
1974        Returns:
1975            Queries: New Queries object with queries from the current collection without queries from the other collection or without the Query object
1976        """
1977        if not isinstance(other, Queries) and not isinstance(other, Query):
1978            raise ValueError('You can subtract only Queries or Query object.')
1979
1980        if isinstance(other, Query):
1981            queries = copy.deepcopy(self)
1982            queries.path = None
1983
1984            if other.get_name() not in queries._queries:
1985                warnings.warn(f'Query {other.get_name()} does not exist in '
1986                              'the collection. Nothing to subtract.',
1987                              UserWarning)
1988                return queries
1989
1990            queries._queries.pop(other.get_name())
1991            return queries
1992
1993        this_queries_names = set(self._queries.keys())
1994        other_queries_names = set(other.get_queries().keys())
1995
1996        self.path = None
1997        if not this_queries_names.issuperset(other_queries_names):
1998            warnings.warn(f'Queries to subtract are not subset of '
1999                          f'queries. Differences: '
2000                          f'{other_queries_names.difference(this_queries_names)}',
2001                          UserWarning)
2002            return copy.deepcopy(self)
2003
2004        return Queries([self._queries[query] for query in
2005                        this_queries_names.difference(other_queries_names)])

This class represents a collection of queries. The queries can be run synchronously or asynchronously.

You can select Query or subset of Query in Queries by using __getitem__ method.

If the given ite, is an integer, the method returns the Query object ( based on position in the collection). If the item is a slice, the method returns the Queries object with the subset of queries. If the item is a string, the method returns the Query object with the name of the query.

You can also provide slices with integers or datetime.datetime objects.

In the case of slices, you will get the subset of queries based on the position in the collection or based on the date of the query. Resulting Queries object will have the same order of queries as in the original collection also it will be a copy of the original

For example:

from queries.query import Queries

queries = Queries('path_to_file')
query = queries[0]
subset = queries[1:3]
query = queries['query_name']
query = queries[datetime.datetime(2021, 1, 1):datetime.datetime(2021, 1, 2)]

Another good thing about this library is that you can use addition to combine multiple Queries objects and get one.

from queries.query import Queries

queries1 = Queries('path_to_file1')
queries2 = Queries('path_to_file2')
queries = queries1 + queries2

As you can see, you can use the + operator to combine multiple Queries objects, but you can also use the += operator to add multiple Queries objects to the original Queries object.

from queries.query import Queries

queries = Queries('path_to_file1')
queries += Queries('path_to_file2')

The order of the output Queries is not specified, and after calling this method path is set to None. For more information look at __add__ method.

You can also subtract Queries objects from the original Queries object. The result is the Queries object with the queries that are in the original Queries object but not in the subtracted Queries object.

from queries.query import Queries

queries = Queries('path_to_file1')
queries -= Queries('path_to_file2')

You can also use the - to subtract Query objects from the original Queries object. The result is the Queries object with the queries that are in the original Queries object but not in the subtracted Query object.

from queries.query import Queries

queries = Queries('path_to_file1')
queries -= queries[0]

For more information look at __sub__ method.

Queries( source: str | list[str] | Query | list[Query], n_workers: int = None, env_config_file: str = None) View Source

1161    def __init__(self,
1162                 source: str | list[str] | Query | list[Query],
1163                 n_workers: int = None,
1164                 env_config_file: str = None):
1165        """
1166        This class represents a collection of queries.
1167        The queries can be run synchronously or asynchronously.
1168
1169        Constructor recieves the path to the file or directory with queries.
1170        The queries are loaded from the file or directory and stored in the
1171        dictionary.
1172
1173        If the path is a directory, the class will load all `.sql` files from
1174        the directory.
1175
1176        If path is not existing, the class will raise a `ValueError`.
1177        If the file is not a SQL file (doesn't end with `.sql`), object
1178        raises a `ValueError`.
1179
1180        If the file is empty, the method raises a `ValueError`.
1181        If there are multiple queries with the same name detected, the method
1182        raises a `ValueError`.
1183
1184        You can pass a path to the config file with different environment
1185        variables. The config file has to be a JSON file.
1186
1187        > Later you can define running environment from config file based on
1188        the parameter you send when calling a method that runs a query.
1189
1190        Args:
1191            source (str | list[str] | Query | list[Query]): path to the file or directory with queries or list of paths to the files or directories or Query object or list of Query objects
1192            n_workers (int): number of workers for running queries in parallel
1193            env_config_file (str): path to the config file with different environment variables
1194        """
1195        self._queries: dict[str, Query] = dict()
1196        self.path = None
1197        """Path to the file or directory with queries."""
1198
1199        self.env_config_file = env_config_file
1200        """Path to the config file with different environment variables."""
1201        self.env_configuration = None
1202        """Configuration from the env config file."""
1203
1204        # check if the config file exists
1205        if self.env_config_file is not None:
1206            if not self.env_config_file.endswith('.json'):
1207                raise ValueError('Config file has to be a JSON file.')
1208
1209            if not os.path.exists(self.env_config_file):
1210                raise ValueError(f'Config file {self.env_config_file} does not exist.')
1211
1212            with open(self.env_config_file, 'r') as file:
1213                self.env_configuration = json.load(file)
1214
1215        # if the source is path to the file or directory
1216        if isinstance(source, str):
1217            self._handle_one_path(source)
1218
1219        if isinstance(source, Query):
1220            self._queries[source.get_name()] = source
1221
1222        # if the source is list
1223        if isinstance(source, list):
1224            # of paths to the files or directories
1225            if all(isinstance(item, str) for item in source):
1226                for path in source:
1227                    self._handle_one_path(path)
1228            # of Query objects
1229            elif all(isinstance(item, Query) for item in source):
1230                names = [q.get_name() for q in source]
1231                if len(set(names)) != len(list(names)):
1232                    raise ValueError('There are multiple queries with the '
1233                                     'same name.')
1234                self._queries = {q.get_name(): q for q in source}
1235
1236        # check if the number of workers is provided
1237        self._semaphore = None
1238        if n_workers is not None:
1239            # create a semaphore for running queries in parallel
1240            self._semaphore = asyncio.Semaphore(n_workers)
1241            self._n_workers = n_workers

This class represents a collection of queries. The queries can be run synchronously or asynchronously.

Constructor recieves the path to the file or directory with queries. The queries are loaded from the file or directory and stored in the dictionary.

If the path is a directory, the class will load all .sql files from the directory.

If path is not existing, the class will raise a ValueError. If the file is not a SQL file (doesn't end with .sql), object raises a ValueError.

If the file is empty, the method raises a ValueError. If there are multiple queries with the same name detected, the method raises a ValueError.

You can pass a path to the config file with different environment variables. The config file has to be a JSON file.

Later you can define running environment from config file based on the parameter you send when calling a method that runs a query.

Arguments:

source (str | list[str] | Query | list[Query]): path to the file or directory with queries or list of paths to the files or directories or Query object or list of Query objects
n_workers (int): number of workers for running queries in parallel
env_config_file (str): path to the config file with different environment variables

path

Path to the file or directory with queries.

env_config_file

Path to the config file with different environment variables.

env_configuration

Configuration from the env config file.

def run_queries( self, env_name: str = None, **kwargs) -> dict[str, pandas.core.frame.DataFrame]: View Source

1387    def run_queries(self,
1388                    env_name: str = None,
1389                    **kwargs) -> dict[str, pd.DataFrame]:
1390        """
1391        This method runs all queries in the collection, synchronously.
1392
1393        If there is an error in the query, the method raises a ValueError.
1394
1395        Example of usage:
1396        ```python
1397        from queries.query import Queries
1398
1399        queries = Queries('path_to_file')
1400        results = queries.run_queries(input_name='value')
1401        ```
1402
1403        Args:
1404            env_name (str): name of the environment from the config file
1405            **kwargs: input parameters for the queries
1406
1407        Returns:
1408            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1409        """
1410        results = dict()
1411        query_name = None
1412        try:
1413            for query_name, query in self._queries.items():
1414                results[query_name] = query.run_query(
1415                    **self._prepare_for_run_with_env(query, env_name),
1416                    **kwargs
1417                )
1418            return results
1419        except ValueError as e:
1420            raise ValueError(f'\n\n'
1421                             f'[ERROR]: {e}\n[QUERY NAME]: {query_name}')

This method runs all queries in the collection, synchronously.

If there is an error in the query, the method raises a ValueError.

Example of usage:

from queries.query import Queries

queries = Queries('path_to_file')
results = queries.run_queries(input_name='value')

Arguments:

env_name (str): name of the environment from the config file
**kwargs: input parameters for the queries

Returns:

dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.

async def run_queries_async( self, env_name: str = None, **kwargs) -> dict[str, pandas.core.frame.DataFrame]: View Source

1423    async def run_queries_async(self,
1424                                env_name: str = None,
1425                                **kwargs) -> dict[str, pd.DataFrame]:
1426        """
1427        This method runs all queries in the collection, asynchronously.
1428
1429        If there is an error in the query, the method raises a ValueError.
1430
1431        Example of usage:
1432        ```python
1433        import asyncio
1434        from queries.query import Queries
1435
1436        queries = Queries('path_to_file')
1437        results = asyncio.run(queries.run_queries_async(input_name='value'))
1438        ```
1439
1440        Args:
1441            env_name (str): name of the environment from the config file
1442            **kwargs: input parameters for the queries
1443
1444        Returns:
1445            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1446        """
1447        try:
1448            res = await asyncio.gather(
1449                *[
1450                    query.run_query_async(
1451                        semaphore=self._semaphore,
1452                        **self._prepare_for_run_with_env(
1453                            query,
1454                            env_name
1455                        ),
1456                        **kwargs
1457                        )
1458                    for query in self._queries.values()
1459                ]
1460            )
1461            return {
1462                key: self._queries[key].get_results()
1463                for key in self._queries.keys()
1464            }
1465        except ValueError as e:
1466            raise ValueError(f'\n\n'
1467                             f'[ERROR]: {e}')

This method runs all queries in the collection, asynchronously.

If there is an error in the query, the method raises a ValueError.

Example of usage:

import asyncio
from queries.query import Queries

queries = Queries('path_to_file')
results = asyncio.run(queries.run_queries_async(input_name='value'))

Arguments:

env_name (str): name of the environment from the config file
**kwargs: input parameters for the queries

Returns:

dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.

def run_query_specific( self, query_name: str, env_name: str = None, **kwargs) -> pandas.core.frame.DataFrame: View Source

1469    def run_query_specific(self,
1470                           query_name: str,
1471                           env_name: str = None,
1472                           **kwargs) -> pd.DataFrame:
1473        """
1474        This method runs a specific query from the collection.
1475        You need to provide the name of the query.
1476
1477        If provided query name does not exist in the collection, the method
1478        raises a ValueError.
1479
1480        Args:
1481            query_name (str): name of the query
1482            env_name (str): name of the environment from the config file
1483            **kwargs: input parameters for the query
1484
1485        Returns:
1486            pd.DataFrame: Results of the query.
1487        """
1488        if query_name not in self._queries:
1489            raise ValueError(f'Query with name {query_name} does not exist.')
1490
1491        return self._queries[query_name].run_query(
1492            **self._prepare_for_run_with_env(self._queries[query_name],
1493                                             env_name),
1494            **kwargs
1495        )

This method runs a specific query from the collection. You need to provide the name of the query.

If provided query name does not exist in the collection, the method raises a ValueError.

Arguments:

query_name (str): name of the query
env_name (str): name of the environment from the config file
**kwargs: input parameters for the query

Returns:

pd.DataFrame: Results of the query.

async def run_query_specific_async( self, query_name: str, env_name: str = None, **kwargs) -> pandas.core.frame.DataFrame: View Source

1497    async def run_query_specific_async(self,
1498                                       query_name: str,
1499                                       env_name: str = None,
1500                                       **kwargs) -> pd.DataFrame:
1501        """
1502        This method runs a specific query from the collection asynchronously.
1503        You need to provide the name of the query.
1504
1505        If provided query name does not exist in the collection, the method
1506        raises a ValueError.
1507
1508        This method uses the semaphore for running the query asynchronously.
1509
1510        Args:
1511            query_name (str): name of the query
1512            env_name (str): name of the environment from the config file
1513            **kwargs: input parameters for the query
1514
1515        Returns:
1516            pd.DataFrame: Results of the query.
1517        """
1518        if query_name not in self._queries:
1519            raise ValueError(f'Query with name {query_name} does not exist.')
1520
1521        if self._semaphore is not None:
1522            async with self._semaphore:
1523                return await self._queries[query_name].run_query_async(
1524                    **self._prepare_for_run_with_env(
1525                        self._queries[query_name],
1526                        env_name
1527                    ),
1528                    **kwargs
1529                )
1530
1531        return await self._queries[query_name].run_query_async(**kwargs)

This method runs a specific query from the collection asynchronously. You need to provide the name of the query.

If provided query name does not exist in the collection, the method raises a ValueError.

This method uses the semaphore for running the query asynchronously.

Arguments:

query_name (str): name of the query
env_name (str): name of the environment from the config file
**kwargs: input parameters for the query

Returns:

pd.DataFrame: Results of the query.

def run_subset_queries( self, query_names: list[str], env_name: str = None, **kwargs) -> dict[str, pandas.core.frame.DataFrame]: View Source

1533    def run_subset_queries(self,
1534                           query_names: list[str],
1535                           env_name: str = None,
1536                           **kwargs) -> dict[str, pd.DataFrame]:
1537        """
1538        This method runs a subset of queries from the collection.
1539        You need to provide the list of query names.
1540
1541        If provided query names do not exist in the collection, the method
1542        raises a ValueError.
1543
1544        Args:
1545            query_names (list): list of query names
1546            env_name (str): name of the environment from the config file
1547            **kwargs: input parameters for the queries
1548
1549        Returns:
1550            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1551        """
1552        if not set(query_names).issubset(set(self._queries.keys())):
1553            raise ValueError(f'Query names are not subset of queries.')
1554
1555        to_ret = dict()
1556        try:
1557            for query_name in query_names:
1558                to_ret[query_name] = self.run_query_specific(
1559                    query_name,
1560                    env_name,
1561                    **kwargs
1562                )
1563            return to_ret
1564        except ValueError as e:
1565            raise ValueError(
1566                f'\n\n'
1567                f'[ERROR]: {e}\n[QUERY NAMES]: {query_names}'
1568                )

This method runs a subset of queries from the collection. You need to provide the list of query names.

If provided query names do not exist in the collection, the method raises a ValueError.

Arguments:

query_names (list): list of query names
env_name (str): name of the environment from the config file
**kwargs: input parameters for the queries

Returns:

dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.

async def run_subset_queries_async( self, query_names: list[str], env_name: str = None, **kwargs) -> dict[str, pandas.core.frame.DataFrame]: View Source

1570    async def run_subset_queries_async(self,
1571                                       query_names: list[str],
1572                                       env_name: str = None,
1573                                       **kwargs) -> dict[str, pd.DataFrame]:
1574        """
1575        This method runs a subset of queries from the collection asynchronously.
1576        You need to provide the list of query names.
1577
1578        If provided query names do not exist in the collection, the method
1579        raises a ValueError.
1580
1581        Args:
1582            query_names (list): list of query names
1583            env_name (str): name of the environment from the config file
1584            **kwargs: input parameters for the queries
1585
1586        Returns:
1587            dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.
1588        """
1589        if not set(query_names).issubset(set(self._queries.keys())):
1590            raise ValueError(f'Query names are not subset of queries.')
1591
1592        try:
1593            res = await asyncio.gather(
1594                *[
1595                    self.run_query_specific_async(
1596                        query_name,
1597                        env_name,
1598                        **kwargs
1599                    )
1600                    for query_name in query_names
1601                ]
1602            )
1603            to_return = {
1604                query_name: self._queries[query_name].get_results()
1605                for query_name in query_names
1606            }
1607            return to_return
1608        except ValueError as e:
1609            raise ValueError(
1610                f'\n\n'
1611                f'[ERROR]: {e}\n[QUERY NAMES]: {query_names}'
1612                )

This method runs a subset of queries from the collection asynchronously. You need to provide the list of query names.

If provided query names do not exist in the collection, the method raises a ValueError.

Arguments:

query_names (list): list of query names
env_name (str): name of the environment from the config file
**kwargs: input parameters for the queries

Returns:

dict: Dictionary with the results of the queries. The key is the name of the query and the value is the result of the query.

def add(self, query: Query) -> None: View Source

1704    def add(self, query: Query) -> None:
1705        """
1706        This method adds a query to the collection.
1707
1708        If the query with the same name already exists in the collection,
1709        the method raises a ValueError.
1710
1711        Args:
1712            query (Query): Query object to add to the collection.
1713        """
1714        if query.get_name() in self._queries:
1715            raise ValueError(f'Query with name {query.get_name()} already '
1716                             'exists in the collection.')
1717
1718        self.path = None
1719        self._queries[query.get_name()] = query

This method adds a query to the collection.

If the query with the same name already exists in the collection, the method raises a ValueError.

Arguments:

query (Query): Query object to add to the collection.

def pop( self, query: str | list[str]) -> Query | list[Query]: View Source

1721    def pop(self, query: str | list[str]) -> Query | list[Query]:
1722        """
1723        This method removes the query from the collection and returns it.
1724
1725        If the query with the name does not exist in the collection, the
1726        method raises a ValueError.
1727
1728        You can provide a list of query names to remove multiple queries.
1729
1730        Args:
1731            query (str | list[str]): name of the query, or list of names
1732
1733        Returns:
1734            Query | list[Query]: Query object or list of Query objects
1735        """
1736        if not isinstance(query, list) and not isinstance(query, str):
1737            raise ValueError(f'You can provide only list or string.'
1738                             f'Got: {query}')
1739
1740        if isinstance(query, str):
1741            if query not in self._queries:
1742                raise ValueError(f'Query with name {query} does not exist.')
1743
1744            self.path = None
1745            return self._queries.pop(query)
1746
1747        queries = []
1748        for query_name in query:
1749            if query_name not in self._queries:
1750                raise ValueError(f'Query with name {query_name} does not exist.')
1751            queries.append(self._queries.pop(query_name))
1752        self.path = None
1753        return queries

This method removes the query from the collection and returns it.

If the query with the name does not exist in the collection, the method raises a ValueError.

You can provide a list of query names to remove multiple queries.

Arguments:

query (str | list[str]): name of the query, or list of names

Returns:

Query | list[Query]: Query object or list of Query objects

def get_queries(self) -> dict[str, Query]: View Source

1809    def get_queries(self) -> dict[str, Query]:
1810        """
1811        This method returns the queries in dictionary format.
1812
1813        Returns:
1814            dict[str, Query]: Dictionary with queries.
1815        """
1816        return self._queries

This method returns the queries in dictionary format.

Returns:

dict[str, Query]: Dictionary with queries.

def keys(self) -> list[str]: View Source

1818    def keys(self) -> list[str]:
1819        """
1820        This method returns the names of the queries.
1821
1822        Returns:
1823            list[str]: List of names of the queries.
1824        """
1825        return list(self._queries.keys())

This method returns the names of the queries.

Returns:

list[str]: List of names of the queries.

def reorder_queries(self, query_names: list[str]) -> None: View Source

1827    def reorder_queries(self, query_names: list[str]) -> None:
1828        """
1829        This method reorders the queries in the collection.
1830        You need to provide the list of query names in the new order.
1831
1832        Args:
1833            query_names (list): List of query names in the new order.
1834
1835        """
1836        queries = dict()
1837        for query_name in query_names:
1838            if query_name not in self._queries:
1839                raise ValueError(f'Query with name {query_name} does not exist.')
1840            queries[query_name] = self._queries[query_name]
1841        self._queries = queries

This method reorders the queries in the collection. You need to provide the list of query names in the new order.

Arguments:

query_names (list): List of query names in the new order.

def set_number_of_workers(self, n_workers: int) -> None: View Source

1872    def set_number_of_workers(self, n_workers: int) -> None:
1873        """
1874        This method sets the number of workers for running queries in parallel.
1875
1876        Args:
1877            n_workers (int): Number of workers for running queries in parallel.
1878        """
1879        if n_workers is not None:
1880            self._semaphore = asyncio.Semaphore(n_workers)
1881            self._n_workers = n_workers

This method sets the number of workers for running queries in parallel.

Arguments:

n_workers (int): Number of workers for running queries in parallel.

def get_number_of_workers(self) -> int: View Source

1883    def get_number_of_workers(self) -> int:
1884        """
1885        This method returns the number of workers for running queries
1886        in parallel.
1887
1888        Returns:
1889            int: Number of workers for running queries in parallel.
1890        """
1891        return self._n_workers

This method returns the number of workers for running queries in parallel.

Returns:

int: Number of workers for running queries in parallel.