Python dictionary of sets in SQL

Question

I have a dictionary in Python where the keys are integers and the values sets of integers. Considering the potential size (millions of key-value pairs, where a set can contain from 1 to several hundreds of integers), I would like to store it in a SQL (?) database, rather than serialize it with pickle to store it and load it back in whenever I need it.

From reading around I see two potential ways to do this, both with its downsides:

• Serialize the sets and store them as BLOBs: So I would get an SQL with two columns, the first column are the keys of the dictionary as INTEGER PRIMARY KEY, the second column are the BLOBS, containing a set of integers. Downside: Not able to alter sets anymore without loading the complete BLOB in, and after adding a value to it, serialize it back and insert it back to the database as a BLOB.

• Add a unique key for each element of each set: I would get two columns, one with the keys (which are now key_dictionary + index element of set/list), one with one integer value in each row. I'd now be able to add values to a "set" without having to load the whole set into python. I would have to put more work in keeping track of all the keys.

In addition, once the database is complete, I will always need sets as a whole, so idea 1 seems to be faster? If I query for all in primary keys BETWEEN certain values, or LIKE certain values, to obtain my whole set in system 2, will the SQL database (sqlite) still work as a hashtable? Or will it linearly search for all values that fit my BETWEEN or LIKE search?

Overall, what's the best way to tackle this problem? Obviously, if there's a completely different 3rd way that solves my problems naturally, feel free to suggest it! (haven't found any other solution by searching around)

I'm kind of new to Python and especially to databases, so let me know if my question isn't clear. :)

Answer 1

You second answer is nearly what I would recommend. What I would do is have three columns:

• Set ID
• Key
• Value

I would then create a composite primary key on the Set ID and Key which guarantees that the combination is unique:

CREATE TABLE something (
  set, 
  key, 
  value, 
  PRIMARY KEY (set, key)
);

You can now add a value straight into a particular set (Or update a key in a set) and select all keys in a set.

This being said, your first strategy would be more optimal for read-heavy workloads as the size of the indexes would be smaller.

will the SQL database (sqlite) still work as a hashtable?

SQL databases tend not to use hashtables. Nor do they usually do a sequential lookup. What they do is usually create an index (Which tends to be some kind of tree, e.g. a B-tree) which allows for range lookups (e.g. where you don't know exactly what keys you're looking for).