python database / sql programming - where to start

Question

What is the best way to use an embedded database, say sqlite in Python:

1. Should be small footprint. I'm only needing few thousands records per table. And just a handful of tables per database.
2. If it's one provided by Python default installation, then great. Must be open-source, available on Windows and Linus.
3. Better if SQL is not written directly, but no ORM is fully needed. Something that will shield me from the actual database, but not that huge of a library. Something similar to ADO will be great.
4. Mostly will be used through code, but if there is a GUI front end, then that is great
5. Need just a few pages to get started with. I don't want to go through pages reading what a table is and how a Select statement works. I know all of that.
6. Support for Python 3 is preferred, but 2.x is okay too.

The usage is not a web app. It's a small database to hold at most 5 tables. The data in each table is just a few string columns. Think something just larger than a pickled dictionary

Update: Many thanks for the great suggestions.
The use-case I'm talking about is fairly simple. One you'd probably do in a day or two.
It's a 100ish line Python script that gathers data about a relatively large number of files (say 10k), and creates metadata files about them, and then one large metadata file about the whole files tree. I just need to avoid re-processing the files already processed, and create the metadata for the updated files, and update the main metadata file. In a way, cache the processed data, and only update it on file updates. If the cache is corrupt / unavailable, then simply process the whole tree. It might take 20 minutes, but that's okay.

Note that all processing is done in-memory.

I would like to avoid any external dependencies, so that the script can easily be put on any system with just a Python installation on it. Being Windows, it is sometimes hard to get all the components installed. So, In my opinion, even a database might be an overkill.

You probably wouldn't fire up an Office Word/Writer to write a small post it type note, similarly I am reluctant on using something like Django for this use-case.

Where to start?

Answer 1

I highly recommend the use of a good ORM. When you can work with Python objects to manage your database rows, life is so much easier.

I am a fan of the ORM in Django. But that was already recommended and you said that is too heavyweight.

This leaves me with exactly one ORM to recommend: Autumn. Very lightweight, works great with SQLite. If your embedded application will be multithreaded, then you absolutely want Autumn; it has extensions to support multithreaded SQLite. (Full disclosure: I wrote those extensions and contributed them. I wrote them while working for RealNetworks, and my bosses permitted me to donate them, so a public thank-you to RealNetworks.)

Autumn is written in pure Python. For SQLite, it uses the Python official SQLite module to do the actual SQL stuff. The memory footprint of Autumn itself is tiny.

I do not recommend APSW. In my humble opinion, it doesn't really do very much to help you; it just provides a way to execute SQL statements, and leaves you to master the SQL way of doing things. Also, it supports every single feature of SQLite, even the ones you rarely use, and as a result it actually has a larger memory footprint than Autumn, while not being as easy to use.

Answer 2

I started here:

http://www.devshed.com/c/a/Python/Using-SQLite-in-Python

It's 5 (short) pages with just the essentials got me going right away.