Python sqlite3.ProgrammingError：除非使用可以解释 8 位字节串的 text_factory，否则不得使用 8 位字节串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3425320/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings
提问by R. Hill
Using SQLite3 in Python, I am trying to store a compressed version of a snippet of UTF-8 HTML code.
在 Python 中使用 SQLite3，我试图存储 UTF-8 HTML 代码片段的压缩版本。
Code looks like this:
代码如下所示：
...
c = connection.cursor()
c.execute('create table blah (cid integer primary key,html blob)')
...
c.execute('insert or ignore into blah values (?, ?)',(cid, zlib.compress(html)))
At which point at get the error:
此时得到错误：
sqlite3.ProgrammingError: You must not use 8-bit bytestrings unless you use a text_factory that can interpret 8-bit bytestrings (like text_factory = str). It is highly recommended that you instead just switch your application to Unicode strings.
If I use 'text' rather than 'blob' and don't compress the HTML snippet, it works all fine (db is to large though). When I use 'blob' and compress via Python zlib library, I get the above error message. I looked around but couldn't find a simple answer for this one.
如果我使用 'text' 而不是 'blob' 并且不压缩 HTML 片段，它一切正常（尽管 db 很大）。当我使用“blob”并通过 Python zlib 库进行压缩时，出现上述错误消息。我环顾四周，但找不到一个简单的答案。
回答by R. Hill
Found the solution, I should have spent just a little more time searching.
找到了解决方案，我应该多花一点时间搜索。
Solution is to 'cast' the value as a Python 'buffer', like so:
解决方案是将值“转换”为 Python 的“缓冲区”，如下所示：
c.execute('insert or ignore into blah values (?, ?)',(cid, buffer(zlib.compress(html))))
Hopefully this will help somebody else.
希望这会帮助别人。
回答by zag
If you want to use 8-bit strings instead of unicode string in sqlite3, set approptiate text_factory for sqlite connection:
如果要在 sqlite3 中使用 8 位字符串而不是 unicode 字符串，请为 sqlite 连接设置适当的 text_factory：
connection = sqlite3.connect(...)
connection.text_factory = str
回答by zwalker
You could store the value using repr(html) instead of the raw output and then use eval(html) when retrieving the value for use.
您可以使用 repr(html) 而不是原始输出来存储值，然后在检索要使用的值时使用 eval(html) 。
c.execute('insert or ignore into blah values (?, ?)',(1, repr(zlib.compress(html))))
回答by MarioVilas
In order to work with the BLOB type, you must first convert your zlib compressed string into binary data - otherwise sqlite will try to process it as a text string. This is done with sqlite3.Binary(). For example:
为了使用 BLOB 类型，您必须首先将 zlib 压缩字符串转换为二进制数据 - 否则 sqlite 会尝试将其作为文本字符串处理。这是通过 sqlite3.Binary() 完成的。例如：
c.execute('insert or ignore into blah values (?, ?)',(cid,
sqlite3.Binary(zlib.compress(html))))
回答by Pranzell
Syntax:
句法：
5 types of possible storage: NULL, INTEGER, TEXT, REAL and BLOB
5 种可能的存储类型：NULL、INTEGER、TEXT、REAL 和 BLOB
BLOB is generally used to store pickled models or dill pickled models
BLOB一般用于存放腌制模型或莳萝腌制模型
> cur.execute('''INSERT INTO Tablename(Col1, Col2, Col3, Col4) VALUES(?,?,?,?)''',
[TextValue, Real_Value, Buffer(model), sqlite3.Binary(model2)])
> conn.commit()
> # Read Data:
> df = pd.read_sql('SELECT * FROM Model, con=conn)
> model1 = str(df['Col3'].values[0]))
> model2 = str(df['Col'].values[0]))
