Stop Emailing 50 MB Spreadsheets: SQLite for Analysts

Hello, tech enthusiasts! 👋

Your sales file has grown. It is now 50 MB, it takes two minutes to open, and Excel freezes when you filter it. Your colleague asks for “the latest version”, so you email it. Now there are three copies, and nobody knows which one is right.

There is a better way, and it is free. It is called SQLite. SQLite is a database that lives in one single file. A database is like a very organised cupboard for data: you put data in, and you ask it questions to get exactly the part you need. You do not need a server, a password or an IT team. Python already includes it.

What you need

  • Python 3 from https://www.python.org (the sqlite3 module is already included)
  • pandas and openpyxl: pip install pandas openpyxl
  • A big file, for example sales.xlsx with the columns date, region, product and amount
  • Optional: DB Browser for SQLite, a free app from https://sqlitebrowser.org

Step 1: Move your Excel data into SQLite

Create a file called load.py:

import sqlite3
import pandas as pd

# Read the big Excel file one time
df = pd.read_excel("sales.xlsx")

# Connect to the database file. If it does not exist, SQLite creates it.
conn = sqlite3.connect("sales.db")

# Save the DataFrame as a table called "sales"
df.to_sql("sales", conn, if_exists="replace", index=False)

conn.close()
print(f"Loaded {len(df)} rows into sales.db")

Run it with python load.py. You now have a file called sales.db. It is often much smaller than the Excel file, and it opens in a moment.

if_exists="replace" means “if the table already exists, throw it away and build it again”. Later, when you add new data, use if_exists="append" to add rows at the end.

Step 2: Ask questions with SQL

SQL is the language we use to ask a database for data. It reads almost like English. The most common command is SELECT, which means “show me”.

Create report.py:

import sqlite3
import pandas as pd

conn = sqlite3.connect("sales.db")

# Show the first 10 rows
first_rows = pd.read_sql("SELECT * FROM sales LIMIT 10", conn)
print(first_rows)

# Total sales per region, biggest first
query = """
    SELECT region,
           COUNT(*)    AS orders,
           SUM(amount) AS total_amount
    FROM sales
    GROUP BY region
    ORDER BY total_amount DESC
"""
by_region = pd.read_sql(query, conn)
print(by_region)

conn.close()

GROUP BY works like a PivotTable. It puts rows with the same region together, then COUNT and SUM do the maths for each group.

Step 3: Filter safely with parameters

Often you want only one region or one month. Do not glue the value into the SQL text yourself. Use a ? placeholder and pass the value separately. This is safer and avoids errors with special characters:

import sqlite3
import pandas as pd

conn = sqlite3.connect("sales.db")

query = """
    SELECT product, SUM(amount) AS total_amount
    FROM sales
    WHERE region = ?
    GROUP BY product
    ORDER BY total_amount DESC
"""
north = pd.read_sql(query, conn, params=("North",))
north.to_excel("north_by_product.xlsx", index=False)

conn.close()
print("Saved north_by_product.xlsx")

Now you email a small file with only the answer, not the whole 50 MB of data. 🎉

Step 4: Make searches faster with an index

An index is like the index at the back of a book. Instead of reading every page, the database jumps to the right place. Add one for columns you filter often:

import sqlite3

conn = sqlite3.connect("sales.db")
conn.execute("CREATE INDEX IF NOT EXISTS idx_sales_region ON sales(region)")
conn.commit()
conn.close()

If you load with if_exists="replace", the table is rebuilt, so run this again after each full load.

Step 5: Look inside with DB Browser for SQLite

Not everyone on your team uses Python. That is fine. Install DB Browser for SQLite and click Open Database to open sales.db.

  • The Browse Data tab shows the table, a bit like Excel. You can filter each column.
  • The Execute SQL tab lets you type a query like the ones above and press Ctrl + Enter to run it.

Be careful in the Browse Data tab: changes are saved only when you click Write Changes. If you only want to look, click Revert Changes before closing.

Common problems

  • database is locked. → Another program, like DB Browser, has unsaved changes. Click Write Changes or Revert Changes there, then run your script again.
  • no such table: sales. → You are connected to a different file. Use the full path, like sqlite3.connect(r"C:\Reports\sales.db").
  • Dates sort in a strange order. → Store them as text in YYYY-MM-DD format. Before to_sql, run df["date"] = pd.to_datetime(df["date"]).dt.strftime("%Y-%m-%d").
  • Duplicate rows after many loads. → You used append with the same file twice. Load only new data, or use replace for a full reload.

Wrap-up

You moved a heavy spreadsheet into one light database file. Now you ask it questions in seconds and share only the answers.

Next, try connecting this with Task Scheduler: load the new data into sales.db every morning, and have your report ready before you arrive. Happy querying!

fsaeed

, , , ,