在Python的Sqlite3中将元组另存为Blob数据类型 [英] Saving Tuples as blob data types in Sqlite3 in Python

问题描述

我有一个python字典.它们的键是具有可变大小的元组，其中包含unicode字符，并且值只是一个整数.我想将此字典插入带有2列表的sqlite db中.

I have a dictionary in python. They keys are tuples with varying size containing unicode characters and the values are just a single int number. I want to insert this dictionary into sqlite db with a 2 column table.

第一列用于键值，第二列应具有相应的int值.我为什么要这样做?好吧，我有一个非常大的词典，并且我使用了cPickle，甚至将协议设置为2.大小仍然很大，保存和加载此文件需要很多时间.所以我决定将其保存在数据库中.该字典在程序开始时仅一次加载到内存中，因此没有额外的操作.

The first column is for the key values and the second column should have the corresponding int value. Why do I want to do this? well I have a very large dictionary and I used cPickle, even setting the protocol to 2. The size is still big and saving and loading this file takes a lot of time. So I decided to save it in db. This dictionary only loads once into memory at the beginning of program, so there is no extra operation.

现在的问题是，我想将元组完全保存为元组(而不是字符串)，因此每当我将表加载到内存中时，我都可以立即毫无问题地构建字典.有人知道我该怎么做吗?

Now the problem is that I want to save the tuples exactly as tuples (not strings), so whenever I load my table into memory, I can immediately build my dictionary with no problem. Does anyone know how I can do this?

推荐答案

几件事.首先，SQLite不允许您直接存储Python数据结构.其次，我猜测您希望能够按需通过元组键查询值，因此您不想进行腌制和腌制然后在字典中搜索键.

A couple of things. First, SQLite doesn't let you store Python data-structures directly. Second, I'm guessing you want to ability to query the value by the tuple key on demand, so you don't want to pickle and unpickle and then search the keys in the dict.

问题是，您无法使用元组进行查询，也无法将元组条目分成各自的列，因为它们的大小各不相同.如果必须使用SQLite，则几乎必须将元组中的unicode字符连接起来，可能使用的分隔符不是元组值中的字符之一.将该键用作键，并将其作为主键列存储在SQLite的列中.

The problem is, you can't query with tuple and you can't break the tuple entries into their own columns because they are of varying sizes. If you must use SQLite, you pretty much have to concatenate the unicode characters in the tuple, possibly with a delimiter that is not 1 of the characters in the tuple values. Use that as a key, and store it into a column in SQLite as a primary key column.

def tuple2key(t, delimiter=u':'):
    return delimiter.join(t)

import sqlite3

conn = sqlite3.connect('/path/to/your/db')
cur = conn.cursor()

cur.execute('''create table tab (k text primary key, value integer)''')

# store the dict into a table
for k, v in my_dict.iteritems():
    cur.execute('''insert into tab values (?, ?)''', (tuple2key(k), v))

cur.commit()

# query the values
v = cur.execute(''' select value from tab where key = ? ''', tuple2key((u'a',u'b'))).fetchone()

这篇关于在Python的Sqlite3中将元组另存为Blob数据类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！