1

I'm querying my SSMS database from pandas and the query I have is pretty huge, I've saved it locally and want to read that query as a pandas dataframe and also there is a date string that I have in the query, I want to replace that datestring with a date that I've already assigned in pandas. For reference sake I'll shorten the query.

I'm currently following below:

query = """SELECT * FROM table where date > 'date_string' """

query_result = pd.read_sql(query, conn)

Instead of writing select * ... in pandas I've saved my query locally. I want pandas to read that query. And also replace date_string with startDate_sql

My date_string keeps changing as I'm looping through a list of dates.

The pandas code would look like

query = 'C:\Users\Admin\Desktop\Python\Open Source\query.sql'
query.replace(date_string, startDate_sql)
query_result = pd.read_sql(query, conn)

In this way I'm not writing my query in pandas as it is a huge query and consumes lot of space.

Can someone please tell me how to solve this and what is the correct syntax? Thank you very much!

Tukki
  • 90
  • 7

1 Answers1

1

Reading a file in Python

Here's how to read in a text file in Python.

query_filename = 'C:\Users\Admin\Desktop\Python\Open Source\query.sql'
# 'rt' means open for reading, in text mode
with open(query_filename, 'rt') as f:
    # read the query_filename file into a variable named query
    query = f.read()
# replace the literal string 'date_string' with the contents of the variable startDate_sql
query = query.replace('date_string', startDate_sql)
# get dataframe from database
query_result = pd.read_sql(query, conn)

Using parameterized queries

You should probably avoid string replacement to construct queries, because it suffers from SQL injection. Parameterized queries avoid this problem. Here's an example of how to use query parameterization with Pandas.

Nick ODell
  • 15,465
  • 3
  • 32
  • 66