Executing A stored Procedure From Python

I am working with Python3 using a Module called ceODBC and trying to call a stored procedure in SQL Server 2005. When I run the stored procedure out of Sql Server Management Studio (without Python involved) I get the correct results and 278 rows are inserted into the desired table, however in Python it stops after 31. Can anyone tell me why the stored procedure might be stopping prematurely? Here is my Python Code:

import ceODBC
connect=ceODBC.connect("""DSN=mydatabase;SERVER=xxx.xxx.x.xxx;
DRIVER={SQLServer};UID=user;PWD=password""", autocommit= True)
cursor = connect.cursor()
cursor.execute(""" 
                   CREATE TABLE mydatabase.dbo.Vision_TempTable
                   (
                   CustId bigint,
                   PayProcId int,
                   WebUserId bigint,
                   SubTypeId bigint,
                   PayAmt decimal(18,2),
                   Paydate datetime,
                   PayType varchar(1),
                   DateCreated datetime,
                   PayStatus varchar(1),
                   Account varchar(30),
                   V_Account varchar(30)
                     )""")#Create a temp table which will be used in the SP insert  
connect.commit()
f=open('F:/clients/UTA/Vision/Data/ExternalPay_Data/lbox.txt')
CUST_ID=44
PAYPROCID=4
SUBTYPE_ID=64
WEBUSER_ID=2432
PAYTYPE='C'
PAYSTATUS='J'
for line in f: #parse a text file and insert values into temp table I just created
    lineLength=len(line.strip())
    if lineLength>=49:
        visionAccount=int(line[10:17])
        visionAccount=str(visionAccount)
        recipientID=line[17:29]
        invoiceAmount=line[31:39]
        invoiceAmount=float(invoiceAmount)
        pmtType=line[39:41]#make sure it will always be ck
        pmtDate=line[45:47]+'/'+line[47:49]+'/'+line[41:45]
        cursor.execute("""INSERT INTO mydatabase.dbo.Vision_TempTable
                            (CustId,PayProcId,WebUserId,SubTypeId,PayAmt,Paydate,
                            PayType,DateCreated,PayStatus,Account,V_Account)
                          VALUES
                              (?,?,?,?,?,?,?,GETDATE(),?,?,?)""",
               CUST_ID,PAYPROCID,WEBUSER_ID,SUBTYPE_ID,
               invoiceAmount,pmtDate,PAYTYPE,PAYSTATUS,recipientID,visionAccount)

        connect.commit()
cursor.callproc("mydatabase.dbo.VisionExternalPMTS")# turn over control to SP

f.close()
connect.close()
print('Done')  

and just so we have the complete picture I will include the stored procedure below:

   USE [mydatabase]
GO
/****** Object:  StoredProcedure [dbo].[VisionExternalPMTS]    Script Date: 06/16/2011 08:38:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[VisionExternalPMTS] as

BEGIN
declare @custid bigint,
@payprocid int,
@webuserid bigint,
@subtypeid bigint,
@payamt decimal(18,2),
@paydate datetime,
@paytype varchar(1),
@datecreated datetime,
@paystatus varchar(1),
@account varchar(30),
@v_account varchar(30)

DECLARE update_webPayments CURSOR for --select from temp table created in python
SELECT  CustId,PayProcId,WebUserId,SubTypeId,PayAmt,Paydate,PayType,
        DateCreated,PayStatus
FROM Vision_TempTable

OPEN update_webPayments

FETCH NEXT FROM update_webPayments INTO @custid,@payprocid,@webuserid,
                @subtypeid,@payamt,@paydate,@paytype,@datecreated,
                @paystatus

WHILE @@fetch_status = 0 --insert into target table
    BEGIN
        INSERT INTO WEBPAYMENTS(CUSTID,PAYPROCID,WEBUSERID,SUBTYPEID,
                                PAYAMT,PAYDATE,PAYTYPE,DATECREATED,PAYSTATUS)
        VALUES (@custid,@payprocid,@webuserid,@subtypeid,@payamt,
                @paydate,@paytype,@datecreated,@paystatus)
        FETCH NEXT FROM update_webPayments INTO @custid,@payprocid,@webuserid,
                    @subtypeid,@payamt,@paydate,@paytype,@datecreated,
                    @paystatus                          
    END 
--DROP TABLE VISION_TempTable
END  

The create table statement and Initial insert from Python always work I get 278 rows in my temp table, it's only when I call the stored procedure from python that things go wrong. The stored procedure terminates early. I thought this was because Python might not be giving it enough time to execute before the program ends so I tried putting some kind of counter in place, but just ended up getting database errors (although the stored procedure worked correctly), so I removed the counter. What I want is to pass control over to the stored procedure and to not re开发者_StackOverflow中文版turn to the python program until the stored procedure is complete. Any help would be greatly appreciated.

Thanks

---

Looking at http://ceodbc.sourceforge.net/html/cursor.html there is this comment:

Cursor.execdirect(statement)

Execute a statement against the database using SQLExecDirect instead of SQLExecute. This is necessary in some situations due to bugs in ODBC drivers such as exhibited by the SQL Server ODBC driver when calling certain stored procedures.

If the statement is a query, the cursor is returned as a convenience since cursors implement the iterator protocol and there is thus no need to call one of the appropriate fetch methods; otherwise None is returned.

Sounds like there is some craziness with stored procedures via SQL Server ODBC (given that they single it out for special treatment). You may have to try:

cursor.execdirect("mydatabase.dbo.VisionExternalPMTS")

Let me know if that works.