SQLite Database with Python: Complete sqlite3 Guide
SQLite is a serverless embedded database. Python includes the sqlite3 module natively — perfect for desktop apps, scripts, and rapid prototyping with zero configuration.
Connect and Create Tables
import sqlite3
# Connect (creates file if it doesn't exist)
con = sqlite3.connect("database.db")
con.row_factory = sqlite3.Row # Access columns by name
cur = con.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
price REAL NOT NULL,
stock INTEGER DEFAULT 0,
created TEXT DEFAULT (datetime('now'))
)
""")
con.commit()
print("Table created")
con.close()
CRUD with Context Manager
import sqlite3
def get_db():
con = sqlite3.connect("database.db")
con.row_factory = sqlite3.Row
return con
# INSERT
def add_product(name, price, stock=0):
with get_db() as con:
cur = con.execute(
"INSERT INTO products (name, price, stock) VALUES (?,?,?)",
(name, price, stock)
)
print(f"Inserted id={cur.lastrowid}: {name}")
return cur.lastrowid
# SELECT
def list_products(limit=10):
with get_db() as con:
rows = con.execute(
"SELECT * FROM products ORDER BY id DESC LIMIT ?", (limit,)
).fetchall()
for r in rows:
print(f" [{r['id']}] {r['name']} | ${r['price']:.2f} | stock={r['stock']}")
return rows
# UPDATE
def update_price(product_id, new_price):
with get_db() as con:
con.execute("UPDATE products SET price=? WHERE id=?", (new_price, product_id))
print(f"Price updated: id={product_id} -> ${new_price:.2f}")
# DELETE
def delete_product(product_id):
with get_db() as con:
con.execute("DELETE FROM products WHERE id=?", (product_id,))
print(f"Deleted: id={product_id}")
add_product("Widget A", 9.99, 150)
add_product("Widget B", 24.99, 80)
add_product("Widget C", 4.99, 320)
list_products()
update_price(1, 11.99)
delete_product(3)
Bulk Insert with executemany
import sqlite3
products = [
("Mechanical Keyboard", 89.99, 45),
("Gaming Mouse", 39.99, 120),
('27" Monitor', 299.99, 18),
("USB Headset", 49.99, 75),
("HD Webcam", 59.99, 60),
]
with sqlite3.connect("database.db") as con:
con.executemany(
"INSERT INTO products (name, price, stock) VALUES (?,?,?)",
products
)
print(f"Inserted {len(products)} products")
Advanced Queries with Dynamic Filters
import sqlite3
def search_products(max_price=None, min_stock=None, text=None):
conditions = []
params = []
if max_price is not None:
conditions.append("price <= ?")
params.append(max_price)
if min_stock is not None:
conditions.append("stock >= ?")
params.append(min_stock)
if text:
conditions.append("name LIKE ?")
params.append(f"%{text}%")
where = ("WHERE " + " AND ".join(conditions)) if conditions else ""
sql = f"SELECT * FROM products {where} ORDER BY price ASC"
with sqlite3.connect("database.db") as con:
con.row_factory = sqlite3.Row
rows = con.execute(sql, params).fetchall()
print(f"Results: {len(rows)}")
for r in rows:
print(f" {r['name']} ${r['price']:.2f} (stock={r['stock']})")
return rows
search_products(max_price=100, min_stock=50)
search_products(text="widget")
Export SQLite to CSV and JSON
import sqlite3, csv, json
def sqlite_to_csv(db_path, table, output_csv):
with sqlite3.connect(db_path) as con:
con.row_factory = sqlite3.Row
rows = con.execute(f"SELECT * FROM {table}").fetchall()
if not rows: return
with open(output_csv, "w", newline="", encoding="utf-8") as f:
writer = csv.DictWriter(f, fieldnames=rows[0].keys())
writer.writeheader()
writer.writerows([dict(r) for r in rows])
print(f"CSV exported: {output_csv} ({len(rows)} rows)")
def sqlite_to_json(db_path, table, output_json):
with sqlite3.connect(db_path) as con:
con.row_factory = sqlite3.Row
rows = con.execute(f"SELECT * FROM {table}").fetchall()
with open(output_json, "w", encoding="utf-8") as f:
json.dump([dict(r) for r in rows], f, indent=2, ensure_ascii=False)
print(f"JSON exported: {output_json} ({len(rows)} records)")
sqlite_to_csv("database.db", "products", "products.csv")
sqlite_to_json("database.db", "products", "products.json")
Import CSV to SQLite
import sqlite3, csv
def csv_to_sqlite(csv_path, db_path, table):
with open(csv_path, encoding="utf-8") as f:
reader = csv.DictReader(f)
rows = list(reader)
if not rows: return
cols = list(rows[0].keys())
placeholders = ",".join(["?"] * len(cols))
sql_create = f"CREATE TABLE IF NOT EXISTS {table} ({', '.join(cols)})"
sql_insert = f"INSERT INTO {table} ({','.join(cols)}) VALUES ({placeholders})"
with sqlite3.connect(db_path) as con:
con.execute(sql_create)
con.executemany(sql_insert, [list(r.values()) for r in rows])
print(f"Imported {len(rows)} rows from {csv_path} -> {db_path}:{table}")
csv_to_sqlite("new_products.csv", "database.db", "new_products")
Additional Resource
For converting database files between SQLite, CSV and JSON without any coding, use KaijuConverter — free and no registration required.
Related conversions
Frequent conversions across the catalogue: