How to access SQL Server 2008 stored procedure with table parameter in Python

Question

Im looking for a way to take a result set and use it to find records in a table located in SQL Server 2008, without scrolling through the records one at a time. The result sets that will be used to search for records can be hundreds of thousands. So far, I have been creating a table in memory using sqlite3, and then trying to pass this table to a stored procedure that takes a table-dependent parameter. Work is underway on the SQL Server side, a user-defined type is created, there is a test procedure that takes a parameter depending on the table, and Ive checked it through TSQL, and it seems to work fine. In Python, a simple memory table was created through sqlite3. Now the catch, the only documentation I found to access the stored procedure with the table value parameter, uses ADO.Net and VB, nothing in Python. Unfortunately, I do not have enough programmer to translate. Has anyone used a SQL Server stored procedure with a table parameter? Is there any other approach I should study?

Here are some links: A worthy explanation of the table parameters and their configuration in SQL and use in .Net

http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

http://msdn.microsoft.com/en-us/library/bb675163.aspx#Y2142

Explaining the use of ADO in Python is almost what I need, I just need a structured parameter type. http://www.mayukhbose.com/python/ado/ado-command-3.php

My simple code

--TSQL to create type on SQL database create Type PropIDList as Table (Prop_Id BigInt primary key) --TSQL to create stored procedure on SQL database. Note reference to create procedure PropIDListTest @PIDList PropIDList READONLY as SET NOCOUNT ON select * from @PIDList p SET NOCOUNT OFF --TSQL to test objects. --Declare variable as user defined type (table that has prop_id) declare @pidlist as propidlist --Populate variable insert into @pidlist(prop_id) values(1000) insert into @pidlist(prop_id) values(2000) --Pass table variable to stored procedure exec PropIDListTest @pidlist 

Now the tricky part is Python.

Here is the code creating the memory table

 import getopt, sys, string, os, tempfile, shutil import _winreg,win32api, win32con from win32com.client import Dispatch from adoconstants import * import sqlite3 conn1 = sqlite3.connect(':memory:') c = conn1.cursor() # Create table c.execute('''create table PropList (PropID bigint)''') # Insert a row of data c.execute("""insert into PropList values (37921019)""") # Save (commit) the changes conn1.commit() c.execute('select * from PropList order by propID') # lets print out what we have to make sure it works for row in c: print row 

Ok my attempt to connect via python

 conn = Dispatch('ADODB.Connection') conn.ConnectionString = "Provider=sqloledb.1; Data Source=nt38; Integrated Security = SSPI;database=pubs" conn.Open() cmd = Dispatch('ADODB.Command') cmd.ActiveConnection = conn cmd.CommandType = adCmdStoredProc cmd.CommandText = "PropIDListTest @pidlist = ?" param1 = cmd.CreateParameter('@PIDList', adUserDefined) # I "think" the parameter type is the key and yes it is most likely wrong here. cmd.Parameters.Append(param1) cmd.Parameters.Value = conn1 # Yeah, this is probably wrong as well (rs, status) = cmd.Execute() while not rs.EOF: OutputName = rs.Fields("Prop_ID").Value.strip().upper() print OutputName rs.MoveNext() rs.Close() rs = None conn.Close() conn = None # We can also close the cursor if we are done with it c.close() conn1.close() 

Answer 1

I have already encoded TVP from ADO.NET. Here is a question about TVPs in classic ADO that interests me, sql server - classic ADOs and table parameters in a stored procedure - stack overflow . This does not give a direct answer, but an alternative.

• The XML option is simpler; you have probably already considered it; this will require more server-side processing.
• Here is the msdn link for low-level ODBC TVP programming. Table Parameters (ODBC) . This is the closest answer if you can switch to ODBC.
• You can pass the csv string to nvarchar (max) and then pass it to the CLR SplitString function , which one is quick, but by default I disagree.

Please write back what works or not here.