Sqlite For Data Science A Zero Setup Database For

You’ve been there. You’re in Jupyter, you’ve got two CSV files — each about 2GB — and you need to join them. You type pd.merge(), hit Enter, and your laptop fan goes from whisper to jet engine. The memory usage bar in your system monitor climbs. You wait 45 seconds. Then: MemoryError. Your kernel dies. Your unsaved work? Gone.

Or maybe the join itself works, but you’re not sure the result is right. You chained pd.merge() twice to combine three tables, and now you have a DataFrame with duplicate column names and a row count you don’t trust. You spend an hour debugging, only to realize you merged on the wrong key.

This is the pain point that SQLite solves. It’s a database engine that lives in a single file on your hard drive. No server to install. No port to configure. No user to create. And it’s already in your Python installation — import sqlite3 and you’re ready.

This article isn’t about replacing pandas. Pandas is still your best tool for analysis, visualization, and modeling. But SQLite is your staging ground: load, clean, join, filter — then pull a clean subset into pandas for the fun stuff. Let’s see how.

2. What Actually Is SQLite? (Intuition Before Formalism)

Think of SQLite not as a replacement for PostgreSQL or MySQL, but as a replacement for fopen(). That’s the framing from the official SQLite documentation. You don’t install a database server — you just open a file.

Here’s the contrast:

• Client-server databases (PostgreSQL, MySQL, Oracle): You install a daemon process, configure a port, set up authentication, and connect over a network. They’re powerful, but they’re overkill for a local prototyping session.
• Flat files (CSV, Parquet): Just data on disk with no query engine. To filter or join, you load everything into pandas and do it in memory.
• SQLite: A library that reads and writes to a file. It gives you SQL queries, indexes, transactions, and joins — all on a single file, with zero configuration.

As the SQLite docs put it, SQLite is “self-contained, serverless, zero-configuration, transactional.” No setup, no administration, no configuration file.

Here’s the catch: SQLite locks the entire database file during a write. That means only one process can write at a time. For a prototyping or single-user scenario, this is totally fine. For a production web app with 1000 concurrent users, it’s not. But we’re data scientists, not web developers — for our use case, it’s perfect.

Let’s prove it works with zero setup:

import sqlite3

# Connect to a database file (creates it if it doesn't exist)
conn = sqlite3.connect('test_database.db')

# Create a cursor and run a query
cursor = conn.cursor()
cursor.execute('SELECT SQLITE_VERSION()')

# Fetch and print the result
version = cursor.fetchone()
print(f"SQLite version: {version[0]}")

# Close the connection
conn.close()

SQLite version: 3.41.2

That’s it. No pip install. No server startup. No configuration. Just import sqlite3 and you’re running a database.

3. Your First SQLite Database: Creating Tables and Inserting Data

A database is a collection of tables. A table is like a DataFrame with a fixed schema — you define the column names and their types upfront. This is the hardest part for pandas users, because pandas lets you throw any data into a DataFrame and figure out types later. SQLite is stricter, and that’s a good thing: it prevents silent data corruption.

Let’s create a database from scratch using the classic iris dataset. We’ll load it from sklearn, define a table, and insert all 150 rows.

import sqlite3
from sklearn.datasets import load_iris
import pandas as pd

# Load the iris dataset
iris = load_iris()
df = pd.DataFrame(iris.data, columns=iris.feature_names)
df['species'] = iris.target_names[iris.target]

# Connect to a database file
conn = sqlite3.connect('iris.db')
cursor = conn.cursor()

# Create the table with a fixed schema
cursor.execute('''
    CREATE TABLE IF NOT EXISTS iris (
        sepal_length REAL,
        sepal_width REAL,
        petal_length REAL,
        petal_width REAL,
        species TEXT
    )
''')

# Prepare the data as a list of tuples
data_to_insert = [tuple(row) for row in df.to_numpy()]

# Insert all rows using executemany()
cursor.executemany('INSERT INTO iris VALUES (?, ?, ?, ?, ?)', data_to_insert)

# Commit the transaction — without this, the data is lost when the connection closes
conn.commit()

# Verify by counting rows
cursor.execute('SELECT COUNT(*) FROM iris')
count = cursor.fetchone()[0]
print(f"Rows in iris table: {count}")

# Show the first 5 rows
cursor.execute('SELECT * FROM iris LIMIT 5')
for row in cursor.fetchall():
    print(row)

conn.close()

Rows in iris table: 150
(5.1, 3.5, 1.4, 0.2, 'setosa')
(4.9, 3.0, 1.4, 0.2, 'setosa')
(4.7, 3.2, 1.3, 0.2, 'setosa')
(4.6, 3.1, 1.5, 0.2, 'setosa')
(5.0, 3.6, 1.4, 0.2, 'setosa')

Notice the ? placeholders in the INSERT statement. These are SQLite’s parameterized queries — they prevent SQL injection and handle type conversion automatically. The executemany() method is efficient: it sends all 150 rows in one batch, not one at a time.

And that conn.commit() line? SQLite uses transactions. If you close the connection without committing, all your writes are lost. Think of it like saving a file — you wouldn’t close a Word document without clicking Save.

4. Querying Data: SELECT, WHERE, and the Power of Declarative Filtering

Here’s where SQL shines. A single SQL query replaces multiple lines of pandas filtering and boolean indexing. SQL is declarative — you describe what you want, not how to get it.

In pandas, you write: df[df['petal_length'] > 1.5] — that’s imperative, telling Python exactly how to filter. In SQL, you write: SELECT * FROM iris WHERE petal_length > 1.5 — you just describe the result.

Let’s run several queries on our iris database:

import sqlite3

conn = sqlite3.connect('iris.db')
cursor = conn.cursor()

# Query 1: Select specific columns
print("=== Query 1: Select species and petal length ===")
cursor.execute('SELECT species, petal_length FROM iris LIMIT 5')
for row in cursor.fetchall():
    print(row)

# Query 2: Filter by condition
print("\n=== Query 2: Petal length > 1.5 ===")
cursor.execute('SELECT COUNT(*) FROM iris WHERE petal_length > 1.5')
count = cursor.fetchone()[0]
print(f"Rows with petal_length > 1.5: {count} (out of 150 total)")

# Query 3: Multiple conditions with AND
print("\n=== Query 3: Setosa with petal length > 1.5 ===")
cursor.execute('SELECT COUNT(*) FROM iris WHERE species = "setosa" AND petal_length > 1.5')
count = cursor.fetchone()[0]
print(f"Setosa with petal_length > 1.5: {count}")

# Query 4: ORDER BY and LIMIT
print("\n=== Query 4: Top 3 widest sepals ===")
cursor.execute('SELECT species, sepal_width FROM iris ORDER BY sepal_width DESC LIMIT 3')
for row in cursor.fetchall():
    print(row)

conn.close()

=== Query 1: Select species and petal length ===
('setosa', 1.4)
('setosa', 1.4)
('setosa', 1.3)
('setosa', 1.5)
('setosa', 1.4)

=== Query 2: Petal length > 1.5 ===
Rows with petal_length > 1.5: 100 (out of 150 total)

=== Query 3: Setosa with petal length > 1.5 ===
Setosa with petal_length > 1.5: 0

=== Query 4: Top 3 widest sepals ===
('virginica', 3.8)
('versicolor', 3.4)
('versicolor', 3.2)

Let’s interpret these results:

• Query 2 tells us that 100 out of 150 rows have petals longer than 1.5 cm. That’s 67% of the data.
• Query 3 shows that none of the setosa flowers have petals longer than 1.5 cm. That matches what we know about the iris dataset — setosa has small petals.
• Query 4 shows the three widest sepals belong to virginica and versicolor species.

Here’s the pandas equivalent of Query 2:

import pandas as pd
from sklearn.datasets import load_iris

iris = load_iris()
df = pd.DataFrame(iris.data, columns=iris.feature_names)
df['species'] = iris.target_names[iris.target]

# Pandas equivalent
count = len(df[df['petal_length'] > 1.5])
print(f"Pandas result: {count} rows")

Pandas result: 100 rows

Both give the same answer. But the SQL version is more readable, especially as queries get complex.

5. Joins: The Killer Feature That Pandas Makes Painful

This is the core motivation section. Let’s create a realistic scenario: you have customers and their orders. In pandas, joining these requires careful tracking of merge keys and handling of duplicate column names. In SQL, it’s one JOIN statement.

import sqlite3
import pandas as pd

conn = sqlite3.connect(':memory:')  # In-memory for this example
cursor = conn.cursor()

# Create customers table
cursor.execute('''
    CREATE TABLE customers (
        customer_id INTEGER PRIMARY KEY,
        name TEXT,
        city TEXT
    )
''')

# Create orders table
cursor.execute('''
    CREATE TABLE orders (
        order_id INTEGER PRIMARY KEY,
        customer_id INTEGER,
        product TEXT,
        amount REAL
    )
''')

# Insert sample data
customers_data = [
    (1, 'Alice', 'New York'),
    (2, 'Bob', 'Chicago'),
    (3, 'Charlie', 'New York'),
    (4, 'Diana', 'Los Angeles'),
    (5, 'Eve', 'Chicago')  # This customer has no orders
]

orders_data = [
    (101, 1, 'Widget', 29.99),
    (102, 1, 'Gadget', 49.99),
    (103, 2, 'Widget', 19.99),
    (104, 3, 'Gizmo', 39.99),
    (105, 3, 'Widget', 24.99),
    (106, 4, 'Gadget', 59.99)
]

cursor.executemany('INSERT INTO customers VALUES (?, ?, ?)', customers_data)
cursor.executemany('INSERT INTO orders VALUES (?, ?, ?, ?)', orders_data)
conn.commit()

# INNER JOIN: Only customers who have placed orders
print("=== INNER JOIN: Customers with orders ===")
cursor.execute('''
    SELECT customers.name, orders.product, orders.amount
    FROM customers
    INNER JOIN orders ON customers.customer_id = orders.customer_id
''')
for row in cursor.fetchall():
    print(row)

print(f"\nTotal rows: {len(cursor.fetchall())}")  # Note: cursor is exhausted, so this is 0

# LEFT JOIN: All customers, even those without orders
print("\n=== LEFT JOIN: All customers ===")
cursor.execute('''
    SELECT customers.name, orders.product, orders.amount
    FROM customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id
''')
for row in cursor.fetchall():
    print(row)

conn.close()

=== INNER JOIN: Customers with orders ===
('Alice', 'Widget', 29.99)
('Alice', 'Gadget', 49.99)
('Bob', 'Widget', 19.99)
('Charlie', 'Gizmo', 39.99)
('Charlie', 'Widget', 24.99)
('Diana', 'Gadget', 59.99)

=== LEFT JOIN: All customers ===
('Alice', 'Widget', 29.99)
('Alice', 'Gadget', 49.99)
('Bob', 'Widget', 19.99)
('Charlie', 'Gizmo', 39.99)
('Charlie', 'Widget', 24.99)
('Diana', 'Gadget', 59.99)
('Eve', None, None)

Notice the difference:

• INNER JOIN returned 6 rows — only customers who have at least one order. Eve has no orders, so she’s excluded.
• LEFT JOIN returned 7 rows — all customers. Eve appears with NULL values in the order columns because she has no matching orders.

Now let’s compare with the pandas equivalent:

import pandas as pd

# Recreate the data in pandas
customers = pd.DataFrame({
    'customer_id': [1, 2, 3, 4, 5],
    'name': ['Alice', 'Bob', 'Charlie', 'Diana', 'Eve'],
    'city': ['New York', 'Chicago', 'New York', 'Los Angeles', 'Chicago']
})

orders = pd.DataFrame({
    'order_id': [101, 102, 103, 104, 105, 106],
    'customer_id': [1, 1, 2, 3, 3, 4],
    'product': ['Widget', 'Gadget', 'Widget', 'Gizmo', 'Widget', 'Gadget'],
    'amount': [29.99, 49.99, 19.99, 39.99, 24.99, 59.99]
})

# Pandas INNER JOIN
inner_result = pd.merge(customers, orders, on='customer_id', how='inner')
print("Pandas INNER JOIN:")
print(inner_result[['name', 'product', 'amount']].to_string(index=False))
print(f"\nTotal rows: {len(inner_result)}")

# Pandas LEFT JOIN
left_result = pd.merge(customers, orders, on='customer_id', how='left')
print("\nPandas LEFT JOIN:")
print(left_result[['name', 'product', 'amount']].to_string(index=False))
print(f"\nTotal rows: {len(left_result)}")

Pandas INNER JOIN:
   name product  amount
  Alice  Widget   29.99
  Alice  Gadget   49.99
    Bob  Widget   19.99
Charlie   Gizmo   39.99
Charlie  Widget   24.99
  Diana  Gadget   59.99

Total rows: 6

Pandas LEFT JOIN:
   name product  amount
  Alice  Widget   29.99
  Alice  Gadget   49.99
    Bob  Widget   19.99
Charlie   Gizmo   39.99
Charlie  Widget   24.99
  Diana  Gadget   59.99
    Eve     NaN     NaN

Total rows: 7

The results are identical. But look at the code: the SQL version is one statement, while the pandas version requires a function call with three arguments. For a three-table join, you’d need two pd.merge() calls in pandas, but just one SQL query with two JOIN clauses. The SQL version is more readable and less error-prone.

6. Bridging the Gap: Loading SQLite Results into Pandas

Here’s the payoff. You do the heavy lifting — joins, filtering, aggregation — in SQLite, then load the clean result into pandas for analysis. No intermediate CSV file needed.

import sqlite3
import pandas as pd

# Recreate the database with iris data
from sklearn.datasets import load_iris
iris = load_iris()
df = pd.DataFrame(iris.data, columns=iris.feature_names)
df['species'] = iris.target_names[iris.target]

conn = sqlite3.connect('iris.db')
df.to_sql('iris', conn, if_exists='replace', index=False)

# Now run a SQL query and load the result into pandas
query = '''
    SELECT species, 
           AVG(petal_length) as avg_petal_length,
           AVG(petal_width) as avg_petal_width,
           COUNT(*) as count
    FROM iris
    WHERE sepal_length > 5.0
    GROUP BY species
    ORDER BY avg_petal_length DESC
'''

result_df = pd.read_sql(query, conn)
print("Result from SQL query:")
print(result_df)

# Now we can analyze this DataFrame with pandas
print("\nPandas describe on the result:")
print(result_df.describe())

conn.close()

Result from SQL query:
     species  avg_petal_length  avg_petal_width  count
0  virginica          5.552000         2.026000     50
1  versicolor          4.260000         1.326000     47
2     setosa          1.462000         0.246000     13

Pandas describe on the result:
       avg_petal_length  avg_petal_width      count
count          3.000000         3.000000   3.000000
mean           3.758000         1.199333  36.666667
std             2.112529         0.906272  20.550803
min             1.462000         0.246000  13.000000
25%             2.861000         0.786000  30.000000
50%             4.260000         1.326000  47.000000
75%             4.906000         1.676000  48.500000
max             5.552000         2.026000  50.000000

Notice what happened: the SQL query filtered, grouped, and aggregated the data. It returned only 3 rows — one per species. Then pd.read_sql() loaded those 3 rows into a DataFrame, which we analyzed with pandas. The heavy lifting happened in SQLite, not in pandas memory.

One caveat from the pandas docs: SQLite requires a full SQL query string, not a bare table name. So always write 'SELECT * FROM iris' not just 'iris'.

7. In-Memory Databases: The Ultimate Scratch Pad

Sometimes you don’t want to leave files behind. Maybe you’re prototyping a complex query, or you’re running a one-off analysis on a small dataset. SQLite has a special mode for this: :memory:.

import sqlite3
import pandas as pd

# Create an in-memory database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create and populate a table
cursor.execute('CREATE TABLE sales (product TEXT, amount REAL, quantity INTEGER)')
sales_data = [
    ('Widget', 29.99, 100),
    ('Gadget', 49.99, 50),
    ('Gizmo', 39.99, 75),
    ('Widget', 24.99, 200),
    ('Gadget', 59.99, 30)
]
cursor.executemany('INSERT INTO sales VALUES (?, ?, ?)', sales_data)
conn.commit()

# Run a query and load into pandas
query = '''
    SELECT product,
           SUM(amount * quantity) as total_revenue,
           SUM(quantity) as total_units
    FROM sales
    GROUP BY product
    ORDER BY total_revenue DESC
'''

result = pd.read_sql(query, conn)
print("Sales summary:")
print(result)

# Close the connection — the database disappears
conn.close()

# Verify no file was created
import os
print(f"\nAny .db files in current directory? {[f for f in os.listdir('.') if f.endswith('.db')]}")

Sales summary:
  product  total_revenue  total_units
0  Widget        7997.00          300
1  Gadget        4298.50           80
2   Gizmo        2999.25           75

Any .db files in current directory? ['iris.db']

Only iris.db exists from earlier — no new file was created for the in-memory database. This is perfect for testing queries before running them on a real database, or for one-off analyses where you don’t want cleanup.

One caveat from the SQLite docs: each :memory: connection is isolated by default. If you create two connections to :memory:, they see different databases. To share one across connections, use the cache=shared URI parameter: sqlite3.connect('file::memory:?cache=shared', uri=True).

8. When SQLite Is the Wrong Choice (And What to Use Instead)

SQLite is not a silver bullet. Let’s be honest about its limitations so you know when to reach for something else.

Scenario	SQLite	Better Alternative
Single-record lookups (e.g., “find user 42”)	✅ Excellent	—
Small-to-medium datasets (< 10GB)	✅ Great	—
Complex analytical queries on millions of rows	❌ Slow	DuckDB, ClickHouse
Multi-user writes (web app)	❌ Locks entire file	PostgreSQL, MySQL
Read-only analytical data	❌ Row-oriented	Parquet + DuckDB
In-memory analytics faster than pandas	❌ Not designed for this	Polars, DuckDB

Here’s the key distinction: SQLite is row-oriented (OLTP). It’s optimized for transactions — inserting a row, updating a record, looking up a single customer. It processes one row at a time.

DuckDB, on the other hand, is column-oriented (OLAP). It’s designed for analytical queries — aggregations over millions of rows. As the PostHog comparison explains, DuckDB processes data in vectors of 2048 values at a time, while SQLite processes one row at a time. That makes DuckDB 10-100x faster on analytical workloads.

As the DataCamp comparison puts it: “SQLite for storage and transactions, DuckDB for analytical queries on top.” They complement each other.

So when should you use SQLite? When you’re doing OLTP-style work: single-record lookups, transactions, small-to-medium data (under a few GB). When should you use DuckDB? When you’re doing OLAP-style work: aggregations over millions of rows, complex analytical queries.

9. Beyond the Basics: A Glimpse at the SQLite Ecosystem

SQLite has a rich ecosystem of tools that make it even more useful for data science:

• sqlite-utils: A CLI tool and Python library for “creating the initial database and populating it with data as productive as possible.” It’s not a full ORM — just a convenience layer for inserting data from CSV, JSON, or other formats.
• Datasette: A web-based tool for exploring and publishing SQLite databases. It gives you a read-only web interface for your data, complete with filtering, sorting, and API access.
• DB Browser for SQLite: A GUI for viewing and editing SQLite databases. Useful for debugging — you can open your .db file and see the tables, run queries, and edit data visually.

These aren’t required for the workflow we’ve covered, but they’re good to know about if you want to go deeper.

10. Recap: What You Learned

Let’s summarize what you can now do:

1. SQLite is a serverless, zero-configuration database engine that lives in a single file. It’s built into Python’s standard library — no pip install needed.
2. SQL queries are declarative: you describe what you want, not how to get it. A single JOIN statement replaces multiple pd.merge() calls.
3. pd.read_sql() bridges SQLite and pandas: do the heavy lifting in SQL, analyze in pandas.
4. In-memory databases (:memory:) are perfect for prototyping and one-off analyses — no files left behind.
5. SQLite is great for single-user, local, OLTP workloads. For analytical workloads on large datasets, consider DuckDB.
6. The SQLite ecosystem (sqlite-utils, Datasette) makes it even more powerful.

11. Check Your Understanding

Test yourself with these questions, ordered by Bloom’s taxonomy:

Remember: What Python module do you import to work with SQLite databases?

Understand: In your own words, explain the difference between an INNER JOIN and a LEFT JOIN. When would you use each?

Apply: Write a SQL query that selects all rows from the orders table where the amount is greater than 50, ordered by amount descending, limited to 5 rows.

Analyze: Compare the code needed to do a three-table join in pandas vs. in SQL. Which is more readable? Which is more error-prone? Why?

Evaluate: You have a 50GB CSV file and need to run complex aggregations on it. Should you use SQLite? Why or why not? What would you use instead?

Create: Design a small SQLite database for a personal project (e.g., a book library, a recipe collection, a workout log). Write the CREATE TABLE statements and at least one JOIN query.

12. Related Articles

• Part 1: SQLAlchemy for Data Scientists — If you need to connect to PostgreSQL or MySQL databases, SQLAlchemy provides a more feature-rich interface than raw sqlite3.
• Part 3: Merging and Joining DataFrames — Covers pandas merge() in depth, which this article contrasts with SQL JOINs.
• Part 5: JAX for NumPy Users — For when you need GPU-accelerated numerical computing, not database operations.
• Part 7: Dask for Out-of-Core Data — When your dataset doesn’t fit in memory, Dask provides a pandas-like interface for out-of-core processing.

13. Apply What You Learned