Python、Sqlite3 - 如何将列表转换为 BLOB 单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/537077/
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
Python, Sqlite3 - How to convert a list to a BLOB cell
提问by Gilad Naor
What is the most elegant method for dumping a list in python into an sqlite3 DB as binary data (i.e., a BLOB cell)?
将python中的列表作为二进制数据（即BLOB单元）转储到sqlite3 DB中的最优雅方法是什么？
data = [ 0, 1, 2, 3, 4, 5 ]
# now write this to db as binary data
# 0000 0000
# 0000 0001
# ...
# 0000 0101
采纳答案by Brian
Assuming you want it treated as a sequence of 8-bit unsigned values, use the arraymodule.
假设您希望将其视为 8 位无符号值序列，请使用该array模块。
a = array.array('B', data)
>>> a.tostring()
'\x00\x01\x02\x03\x04\x05'
Use different typecodes than 'B'if you want to treat the data as different types. eg. 'b' for a sequence of signed bytes, or 'i'for a signed integer.
使用不同的类型代码，而不是'B'要将数据视为不同的类型。例如。'b' 表示有符号字节序列或'i'有符号整数。
回答by monkut
It seems that Brian's solution fits your needs, but keep in mind that with that method your just storing the data as a string.
看起来 Brian 的解决方案符合您的需求，但请记住，使用该方法您只需将数据存储为字符串。
If you want to store the raw binary data into the database (so it doesn't take up as much space), convert your data to a Binarysqlite object and then add it to your database.
如果要将原始二进制数据存储到数据库中（因此它不会占用太多空间），请将数据转换为Binarysqlite 对象，然后将其添加到数据库中。
query = u'''insert into testtable VALUES(?)'''
b = sqlite3.Binary(some_binarydata)
cur.execute(query,(b,))
con.commit()
(For some reason this doesn't seem to be documented in the python documentation)
（出于某种原因，这似乎没有记录在 python 文档中）
Here are some notes on sqlite BLOB data restrictions:
下面是一些关于 sqlite BLOB 数据限制的注意事项：
回答by Diego Castro
I have the same problem, and I'm thinking about solving this in another way.
我有同样的问题，我正在考虑以另一种方式解决这个问题。
I think the picklemodule is done exactly for something like this (serialization on python objects)
我认为pickle模块正是为这样的事情完成的（python对象的序列化）
Example (this one is for dumping to file... but I think it's easily changeble for db storage)
示例（这个用于转储到文件......但我认为它很容易改变数据库存储）
Saving:
保存：
# Save a dictionary into a pickle file.
import pickle
favorite_color = { "lion": "yellow", "kitty": "red" }
pickle.dump( favorite_color, open( "save.p", "w" ) )
Loading:
加载：
# Load the dictionary back from the pickle file.
import pickle
favorite_color = pickle.load( open( "save.p" ) )
IMHO I think this way is more elegant and safer(it works for any python object).
恕我直言，我认为这种方式更优雅、更安全（它适用于任何 python 对象）。
That's my 2 cents
这是我的 2 美分
UPDATE: After doing a bit of search on my idea, they show some gotchas on my solution ( I can't make sql searches on that field). But I still think that it's a decent solution (if you don't need to search that field.
更新： 在对我的想法进行了一些搜索之后，他们在我的解决方案中显示了一些问题（我无法在该字段上进行 sql 搜索）。但我仍然认为这是一个不错的解决方案（如果您不需要搜索该字段。
回答by Diego Castro
See this general solution at SourceForge which covers any arbitrary Python object (including list, tuple, dictionary, etc):
在 SourceForge 上查看这个通用解决方案，它涵盖了任意 Python 对象（包括列表、元组、字典等）：
y_serial.py module :: warehouse Python objects with SQLite
y_serial.py 模块 :: 使用 SQLite 仓库 Python 对象
"Serialization + persistance :: in a few lines of code, compress and annotate Python objects into SQLite; then later retrieve them chronologically by keywords without any SQL. Most useful "standard" module for a database to store schema-less data."
“序列化 + 持久性 :: 在几行代码中，将 Python 对象压缩并注释到 SQLite 中；然后在没有任何 SQL 的情况下通过关键字按时间顺序检索它们。用于存储无模式数据的数据库最有用的“标准”模块。”
回答by pervlad
It is possible to store object data as pickle dump, jason etc but it is also possible to index, them, restrict them and run select queries that use those indices. Here is example with tuples, that can be easily applied for any other python class. All that is needed is explained in python sqlite3 documentation (somebody already posted the link). Anyway here it is all put together in the following example:
可以将对象数据存储为 pickle dump、jason 等，但也可以对它们进行索引、限制和运行使用这些索引的选择查询。这是元组示例，可以轻松应用于任何其他 python 类。所有需要的都在 python sqlite3 文档中进行了解释（有人已经发布了链接）。无论如何，它都放在下面的例子中：
import sqlite3
import pickle
def adapt_tuple(tuple):
return pickle.dumps(tuple)
sqlite3.register_adapter(tuple, adapt_tuple) #cannot use pickle.dumps directly because of inadequate argument signature
sqlite3.register_converter("tuple", pickle.loads)
def collate_tuple(string1, string2):
return cmp(pickle.loads(string1), pickle.loads(string2))
#########################
# 1) Using declared types
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
con.create_collation("cmptuple", collate_tuple)
cur = con.cursor()
cur.execute("create table test(p tuple unique collate cmptuple) ")
cur.execute("create index tuple_collated_index on test(p collate cmptuple)")
cur.execute("select name, type from sqlite_master") # where type = 'table'")
print(cur.fetchall())
p = (1,2,3)
p1 = (1,2)
cur.execute("insert into test(p) values (?)", (p,))
cur.execute("insert into test(p) values (?)", (p1,))
cur.execute("insert into test(p) values (?)", ((10, 1),))
cur.execute("insert into test(p) values (?)", (tuple((9, 33)) ,))
cur.execute("insert into test(p) values (?)", (((9, 5), 33) ,))
try:
cur.execute("insert into test(p) values (?)", (tuple((9, 33)) ,))
except Exception as e:
print e
cur.execute("select p from test order by p")
print "\nwith declared types and default collate on column:"
for raw in cur:
print raw
cur.execute("select p from test order by p collate cmptuple")
print "\nwith declared types collate:"
for raw in cur:
print raw
con.create_function('pycmp', 2, cmp)
print "\nselect grater than using cmp function:"
cur.execute("select p from test where pycmp(p,?) >= 0", ((10, ),) )
for raw in cur:
print raw
cur.execute("select p from test where pycmp(p,?) >= 0", ((3,)))
for raw in cur:
print raw
print "\nselect grater than using collate:"
cur.execute("select p from test where p > ?", ((10,),) )
for raw in cur:
print raw
cur.execute("explain query plan select p from test where p > ?", ((3,)))
for raw in cur:
print raw
cur.close()
con.close()
