Python SQLite3 Module

The sqlite3 module lets you use SQLite databases directly from Python without a separate database server.

import sqlite3

SQLite is a good choice for small apps, local tools, tests, and learning SQL. The database can live in a single file, or only in memory with ':memory:'.

Connecting to a database

SQLite can create the database file automatically if it does not exist.

import sqlite3

connection = sqlite3.connect(':memory:')
print(type(connection).__name__)

Connection

Creating a table and inserting rows

import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()

cursor.execute('CREATE TABLE users (name TEXT, age INTEGER)')
cursor.execute('INSERT INTO users VALUES (?, ?)', ('Ada', 36))
cursor.execute('INSERT INTO users VALUES (?, ?)', ('Grace', 40))
connection.commit()

Use placeholders instead of formatting SQL strings manually.

WARNING

Do not build SQL queries by concatenating user input. Use placeholders like ? to avoid SQL injection bugs.

Querying rows

rows = cursor.execute('SELECT name, age FROM users ORDER BY age').fetchall()
print(rows)

[('Ada', 36), ('Grace', 40)]

Using Row objects

Row lets you access columns by name.

connection = sqlite3.connect(':memory:')
connection.row_factory = sqlite3.Row
cursor = connection.cursor()
cursor.execute('CREATE TABLE projects (name TEXT)')
cursor.execute('INSERT INTO projects VALUES (?)', ('python-cheatsheet',))
row = cursor.execute('SELECT name FROM projects').fetchone()
print(row['name'])

python-cheatsheet

Using a context manager

The connection can be used with with so changes are committed automatically if there is no error.

import sqlite3

with sqlite3.connect(':memory:') as connection:
    connection.execute('CREATE TABLE notes (body TEXT)')
    connection.execute('INSERT INTO notes VALUES (?)', ('Learn sqlite3',))
    rows = connection.execute('SELECT body FROM notes').fetchall()

print(rows)

[('Learn sqlite3',)]
Previous pageShelveNext pageStatistics