将二进制文件中使用Python SQLite数据库 [英] Insert binary file in SQLite database with Python

问题描述

我想写插入的.odt文件到SQLite数据库一个​​简单的Python脚本。在这里，我有这么远，但它似乎没有工作：

I trying to write a simple Python script that inserts .odt documents into an SQLite database. Here what I have so far, but it doesn't seem to work:

f=open('Loremipsum.odt', 'rb')
k=f.read()
f.close()
cursor.execute="INSERT INTO notes (note) VALUES ('%s')" %(sqlite.Binary(k))
cursor.close()
conn.close()

我没有得到任何错误消息，但据我可以看到记录未插入。我究竟做错了什么？另外，我怎么可以提取储存的文件回来？谢谢！

I don't get any error messages, but as far as I can see the record is not inserted. What am I doing wrong? Also, how can I extract the stored document back? Thanks!

推荐答案

不知道那是什么 sqlite.Binary 你使用，但是，无论如何，这里有一个工作例如：

Not sure what is that sqlite.Binary you're using, but, anyway, here's a working example:

import sqlite3

# let's just make an arbitrary binary file...
with open('/tmp/abin', 'wb') as f:
  f.write(''.join(chr(i) for i in range(55)))
# ...and read it back into a blob
with open('/tmp/abin', 'rb') as f:
  ablob = f.read()

# OK, now for the DB part: we make it...:
db = sqlite3.connect('/tmp/thedb')
db.execute('CREATE TABLE t (thebin BLOB)')
db.execute('INSERT INTO t VALUES(?)', [buffer(ablob)])
db.commit()
db.close()

# ...and read it back:
db = sqlite3.connect('/tmp/thedb')
row = db.execute('SELECT * FROM t').fetchone()
print repr(str(row[0]))

在与Python 2.6运行时，此code表示，预期和期望：
    '\\x00\\x01\\x02\\x03\\x04\\x05\\x06\\x07\\x08\\t\
\\x0b\\x0c\\r\\x0e\\x0f\\x10\\x11\\x12\\x13\\x14\\x15\\x16\\x17\\x18\\x19\\x1a\\x1b\\x1c\\x1d\\x1e\\x1f ！＃$％＆放大器; \\'（）* +， - 。/ 0123456

When run with Python 2.6, this code shows, as expected and desired: '\x00\x01\x02\x03\x04\x05\x06\x07\x08\t\n\x0b\x0c\r\x0e\x0f\x10\x11\x12\x13\x14\x15\x16\x17\x18\x19\x1a\x1b\x1c\x1d\x1e\x1f !"#$%&\'()*+,-./0123456'

请注意，需要使用缓存插入BLOB和 STR 读回为一个字符串（因为它使用了缓存键入结果以及） - 如果你只是要其写入到磁盘中，后者通过将不再需要（因为文件写入方法不接受缓冲区对象一样好，它接受字符串）。

Note the need to use buffer to insert the blob, and str to read it back as a string (since it uses the buffer type as a result as well) -- if you're just going to write it to disk the latter passage would not be needed (since the write method of files does accept buffer objects just as well as it accepts strings).

这篇关于将二进制文件中使用Python SQLite数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！