Sqlite3 module in python

sqlite3 Python Module Guide

SQLite3 is a Python module written in C that allows interaction with lightweight, local SQL databases. It’s part of Python’s standard library, no installation needed. This article assumes you know basic Python and SQL.

---

Table of Contents

• Setting Up
• SQL Query Types
  • DDL Example: Creating and Dropping Tables
  • DML Example: Inserting and Selecting Data
• Fetching Data
• Summary
• Critique

---

Setting Up

To begin:

import sqlite3 as sql

Then establish a connection and cursor:

connection = sql.connect("my_database.db")
cursor = connection.cursor()

connection links your Python program to the database.
cursor sends SQL commands and fetches results.

---

SQL Query Types

There are two main types of SQL commands you'll use:

DDL (Data Definition Language): CREATE, DROP, ALTER
DML (Data Manipulation Language): INSERT, SELECT, UPDATE, DELETE

---

DDL Example: Creating and Dropping Tables

connection = sql.connect("my_database.db")
cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
);
""")

# Example DROP
cursor.execute("DROP TABLE IF EXISTS old_users;")

connection.commit()
connection.close()

All DDL commands must be followed by connection.commit() else your changes will not be saved.

---

DML Example: Inserting and Selecting Data

Insert Example

connection = sql.connect("my_database.db")
cursor = connection.cursor()

cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Alice", 25))
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Bob", 30))

connection.commit()
connection.close()

Select Example

connection = sql.connect("my_database.db")
cursor = connection.cursor()

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

connection.close()

print(data)

All SELECT commands must be followed by appropriate fetch command.

Use these methods to retrieve data:

Method	Returns
fetchone()	One row as a tuple
fetchmany(n)	Next n rows as a list of tuples
fetchall()	All rows as a list of tuples

Example:

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
     print(row)

output:

[(1, 'Alice', 25), (2, 'Bob', 30)]

---

Next Steps

Now that you understand the basics of the sqlite3 module and how to interact with a database, here are some important topics we should probably also cover:

Using the with Statement

Using with ensures your database connection closes automatically, even if errors occur:

import sqlite3 as sql

with sql.connect("my_database.db") as connection:
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users")
    data = cursor.fetchall()
    print(data)

---

Basic Error Handling

Wrap your database operations in try-except blocks to catch DatabaseError:

try:
    connection = sql.connect("my_database.db")
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM users")
    data = cursor.fetchall()
except sql.DatabaseError as e:
    print(f"Database error occurred: {e}")
finally:
    connection.close()

Exception	Description
sqlite3.DatabaseError	Generic database error
sqlite3.IntegrityError	Constraint violations (e.g., unique, foreign key)
sqlite3.OperationalError	Operational issues (e.g., missing tables, syntax)
sqlite3.ProgrammingError	API misuse (e.g., wrong parameters)
sqlite3.InterfaceError	Database interface-related errors

---

Preventing SQL Injection

Always use parameterized queries (with ? placeholders) to safely insert user input and avoid SQL injection risks:

name = "Alice"
age = 25
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", (name, age))