CODEWITHSUNDEEP
javapythonsqlpymssql
RAFJR
RAFJR

Reputation: 442

How to retrieve multiple SQL result set using pymssql/python

A stored procedure that I am suppose to call returns multiple return result sets instead of the usual 1 result set table. I need to execute that stored procedure and retrieve it's results via python using pymssql.

In Java, this is achievable by extending org.springframework.jdbc.object.StoredProcedure, supplying multiple SqlReturnResultSet and calling the .execute .execute(params) returning a Map<String, Object> where you can access each returned result set via the String key initially supplied in SqlReturnResultSet:

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.SqlReturnResultSet;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.dao.DataAccessException;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyStoredProcedure extends StoredProcedure
{
    public GetMultiple()
    {
        final SqlParameter[] sqlResultParameters = new SqlParameter[] {
                new SqlReturnResultSet("returnResultSet1", new RowMapper()),
                new SqlReturnResultSet("returnResultSet2", new RowMapper())
        };

        declareParameter(sqlResultParameters)

        final Map<String, Object> spResult = super.execute();

        spResult.getOrDefault("returnResultSet1", Collections.emptyList())
        spResult.getOrDefault("returnResultSet2", Collections.emptyList())
    }
}

How do I accomplish this in Python?

Upvotes: 0

Views: 2884

Answers (1)

zwer
zwer

Reputation: 25829

Python won't map your result sets automatically, instead it will create buffered cursors, but you can iterate through them with cursor.nextset(), e.g.:

connection = pymysql.connect(host="host", user="user", password="pass", db="schema")  # etc.

with connection.cursor() as cursor:
    cursor.callproc("procedure_name", ("foo", "bar"))  # pass procedure parameters as a tuple
    while True:  # loop while there are result sets
        if cursor.rowcount:  # make sure there are actually results in the current set
            result_set = cursor.fetchall()  # or cursor.fetchone() / cursor.fetchmany()
            # do whatever you want with the result_set, store it in a dict if you want
        # after done processing the current result set, move on to the next
        if not cursor.nextset():  # switch to the next result set, if available...
            break  # exit the loop if not

Upvotes: 6

Related Questions