Python Cookbook, 2nd Edition
Recipe 7.10. Storing a BLOB in a MySQL Database
Recipe 7.10. Storing a BLOB in a MySQL Database
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中直接存储二进制数据,尝试到今天也没有解决这个问题,但仍有几点要记录一下,比较乱:
- 可惜设置不了0编号,就将就着在这里写下版本吧。Python 2.4.4,MySQLdb 1.2.1_p2,MySQL 5.0.32
- 不要使用'%r',比如'INSERT INTO mytbl VALUES (%r)',这样做就很没意思了,读取一个二进文件内容到data,然后再使用file.write('%r' % data)写入到文件,打开看以下你就会明白。使用'%r'还不如使用base64编码。
- MySQLdb.escape_string对二进制数据使用没有问题,escape后的数据只增大了一点。据说新的版本下这个可以省略。
- 使用'INSERT INTO mytbl VALUES (%s)' % MySQLdb.escape_string(data)应该是对的,但会提示UnicodeDecodeError。
- 第4点中的错误,怀疑是数据库的charset关系,我的db的character_set_name()返回"latin1";也有可能是组织SQL命令字串时就出现的问题。
- Python Codebook, Page 355, Chapter 8.7 Storing a BLOB in a MYSQL Database,有时间仔细看看。
- 使用MySQL的LOAD_FILE函数是跳过以上问题的好方法,看开始时我总是得到NULL,折腾了半天发现要使用这个函数需要得到file权限,这个权限必须设置给全局(又折腾了一会),(using ON *.* syntax)。详细可以参见MySQL的GRANT语法。
GRANT FILE ON *.* to you@localhost identified by "secret"; - 使用LOAD_FILE得到的结果正是我所需要的,如果第4步成功,也应该是这样。
- 更正:第5点中的错误是从cursors.py的146行产生的:query = query.encode(charset)。带二进制的MySQL命令字串是可以产生的,但对它使用execute就会有这样的问题。MySQL中倒是有binary的charset类型,但python中没有,所以不能在创建数据库时指定charset为binary。MySQL字符集相关资料可以参见这里。
- 万般无奈之下我决定抄下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