0

I am using Python 3.9.7, pyodbc to connect to Microsoft SQL Server. I want to use string parametrize the select @MonthStartDate and select MonthEndDate variables. I tried looking at this Stack Overflow post and PyNative tutorial

query = """

 

    Use [Data]

 

    drop table #temp

 

    declare @MonthStartDate date

    declare @MonthEndDate datetime

    declare @LastOpenDate datetime

 

    select @MonthStartDate= ?

    select @MonthEndDate= ?

    select @LastOpenDate=DATEADD(dd, 7, @MonthEndDate)

 

    select S.Keys into #temp

    from DataJobs J

    inner join DataSent S on J.Id=S.Id

    where  S.EventDate between @MonthStartDate and @MonthEndDate

    group by S.Key

    having count(distinct S.D)>=20 

 

"""

 

params = ('2015-04-01', '2015-04-30 23:59:59.999')
cursor.execute(query, params)

var =cursor.fecthall()

I just get this error pyodbc.ProgrammingError: No results. Previous SQL was not a query.

Zaynaib Giwa
  • 5,366
  • 7
  • 21
  • 26
  • `where ltrim(S.EventDate between @MonthStartDate and @MonthEndDate)` makes no sense. How to do left trim a Boolean expression? – Thom A Jun 14 '22 at 18:58
  • 2
    Also, you don't attempt to return a dataset in that batch (the final statement is a `SELECT...INTO`). The `fecthall()` will also just error; if you were using `fetchall()` then it has no results to display, as none were returned. – Thom A Jun 14 '22 at 19:01
  • @Shmiel Thank. you I edited the link to the stack overflow post – Zaynaib Giwa Jun 14 '22 at 19:04
  • @Larnu I don't really understand. Yes. I am putting the results of the query into another dataset. But I was able to print out the results before I attempted parameterization. – Zaynaib Giwa Jun 14 '22 at 19:06
  • 2
    You're putting those results (from an invalid query), into a temporary table; you then do **nothing** with that table, your batch ends and SQL Server disposes the table (and the data you didn't use). Why put the data into a temporary table at all? Why all those variables and not directly parametrise the query? Why use a `USE` when you should be just connecting the right database? And, again, what is that `LTRIM` trying to do? That isn't valid syntax. – Thom A Jun 14 '22 at 19:08
  • @Larnu there is a second part to the query. I split the query up for debugging purposes. The second part just joins the temp table that I created with other tables in the dataframe. So the query that I originally displayed should return rows. – Zaynaib Giwa Jun 14 '22 at 19:39
  • @Larnu Sorry if I come off direct with my response. I want to thank you for your feedback. This code was handed down to me and I'm just trying to make it better. Your questions are helping me out a lot. Yes. You are right about the USE. I don't need that. – Zaynaib Giwa Jun 14 '22 at 19:43
  • @Larnu **Why all those variables and not directly parametrize the query?** This is what I'm trying to do but I just needed a point in the right direction. I will do more research on how to do this. I will look more into the documentation. – Zaynaib Giwa Jun 14 '22 at 19:47

0 Answers0