Geert JM Vanderkelen

Update 2010-08-27: Comments indicated that what I did here is not the best solution. Like noted in my original post, a set would be better in this case. I eventually used set(r).issubset(set(l)). Marius also pointed out to set(r) <= set(l), but I like the issubset one more.

I wanted to check if every element of one list or tuple is part of another one using Python. A set has the issubset()-method, but I couldn't find anything build-in for a tuple. It was, however, rather quickly done:

>>> r = (1,2)
>>> l = (3,4,1,5,2)
>>> False not in [ e in l for e in r ]
True
>>> r = (1,9)
>>> False not in [ e in l for e in r ]
False

Why I'm posting this? I just found it cute code, somehow.

In Pylons v1.0 you can define global variables by adding them to the Globals-class. If you want a variable called spam and you want it to be globally available, your lib.app_globals.Globals-class would look like this:

class Globals(object):
  def __init__(self, config):
    self.cache = CacheManager(**parse_cache_config_options(config))
    self.spam = False

To use it in a model-module for example, you have to import app_globals from the pylons module, like this:

from pylons import app_globals as g

print g.spam

It took me a while to figure this out.. And I'm starting to like Pylons somehow.

MySQL Connector/Python is (or should be) compliant with the Python DB-API 2.0 specification. This means that you can use DBUtils' PooledDB module to implement database connection pooling.

Here below you'll find an example which will output the connection ID of each connection requested through the pooling mechanism.

from DBUtils.PooledDB import PooledDB
import mysql.connector

def main():
    pool_size = 3
    pool = PooledDB(mysql.connector, pool_size,
        database='test', user='root', host='127.0.0.1')
    
    cnx = [None,] * pool_size
    for i in xrange(0,pool_size):
        cnx[i] = pool.connection()
        cur = cnx[i].cursor()
        cur.execute("SELECT CONNECTION_ID()")
        print "Cnx %d has ID %d" % (i+1,cur.fetchone()[0])
        cur.close()
    
    for c in cnx:
        c.close()

The output will be something like this:

Cnx 1 has ID 42
Cnx 2 has ID 41
Cnx 3 has ID 40

We just released MySQL Connector/Python 0.1.5 which includes a critical bug fix. It was impossible to read big result sets. The files for 0.1.4-release have been removed.

You can download MySQL Connector/Python from Launchpad.

Highlights:

• It was impossible to retrieve big result sets. (bug lp:551533 and lp:586003)
• Changing copyright from Sun to Oracle (also fixing silly typo)

A very Big Thanks goes to the reporters of bug lp:551533 and lp:586003. Apologies for not being able to reproduce the bug earlier, before releasing 0.1.4.

About MySQL Connector/Python: MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. It is compatible with Python v2.5 and later as well as Python v3.1 and later.

Next development release 0.1.4 of MySQL Connector/Python is now available for download. This will be the last in the 0.1-series as we move on to 0.2. The aim is to release more often to get to v1.0. Hurray!

Highlights:

• Reading from network was broken for bigger packages.
• Reimplementing protocol.MySQLProtocol marking simpler and easier to maintain.
• It is now possible to send multiple statements to MySQL using MySQLCursor.execute(). The results are accessible by calling the method next_resultset().
• MySQLCursor.callproc() will now store all result sets as a MySQLCursorBuffered. They are accessible using the next_proc_resultset() method. The result of the stored procedure is returned by callproc() itself as defined by PEP249.
• MySQL DATETIME conversion to Python datetime.datetime is now much faster.
• Some overall performance improvements.
• Copyright notice changes.

Big thanks to everyone using and reporting bugs found in MySQL Connector/Python. Don't hesitate to ask questions and report problems or feature requests using Launchpad.

About MySQL Connector/Python: MySQL Connector/Python is implementing the MySQL Client/Server protocol completely in Python. No MySQL libraries are needed, and no compilation is necessary to run this Python DB API v2.0 compliant driver. It is compatible with Python v2.5 and later as well as Python v3.1 and later.

Few days ago, the folks at SQLAlchemy pushed some proposed modification to the MySQL Connector/Python dialect. Before this patch, previous report yielded 72 errors and 11 failures. Now we got down to 9 errors, but the failures are still lingering. Is this an improvement? Yes and no, failures should go down, but there are some SQLAlchemy tests I just can't figure out, yet.. clues are welcome!

Here are some detailed results which also included MySQLdb and oursql. I used SQLAlchemy revision 6788 (i.e. from svn trunk) which has now 2143 unittests:

• mysql.connector rev216: SKIP=1, errors=9, failures=11 (355.707s)
• MySQLdb 1.2.3c1: SKIP=2, errors=8, failures=1 (315.884s)
• oursql 0.9.1: SKIP=1, errors=8, failures=2 (322.318s)

Software used: MacOSX v10.6.2, MySQL v5.1.42 and Python v2.6.1.

Using SQLAlchemy might not be the best way to messure how mature MySQL Connector/Python is, but it sure helps lots.

To far fetched (pun inteded), some might think.. Below you'll find a procedure to get a list of MySQL supported character sets and their collations. The output is Python and can be used to build a (big) tuple.

The problem is that character set IDs in MySQL have 'gaps'. For example hebrew has ID 16, and the next character set in the COLLATIONS-table, tis620, has ID 18. Not a big problem, just a bit annoying. This list is hardcoded in MySQL Connector/Python and I needed something to easily maintain it.

Solutions were using text editing skills, parsing it in Python to produce a list, etc.. But why not a Stored Routine? The following stored procedure is looping over a cursor, calculating the number of rows the gap has, and inserts blanks as needed.

DROP PROCEDURE IF EXISTS python_getcharsets;
DELIMITER //
CREATE PROCEDURE python_getcharsets()
BEGIN
  DECLARE i,diff,cid,done INT DEFAULT 0;
  DECLARE chname,coname VARCHAR(32);
  DECLARE cur CURSOR FOR SELECT ID,CHARACTER_SET_NAME,
    COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
    ORDER BY ID;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  OPEN cur;
  loop_cur: LOOP
    FETCH cur INTO cid,chname,coname;
    IF done THEN LEAVE loop_cur; END IF;
    SET i = i + 1; SET diff = cid - i;
    WHILE diff DO
      SELECT "None,";
      SET diff = diff - 1; SET i = i + 1;
    END WHILE;
    SELECT CONCAT('("',chname,'","',coname,'"), # ',cid);
  END LOOP loop_cur;
  CLOSE cur;
END//
DELIMITER ;

CALL python_getcharsets();
DROP PROCEDURE IF EXISTS python_getcharsets;

You would save the above to a file called getcharsets.sql for example, and execute it like this:

shell> mysql -N test < getcharsets.sql 
..
("cp1251","cp1251_bulgarian_ci"), # 14
("latin1","latin1_danish_ci"), # 15
("hebrew","hebrew_general_ci"), # 16
None,
("tis620","tis620_thai_ci"), # 18
("euckr","euckr_korean_ci"), # 19
..

I'll spare you the complete output, but as you can see from above sample: the gap has been stuffed with a None-Python value. Taking this output, you'll inserted it your code:

desc = (
    None,
    ("big5","big5_chinese_ci"), # 1
    ("latin2","latin2_czech_cs"), # 2
    ("dec8","dec8_swedish_ci"), # 3
    ("cp850","cp850_general_ci"), # 4
..

Silly? Definitely basic stuff, but I got my code a bit faster, and cleaner!

Is there a performance penalty using a pure Python database interface? Yes there is. But how much? .. also depends on who wrote it.

I started implementing some benchmarking for MySQL Connector/Python. Main reason is to identified bottlenecks or just plain bad coded. Another reasons: it's cool and the question was raised during my talk at FOSDEM and also online.

Oh, yes, MySQL Connector/Python ain't fast right now. Just compare a script spawning 10 threads opening 1000 connections. It indeed just connects.

mysql.connector 0.1.3-devel average: 0.0091820
MySQLdb 1.2.3c1             average: 0.0026477
oursql 0.9.1                average: 0.0007394

(MacOSX 10.6.2, MySQL 5.1.42, Python 2.6.1)

I'm not surprised with how bad Connector/Python did. I'm more intrigued by how oursql is performing compared to MySQLdb. Nice!

Well, it's a start. Now off for a walk!

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:

• MySQL Cluster Tutorial, Mon 08:30 on 12 Apr 2010, together with Andrew Hutchings and Andrew Morgan
• MySQL Cluster: An Introduction, Tue 11:55 on 13 Apr 2010
• Connecting MySQL and Python, Tue 15:05 on 13 Apr 2010

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!

Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you'll have to commit (or roll back) your inserts, deletes or updates.

I've seen it a few times now with people being surprised that no data is going into the tables. It's not so a silly problem in the end. If you are used to the defaults in MySQL you don't have to commit anything since it is automatically done for you.

Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off. You could turn it back on, but it's good practice to be explicit and commit in your code. Remember the Zen of Python!

Here is just a small example to show it. Uses MySQL Connector/Python, but it should also work with the other MySQL database interfaces:

import mysql.connector
cnx = mysql.connector.connect(db='test')
cur = cnx.cursor()
cur.execute("""CREATE TABLE innodb_t1 (
id INT UNSIGNED NOT NULL,
c1 VARCHAR(128),
PRIMARY KEY (id)
) ENGINE=InnoDB""")
ins = "INSERT INTO innodb_t1 (id,c1) VALUES (%s,%s)"
cur.execute(ins,
(1,'MySQL Support Team _is_ already the best',))
cnx.commit()
cur.close()
cnx.close()

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.

Apparently, my talk at FOSDEM 2010 about Connecting MySQL and Python was the only one about Python? There should be more, or?

I have a hand-out ready in PDF. The slides are not usable without my chatter. It contains a few examples and links. Any comments, corrections, criticism.. are welcome!

The longer version of this talk will be given at the O'Reilly MySQL Conference&Expo 2010 in Santa Clara, California (USA).

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

• 12:15 CET - Giuseppe's 'Sharding for the Masses
• 14:25 CET - Geert's Connecting MySQL and Python (title also changed)

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

Using SQLAlchemy unit test cases to further develop MySQL Connector/Python. It's probably debatable whether that's a good method or just lame. But it sure helps lots!

We've been pushing some code past days that makes Connector/Python almost pass all tests. Well, 4% is still failing, but I'm confident that in a few most problems will be dealt with. I had to make some changes to the SQLAlchemy v0.6 dialect as well, and some test cases had to be corrected. Hopefully those corrections will also go in the SQLAlchemy trunk later on.

shell> nosetests --dburi=mysql+mysqlconnector://root:@localhost/sqlalchemy
..
----------------------------------------------------------------------
Ran 2092 tests in 314.656s

FAILED (errors=72, failures=11)

If you run the same tests against MySQLdb, you'll see (of course) less failures (9 to be exact).

Last week I took SQLAlchemy v0.6 out of its trunk and tested it again with our MySQL Connector/Python. And surprise! SQLAlchemy comes with a 'dialect' supporting it! Nice!

However, we're still a long way on making it work. I found some additional problems which need to be fixed first.

shell> nosetests --dburi=mysql+mysqlconnector://root:@localhost/sqlalchemy \
    test/dialect/test_mysql.py
..
Ran 32 tests in 4.507s

FAILED (errors=6, failures=1)

Oh, there were more errors and failures and all that due to bugs in MySQL Connector/Python. There were already some modification to the dialect (e.g. name change), and there might be some more.

If we got this sorted, Turbogears should work too!

Something to keep you warm during cold winter nights, or cool during hot summer days: a chessboard in MySQL!

Note: You should see chess pieces here below. If not, you're not watching it using UTF-8, or get yourself a good browser!

CREATE TABLE `chessboard` (
  `x` tinyint unsigned NOT NULL,
  `a` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `b` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `c` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `d` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `e` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `f` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `g` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟'),
  `h` enum('','♔','♕','♖','♗','♘','♙','♚','♛','♜','♝','♞','♟')
) DEFAULT CHARSET=utf8;

Populating it with Python using MySQL Connector/Python (only piece of script shown):

def create_board(db):
    c = db.cursor()
    
    table = """
        CREATE TABLE chessboard (
        x tinyint unsigned not null,
        a ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        b ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        c ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        d ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        e ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        f ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        g ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F'),
        h ENUM('','\u2654','\u2655','\u2656','\u2657','\u2658','\u2659',
            '\u265A','\u265B','\u265C','\u265D','\u265E','\u265F')
        ) default charset='utf8'
    """
    c.execute("DROP TABLE IF EXISTS chessboard")
    c.execute(table)

def set_start_position(db):
    c = db.cursor()
    # Numbers correspondent to the ENUM fields
    wdata = { 'x' : 1,
        'a': 4, 'b': 6, 'c': 5,
        'd': 3, 'e': 2,
        'f': 5, 'g': 6, 'h': 4}
    bdata = { 'x' : 8,
        'a': 10, 'b': 12, 'c': 11,
        'd': 9, 'e': 8,
        'f': 11, 'g': 12, 'h': 10}
    
    stmt = """UPDATE chessboard SET a=%(a)s,b=%(b)s,c=%(c)s,
        d=%(d)s,e=%(e)s,f=%(f)s,g=%(g)s,h=%(h)s WHERE x = %(x)s"""
    c.executemany(stmt, [wdata,bdata])
    
    stmt = """UPDATE chessboard SET a=7,b=7,c=7,d=7,e=7,f=7,g=7,h=7
        WHERE x = 2"""
    c.execute(stmt)
    stmt = """UPDATE chessboard SET a=13,b=13,c=13,d=13,e=13,f=13,g=13,h=13
        WHERE x = 7"""
    c.execute(stmt)

Now you can select the chessboard:

mysql> select * from chessboard;
+---+------+------+------+------+------+------+------+------+
| x | a    | b    | c    | d    | e    | f    | g    | h    |
+---+------+------+------+------+------+------+------+------+
| 8 | ♜    | ♞    | ♝    | ♛    | ♚    | ♝    | ♞    | ♜    |
| 7 | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    | ♟    |
| 6 |      |      |      |      |      |      |      |      |
| 5 |      |      |      |      |      |      |      |      |
| 4 |      |      |      |      |      |      |      |      |
| 3 |      |      |      |      |      |      |      |      |
| 2 | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    | ♙    |
| 1 | ♖    | ♘    | ♗    | ♕    | ♔    | ♗    | ♘    | ♖    |
+---+------+------+------+------+------+------+------+------+

The possibilities.. oh yeah!

Here is two small examples showing the wonderful world of character sets and unicode using MySQL Connector/Python (using 0.1.2-devel and up) in both Python v2.x and v3.1.

The following table will be used with default character set latin7, i.e. ISO-8859-13. Just setting it to UTF-8 would be bit boring!

CREATE TABLE `latin7test` (
  `c1` varchar(60) DEFAULT NULL
) DEFAULT CHARSET=latin7

Things to note for the code listed below are:

• We're using charset='latin7' as connection option. This is important!
• We set use_unicode=True so the results coming from MySQL are encoded to unicode. For testing, we disable this later.

Python v2.x

Here is the code which will insert (Polish) latin7 text and selects them again from the table.

db = mysql.connect(user='root',db='test',
    buffered=True,
    charset="latin7",
    use_unicode=True)

latin7 = [
    # Hello in Polish
    'dzie\xf1 dobry!',
    'cze\xfa\xe3!'
    ]

cur = db.cursor()
stmt = 'INSERT INTO latin7test VALUES (%(c1)s)'
cur.execute(stmt, { 'c1' : latin7[0] } )
stmt = 'INSERT INTO latin7test VALUES (%s)'
cur.execute(stmt, (latin7[1],) )

stmt = 'SELECT * FROM latin7test'
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

db.set_unicode(False)
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

cur.close()
db.close()

The result:

[(u'dzie\u0144 dobry!',), (u'cze\u015b\u0107!',)]
[('dzie\xf1 dobry!',), ('cze\xfa\xe3!',)]

The above might look weird, but if you put this in a webpage with proper encoding or print it in a terminal which supports UTF8 or latin1, it should look nice.

Python v3.1

db = mysql.connect(user='root',db='test',
    charset="latin7",use_unicode=True)

latin7 = [
    # Hello in Polish
    b'dzie\xf1 dobry!',
    b'cze\xfa\xe3!'
    ]

cur = db.cursor()
stmt = 'INSERT INTO latin7test VALUES (%(c1)s)'
cur.execute(stmt, { 'c1' : latin7[0] } )
stmt = 'INSERT INTO latin7test VALUES (%s)'
cur.execute(stmt, (latin7[1],) )

stmt = 'SELECT * FROM latin7test'
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

db.set_unicode(False)
cur.execute(stmt)
rows = cur.fetchall()
print(rows)

cur.close()
db.close()

The result:

[('dzień dobry!',), ('cześć!',)]
[(b'dzie\xf1 dobry!',), (b'cze\xfa\xe3!',)]

The above looks nicer than the Python v2.4+ one. That's because in Python v3.x every string is now unicode. The second line shows the same data, but encoded in latin7 and returned as bytes-objects since use_unicode is set to False.