How to modify the input value of a stored procedure with python

Question

Currently, I am trying to modify two input values of the following stored procedure that I execute with Python.

country_cursor.execute(
    "[DEV].[DevelopmentSchema].[QA_Extractor] @start_date='2017-05-05', @end_date='2017-05-11'")

I do not want to run this program every day and change the start_date and end_date manually from the code but instead trying to create a prompt where I can type down the dates that I want to look for the retrieval.

So far, I have done the following:

end_date   = str(datetime.now()).rpartition(' ')[0]
start_date = str(datetime.now() - timedelta(days=7)).rpartition(' ')[0]

country_cursor.execute(
    "[DEV].[DevelopmentSchema].[QA_Extractor] @start_date='2017-05-05', @end_date= "+"'"+end_date+"'"+"\"")

I just replaced one input date with a variable but when I execute this program I encounter the following SQL error:

pypyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server 
Driver][SQL Server]An object or column name is missing or empty. For SELECT 
INTO statements, verify each column has a name. For other statements, look 
for empty alias names. Aliases defined as "" or [] are not allowed. Change 
the alias to a valid name.')

My point of view is that the Stored Procedure does not accept this variable as the end date, in consequence, the column to look for the retrieval does not exist. I also read in SQL Server query erroring with 'An object or column name is missing or empty' which supports my view. Am I right with my thinking or am I totally wrong?

How can I fix this problem? Any ideas, suggestions and improvements are welcome ;)

BoarGules · Accepted Answer · 2017-05-14 00:19:30Z

1

If I do this:

print("[DEV].[DevelopmentSchema].[QA_Extractor] @start_date='2017-05-05', @end_date= "+"'"+end_date+"'"+"\"")

I get this:

[DEV].[DevelopmentSchema].[QA_Extractor] @start_date='2017-05-05', @end_date= '2017-05-14'"

It seems to me that there is a stray " at the end of this query string.

Part of the problem is that you are working way too hard to format the dates as strings.

I am guessing that there is

from datetime import *

at the top of your code (ugly, but hardly your fault). If so, you can do

start_date = datetime.now() - timedelta(days=7)
end_date = datetime.now()
query_string = f"[DEV].[DevelopmentSchema].[QA_Extractor] @start_date='{start_date:%Y-%m-%d}', @end_date='{end_date:%Y-%m-%d'}"
country_cursor.execute(query_string)

which arguably makes it easier to see stray punctuation.

answered May 14, 2017 at 0:19

BoarGules

17.1k3 gold badges29 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

How to modify the input value of a stored procedure with python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related