How to Insert a Mock Route Record Using SQLite in Python

This code creates an in-memory SQLite table for routes and inserts a mock route record, returning the inserted row for verification.

Easy Python 3.9+ Aug 9, 2026 Database scaling & optimization 14 views 0 copies

sqlite database insert mock-data

Python code

40 lines

Python 3.9+

import sqlite3
from datetime import datetime

def insert_mock_record(db_path=":memory:"):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS routes (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            origin TEXT NOT NULL,
            destination TEXT NOT NULL,
            departure_time TEXT NOT NULL,
            arrival_time TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'scheduled'
        )
    """)
    
    mock_data = {
        "origin": "New York (JFK)",
        "destination": "Los Angeles (LAX)",
        "departure_time": datetime(2025, 3, 15, 10, 30).isoformat(),
        "arrival_time": datetime(2025, 3, 15, 13, 45).isoformat(),
        "status": "scheduled"
    }
    
    cursor.execute("""
        INSERT INTO routes (origin, destination, departure_time, arrival_time, status)
        VALUES (:origin, :destination, :departure_time, :arrival_time, :status)
    """, mock_data)
    
    conn.commit()
    route_id = cursor.lastrowid
    cursor.execute("SELECT * FROM routes WHERE id = ?", (route_id,))
    inserted = cursor.fetchone()
    conn.close()
    return inserted

if __name__ == "__main__":
    result = insert_mock_record()
    print(f"Inserted route ID {result[0]}: {result[1]} -> {result[2]} on {result[3]}")

Output

stdout

Inserted route ID 1: New York (JFK) -> Los Angeles (LAX) on 2025-03-15T10:30:00

How it works

The function connects to an in-memory SQLite database by default, creates a routes table if it does not exist, then inserts a mock record using parameterized SQL to prevent SQL injection. After committing, it retrieves the inserted row using cursor.lastrowid to get the auto-incrementing primary key. The returned tuple contains the row data in the order of the table columns.

Common mistakes

Forgetting to call `conn.commit()` after the insert, which loses the change when the connection closes.
Using string concatenation for SQL values, which risks SQL injection and syntax errors.
Not handling the case where `cursor.lastrowid` might be `None` if using a different DB backend.
Assuming `fetchone()` returns a row even if the insert failed silently.

Variations

Use `conn.execute()` with the INSERT directly and then `conn.execute("SELECT last_insert_rowid()")` to get the ID.
Wrap the insert in a `try/except` to rollback on error and re-raise for debugging.

Real-world use cases

Seeding a development database with sample route data for integration testing API endpoints.
Creating a quick script to inject starting records into a production-like environment for load testing.
Storing mock transport route data in a CI/CD pipeline's ephemeral database to verify schema changes.

How to Insert a Mock Route Record Using SQLite in Python

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Tutorials

Quizzes

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Keep learning

Tutorials

Quizzes