CODEWITHSUNDEEP
python-3.xsql-serverstored-procedurestable-valued-parameters
Ankit Singh
Ankit Singh

Reputation: 1

Calling table value MSSQL stored procedure from python

I want to call a MSSQL Stored Procedure from Python which takes table value as a parameter, and which was earlier triggered from API by passing  

{
  "data": 
        [{"xyz": 340, "abc": 0, "ijk": '296202302M13', 
          "files": [{
                     "pfile": 'xyz.txt',
                     "fName": 'abc.txt',
                     "fileDate": '2023-02-28 23:25:03.000'}]
                   }]
}

 

This calls the stored procedure by storing this JSON into table value parameter and passes it to the SP.

I have tried using pytds library to pass table value parameter and to call the stored procedure but I am getting the error as:

Unable to determine database type from python TableValuedParam type

If I try to call the procedure by passing as a list then getting the same error

Unable to determine database type from python List type

 

I am using callproc() to call the stored procedure.

Edit: Python code

mydb = db_connection(server, username, password, database) cursor = mydb.cursor() 

file = ['test1.txt', 'test2.txt', '2023-02-28 23:25:03.000'] 
my_tvp = ['444', '0' , '20230327XYZ', file] 

tvp = pytds.TableValuedParam(type_name='FileListType', rows=my_tvp) 

param = (tvp,) 
cursor.callproc('test1', param)

This is the part of SP which might be useful

ALTER PROCEDURE [dbo].[test1] (
    @FileList FileListType Readonly

    ,@ReturnMessage VARCHAR(1000) = '' OUTPUT
)

Upvotes: 0

Views: 256

Answers (0)

Related Questions