python - 使用Python执行动态SQL但收到异常

Question

我有以下动态 SQL 脚本，可以在 SQL Server 上运行，没有任何问题。

DECLARE 
@file_type_id int = 1,
@filing_id bigint = 57,
@created_at datetime = GETDATE(),
@created_by bigint = 2,
@is_required bit = 1,
@insertquery nvarchar(MAX),
@Filepath nvarchar(MAX) = 'C:\SampleTestFiles\MyWordDoc.doc';

SET @insertquery = 
'DECLARE @Document AS VARBINARY(MAX);
 SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK ' + QUOTENAME(@filepath,'''') +', SINGLE_BLOB ) AS Doc; 
 INSERT INTO [TEST].[dbo].[MyTable]  ( [file_type_id], [file],  [file_name], [filing_id], [created_at], [created_by], [is_required])
 VALUES (@file_type_id, @Document, @file_name, @filing_id , @created_at, @created_by, @is_required);';

EXEC sp_executesql 
@insertquery, 
N'@file_name varchar(100),@file_type_id int,@filing_id bigint,@created_at datetime, @created_by bigint,@is_required bit',
@file_name, @file_type_id, @filing_id, @created_at, @created_by, @is_required;

我尝试使用 Python 执行相同的 SQL，但它无法运行，并且在 Python IDE 中给出很多语法错误。请有人帮我纠正Python代码。

filepath = 'C:\SampleTestFiles\MyWordDoc.doc'
file_type_id = 1
file_name = 'test'
filing_id = 57
created_at = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
created_by = 1
is_required = 1

Query = '''
SET @insertquery = 
DECLARE @Document AS VARBINARY(MAX);
SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK \' + QUOTENAME(?,\'\'\'\') +\', SINGLE_BLOB) AS Doc;
INSERT INTO[TEST].[dbo].[MyTable]([file_type_id], [file], [file_name], [filing_id], [created_at], [created_by], [is_required])
VALUES(?,@Document, ?, ?, ?, ?, ?)
        '''
values = (filepath, file_type_id, file_name, filing_id, created_at, created_by, is_required)

# Execute SQL Insert Query
cursor = conn.cursor()
cursor.execute(Query, values)
cursor.close()
conn.commit()
print("File inserted..")

Answer 1

这个错误是有道理的:

每个问号“？”是一个参数。在

Query = '''
SET @insertquery = 
DECLARE @Document AS VARBINARY(MAX);
SELECT @Document = CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET( BULK \' + QUOTENAME(?,\'\'\'\') +\', SINGLE_BLOB) AS Doc;
INSERT INTO[TEST].[dbo].[MyTable]([file_type_id], [file], [file_name], [filing_id], [created_at], [created_by], [is_required])
VALUES(?,@Document, ?, ?, ?, ?, ?) 

你有 6 个。value = (filepath, file_type_id, file_name,filing_id,created_at,created_by,is_required)提供参数列表，它包含7个值。

如果你问我

VALUES(?,@Document, ?, ?, ?, ?, ?) 

应该

VALUES(?,?, ?, ?, ?, ?, ?) 

然后参数标记的数量将与提供的参数标记的数量相匹配