Getting Started — import sqlite3

Already installed, already useful

Python ships sqlite3 in the standard library. No pip install, no extra dependency. The module wraps the C SQLite library that comes bundled with CPython.

The minimum-viable usage:

import sqlite3
conn = sqlite3.connect('myapp.db')
conn.execute('CREATE TABLE IF NOT EXISTS notes(id INTEGER PRIMARY KEY, body TEXT)')
conn.execute('INSERT INTO notes(body) VALUES (?)', ('hello',))
conn.commit()
for row in conn.execute('SELECT * FROM notes'):
    print(row)
conn.close()

That works, but it skips most of what you should know. The next nine lessons fill that in. The key first checks:

sqlite3.sqlite_version — the bundled libsqlite version (often older than the system CLI).
sqlite3.version — the Python module version (less interesting).
Use conn.row_factory = sqlite3.Row to get dict-like access — covered in lesson py05.

Tip: If your CLI sqlite3 is much newer than Python's bundled libsqlite, install pysqlite3-binary and import that instead — same API, newer engine, supports STRICT/JSONB/etc.

Code

Hello, sqlite3·python

import sqlite3
print('libsqlite:', sqlite3.sqlite_version)
print('module:   ', sqlite3.version)

conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)')
conn.execute('INSERT INTO t(v) VALUES (?)', ('hello',))
conn.commit()
print(conn.execute('SELECT * FROM t').fetchall())
conn.close()

Exercise

Confirm your bundled libsqlite version supports the features you'll use in this quest: STRICT (3.37+), RETURNING (3.35+), JSONB (3.45+). If it doesn't, install pysqlite3-binary and re-run the version check. Then write a 10-line script that creates a database, makes a STRICT table, inserts a row, and selects it back.

Getting Started — import sqlite3

Already installed, already useful

Code

External links

Exercise

Progress

Comments 0