Master Python Database Operations: SQLite, MySQL, LMDB & LevelDB

Overview

While experimenting with Caffe on a custom dataset, the author needed to learn LMDB and LevelDB and revisited SQLite and MySQL, compiling the findings into this tutorial.

1. SQLite

1.1 Preparation

SQLite is an embedded, file‑based database. Python 2.5+ includes sqlite3; simply import sqlite3.

1.2 Workflow

Typical steps:

Open a connection with sqlite3.open() (or sqlite3.connect()).

Create a cursor via connection.cursor().

Execute SQL with cursor.execute().

Commit changes with connection.commit() or fetch results with cursor.fetchall().

Close the connection with connection.close().

1.3 Example

Creating a simple student table (id, name) and inserting records:

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
c.execute('CREATE TABLE student(id INTEGER, name TEXT)')
conn.commit()
conn.close()

Insert, delete, update using placeholders ( ?) to avoid SQL injection, and note that committing after every execute is unnecessary.

1.4 Query

After committing, run a SELECT and fetch results with cursor.fetchall().

2. MySQL

2.1 Preparation

Install MySQL and the MySQLdb Python driver, then import MySQLdb.

2.2 Workflow

Similar to SQLite: create a connection, obtain a cursor, execute SQL, commit with conn.commit(), and fetch results.

2.3 Example

A complete MySQL script mirrors the SQLite example, with the addition of conn.select_db() to choose the database.

3. LMDB

3.1 Preparation

LMDB is a key‑value store (think of a Python dict). Install with pip install lmdb and import lmdb.

3.2 Workflow

Open an environment: env = lmdb.open('path') Begin a transaction: txn = env.begin(write=True) Insert/modify: txn.put(key, value) Delete: txn.delete(key) Query: txn.get(key) Iterate: txn.cursor() Commit:

txn.commit()

3.3 Example

Creating an environment, inserting records, and querying them, with screenshots illustrating the output.

4. LevelDB

4.1 Preparation

LevelDB is also a key‑value store; install the Python binding and import leveldb.

4.2 Workflow

Operations are simpler than LMDB: db.Put(key, value), db.Get(key), db.Delete(key). No explicit transaction or commit is required; batch writes can be performed with WriteBatch.

4.3 Example

A full script demonstrates creating a database, performing CRUD operations, and printing results.

5. Learning Summary

The author studied the databases in the order SQLite → MySQL → LMDB → LevelDB, noting that relational databases share a connection‑cursor‑execute‑commit‑fetch pattern, while key‑value stores use put/delete/get with optional transactions.

6. References

SQLite tutorials and Python docs.

MySQLdb documentation and Python MySQL guides.

LMDB creation guide and Python lmdb documentation.

LevelDB examples and Python bindings.