Calling table value MSSQL stored procedure from python

Question

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
)

Hi Aaron, I tried the prescribed method but I am getting the "Unable to determine database type from python TableValuedParam type" when I tried with pytds I have to pass the above json to the stored procedure — Ankit Singh, Mar 27 '23 at 11:35
We can't help you troubleshoot what we can't see. Why haven't you posted the actual code you're trying? — Aaron Bertrand, Mar 27 '23 at 12:04
Hi, Aaron and Thom I have edited my actual code Thanks for the changes, This is actually my first question — Ankit Singh, Mar 27 '23 at 12:07
I can't say I've used pytds before, but I had no problem using PyODBC [in the past](https://stackoverflow.com/a/67725115/2029983); note that I use `cursor.execute` rather than `callproc` though. The links that @AaronBertrand gave too all appear to use `execute` *not* `callproc`. Why do you need to use `callproc` instead of `execute`? — Thom A, Mar 27 '23 at 12:10
There is no particular reason to use callproc, It is just that I had to call multiple procedures and the rest of them executed perfectly with callproc, but then again their parameters were also not table value parameter — Ankit Singh, Mar 27 '23 at 12:21
Try `type_name='dbo.FileListType'`. Side note: you should use `with` to ensure the connection and command get closed in an exception — Charlieface, Mar 27 '23 at 15:31
Hi @Charlieface I with the prefix dbo still getting the same error — Ankit Singh, Mar 29 '23 at 05:06

Calling table value MSSQL stored procedure from python

0 Answers0