如何使用pymssql/python检索多个SQL结果集

Question

我要调用的存储过程返回多个返回结果集，而不是通常的1结果集表。我需要执行该存储过程，并使用pymssql通过python检索它的结果。在

在Java中，这可以通过扩展org.springframework.jdbc.object.StoredProcedure，提供多个SqlReturnResultSet并调用.execute.execute(params)返回一个Map<String, Object>，在这里您可以通过SqlReturnResultSet中最初提供的String键访问每个返回的结果集：

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())
    }
}

如何用Python实现这一点？在

Answer 1

Python不会自动映射您的结果集，而是创建缓冲游标，但是您可以使用cursor.nextset()迭代这些游标，例如：

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