Snippet: Fetching results after calling stored procedures using MySQL Connector/Python

Problem

Using MySQL Connector/Python, you are calling a stored procedure which is also selecting data and you would like to fetch the rows of the result.

Solution

For this example we create a stored procedure which is executing SHOW SLAVE STATUS.

cnx = mysql.connector.connect(user='scott', password='tiger',
                              database='mining')
cur = cnx.cursor()
cur.execute("DROP PROCEDURE IF EXISTS slave_status")
proc = "CREATE PROCEDURE slave_status () BEGIN SHOW SLAVE STATUS; END"
cur.execute()
cur.call("slave_status")

for result_cursor in cur.stored_results():
 for row in result_cursor:
   print(row[0])

The result from the above would be:

shell> python foo.py
Waiting for master to send event

Discussion

The stored_results() method of cursor object is retiring an iterator object which will go over the results proceeded after calling the stored procedure. Each result is actually a MySQLCursorBuffered object.

You could also use the with_rows cursor property to check if the cursor actually could return rows or not (for example, for SELECT statements). An example is provided in the documentation.

My sessions at the MySQL Conference & Expo 2010

A preliminary schedule is now available for the MySQL Conference & Expo 2010 (in Santa Clara, California, USA). I have two talks and a tutorial, currently scheduled as follows:

Schedule might change a bit, so I suggest keeping an eye on the conference website.

And please remember: we might have changed colors a few times past 2 years, but we are still the nice and friendly people we were back in MySQL AB!

Python, oursql and MacOS X 10.6 (Snow Leopard)

This post explains how to compile oursql and install it on MacOS 10.6. oursql is a Python database interface for MySQL, an alternative to MySQL for Python (i.e. MySQLdb) and MySQL Connector/Python.

First, find out which MySQL you installed. This can be either the 32-bit or the 64-bit version. To make sure, find the mysqld (e.g. in /usr/local/mysql/bin) and do the following in a Terminal window:

shell> file /usr/local/mysql/bin/mysqld
.../mysqld: Mach-O 64-bit executable x86_64

If you see x86_64, you got 64-bit, otherwise 32-bit. If you see both, then you have a universal build. This is important for specifying the ARGSFLAG when building.

Download oursql from Launchpad and unpack it into some directory. Using the information from above, you’ll have to do following for 64-bit platform (or universal build) in a Terminal window:

shell> ARCHFLAGS="-arch x86_64" python setup.py build
shell> sudo python setup.py install

For 32-bit, you’ll have to do:

shell> ARCHFLAGS="-arch i386" python setup.py build
shell> sudo python setup.py install

Following error will be reported when you don’t specify the correct ARCHFLAGS:

ld: warning: in .../lib/libmysqlclient.dylib,
file is not of required architecture

Tips:

  • When building failed, it is good to remove oursql, unpack it and try again.
  • If you don’t want to compile anything, or run into more troubles, give MySQL Connector/Python a try (alpha releases). It’s a pure Python implementation of the MySQL Client/Server protocol and doesn’t need compiling or a MySQL installation.
  • You can download MySQL from either www.mysql.com or dev.mysql.com.
By |February 8th, 2010|Python|0 Comments|

FOSDEM: Change: Python/MySQL talk at 14:45 in MySQL Dev Room

Today we made a change in the schedule of talks held in the MySQL Developer Room at FOSDEM 2010, swapping two talks. Change is:

The printed booklets found at the conference will not reflect the change, but the printable schedule has already been updated.

By |February 3rd, 2010|Python|0 Comments|

FOSDEM 2010: Python sneaks into the MySQL DevRoom!

FOSDEM 2010, Sunday 7 February, the MySQL Developer Room packed with 12 talks! And this year we serve Python just before the lunch break.

In 20 minutes I’ll try to give an overview of the drivers currently available for connecting your Python applications with MySQL. Incase you wonder, this will not evolve around MySQL Connector/Python alone!

We’ll also go over some frameworks and tools like SQLAlchemy.

20 minutes, it’s not much, but should be enough. I hope to get a similar talk accepted for the MySQL Conference&Expo 2010.