MySQL Connector/Python on GitHub

Last week we released Connector/Python v2.0 (alpha); today we publish the source on GitHub. Yes, we are using Git internally and are now able to push it out on each release. Previous versions are still available through LaunchPad.

Here is the full process to get Connector/Python installed in a virtual environment. You’ll need Git installed of course.

shell> git clone https://github.com/oracle/mysql-connector-python.git cpy
shell> virtualenv ENVCPY
shell> source ENVCPY/bin/activate
(ENVCPY)shell> cd cpy
(ENVCPY)shell> python setup.py install
(ENVCPY)shell> python
>>> import mysql.connector
>>> mysql.connector.__version__
'2.0.0a1'

Please report issues and feature request through the MySQL Bugs System.

MySQL Connector/Python v2.0.0 alpha

A new major version of Connector/Python is available: v2.0.0 alpha has been been released and is available for download! As with any alpha-software, it’s probably not good to throw it in production just yet.

Our manual has the full change log but here’s an overview of most important changes for this relase.

Some incompatibilities

The world evolves, at least the software does, and Python is not different. I’m not as bold as the guys at Django who dropped support of Python v2.6 with the Django v1.7 release. I’m leaving it in because I’m nice.

Supported Python: 2.6 and 2.7 and 3.3 and 3.4

We do not support any longer Python 3.1 and 3.2. One of the reasons is that we consolidated the code bases for both major Python versions, and the unicode syntax brought back in 3.3 was a blessing (for example, u’パイソン’).

Raw Cursors Return bytearray Objects

Since we consolidated the code bases for Python 2 and 3, we needed make the behaviour as much as possible the same between the two. It’s not easy with Unicode strings, but with bytes we have the bytearray type. Raw cursors will return them instead of strings in Python 2 and bytes in Python 3.

If you want to have previous behaviour back, you can inherit from MySQLCursorRaw and change some methods. Please leave comments if you’d like an example for this.

LOAD LOCAL DATA INFILE  On by Default

In Connector/Python v1.x you needed to set the client flags to enable the LOAD LOCAL DATA INFILE on the client side. Here an example:

# Connector/Python v1.2
import mysql.connector
from mysql.connector import ClientFlag
cnx = mysql.connector.connect(.. , client_flags=[-ClientFlag.[LOCAL_FILES])

Now in Connector/Python v2.0 it is on. However, some people might not like it so there is a switch to disable it:

# Connector/Python v2.0
import mysql.connector
cnx = mysql.connector.connect(.. , allow_local_infile=False)

Note that you still need to make sure that the MySQL Server is configured to allow this statement.

New Cursors: dict and namedtuple

At last, we have cursors which return rows as dictionaries or named tuples. PEP-249 does not define these since not all database systems might return the columns in a case insensitive or sensitive way.

But this is MySQL.

Here is an example how to use cursor returning dictionaries:

query = (
    "SELECT TABLE_NAME, TABLE_ROWS "
    "FROM INFORMATION_SCHEMA.TABLES "
    " WHERE TABLE_SCHEMA='mysql' ORDER BY TABLE_NAME"
)
cur = cnx.cursor(dictionary=True)
cur.execute(query)
for row in cur:
    print("{TABLE_NAME:>30s} {TABLE_ROWS}".format(**row))

That’s far less code for something simple. Each row would look this:

    {u'TABLE_NAME': u'user', u'TABLE_ROWS': 11}

If you like named tuples better, you can do the same, simply giving the named_tuple argument.

cur = cnx.cursor(named_tuple=True)
cur.execute(query)
for row in cur:
    if row.TABLE_ROWS > 0:
        print("{name:>30s} {rows}".format(
            name=row.TABLE_NAME,
            rows=row.TABLE_ROWS))

You can also combine it with the raw=True argument to have raw cursors.

Options Files Support Added

Option files can now be read so you don’t have to have all these connection arguments repeated everywhere in your source code. There are lots of ways to do it this, but we needed to be able to read and support the MySQL options files read by client tools and server.

import mysql.connector
cnx = mysql.connector.connect(options_files='/etc/mysql/connectors.cnf')

By default we do not read any file. You have to explicitly specify which files and in which order have to be read. The options groups that are read are client and connector_python. You can also override this and specify which particular group(s) using the argument option_groups.

And more..

  • The  packaging and other supporting files have been removed. If you like to see WiX file (I dare you) for the Windows packaging, then you can (still) check the v1.x branches. We moved them away because these modules and files are (or can be) fixed and developed by other teams.
  • And watch out for v2.0.1 !

Useful links

MySQL Connector/Python v1.2.2 GA supporting MySQL Fabric

A few weeks ago MySQL Fabric got GA and shipped together with MySQL Utilities 1.4. Today, 2 weeks late, a tooth less and having fixed my website(s), I can blog about MySQL Connector/Python v1.2 going GA.

My previous post about the release candidate already summed up the important changes, and nothing changed since then except few bug fixes. Here are again the important new features/bugs fixed for Connector/Python v1.2:

  • Added support for MySQL Fabric.
  • Support for MySQL’s Authentication Plugins: mysql_clear_password and sha256_password are now supported over an SSL connection.
  • Failing over connections (note: not MySQL Fabric HA)

Of course, Fabric is the biggest new thing and there have been quite a few blogs about it. Here are some links to get you going:

Some useful links:

MySQL Connector/Python v1.2.1 RC

Monday 31 March 2014 proved to be a big day for MySQL with 5 announcements of products getting new releases: the next milestone release MySQL Server 5.7.4, MySQL Workbench 6.1 going GA and Connector/J 5.1.30 and MySQL Utilities 1.4.2 RC which include MySQL Fabric. That’s 4, the fifth being Connector/Python.

Connector/Python v1.2.1 has been released as Release Candidate. Check out the Change History if you want to keep up with what is being added and changed.

Here is an overview of new features and important bug fixes for Connector/Python v1.2:

  • Added support for MySQL Fabric
  • Support for MySQL’s Authentication Plugins: mysql_clear_password and sha256_password are now supported over an SSL connection.
  • Failing over connections (note: not MySQL Fabric HA)

I am sure the a above will spark some interesting follow-up blog posts (at last, some activity, some might say!).

Some useful links:

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.