使用 Python 在 SQLite 数据库中插入二进制文件

Insert binary file in SQLite database with Python

提问by dmpop

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

我正在尝试编写一个简单的 Python 脚本，将 .odt 文档插入到 SQLite 数据库中。这是我到目前为止所做的，但似乎不起作用：

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!

我没有收到任何错误消息，但据我所知，没有插入记录。我究竟做错了什么？另外，如何将存储的文档提取回来？谢谢！

采纳答案by Alex Martelli

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

不确定sqlite.Binary您使用的是什么，但无论如何，这是一个有效的示例：

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

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'

当使用 Python 2.6 运行时，此代码按预期和需要显示： '\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'

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

请注意需要用于buffer插入 blob，并将str其作为字符串读回（因为它也使用buffer类型作为结果）——如果您只是要将其写入磁盘，则不需要后一段（因为writefiles的方法确实接受缓冲区对象，就像它接受字符串一样）。

回答by John Machin

Problems:

问题：

1. You didn't show the full code that you ran. You shouldn't leave answerers guessing what things like sqlite.Binary(k).

2. Fundamental problem: You didn't commit your transaction.Use conn.commit()before conn.close().

1. 您没有显示您运行的完整代码。您不应该让回答者猜测sqlite.Binary(k).

2. 根本问题：您没有提交交易。conn.commit()之前使用conn.close()。

回答by Jan Bodnar

There are multiple problems with the given example. I will address them one by one.

给定的示例存在多个问题。我会一一解决。

• There is no error checking. We either need to use the try/except/finally construct or use the withkeyword.
• Python methods are not like C# properties. You are not running the execute()method, you are assigning some string to an object. (In Python, methods are objects too.)
• Very important is that your code is subject to SQL Injection attacks. We should never build SQL statements using Python string operations. We should always use placeholders.
• The example is incomplete. Which leads to a tricky issue. Supposing, that there was a CREATE TABLEstatement then a new implicit transactionwould be created. And a commit()statement must be issued to save the data to the database file. In SQLite, any statement other than SELECTstarts an implicit transaction. (Some databases, like MySQL, are in the autocommit mode by default. This is not true for SQLite.)

• 没有错误检查。我们要么需要使用 try/except/finally 构造，要么使用with关键字。
• Python 方法与 C# 属性不同。您没有运行该execute()方法，而是将一些字符串分配给对象。（在 Python 中，方法也是对象。）
• 非常重要的是您的代码会受到SQL 注入攻击。我们永远不应该使用 Python 字符串操作来构建 SQL 语句。我们应该始终使用占位符。
• 该示例不完整。这导致了一个棘手的问题。假设有一个CREATE TABLE语句，那么将创建一个新的隐式事务。并且commit()必须发出一条语句将数据保存到数据库文件中。在 SQLite 中，除了SELECT启动隐式事务之外的任何语句。（某些数据库，例如 MySQL，默认情况下处于自动提交模式。对于 SQLite，情况并非如此。）

Here is a proper working example, which will write a LibreOffice document to a Docs table of an SQLite database:

这是一个正确的工作示例，它将将 LibreOffice 文档写入 SQLite 数据库的 Docs 表：

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite

fl = open('book.odt', 'rb')

with fl:
    data = fl.read()

con = lite.connect('test.db')

with con:

    cur = con.cursor()     

    cur.execute("CREATE TABLE IF NOT EXISTS Docs(Data BLOB)")

    sql = "INSERT INTO Docs(Data) VALUES (?)" 
    cur.execute(sql, (lite.Binary(data), ))

The book.odt file is located in the current working directory. We did not call the commit() method manually, since this is handled by the with keyword behind the scenes.

book.odt 文件位于当前工作目录中。我们没有手动调用 commit() 方法，因为这是由幕后的 with 关键字处理的。