HighSecLab Wiki

SQLite in Python: Tips and Best Practices

Properties2
tagsprogramming, languages, sqlite
aliasesSqlite, SQLite in Python: Tips and Best Practices

4 min read

SQLite in Python: Tips and Best Practices

SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. It is the most used database engine in the world. Being serverless and self-contained, it’s an excellent choice for local data storage, application development, and testing. Python has built-in support for SQLite through the sqlite3 module, meaning no external libraries are typically needed to get started with basic database interactions.

1. Web-Based UI for SQLite Databases (sqlite-web)

While SQLite databases are file-based, a graphical interface can greatly simplify browsing, querying, and managing data. sqlite-web provides a convenient web-based interface.

Installation

pip install sqlite-web

Starting sqlite-web

Navigate to the directory containing your .db file and run:

sqlite_web <database_file.db>
# Example:
sqlite_web my_application.db

This will typically start a web server (e.g., on http://127.0.0.1:8080) where you can interact with your database through your browser.

2. Database Schema Design: Relationships and Constraints

Designing your database schema effectively is crucial for data integrity. Here’s an example demonstrating table relationships using FOREIGN KEY constraints.

meraki_api_keys Table (Parent Table)

This table stores API keys and associated account information. The name column is made UNIQUE to serve as a reliable reference for foreign keys.

CREATE TABLE meraki_api_keys (
  id INTEGER PRIMARY KEY,
  name TEXT UNIQUE, -- Enforce uniqueness for 'name' to be a good foreign key reference
  key TEXT,
  email_address TEXT
);

meraki_orgs Table (Child Table)

This table stores organization details and links back to the meraki_api_keys table using a FOREIGN KEY.

CREATE TABLE meraki_orgs (
  id INTEGER PRIMARY KEY,
  account_name TEXT,
  org_id TEXT,
  org_name TEXT,
  -- Define foreign key relationship: account_name in this table references
  -- the 'name' column in the meraki_api_keys table.
  -- ON DELETE CASCADE: If a row in meraki_api_keys is deleted,
  -- all associated rows in meraki_orgs will also be deleted.
  FOREIGN KEY (account_name) REFERENCES meraki_api_keys(name) ON DELETE CASCADE
);

Explanation: The account_name in meraki_orgs table establishes a link to the name column in the meraki_api_keys table. The ON DELETE CASCADE ensures that if an entry in meraki_api_keys is removed, all corresponding meraki_orgs entries are automatically deleted, maintaining referential integrity.

Enforcing Uniqueness for Foreign Key References

To ensure the FOREIGN KEY constraint works reliably, the referenced column in the parent table (name in meraki_api_keys) should ideally be UNIQUE.

CREATE UNIQUE INDEX idx_unique_name ON meraki_api_keys(name);

3. Python Interaction: Connection and Fetching Data

Python’s sqlite3 module makes it easy to connect to SQLite databases and perform SQL operations.

Enabling Foreign Key Enforcement

By default, SQLite does not enforce FOREIGN KEY constraints. You must explicitly enable it for each connection.

conn.execute("PRAGMA foreign_keys = ON")

Accessing Query Results by Column Name (row_factory)

When you fetch data from an SQLite database using cursor.fetchone() or cursor.fetchall(), results are returned as tuples by default. To access columns by name (like dictionary keys), you can set the row_factory attribute of your connection object.

import sqlite3
 
# Example without row_factory:
conn_default = sqlite3.connect(':memory:') # Use in-memory database for example
cursor_default = conn_default.cursor()
cursor_default.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cursor_default.execute("INSERT INTO users VALUES (1, 'Alice')")
conn_default.commit()
 
cursor_default.execute("SELECT id, name FROM users WHERE id = 1")
row_tuple = cursor_default.fetchone()
print(f"Tuple result: {row_tuple}") # Output: (1, 'Alice')
print(f"Access by index: {row_tuple[1]}") # Output: Alice
 
# Example with row_factory:
conn_row_factory = sqlite3.connect(':memory:')
conn_row_factory.row_factory = sqlite3.Row # Enable Row factory
cursor_row_factory = conn_row_factory.cursor()
cursor_row_factory.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cursor_row_factory.execute("INSERT INTO users VALUES (1, 'Alice')")
conn_row_factory.commit()
 
cursor_row_factory.execute("SELECT id, name FROM users WHERE id = 1")
row_obj = cursor_row_factory.fetchone()
print(f"Row object result: {row_obj}") # Output: <sqlite3.Row object at 0x...>
print(f"Access by column name: {row_obj['name']}") # Output: Alice

Using conn.row_factory = sqlite3.Row makes your code more readable and less prone to errors if column order changes.

Database Connection Function Example

It’s good practice to encapsulate your database connection logic in a function.

import sqlite3
import os
 
def get_db_connection(db_name="netdevops.db", db_dir=".."):
    """
    Establishes a connection to the SQLite database.
 
    Args:
        db_name (str): The name of the database file.
        db_dir (str): The directory where the database file is located.
 
    Returns:
        sqlite3.Connection: A database connection object.
    """
    # Construct the full path to the database file
    db_path = os.path.join(db_dir, db_name)
    
    conn = sqlite3.connect(db_path)
    conn.execute("PRAGMA foreign_keys = ON")  # Ensure foreign key constraints are enforced
    conn.row_factory = sqlite3.Row           # Enable column access by name
    return conn
 
# Example Usage:
# conn = get_db_connection()
# cursor = conn.cursor()
# cursor.execute("SELECT * FROM meraki_api_keys")
# keys = cursor.fetchall()
# for key in keys:
#     print(key['name'], key['email_address'])
# conn.close()

0 items under this folder.