Database Access

Python supports various databases through DB-API 2.0. SQLite is already built in without additional installation, suitable for learning and prototyping.

DB-API 2.0 is the Python database interface standard adopted by drivers for PostgreSQL (psycopg2), MySQL (mysql-connector), and others. This means the code patterns you learn with SQLite can be applied to other databases simply by swapping the connection module. SQLite itself stores the entire database in a single file, making it very practical for desktop applications, prototypes, and testing.

Security is also a primary concern when accessing databases. The code example below uses parameter placeholders (?) to prevent SQL injection — never concatenate user input directly into a SQL string. In addition, always close the connection with conn.close() or use a with context manager so resources don't leak. For production applications, consider using connection pooling and an ORM like SQLAlchemy.

Code Example

import sqlite3

# Connect to the database (auto-create if it doesn't exist)
conn = sqlite3.connect("students.db")
cursor = conn.cursor()

# Create a table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        score INTEGER
    )
''')

# Insert data
cursor.execute("INSERT INTO students (name, score) VALUES (?, ?)",
               ("John", 85))
conn.commit()

# Query data
cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
    print(row)

conn.close()

Code Explanation

sqlite3.connect("students.db") opens or creates a SQLite database file. If the file doesn't exist, SQLite creates it automatically.
cursor = conn.cursor() creates a cursor object used to execute SQL statements.
CREATE TABLE IF NOT EXISTS creates the students table with three columns: id (auto-increment primary key), name (text, not null), and score (integer).
The ? parameter in INSERT INTO is a placeholder for the values ("John", 85), preventing SQL injection by separating the query from the data.
conn.commit() permanently saves the changes to the database.
cursor.fetchall() retrieves all rows from the query result, then loops through them to print.
conn.close() closes the connection and frees resources.

Everyday Example

A personal finance tracking application uses SQLite to store daily transactions. Every time the user adds an income or expense, the application runs INSERT INTO transactions. When the user opens a monthly report, the application performs SELECT SUM(amount) WHERE category='food' AND month=5. All data is neatly stored in a single finance.db file that is easy to back up or move to another device.

Summary

SQLite and DB-API 2.0 give Python lightweight and portable database capabilities. The connect-cursor-execute-close pattern is the foundation for working with any database in Python. Master this before moving to an ORM or large-scale database servers.