Skip to content

SQL Query

SQLQueryBuilder

A builder for developing RTDIP queries using any delta table

Source code in src/sdk/python/rtdip_sdk/queries/sql/sql_query.py 
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
class SQLQueryBuilder:
    """
    A builder for developing RTDIP queries using any delta table
    """

    sql_query: dict
    connection: ConnectionInterface

    def get(
        self, connection=object, sql_query=str, to_json=False, limit=None, offset=None
    ) -> pd.DataFrame:
        """
        A function to return back raw data by querying databricks SQL Warehouse using a connection specified by the user.

        The available connectors by RTDIP are Databricks SQL Connect, PYODBC SQL Connect, TURBODBC SQL Connect.

        The available authentication methods are Certificate Authentication, Client Secret Authentication or Default Authentication. See documentation.

        This function requires the user to input a dictionary of parameters. (See Attributes table below)

        Args:
            connection (obj): Connection chosen by the user (Databricks SQL Connect, PYODBC SQL Connect, TURBODBC SQL Connect)
            sql_query (str): A string of the SQL query to be executed.
            limit (optional int): Limit the number of rows to be returned
            offset (optional int): Offset the start of the rows to be returned

        Returns:
            DataFrame: A dataframe of data.
        """
        try:
            parameters_dict = {"sql_statement": sql_query}
            parameters_dict["to_json"] = to_json
            parameters_dict["supress_warning"] = True
            if limit:
                parameters_dict["limit"] = limit
            if offset:
                parameters_dict["offset"] = offset

            query = _query_builder(parameters_dict, "sql")
            try:
                cursor = connection.cursor()
                cursor.execute(query)
                df = cursor.fetch_all()
                cursor.close()
                connection.close()
                return df
            except Exception as e:
                logging.exception("Error returning dataframe")
                raise e

        except Exception as e:
            logging.exception("error with sql query")
            raise e

get(connection=object, sql_query=str, to_json=False, limit=None, offset=None)

A function to return back raw data by querying databricks SQL Warehouse using a connection specified by the user.

The available connectors by RTDIP are Databricks SQL Connect, PYODBC SQL Connect, TURBODBC SQL Connect.

The available authentication methods are Certificate Authentication, Client Secret Authentication or Default Authentication. See documentation.

This function requires the user to input a dictionary of parameters. (See Attributes table below)

Parameters:

Name Type Description Default
connection obj

Connection chosen by the user (Databricks SQL Connect, PYODBC SQL Connect, TURBODBC SQL Connect)

 object
sql_query str

A string of the SQL query to be executed.

 str
limit optional int

Limit the number of rows to be returned

 None
offset optional int

Offset the start of the rows to be returned

 None

Returns:

Name Type Description
DataFrame DataFrame

A dataframe of data.
Source code in src/sdk/python/rtdip_sdk/queries/sql/sql_query.py 
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
def get(
    self, connection=object, sql_query=str, to_json=False, limit=None, offset=None
) -> pd.DataFrame:
    """
    A function to return back raw data by querying databricks SQL Warehouse using a connection specified by the user.

    The available connectors by RTDIP are Databricks SQL Connect, PYODBC SQL Connect, TURBODBC SQL Connect.

    The available authentication methods are Certificate Authentication, Client Secret Authentication or Default Authentication. See documentation.

    This function requires the user to input a dictionary of parameters. (See Attributes table below)

    Args:
        connection (obj): Connection chosen by the user (Databricks SQL Connect, PYODBC SQL Connect, TURBODBC SQL Connect)
        sql_query (str): A string of the SQL query to be executed.
        limit (optional int): Limit the number of rows to be returned
        offset (optional int): Offset the start of the rows to be returned

    Returns:
        DataFrame: A dataframe of data.
    """
    try:
        parameters_dict = {"sql_statement": sql_query}
        parameters_dict["to_json"] = to_json
        parameters_dict["supress_warning"] = True
        if limit:
            parameters_dict["limit"] = limit
        if offset:
            parameters_dict["offset"] = offset

        query = _query_builder(parameters_dict, "sql")
        try:
            cursor = connection.cursor()
            cursor.execute(query)
            df = cursor.fetch_all()
            cursor.close()
            connection.close()
            return df
        except Exception as e:
            logging.exception("Error returning dataframe")
            raise e

    except Exception as e:
        logging.exception("error with sql query")
        raise e