how do I retreive multiple resultsets from a query?

[The Tester]

I'm really sorry but I can't find the documentation and my guesses haven't worked. Can someone post a link to it, if it exists?

I'm calling a stored procedure that I wrote, which returns two result sets. I can only get the first one, but there's a changelog entry saying that someone fixed things so I can get both. Only without a working example.

I tried this:

   cursor.execute("CALL MyStoredProc(param)")

   for row in cursor.fetchall():

        print( row )

   # second resultset?

   for row in cursor.fetchall():

        print( row )

And that's working for the first resultset but I get nothing with a second call.

Then this example: http://stackoverflow.com/questions/15320265/cannot-return-results-from-stored-procedure-using-python-cursor

cursor.callproc("MyStoredProc",[param])

for result in cursor.stored_results():
    people=result.fetchall()

Although the CALL... syntax in the error message looks correct that gave me an error

    error <type 'exceptions.AttributeError'>

When I called stored_results() so I'm guessing that's wrong too.

Can anyone advise?

thanks

[Naoki INADA]

On Wednesday, August 26, 2015 at 3:34:49 PM UTC+9, The Tester wrote:

I'm really sorry but I can't find the documentation and my guesses haven't worked. Can someone post a link to it, if it exists?

There is cursor.nextset()

https://www.python.org/dev/peps/pep-0249/#nextset

 

I'm calling a stored procedure that I wrote, which returns two result sets. I can only get the first one, but there's a changelog entry saying that someone fixed things so I can get both. Only without a working example.

I tried this:

   cursor.execute("CALL MyStoredProc(param)")

   for row in cursor.fetchall():

        print( row )

   # second resultset?

   for row in cursor.fetchall():

        print( row )

And that's working for the first resultset but I get nothing with a second call.

Then this example: http://stackoverflow.com/questions/15320265/cannot-return-results-from-stored-procedure-using-python-cursor

cursor.callproc("MyStoredProc",[param])

for result in cursor.stored_results():
    people=result.fetchall()

Although the CALL... syntax in the error message looks correct that gave me an error

    error <type 'exceptions.AttributeError'>

When I called stored_results() so I'm guessing that's wrong too.

Can anyone advise?

1. When ask a question about error happen, don't strip traceback.

Traceback is very important hint to investigate problem.

2. Read source

Python is readable language.

Python is explicit language than natural languages.

 

thanks

[The Tester]

Thanks for that.

I'm finding Python a very odd language, mostly because I'm used to having an IDE instead of trying to make do with a basic text editor. So "read the library source" means manually finding the source (I'm sure there's a standard, and the library path is obvious, and so on, but I don't know them off the top of my head at this stage), and "don't hide the traceback" means "write my own error handler to display the traceback".  Or is the standard in Python to simply crash on errors and let the interpreter vomit the traceback at the user?

---

From: Naoki INADA <songof...@gmail.com>
To: PyMySQL Users <pymysq...@googlegroups.com>
Cc: prw...@yahoo.com.au
Sent: Wednesday, 26 August 2015, 19:04
Subject: Re: how do I retreive multiple resultsets from a query?

[Naoki INADA]

On Thursday, August 27, 2015 at 7:03:30 AM UTC+9, The Tester wrote:

Thanks for that.

I'm finding Python a very odd language, mostly because I'm used to having an IDE instead of trying to make do with a basic text editor. So "read the library source" means manually finding the source

I recommend you to use PyCharm [1] and IPython [2].

[1] https://www.jetbrains.com/pycharm/

[2] http://ipython.org/

For example, playing pymysql on ipython is like following.  It's very easy to find methods and read source.

In [1]: import pymysql


In [2]: conn = pymysql.connect()


In [3]: cur = conn.cursor()


In [5]: cur.execute("SELECT 1; SELECT 2;")
Out[5]: 1


In [6]: cur.fetch
cur.fetchall   cur.fetchmany  cur.fetchone


In [6]: cur.fetchall()
Out[6]: ((1,),)


In [7]: cur.fetchall()
Out[7]: ()


In [8]: cur. <press TAB here>
cur.DataError          cur.InternalError      cur.arraysize          cur.execute            cur.lastrowid          cur.rownumber
cur.DatabaseError      cur.NotSupportedError  cur.callproc           cur.executemany        cur.max_stmt_length    cur.scroll
cur.Error              cur.OperationalError   cur.close              cur.fetchall           cur.mogrify            cur.setinputsizes
cur.IntegrityError     cur.ProgrammingError   cur.connection         cur.fetchmany          cur.nextset            cur.setoutputsizes
cur.InterfaceError     cur.Warning            cur.description        cur.fetchone           cur.rowcount


In [8]: cur.nextset?
Signature: cur.nextset()
Docstring: <no docstring>
File:      ~/venvs/pymysql/lib/python3.4/site-packages/PyMySQL-0.6.6-py3.4.egg/pymysql/cursors.py
Type:      method


In [9]: cur.nextset??
Signature: cur.nextset()
Source:
    def nextset(self):
        return self._nextset(False)
File:      ~/venvs/pymysql/lib/python3.4/site-packages/PyMySQL-0.6.6-py3.4.egg/pymysql/cursors.py
Type:      method

 

(I'm sure there's a standard, and the library path is obvious, and so on, but I don't know them off the top of my head at this stage), and "don't hide the traceback" means "write my own error handler to display the traceback".  Or is the standard in Python to simply crash on errors and let the interpreter vomit the traceback at the user?

Standard error handler shows tracebacks.

You must not handle errors except when you know about the error well.