PlanetMysql.ru – информация о СУБД MySQL

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!

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 does work also with others:

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()

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).

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).

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!

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).

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.