python往mysql的blob字段写入二进制数据

Python Cookbook, 2nd Edition
Recipe 7.10. Storing a BLOB in a MySQL Database

Python code

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

import  MySQLdb, cPickle # Connect to a DB, e.g., the test DB on your localhost, and get a cursor connection  =  MySQLdb.connect(db = "test" ) cursor  =  connection.cursor( ) # Make a new table for experimentation cursor.execute( "CREATE TABLE justatest (name TEXT, ablob BLOB)" ) try :      # Prepare some BLOBs to insert in the table      names  =  'aramis' ,  'athos' ,  'porthos'      data  =  {  }      for  name  in  names:          datum  =  list (name)          datum.sort( )          data[name]  =  cPickle.dumps(datum,  2 )      # Perform the insertions      sql  =  "INSERT INTO justatest VALUES(%s, %s)"      for  name  in  names:          cursor.execute(sql, (name, MySQLdb.escape_string(data[name])) )      # Recover the data so you can check back      sql  =  "SELECT name, ablob FROM justatest ORDER BY name"      cursor.execute(sql)      for  name, blob  in  cursor.fetchall( ):          print  name, cPickle.loads(blob), cPickle.loads(data[name]) finally :      # Done. Remove the table and close the connection.      cursor.execute( "DROP TABLE justatest" )      connection.close( )

一直想在MySQL中直接存储二进制数据，尝试到今天也没有解决这个问题，但仍有几点要记录一下，比较乱：

1. 可惜设置不了0编号，就将就着在这里写下版本吧。Python 2.4.4，MySQLdb 1.2.1_p2，MySQL 5.0.32
2. 不要使用'%r'，比如'INSERT INTO mytbl VALUES (%r)'，这样做就很没意思了，读取一个二进文件内容到data，然后再使用file.write('%r' % data)写入到文件，打开看以下你就会明白。使用'%r'还不如使用base64编码。
3. MySQLdb.escape_string对二进制数据使用没有问题，escape后的数据只增大了一点。据说新的版本下这个可以省略。
4. 使用'INSERT INTO mytbl VALUES (%s)' % MySQLdb.escape_string(data)应该是对的，但会提示UnicodeDecodeError。
5. 第4点中的错误，怀疑是数据库的charset关系，我的db的character_set_name()返回"latin1"；也有可能是组织SQL命令字串时就出现的问题。
6. Python Codebook, Page 355, Chapter 8.7 Storing a BLOB in a MYSQL Database，有时间仔细看看。
7. 使用MySQL的LOAD_FILE函数是跳过以上问题的好方法，看开始时我总是得到NULL，折腾了半天发现要使用这个函数需要得到file权限，这个权限必须设置给全局（又折腾了一会），(using ON *.* syntax)。详细可以参见MySQL的GRANT语法。
   GRANT FILE ON *.* to you@localhost identified by "secret";
8. 使用LOAD_FILE得到的结果正是我所需要的，如果第4步成功，也应该是这样。
9. 更正：第5点中的错误是从cursors.py的146行产生的：query = query.encode(charset)。带二进制的MySQL命令字串是可以产生的，但对它使用execute就会有这样的问题。MySQL中倒是有binary的charset类型，但python中没有，所以不能在创建数据库时指定charset为binary。MySQL字符集相关资料可以参见这里。
10. 万般无奈之下我决定抄下Python Codebook, Chapter 8.7.2的代码来运行，竟然没遇到折磨我的encode错误，再把程序中的二进制数据换成我测试用的图片数据，还是正常。仔细看下，原来我用execute的方式不一样：
    cursor.execute(query % binary_data)
    而codebook里是这样写的：
    cursor.execute(query, binary_data)
    看看"/usr/lib/python2.4/site-packages/MySQLdb/cursors.py"中的execute，就知道区别在那里了，codebook的代码当然没有encode错误，因为不会对二进制数据操作。

终于结束摸索了，总结一下：

• 插入BLOB不需要使用特别的CHARACTER SET，和这个问题无关
• 如果要插入二进制数据，使用"execure(query, binary_data)"的方式
• 在新的版本下不要对binary_data使用escape_string函数
• 从数据库中获取到的BLOB的数据类型是array，我通常使用array.tofile来直接保存到文件，当然也可以使用其它的方式，请自己查找python::array的资料
• 如果使用LOAD_FILE，请确保你的MySQL用户有FILE的权限，另外文件要可访问并且不要太大。

Welcome to PicVew.com