MySQL Connector/Python v1.1.3 beta

Connector/Python v1.1.3 is available for testing since last week. It is a “beta” release, so it would be great if we even get more feedback. Check out the Change History if you want to keep up with what is being added and changed.

Notable changes for v1.1.3 include a fix for encoding using \x5c or backslashes in multi-byte characters. We also made the code more PEP-8 compliant, which we think is quiet important.

Some useful links:

Simulating server-side cursors with MySQL Connector/Python

Update Feb 6 2014: I have edited the example code a bit. Note that this has never been included in any GA release of Connector/Python.

Last week, my colleague Massimo and I discussed how to handle big result sets coming from MySQL in Python. The problem is that MySQL doesn’t support server-side cursors, so you need to select everything and then read it. You can do it either buffered or not. MySQL Connector/Python defaults to non-buffered, meaning that you need to fetch all rows after issuing a SELECT statement. You can also turn on the buffering, mimicking what MySQL for Python (MySQLdb) does.

For big result sets, it’s better to limit your search. You can do this using an integer primary key or some temporal field for example. Or you can use the LIMIT keyword. The latter solution is what is used in the MySQLCursorServerSide cursor-class. Using the SELECT it creates a temporary table from which the fetch-methods will get the information. It is something people have probably implemented in their applications, but I hope this new class will make it easier since it’s done transparently.

The code is not pushed yet, but expect it to be available in next release. Here is an example how you could use it. This code selects cities staring with Z, loops over the result getting the country (yes, this is a simple join made difficult):

    cnx = db.connect(user='root', db='world')
    cur = cnx.cursor()
    curCity = cnx.cursor(db.cursor.MySQLCursorServerSide)

    curCity.execute(
        "SELECT ID,Name,CountryCode FROM City "
        "WHERE NAME LIKE 'Z%' ORDER BY ID")

    query = "SELECT Code, Name FROM Country WHERE CODE = %s"
    for city in curCity:
        cur.execute(query, (city[2],))
        country = cur.fetchone()
        print("{0} ({1})".format(city[1], country[1]))

    cur.close()
    cnx.close()

I guess the main advantage is that you can use two or more cursor objects with the same connection without the need of buffering everything in Python. On the MySQL side, the temporary table could go to disk when to big. It’s maybe slower, but keeping big result sets in memory ain’t good either.

Comments are welcome!

Multiple result sets in MySQL Connector/Python

Latest code of MySQL Connector/Python on launchpad has support for multiple result sets when you execute a stored procedure. We also changed the way the actual result of the routine is returned to conform to PEP249.

Here is some example code: it creates a stored procedure which generates 2 result sets. You can get the result by calling next_resultset(), which is returning a MySQLCursorBuffered.

    cur = cnx.cursor()

    cur.execute("DROP PROCEDURE IF EXISTS multi")
    proc = """
      CREATE PROCEDURE multi(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
      BEGIN
        SELECT 1,'a' as FooBar;
        SELECT 2;
        SET pProd := pFac1 * pFac2;
      END"""

    cur.execute(proc)
    result = cur.callproc("multi", (5, 6, 0))
    print "Result:", result
    
    extcur = cur.next_resultset()
    i = 1
    while extcur:
        rows = extcur.fetchall()
        print "Result set #%d:" % i
        print rows
        extcur = cur.next_resultset()
        i += 1
        
    cur.close()

The output:

Result: ('5', '6', 30)
Result set #1:
[(1, u'a')]
Result set #2:
[(2,)]

As mentioned above: this will be part of 0.1.4-devel release due next week. Comments are welcome through the associated bug report.

MySQL Connector/Python 0.1.3-devel available

MySQL Connector/Python v0.1.3-devel is now available for download from Launchpad.org. Please note that this is a development (i.e. alpha, unstable, ..) release and we welcome everyone to test and report problems.

Highlights for this v0.1.3-devel:

  • Important memory leak fixed when closing cursors.
  • Warnings can now be raised as exceptions.
  • Fixing unicode usage and broken error message when MySQL chops them
  • Client flags can now be set correctly when connecting
  • Conversion fixes for BIT/YEARSET and Python to DATE/DATETIME
  • Adding MySQL Client Errors and raising better exceptions based on errno.

Enjoy!

By |January 28th, 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.