SQLite - Perl

SQLite - Python Integration

Python is a popular, modern language for interacting with databases. This chapter demonstrates how to use SQLite in Python programs. One of Python’s key advantages is its built-in sqlite3 module, which means no external libraries need to be installed to get started.

Connecting to a Database

Connecting to a SQLite database is a single function call. If the specified database file does not exist, it will be created automatically. It’s best practice to manage the connection using a with statement, which ensures the connection is closed automatically.

import sqlite3
from sqlite3 import Error

def create_connection(db_file):
    """ create a database connection to a SQLite database """
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        print(f"Connected to {db_file}, SQLite version: {sqlite3.version}")
    except Error as e:
        print(e)
    return conn

if __name__ == '__main__':
    conn = create_connection("py_company.db")
    if conn:
        conn.close()

Executing Queries: CRUD Operations

All SQL operations are performed using a cursor object. The most important best practice is to use parameterized queries (with ? placeholders) to prevent SQL injection vulnerabilities.

CREATE: Creating a Table

You execute CREATE TABLE statements using cursor.execute().

def execute_sql(conn, sql_statement):
    try:
        c = conn.cursor()
        c.execute(sql_statement)
    except Error as e:
        print(e)

conn = create_connection("py_company.db")
if conn:
    create_employees_table = """
    CREATE TABLE IF NOT EXISTS employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        position TEXT NOT NULL,
        salary REAL NOT NULL
    );
    """
    execute_sql(conn, create_employees_table)
    conn.close()

INSERT: Adding Data

Use ? as a placeholder for data. This is the secure way to insert data.

def add_employee(conn, employee):
    sql = 'INSERT INTO employees(name, position, salary) VALUES(?,?,?)'
    cur = conn.cursor()
    cur.execute(sql, employee)
    conn.commit() # Commit the changes to the database
    return cur.lastrowid

conn = create_connection("py_company.db")
if conn:
    with conn:
        employee_data = ('Alice', 'Developer', 80000)
        employee_id = add_employee(conn, employee_data)
        print(f"Added employee with id: {employee_id}")

SELECT: Reading Data

After executing a SELECT statement, you can retrieve results using fetchone(), fetchall(), or by iterating over the cursor.

def select_all_employees(conn):
    cur = conn.cursor()
    cur.execute("SELECT * FROM employees")

    rows = cur.fetchall()

    for row in rows:
        print(row)

conn = create_connection("py_company.db")
if conn:
    with conn:
        print("All employees:")
        select_all_employees(conn)

Output:

All employees:
(1, 'Alice', 'Developer', 80000.0)

UPDATE: Modifying Data

Always use a WHERE clause when updating to avoid modifying all rows. Again, use ? placeholders.

def update_employee_salary(conn, data):
    sql = 'UPDATE employees SET salary = ? WHERE id = ?'
    cur = conn.cursor()
    cur.execute(sql, data)
    conn.commit()

conn = create_connection("py_company.db")
if conn:
    with conn:
        update_data = (85000, 1) # New salary, employee id
        update_employee_salary(conn, update_data)
        print("Salary updated.")

DELETE: Removing Data

The DELETE operation is destructive. It is critical to use a WHERE clause.

def delete_employee(conn, id):
    sql = 'DELETE FROM employees WHERE id = ?'
    cur = conn.cursor()
    cur.execute(sql, (id,))
    conn.commit()

conn = create_connection("py_company.db")
if conn:
    with conn:
        delete_employee(conn, 1)
        print("Employee deleted.")

Common Pitfalls & Debugging

• Forgetting conn.commit(): For any operation that modifies data (INSERT, UPDATE, DELETE), you must call conn.commit() to save the changes. Using a with conn: block handles this automatically upon successful exit.
• SQL Injection: Never use f-strings or string concatenation to build queries with user data (e.g., f"SELECT * FROM users WHERE name = '{user_name}'"). Always use ? placeholders.
• Unclosed Connections: Forgetting to call conn.close() can leave database files locked. The with statement is the best way to prevent this.