search

piskvorky / sqlitedict

Persistent dict, backed by sqlite3 and pickle, multithread-safe.
Apache License 2.0
1.16k stars 130 forks source link

Feature Proposal: SqliteList #155

Open annovialb opened 2 years ago

annovialb commented 2 years ago

Hello, I am a happy user of the SqliteDict. Thanks for making it!

I would like to store a list as a table in the same sql file. At the moment, in order to use the same sql file, I am storing the list as a value inside a dict. This is highly inefficient since to add an element, I rewrite the whole list to sql.

I think it would be a pretty useful feature. Just my 2 cents :)

AndCycle commented 2 years ago

I have same idea, after I think about this idea twice it's not as simple as it looks, how do you implement a List over sql database? definitely involve save additional information to work row as doubly linked list data structure which might hurt performance, left my 2 cents here I might implement a simple one for fun.

annovialb commented 2 years ago

Hi, thanks for they reply. I am not sure what a good implementation of a List in sql would be. I am just throwing in a 2 cent idea. The primary key could be a floating point number stored as a string (or just float). As the first elements are inserted, they are stored with key "0.", "1.", "2." ... Inserting element(s) could use intermediate float values e.g. "1.5" then "1.75" to insert 2 elements in between "1." and "2.".

We are at 6 cents so far :)

za3k commented 1 year ago

One can write a list-like wrapper around the sqlitedict, as a stopgap until this is implemented as a feature.

Here is what I did https://github.com/za3k/day29_adventure/blob/master/base.py#L18 . It's definitely not perfect performance-wise, just an example.

WolfgangFahl commented 1 year ago

Why not put the lists in their own table according to standard database normalization?