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 — JonSG
– JonSG, Commented Jan 4, 2023 at 17:32

Mureinik · Accepted Answer · 2023-01-04 17:29:58Z

2

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,))

answered Jan 4, 2023 at 17:29

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yuuuu Over a year ago

Thank you so much for your help. I didn't realize that we can directly pass parameters in read_sql function

Collectives™ on Stack Overflow

Update SQL parameter using Python

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related