Update SQL parameter using Python

Question

I want to ask that if I can update the parameter in SQL query using python. I want to read a SQL query first, and process the SQL outputted data using python. However, I need to specify the data that I want to filter in SQL query, and I wonder if there is any way that I can update the parameter in python instead of updating the SQL query.

The SQL query is like the following:

set nocount on;

declare @pdate datetime
set @pdate = '2022-12-31'

select
  cast(L.Date as datetime) as Date
, Amount
, AccountNumber
, Property
, County
, ZipCode
, Price
, Owner

from Account.Detail L

inner join Owner.Detail M
    on L.Date = M.Date
    and L.Number = M.Number

inner join Purchase.Detail P
    on L.Date = P.Date
    and L.Purchase.Number = P.Purchase.Number

where L.Date = @pdate
    and Purchase.Number not in ('CL1', 'CL2')
    and Amount > 0

And I want to run the python code like following:

import pyodbc

server = 'my_server_name'
database = 'my_database_name'

connection = pyodbc.connect(Trusted_Connection = "yes", DRIVER = "{SQL Server}", SERVER = server, DATABASE = database)
cursor = connection.cursor()
query = open('Pathway_for_SQL_Query.sql').read()
data = pd.read_sql(query, connection)
connection.close()

I need to declare the @pdate in SQL query every time, I want to ask if I can update the @pdate using Python?

Does this answer your question? [Pandas read\_sql with parameters](https://stackoverflow.com/questions/24408557/pandas-read-sql-with-parameters) — JonSG, Jan 04 '23 at 17:32

score 2 · Answer 1 · answered Jan 04 '23 at 17:29

Instead of parsing and replacing an SQL script, you could use bind variables and have Python control the value (note the "?" in the query):

pdate = "some value"

# query could be read from file, given here for simplicity
query = """
select
  cast(L.Date as datetime) as Date
, Amount
, AccountNumber
, Property
, County
, ZipCode
, Price
, Owner

from Account.Detail L

inner join Owner.Detail M
    on L.Date = M.Date
    and L.Number = M.Number

inner join Purchase.Detail P
    on L.Date = P.Date
    and L.Purchase.Number = P.Purchase.Number

where L.Date = ?
    and Purchase.Number not in ('CL1', 'CL2')
    and Amount > 0
"""

data = pd.read_sql(query, connection, params=(pdate,))

Thank you so much for your help. I didn't realize that we can directly pass parameters in read_sql function — Yuuuu, Jan 04 '23 at 19:38

Update SQL parameter using Python

1 Answers1