Running multiple stored procedures using pypyodbc giving incomplete results
Question: I'm running a relatively simple python script that is meant to read a text
file that has a series of stored procedures - one per line. The script should
run the stored procedure on the first line, move to the second line, run the
stored procedure on the second line, etc etc. Running these stored procedures
should populate a particular table.

So my problem is that these procedures aren't populating the table with all of
the results that they should be. For example, if my file looks like

    
    
    exec myproc 'data1';
    exec myproc 'data2';
    

Where myproc 'data1' should populate this other table with about ~100 records,
and myproc 'data2' should populate this other table with an additional ~50
records. Instead, I end up with about 9 results total - 5 from the first proc,
4 from the second.

I know the procedures work, because if I run the same sql file (with the
procs) through OSQL and I get the correct ~150 records in the other table, so
obviously it's something to do with my script.

Here's the code I'm running to do this:

    
    
    import pypyodbc
    conn = pypyodbc.connect(CONN_STR.format("{SQL Server}",server,database,user,password))
    conn.autoCommit = True
    
    procsFile = openFile('otherfile.txt','r+')
    
    #loop through each proc (line) in the file
    for proc in procsFile:
        #run the procedure        
        curs = conn.cursor()
        curs.execute(proc)
    
        #commit results
        conn.commit()
        curs.close();
    
    conn.close();
    procsFile.close();
    

I'm thinking this has something to do with the procedures not committing...or
something?? Frankly I don't really understand why only 5/100 records would be
committed.

I dont know. any help or advice would be much appreciated.


Answer: There a couple things to check. One is that your data1 is actually a string
'data1', or if you want the value of data1? If you want the string 'data1',
then you will have to add quotes around it. So your string to execute would
look like this:

    
    
    exec_string = 'exec my_proc \'data1\';'
    

* * *

In your case you turn ON auto-commit and also you manually commit for the
entire connection.

I would comment out the auto-commit line:

    
    
    #conn.autoCommit = True
    

And then change conn.commit() to the cursor instead

    
    
    curs.commit()
    

As a one-liner:

    
    
    conn.cursor().execute('exec myproc \'data1\';').commit()
    

* * *

Also, your Python semi-colons (;) at the end of the python line are
unnecessary, and may be doing something weird in your for-loop. (Keep the SQL
ones though.)

