Dynamic SQL Queries to Transform Data

Timeboxed analytical requirements often make us hardcode arguments/values in our queries. A minor code change in a complex codebase could lead to delayed deliverables.

For simplicity and easy integration options, let’s use snowflake’s Data warehouse to illustrate our problem. This example plans to show you how we can dynamically pass values to SQL queries using the snowflake python connector during run time to make our code more flexible and manageable.

_{What is Snowflake? How to Install Snowflake Connector?}

Snowflake is the modern data warehouse with on-demand storage, sophisticated analytical, and database operations at an expeditious latency. Here is the link to install the snowflake connector: https://docs.snowflake.com/en/user-guide/python-connector-install.html#step-1-install-the-connector

Let’s get straight into it and first install version-specific libraries for the connector

pip install -r https://raw.githubusercontent.com/snowflakedb/snowflake-connector-python/v2.7.6/tested_requirements/requirements_38.reqs

now run pip install to install the desired version of the snowflake connector

pip install snowflake-connector-python==

Note: the Python connector provides integration to use the pandas’ library checkout installation docs.

_{Configuring Snowflake Connection}

To start querying our data, first, we need to connect to the cloud data warehouse. In our case, we need to connect to the snowflake.
To ensure we are connecting to the database and querying desired tables by establishing a secure connection, snowflake has some guidelines.

4 out of 6 parameters are found on the home page of the Snowflake,

The declaration of the connection will be equivalent to the below python variable.
Please pass relevant values to establish a connection.

conn = snowflake.connect(
    user=sfUser,
    password=sfPwd,
    account=sf_account,
    warehouse=sf_warehouse,
    database=sf_database,
    schema=sf_schema
)

We now have established a database connection successfully. Let’s declare our cursor and start querying.

_{Define Cursor Object}

The cursor is a python class instance heavily used to invoke python SQL methods to fetch, query, and modify tables.

A cursor is created by calling the cursor() method of the connection

cur = conn.cursor()

Throughout the lifetime of a session, the cursor is bound to the provided connection specifications.

Assigning SQL Query to a Python Variable

The cursor connection will accept the SQL query in the form of a python string variable.

query = 'select column1, column2 from ' 
            'test_table WHERE {0} = {1} '

Here {0} and {1} are string interpolators allowing us to pass values dynamically during run time. Dynamic values can be substituted directly to {0} and {1} by their names, such as {column_name} and {value_holder}. But it is often beneficial to avoid call by name approach.

_{Defining configurations / dynamic values}

We declare the dynamic values we intend to pass to our SQL queries during runtime.

column_name = 'Site'
value_holder = 'Analytics_vidya'

_{Formatting SQL query using Python’s repr}

repr() is a Python built-in function that returns a printable representation of a specified object.

We will format the query and pass our dynamic values as a part of the formatting. The order of the arguments is sequential.

The first element will be at index 0 and so on.

final_query = query.format(repr(column_name), repr(value_holder))

_{Executing the final query}

The cursor object offers a method to execute our formatted queries as below.

cur.execute(final_query)

Execute method will utilize the connection details and fetch the records from the specified table that meeds the dynamic conditions.

_{Points to Note}

1. A query gets executed if we explicitly pass it to the cursor’s execute method.

cur.execute('select * from test_table')

2. We can use these dynamic calls through conditional statements. 

if condition == 'N':
        final_query = query.format('static_column_name', repr(value_holder)))
    else:
        final_query = query.format(repr(column_name), 'static_value_holder')

3. We can pass method/function arguments as dynamic values.

4. we can reuse the dynamic variables in multiple SQL statements

5. The execute method of the cursor is equipped with other operations like close(), fetchall(), executemany(), etc.

Advantages of this approach

1. This approach helps in tackling SQL injection attacks.
2. Code refactoring, Code reusability, and maintainability will increase drastically.
3. Programming language Support: cloud data warehouses like Snowflake and AWS redshift provide multiple programming language support like Python.

_{Conclusion to SQL Queries}

While building data-intensive applications, data pipelines, and Big data jobs, it is crucial to focus on efficiently designing our queries and jobs to attain dynamic essence and process our data transformations on the go.

Cloud data warehouses provide on-demand resource allocation, auto-Scaling, time travel for data recovery, and visualizations with inbuilt features like Materialized views, REGEX flavors, and flattening options to tackle complex SQL operations.

With the rise of python, we now have a python connector integration option for many popular database engines like Postgres, Cassandra, and cloud data warehouses.

The motive behind this post is to show that one does not need to run a search through the entire codebase to make a small change. Dynamically maintaining configurations and arguments separately and implementing them to your problems helps tackle countless issues during production deployment.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.