0

Using SQL Server stored procedures from Python (pyodbc) Following on from this thread, how do you handle spaces in variables? e.g. 

for CustomerID,TestCode,TestResult in csv_input:
        #query 
        query = """\
        SET NOCOUNT ON;
        Declare @CustomerID nvarchar(100),
        @TestCode nvarchar(20),
        @TestResult nvarchar(10),

        exec TestData.sp_uat_TestData_Customer @CustomerID = ? , @TestCode = ?, @TestResult = ?;"""
        #set the ID as per the iteration of the loop
        args = (CustomerID,TestCode,TestResult,)
        #execute the query above using the provided variable
        cursor.execute(query,args)

        # print out the data returned by the Stored Procedure
        for row in cursor:
            print(row)
            csv_output.writerow(row)

In the example above @TestResult will have the value "Passed the test" in the CSV file 3rd column.

I have tried the following but with no joy: @TestResult = '''?''', @TestResult = '?', @TestResult = [?] and they all give pyodbc.ProgrammingError: ('The SQL contains 2 parameter markers, but 3 parameters were supplied', 'HY000')

I even tried adding single quotes to the value in the input CSV and still no progress. Any tips appreciated.

Improve this question
5
  • Spaces in the literals should not be an issue. Please post exact error from code you posted where qmarks do not have punctuation and not your attempts. Commented Jan 29, 2020 at 18:06
  • That comma after ` nvarchar(10)` looks wrong. Commented Jan 29, 2020 at 18:11
  • @GordThompson it is, I removed some params to make it simpler and forgot to remove that comma. Commented Jan 29, 2020 at 23:14
  • In the stored procedure definition is @TestResult declared as an OUTPUT parameter by chance? Commented Jan 30, 2020 at 0:15
  • OK, figured it out. the values in the CSV file were in UTF-8 encoding and the values in the Stored Procedure are in Ansi format. when the 2 strings were compared they were different. Commented Jan 30, 2020 at 0:31
Related questions
0
how can I use stored procedure with parameters in python code
4
using pypyodbc to access a table with spaces in its name
2
Pyodbc & SQL Server-TypeError: not all arguments converted during string formatting
Load 7 more related questions Show fewer related questions

0

Reset to default

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.