CODEWITHSUNDEEP
pythondatabasesqlite
Hossein
Hossein

Reputation: 41921

Saving Tuples as blob data types in Sqlite3 in Python

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.

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?

Upvotes: 0

Views: 4820

Answers (3)

pervlad
pervlad

Reputation: 41

It is possible to store tuples into sqlite db and to create indices on tuples. It needs some extra code to get it done. Whether the storing of tuples into db an appropriate solution in this particular case is another issue (probably a two-key solution is better suited).

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

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

#insert
p = (1,2,3)
p1 = (1,2)

cur.execute("insert into test(p) values (?)", (p,))
cur.execute("insert into test(p) values (?)", (p1,))

#ordered select
cur.execute("select p from test order by p collate cmptuple")

Upvotes: 2

Wai Yip Tung
Wai Yip Tung

Reputation: 18784

I think it is better to create 3 columns in your table - key1, key2 and value.

If you prefer to save the key as a tuple, you can still use pickle but apply to the key only. Then you can save it as blob.

>>> pickle.dumps((u"\u20AC",u"\u20AC"))
'(V\\u20ac\np0\ng0\ntp1\n.'
>>> pickle.loads(_)
(u'\u20ac', u'\u20ac')
>>>

Upvotes: -1

Y.H Wong
Y.H Wong

Reputation: 7244

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.

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

Upvotes: 3

Related Questions