Create an In-Memory SQLite Table and Query It in Python

This code creates an in-memory SQLite database, defines an employees table, inserts sample rows, and runs a filtered query with sorted results.

Easy Python 3.9+ Aug 9, 2026 Files & data 13 views 0 copies

sqlite in-memory database query sql

Python code

27 lines

Python 3.9+

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()

cursor.execute("""
    CREATE TABLE employees (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        department TEXT NOT NULL,
        salary REAL
    )
""")

employees = [
    (1, "Alice", "Engineering", 95000),
    (2, "Bob", "Marketing", 70000),
    (3, "Charlie", "Engineering", 85000),
    (4, "Diana", "Sales", 65000),
]
cursor.executemany("INSERT INTO employees (id, name, department, salary) VALUES (?, ?, ?, ?)", employees)

cursor.execute("SELECT name, department, salary FROM employees WHERE department = ? ORDER BY salary DESC", ("Engineering",))
for name, department, salary in cursor.fetchall():
    print(f"{name}: {department} - ${salary:,.0f}")

conn.close()

Output

stdout

Alice: Engineering - $95,000
Charlie: Engineering - $85,000

How it works

sqlite3.connect(":memory:") creates a temporary database that lives entirely in RAM and disappears when the connection closes. The cursor.execute method runs DDL statements like CREATE TABLE, while parameterized queries using ? placeholders safely insert and retrieve data. executemany efficiently inserts multiple rows in one call. After executing a SELECT, fetchall() returns the result rows, which are then formatted for display. Closing the connection frees all memory resources.

Common mistakes

Using `:memory:` with a shared connection expecting persistence across connections
Forgetting to call `commit()` after writes if using a file-based database
Mixing up `cursor.execute` and `cursor.executemany` for single vs. multiple rows
Not using parameterized queries, leading to SQL injection risks

Variations

Use `conn.row_factory = sqlite3.Row` to access columns by name
Wrap the code in a `with sqlite3.connect(":memory:") as conn:` block to auto-close

Real-world use cases

Unit testing data access logic against a disposable database that resets per test.
Prototyping SQL queries or schemas before applying them to a production database.
Running lightweight analytics on a dataset without writing temporary files to disk.

Create an In-Memory SQLite Table and Query It in Python

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Files & data

Tutorials

Quizzes

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Files & data

Keep learning

Tutorials

Quizzes