1

I have below dynamic SQL script which runs on SQL Server with no issues.

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;

I am trying to execute the same SQL using Python but it doesn't runs and gives lot of syntax errors in Python IDE. Please can someone help me in correcting the Python code.

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..")
13
  • 1
    If you're getting syntax issues, you should be telling us what those are. Commented Nov 21, 2019 at 21:13
  • @Larnu: This the same question which you solved early today:stackoverflow.com/questions/58977442/… I am trying to execute the same SQL code using Python. Commented Nov 21, 2019 at 21:16
  • 1
    I recognise my own style of Dynamic SQL, don't worry. But that doesn't tell us the errors you're getting. Commented Nov 21, 2019 at 21:18
  • 1
    I think here is error: filepath = 'C:\SampleTestFiles\MyWordDoc.doc', should be filepath = 'C:\\SampleTestFiles\\MyWordDoc.doc' Commented Nov 21, 2019 at 21:43
  • 1
    It worked!. I hardcoded the path in BULK statment as this: ( BULK 'C:\\SampleTestFiles\\MyWordDoc.doc', SINGLE_BLOB) Now I am trying to see if I can replace the path with variable in python. Commented Nov 21, 2019 at 21:53

1 Answer 1

0

the error makes sense:

Every question mark "?" is a parameter. In

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, ?, ?, ?, ?, ?) 

you have 6 of them. values = (filepath, file_type_id, file_name, filing_id, created_at, created_by, is_required) provide the list of parameters and it contains 7 values.

if you ask me

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

should

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

And then the count of parameters markers will match the count of those provided

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

2 Comments

The 7th parameter, I am using at:` QUOTENAME(?,\'\'\'\')`
You've dropped some needed single quotes (') in this as well, making the query itself no longer valid.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.