Execute multiple variable query in SQL Server using Python cursor-CodePudding

I'm trying to execute multiple queries in SQL Server using the pymssql library.

This is my code:

cur = conn.cursor()
cur.execute("DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);  
             SET @begin_time = DATEADD(day, -1, GETDATE()) ;  
             SET @end_time = GETDATE(); 
             SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);  
             SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
             SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_users(@from_lsn, @to_lsn, 'all');")
output = cur.fetchall()
print(output)
conn.close()

The code is running fine and fetching the result, however when I'm calculating the date using Python library and passing it to the code, I'm getting an error.

Sample code

from datetime import datetime, timedelta
end_date = datetime.now()
start_date = datetime.now()   timedelta(hours=-1)

cur = conn.cursor()
query =  f"""DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);  
                SET @begin_time = {start_date};  
                SET @end_time = {end_date}; 
                SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);  
                SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time); 
                SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_users (@from_lsn, @to_lsn, 'all');"""
print(query)
cur.execute(query)
output = cur.fetchall()
print(output)
conn.close()

Error:

ProgrammingError: (102, Incorrect syntax near '11'.

DB-Lib error message 20018, severity 15:
General SQL Server error: Check messages from the SQL Server

I'm not sure what I'm doing wrong here. Would really appreciate if someone can help me and explain the issue.

CodePudding user response：

Consider actual SQL parameterization of the time variables and not string interpolation or concatenation with F-strings which generally is not safe or efficient for passing values from application layer to backend database. The library, pymssql, supports parameters. Python's datetime.datetime should translate to MSSQL's DATETIME.

# PREPARED STATEMENTS WITH %s PLACEHOLDERS
query = """DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);  
           SET @begin_time = %s;  
           SET @end_time = %s; 
           SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);  
           SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time); 
           SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_users (@from_lsn, @to_lsn, 'all');
        """
print(query)

# EXECUTE QUERY WITH BINDED PARAMS
cur.execute(query, [start_date, end_date])

In fact, you can shorten the query since parameters do not need declaration:

# PREPARED STATEMENTS WITH %s PLACEHOLDERS
query = """DECLARE @from_lsn binary(10), @to_lsn binary(10);
           SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', %s);  
           SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', %s); 
           SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_users (@from_lsn, @to_lsn, 'all');
        """

By the way, please note pymmsql is no longer a maintained library. Consider pyodbc for most secure, updated DB-API for Python-SQL Server connections. But note the parameter placeholder for pyodbc is qmarks ? and not %s (unlike most Python DB-APIs).