pymssql executes stored procedure but returns no results

Question

I am trying to execute a stored procedure on a SQL server and save the results using python -- I decided to use pymssql because it seemed like the simplest solution.

print pymssql.__version__

server = 'MY\SERVER'
user = 'user'
password = 'password'
database = 'db'
proc = 'checkin'

with pymssql.connect(server, user, password, database) as conn:
    with conn.cursor() as cursor:

        cursor.callproc(proc, (eha, ip, url, alias, location, rotation))
        conn.commit()

        f = open('/var/wwwdata/locations.txt', 'w')
        for row in cursor:
            print(row['Alias'])
            f.write(row['Alias'] + '\n')
        f.close()

The SQL Query performs some insert/update and ends with

SELECT Alias FROM MyTable

Running the SP from SSMS works correctly, however running the SP from Python performs the insert/update functionality however returns no results.

According to pymssql documentation, this is a known problem. However, I cannot find a solution that works.

I tried a few different suggestions I found around the web, including

• Checked my pymssql version (2.1.1)
• Declare cursor with dict=true
• Use cursor.nextset() after cursor.commit()
• Fetch results using cursor.fetchall() or cursor.fetchone(), both of which result in similar exceptions:

OperationalError: Statement not executed or executed statement has no resultset

Does anyone know a fix for this particular problem? Or perhaps is there a more stable solution for python interfacing with SQL server (especially for calling stored procedures)? I guess I should also ask, am I going about this entirely wrong?

Also thought it might be worth noting: OS is Raspbian running on Raspberry Pi 2 Model B

Answer 1

I have managed to work out the problem. Calling conn.commit() was making the cursor lose its results. I probably misread some of the pymssql documentation and added that line by mistake - code works perfect without it.

EDIT: I noticed after I made this change, the stored procedure would return the results however the insert/update parts of the procedure were not saving. Now it's clear to me what conn.commit() was doing. If your stored procedure returns results AND makes changes to the database, you need to call conn.commit() after you fetch them.

server = 'MY\SERVER'
user = 'user'
password = 'password'
database = 'db'
proc = 'checkin'

with pymssql.connect(server, user, password, database) as conn:
    with conn.cursor() as cursor:
        cursor.callproc(proc, (eha, ip, url, alias, location, rotation))
        cursor.nextset()
        results = cursor.fetchall()
        conn.commit()
        f = open('/var/wwwdata/locations.txt', 'w')
        for result in results:
            print result[0]
            f.write(result[0])
        f.close()