There's also the shelve[0] module which allows storing any pickleable object in a persistent key-value store, not just string/bytes. I've found it's very handy for caching while developing scripts which query remote resources, and not have to worry about serialization.
Obligatory pickle note: one should be aware of pickle security implications and should not open a "Shelf" provided by untrusted sources, or rather should treat opening a shelf (or any pickle deserialization operation for that matter) as running an arbitrary Python script (which cannot be read).
There's even an object database built around pickle, ZODB. It even has a networked implementation with replication / failover. Used to be part of Zope, originally written in the 1998 time frame or so - GvR actually committed a bunch of stuff to it.
ZODB is awesome and overlooked, IMHO. I'm biased I guess because I was involved in making Durus which is inspired by ZODB. The ZODB model is not appropriate for all applications (optimistic concurrency control) but for the applications it works for, it's great. Very easy to develop with (no relational-to-OO mismatch) and performance can be great if you design your model carefully. The client caching of data is great. It is a bit like memcache but the data is already there as objects in RAM. The database server will invalidate the cache for you, no manual invalidation needed.
I developed a web application in 2004 using Quixote and Durus. I wonder how many developers outside of MEMS Exchange ever used both of those packages. Somehow I had not yet encountered a proper ORM (I didn't discover SQLObject until later, and Django wasn't out yet), so I liked Durus at the time. That web application is still running, and in the years since then, I've had to handle several escalated customer support cases, and I often wished I had chosen a relational database so I could easily do ad-hoc queries. So Durus probably wasn't the best choice for my application, but that's not your fault. And one thing I liked about Durus was that the implementation was simple enough that I felt I could really understand how it worked. So thanks for that.
Wow, blast from the past.. I investigated Plone as a place to worry technical documents back in 2005 at a bank. Makes me want to go check out the project again.
Another problem with pickles with any sort of living code base is when one makes modification to the type itself - renaming, refactoring, whatever. Picking objects (and nested objects) that aren't explicitly meant for data storage/retention/transmissions leads to headaches.
It's best to use another dedicated type or library specifically for this task.
I once wrote a locking wrapper around the shelve module so I could use it as a thread- and multiprocess-safe key-value cache (including a wrapper around the requests modules `get()` to transparently cache/validate http resources according to headers):
It works despite some cross-platform issues (flock and macos's version of gdbm interacting to create a deadlock), but if I were to do again I would just use sqlite (which Python's standard library has an interface for).
> but if I were to do again I would just use sqlite
Yeah, I tried to use shelve for some very simple stuff because it seemed like a great fit, but ultimately found that I had a much better time with tortoise-orm on top of sqlite.
If you need any kind of real feature, just use sqlite.
I created a password management utility using shelve and the rsa library for a company that is still using it after 15 years. Though today the company is transitioning to Hashicorp Vault, the utility will still serve as an interface to Vault.
I like to use `joblib.Memory` for my caching as it catches changes in functions which prompt rerunning over loading from the cache and then overwrite the old result.
Pickle could in theory could be architecture dependent since __getstate__, and __setstate__ are user provided options. But you would have to try to do that on purpose.
And you don't even have to forget about Python 2! If you use format version 2 you can pickle objects from every version from Python 2.3+ and all pickle format are promised to be backwards compatible. If you only care about Python 3 then you can use version 3 and it will work for all Python 3.0+.
The reason against using pickle hasn't changed though, if you wouldn't exec() it, don't unpickle it. If you're going to send it over the network use MAC use MAC use MAC. Seriously, it's built in -- the hmac module.
We had a program that was sending a pickled session state as a cookie. We solved that by packing the cookie as a random string, a timestamp, the object, and a MAC. We validated the MAC, then checked the timestamp, and finally unpickle the object. It still bothers me that we are unpickling data passed by the client but I ran arguments against doing it.
There's a version on the pickle format, so you might be able to do it across versions but I suspect the version has changed for security reasons over time?
So I read the script used to compare 'dbm' and 'sqlite3', and in sqlite it creates a table with no index. Hard to take that comparison seriously. I wrote a little benchmark script of my own just now and sqlite3 beats DBM handily if you add an index on the key.
Yeah I tried that myself - I took that benchmark script and changed the CREATE TABLE lines to look like this (adding the primary key):
CREATE TABLE store(key TEXT PRIMARY KEY, value TEXT)
Here are the results before I made that change:
sqlite
Took 0.032 seconds, 3.19541 microseconds / record
dict_open
Took 0.002 seconds, 0.20261 microseconds / record
dbm_open
Took 0.043 seconds, 4.26550 microseconds / record
sqlite3_mem_open
Took 2.240 seconds, 224.02620 microseconds / record
sqlite3_file_open
Took 7.119 seconds, 711.87410 microseconds / record
And here's what I got after adding the primary keys:
sqlite
Took 0.040 seconds, 3.97618 microseconds / record
dict_open
Took 0.002 seconds, 0.19641 microseconds / record
dbm_open
Took 0.042 seconds, 4.18961 microseconds / record
sqlite3_mem_open
Took 0.116 seconds, 11.58359 microseconds / record
sqlite3_file_open
Took 5.571 seconds, 557.13968 microseconds / record
Yeah now that I dig in a little further it looks like it's not as clear cut as I thought. DBM read performance is better for me with larger test cases than I was initially using, though I am getting some weird performance hiccups when I use very large test cases. It looks like I'm too late to edit my top level response to reflect that.
The index is the least of the issue with the SQLite implementation. It's calling one INSERT per each record in that version, so the benchmark is spending something like 99.8% of its time opening and closing transactions as it sets up the database.
Fixing that on my machine took the sqlite3_file_open benchmark from 16.910 seconds to 1.033 seconds. Adding the index brought it down to 0.040 seconds.
Also, I've never really dug into what's going on, but the dbm implementation is pretty slow on Windows, at least when I've tried to use it.
I was working on a project to insert a billion rows in SQLite under a minute, batching the inserts made it crazy fast compared to individual transactions.
It uses a text file to store keys and pointers into a binary file for values. It works .. most of the time .. but yeah, that's not going to win any speed awards.
Sure, mutating data sets might be a useful use case. But, inserting thousands of items at once one at a time in a tight loop, then asking for all of them is testing an unusual use case in my opinion.
My point was that we're comparing apples and oranges. By default, I think, Python's dbm implementation doesn't do any sort of transaction or even sync after every insert, where as SQLite does have a pretty hefty atomic guarantee after each INSERT, so they're quite different actions.
This is libdbm, a relatively ancient db library that you could find on most unixes.
In the 90s you might target dbm for your portable Unix application because you could reasonably expect it to be implemented on your platform. It lost a lot of share to gdbm and sleepycat's BerkeleyDB, both of which I consider its successor.
According to documentation it is not so much a db included as an interface to one:
> dbm is a generic interface to variants of the DBM database — dbm.gnu or dbm.ndbm. If none of these modules is installed, the slow-but-simple implementation in module dbm.dumb will be used. There is a third party interface to the Oracle Berkeley DB
The main issue here is that, given it relies on whatever implementation of DBM you have installed, its performance will vary considerably from OS to OS.
I don't know how fast or slow the "dumb" implementation is, but I can bet it is way slower than gdbm. I can see someone using this module, considering it "fast enough" on GNU/Linux and then finding out that it's painfully slow on Windows and shipping binaries of another implementation is a massive PITA.
Each of which has it's own set of limitations and watch-outs. I would not use the python dbm interface for anything serious. Ndbm, for example, has a per item value limit that varies between ~1-4k.
There exists the 'diskcache' library too, which gives you a persistent dictionary that stores any kind of pickle-able python object. There's also a nice persistent memoization decorator, with optional TTL... I've always found 'diskcache' to be very ergonomic.
This is a wrapper around dbm, the unix library? Though having a portable interface to that tool is nothing small, and most languages don't have one in their standard library. Though I think all languages with a stdlib as big as python's probably do. It's not inherently a python thing though, unless I'm badly misunderstanding.
I look at it the other way, this is the least effort disk persistence that I have seen in python. A fair amount of dev stuff is reading from disk and putting it into some kind of object / dictionary format. I am not really bothered about what it uses under the hood (ok it is interesting enough that I have looked up DMB). To me the he main appeal of this that you can use what appears to be a normal python dictionary and have it save to disk with minimal effort. This is a lot less friction that using a database.
Oh yeah for sure. I don't mean to convey any disdain for dbm itself or for having it rolled into the standard library. Reading and writing custom files for "convenience persistence" or configuration is one of those really common things that you don't usually need to actually do if you know about tools like this.
I was just pointing out that the title and even the article seem to associate this system with python per se, rather than understanding it as python's interface to a common and pre-existing system.
> Why is sqlite3 so slow? Well, the benchmark is probably not representative of the typical workload for sqlite (lots of individual insertions and selections). If we perform the same operations using executemany and one select of all the keys at once, we get:
I think this is because each time you call execute (which is probably sqlite3_exec under the hood), your statement is prepared again, and then deleted, while with executemany it's prepared once and then used with the data. According to the SQLite3 documentation of sqlite3_exec:
> The sqlite3_exec() interface is a convenience wrapper around sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(), that allows an application to run multiple statements of SQL without having to use a lot of C code.
I knew that when you execute a request a lot, prepared statements are faster, but it seems that it's not exactly the case and that all statements are prepared, the performance improvements come from preparing (and deleting) only once. The documentation page about the Prepared Statement Object has a good explanation of the lifecycle of a prepared statement, which also seems to be the lifecycle of all statements.
The Python sqlite3 module caches prepared statements, I think it's the last 100 by default but can be overridden. To be effective, this cache requires you to use proper parameters (rather than string interpolation) but the benchmark code for this article does that. So, in effect, it already uses stored procedures.
As others have pointed out, it's the lack of a suitable index that's the real problem here.
In AppleScript you can mark a variable as a property, and then its value is automatically remembered across runs. For example a script can easily remember how many times it has run:
property runCount : 0
set runCount to runCount + 1
I feel like AppleScript could have really shown if things had played out differently in Mac OS automation. I learned to code with AppleScript on OS 9 and it really got me in to programming. MacScripter.net was my old hangout. Good times.
> There is no point to use sqlite3 in default journaling mode.
There are situations where you wouldn't want to, but they are probably very uncommon outside of the embedded computing world. Copying 3 files vs 1 is not a gigantic deal. Most of the time you aren't even moving a SQLite database around.
You can still copy just the primary file and not the WAL or any other files if you want to live dangerously and possibly lose transactions if there are write operations in parallel.
Of course, you'd be better off with WAL-based replication tech like litestream instead of plain file copies if you are truly worried about parallel operations during your file copies.
You can even distribute individual WAL frames and arrange distributed writes with raft consensus and a time lease. I never formally modelled this but it seemed to work perfectly: the approach only lacked checkpointing the Wal file and synchronising the database file across raft nodes.
There's never a need to copy 3 files anyway. If the database is closed cleanly then the WAL file and lock file are deleted. If not (either it's still open or not closed cleanly) then I think any half-finished transactions will be discarded if you copy all files to a new location. Certainly safest not to copy the 2 extra files in any case.
There isn’t really a thing called a memory namespace, in Linux or the BSDs at least. In Linux there are cgroups that deal with accounting and you can share VM space among cloned processes (which we then typically refer to as threads). Neither of these affect the ability to share memory among processes.
The only namespace that matters here is the filesystem/mount namespace.
There is no reason you can’t access the same shared SQLite database on a common volume between containers on Linux for instance.
If you're using a system with SELinux, your containers won't be able to use the same shared memory file. Which is relatively common, as CoreOS nowadays has SELinux by default.
> If you're using a system with SELinux, your containers won't be able to use the same shared memory file.
That is false. Whether you mmap the same file is dependent on the specific SELinux policy - which is by design, highly configurable.
I am also surprised/skeptical that the default configuration for those OSes for a shared volume/mount point would disallow mmaping by default (which is all that is required) - since what is the attack vector they’re preventing? If you can exfiltrate via memory you could just do so via read/write.
Yes, but the processes still need to share a memory namespace. In WAL mode, SQLite creates a shared memory file which gets mapped into any process trying to access it (that’s why WAL mode creates additional .shm files).
But that only works if all given processes run on the same physical machine, and not in containers/jails/VM.
I dunno what SQLite is doing specifically but you can certainly mmap a file across containers and it behaves like you’d think. SELinux and other hardening options might interfere though, because shared mmaps should imho be viewed as a very likely vector for cross-process exploitation.
You are probably thinking of issues with networked filesystems like nfs, 9p, or vboxsf where you can't mmap a file and actually share memory. Basically any other real filesystem that 2 processes actually open the same file on will allow shared memory.
Which is relatively common if you're running a service on your k8s cluster with networked storage and SELinux enabled for your containers (CoreOS now has SELinux per default).
You'll either have to reduce safety and usability, or drop SQLite.
Huh. I am working on a little utility to deduplicate csvs using sqlite. Seems like this would likely be better.
I used sqlite in the projects name. This makes me think of an article I read recently that suggested not using descriptive names for projects. For reasons such as this.
This is probably dumb intervention but forgive me because it made me giddy to find: the pandas drop_duplicates() method is pretty great, if you're in a pinch. Again -- forgive me if you know about it!
My money is that’s mostly because it actually stores the data. I don’t think dbm guarantees any of the data written makes it to disk until you sync or close the database.
This writes, guesstimating, on the order of 100 kilobytes, so chances are the dbm data never hits the disk.
Back in the 90's I used to use MLDBM as the database for simple CGI apps that didn't call for a full-blown RDBMS. There are some obvious limitations, but at the time it was a pretty powerful setup for simple applications.
It's amazing how few people seemed to understand that NoSQL existed long before Mongo's marketing team cashed in on the trend. I was using various NoSQl solutions before they were trendy.
The script linked at the bottom of this page uses sqlite without an index on the table. In my testing if you add an index sqlite quickly outperforms dbm in terms of read performance
You get one file for the whole store, not one per key. Also, it tends to better handle having lots of keys significantly better than filesystems handle lots of files in one directory.
It's not built-in, of course, but if you can live with the quirky API, LMDB as an embedded key-value store will be hard to beat in terms of speed. It can also do "one-writer, many-readers" and transactions.
I suspect it'll be way, way faster. There's a fair amount of overhead in filesystem calls.
(simplification) With any time you are mapping some file to some data structure (dbm, sqlite, mmap), the file descriptor generally stays open while you are working with it, meaning reads/writes/seeks are just about as fast as IO can go.
The article makes it sound like it's something to consider today (well 2018), that's why I think it's fair to see how well it satisfies modern requirements.
[0] https://docs.python.org/3.10/library/shelve.html
Obligatory pickle note: one should be aware of pickle security implications and should not open a "Shelf" provided by untrusted sources, or rather should treat opening a shelf (or any pickle deserialization operation for that matter) as running an arbitrary Python script (which cannot be read).