Introduction to SQLite with Python

What is SQLite?

SQLite is a lightweight, file-based database that requires no server setup. It is built into Python and perfect for small to medium projects.

Connecting to a Database

Python's sqlite3 module is included in the standard library.

import sqlite3

conn = sqlite3.connect("mydb.db")

cursor = conn.cursor()

Creating a Table

Use CREATE TABLE IF NOT EXISTS to safely create tables.

cursor.execute("""

CREATE TABLE IF NOT EXISTS users (

id INTEGER PRIMARY KEY AUTOINCREMENT,

name TEXT NOT NULL,

email TEXT UNIQUE NOT NULL

)

""")

conn.commit()

Inserting Data

Use parameterized queries to safely insert data.

cursor.execute(

"INSERT INTO users (name, email) VALUES (?, ?)",

("Alice", "alice@example.com")

)

conn.commit()

Querying Data

Fetch results with fetchone or fetchall.

rows = cursor.execute("SELECT * FROM users").fetchall()

for row in rows:

print(row)

Updating and Deleting

Use UPDATE and DELETE with WHERE clauses.

cursor.execute("UPDATE users SET name = ? WHERE id = ?", ("Bob", 1))

cursor.execute("DELETE FROM users WHERE id = ?", (1,))

conn.commit()

Closing the Connection

Always close the connection when done.

conn.close()

Using Row Factory

Set row_factory to access columns by name instead of index.

conn.row_factory = sqlite3.Row

cursor = conn.cursor()

row = cursor.execute("SELECT * FROM users WHERE id = 1").fetchone()

print(row["name"])