0

Trying to load a SQL Server database table into a Python pandas DataFrame dynamically based on production date of the reports. My issue is the following, when I write:

import pyodbc
conn = pyodbc.connect(server = "10.87.254.73", Database= "CTData", Driver ="{SQL Server}")
PCBondsDB = pd.read_sql_query("Select * From [MarketData].[pcbond].[PCbonds] where ClosingDate = '2020-04-13'", conn)
conn.close()

It works, I can connect to the Database and extract my table, but if I change the where ClosingDate= to a variable containing the date as a string it won't work.

Suppose DateProd = '2020-04-13' and now I try:

 PCBondsDB = pd.read_sql_query("Select * From [MarketData].[pcbond].[PCbonds] where ClosingDate = 
'DateProd'",

It throws me an error saying: Execution failed on sql 'Select * From [MarketData].[pcbond].[PCbonds] where ClosingDate = 'DateProd'': ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting date and/or time from character string. (241) (SQLExecDirectW)')

Which makes absolutely no sense as to why when I pass it as a litteral where ClosingDate= '2020-04-13' it works, but when I try to use a variable where ClosingDate = 'DateProd' (which contains the exact same value and datatype being a string) it won't work and throws an error.

I tried doubling, tripling the quotes, inversing single and double quotes, nothing seems to work. I looked around the forum and couldn't find an answer to this specific problem. I think the solution is easy but I am a rather novice in Python and would like to know.. I am trying to make this dynamic, so yes I need to use a variable...

Thanks

YoungFella
  • 67
  • 2
  • 9
  • You need to read the documentation or find one of the many examples, tutorials, or forum posts about parameterizing a sql call in python. In your attempt, you are not passing a "variable" at all - you are simply passing your variable name as a string literal. – SMor Apr 21 '20 at 12:41
  • I now understand the params part better now that the other guy answered and now I can see how my question was somehow answered in that other thread. But honestly, it would have taken me much more time without that hint hahaha. thanks for the input as well though. I'm like 3 weeks into Python now please bare with me xD – YoungFella Apr 21 '20 at 14:20

1 Answers1

0

You are passing the string literal 'DateProd' as the date. You need to pass the value of the variable as a parameter, like so:

date_prod = '2020-04-13'
sql = "Select * From [MarketData].[pcbond].[PCbonds] where ClosingDate = ?"
param_values = (date_prod, )
pc_bonds_db = pd.read_sql_query(sql, engine, params=param_values)
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • thanks man works perfectly ! Just a question for my understanding.. I tried directly doing params = DateProd and it gave me an error saying "'The SQL contains 1 parameter markers, but 10 parameters were supplied', 'HY000')" I noticed that by that doing param_values = (DateProd,) , it converted the value into a tuple. Would you know why the tuple works better than directly putting the string as the parameter? Thanks again! – YoungFella Apr 21 '20 at 14:16
  • See the pandas docs. `params` needs to be a list, tuple, or dict. Simply passing the str won't work because len("2020-04-13") is 10, whereas len(("2020-04-13", )) is 1. – Gord Thompson Apr 21 '20 at 17:27
  • I see. Perfect thank you so much!!! – YoungFella Apr 21 '20 at 23:24