Pymsql datetime query

Question

I am querying on SQL tables with the below query

select  ms.Ename as Name,ms.DateTimeStamp, s.Author
from Spt s,  Mst ms
where s.MstID = ms.MstID
and s.Version = ms.CurrentVersion

and  ms.DateTimeStamp > (SELECT DATEADD(day,-10, GETDATE()))

I can see the above rows.

I am now trying in pymsql in pycharm.

import requests
import json
from datetime import datetime
import pymssql

server = 'devserver'
database = 'devdb'
username = 'devuser'
password = 'passwd'

cnxn = pymssql.connect(server, sqlusername, sqlpassword, database)

query = "select ms.Ename as Name, " \
        "ms.DateTimeStamp, s.Author " \
        "from Spt s,  Mst ms " \
        "where s.MstID = ms.MstID  " \
        "and s.Version = ms.CurrentVersion " \
        "and ms.DateTimeStamp >  SELECT DATEADD(day, -" + 10 + ", GETDATE())" \
        "order by ms.DateTimeStamp ; "

cursor = cnxn.cursor()
cursor.execute(query)
row = cursor.fetchall()
cursor.close()
print(row)

I am getting the below error : I understand that it is related to datetime format. But could not find the solution for that. Could anyone please help for this issue.

File "C:/Users/dp/code_another.py", line 57, in get_details "and ms.DateTimeStamp > (SELECT DATEADD(day, -" + 10 + ", GETDATE()))"
TypeError: can only concatenate str (not "int") to str

Answer 1

The problem is that you need to preformat your date offset as a string before you can concatenate it to your query.

Assuming you're using Python 3.7 or higher, you can handle this in a simple way by using the formatted string operator (and triple quotes allows you to have a multiline string). Here, I've defined a variable days_offset as -10, then used {braces} to indicate where I want it to appear in the formatted string. Like this:

days_offset = -10
query = f"""
        select ms.Ename as Name, ms.DateTimeStamp, s.Author 
        from Spt s,  Mst ms where s.MstID = ms.MstID and s.Version = ms.CurrentVersion 
        and ms.DateTimeStamp >  SELECT DATEADD(day,{days_offset}, 
        GETDATE()) order by ms.DateTimeStamp ; 
        """

If you're still working with Python 2.7, then you can cast the value to a string in your original code, like this:

query = "select ms.Ename as Name, " \
        "ms.DateTimeStamp, s.Author " \
        "from Spt s,  Mst ms " \
        "where s.MstID = ms.MstID  " \
        "and s.Version = ms.CurrentVersion " \
        "and ms.DateTimeStamp >  SELECT DATEADD(day, -" + str(10) + ", GETDATE())" \
        "order by ms.DateTimeStamp ; "