Python Database Operations: SQLite, MySQL, LMDB, and LevelDB Tutorial

The article begins with a brief preface stating the author’s need to run Caffe on custom data, prompting a review of SQLite and MySQL alongside LMDB and LevelDB.

Environment : Ubuntu 14.04 with Python 2.7.6 is used.

SQLite

Preparation: SQLite is an embedded file‑based database; Python 2.5+ includes sqlite3 . The typical workflow is to open a connection with sqlite3.connect('test.db') , create a cursor, execute SQL statements, commit changes, fetch results, and finally close the connection.

Example code creates a simple student table, inserts three rows, updates one, deletes another, and queries the data, illustrating the use of cursor.execute() and connection.commit() . The script can also operate on an in‑memory database using ':memory:' .

MySQL

Preparation: Install MySQL server and the MySQLdb Python driver, then import it with import MySQLdb . The operation flow mirrors SQLite: create a connection, obtain a cursor, execute statements, commit, and fetch results.

An example script demonstrates connecting to a MySQL instance, creating the same student table, performing insert, delete, and update operations, and querying the results. The only notable difference is the need to select a database with connection.select_db() .

LMDB

Preparation: Install the LMDB library and import it via import lmdb . LMDB is a key‑value store where both keys and values are strings.

Workflow: Open an environment with env = lmdb.open('path') , begin a transaction ( txn = env.begin(write=True) ), use txn.put(key, value) to insert or update, txn.delete(key) to remove, txn.get(key) to retrieve, and txn.commit() to persist changes. Cursor iteration is also supported.

Example code shows creating an environment, inserting, deleting, and updating entries, then querying a single record with txn.get() and iterating over the database with a cursor.

LevelDB

Preparation: Install LevelDB and import it via import leveldb . Like LMDB, it is a key‑value store but does not require explicit transactions.

Workflow: Use db = leveldb.LevelDB('path') , then db.Put(key, value) , db.Delete(key) , and db.Get(key) . Range iteration is available through db.RangeIter() . For batch writes, WriteBatch can group multiple operations before committing.

Example code demonstrates creating a LevelDB instance, performing Put/Delete/Get operations, and using a WriteBatch for bulk updates.

Learning Summary

The author reflects that SQLite and LMDB required more time due to their differing models, while MySQL and LevelDB were quicker to grasp. In summary, SQLite and MySQL are relational databases accessed via connections and cursors with SQL statements, whereas LMDB and LevelDB are key‑value stores accessed via put/get operations, with LMDB requiring explicit transaction commits and LevelDB not.