{"id":48,"date":"2024-04-21T19:10:00","date_gmt":"2024-04-21T16:10:00","guid":{"rendered":"https:\/\/fsaeed.blog\/?p=48"},"modified":"2026-10-31T00:18:53","modified_gmt":"2026-10-30T21:18:53","slug":"sqlite-for-analysts","status":"publish","type":"post","link":"https:\/\/fsaeed.blog\/index.php\/2024\/04\/21\/sqlite-for-analysts\/","title":{"rendered":"Stop Emailing 50 MB Spreadsheets: SQLite for Analysts"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Hello, tech enthusiasts! \ud83d\udc4b<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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 &#8220;the latest version&#8221;, so you email it. Now there are three copies, and nobody knows which one is right.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">There is a better way, and it is free. It is called <strong>SQLite<\/strong>. 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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">What you need<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Python 3 from <a href=\"https:\/\/www.python.org\">https:\/\/www.python.org<\/a> (the <code>sqlite3<\/code> module is already included)<\/li>\n\n\n<li>pandas and openpyxl: <code>pip install pandas openpyxl<\/code><\/li>\n\n\n<li>A big file, for example <code>sales.xlsx<\/code> with the columns date, region, product and amount<\/li>\n\n\n<li>Optional: DB Browser for SQLite, a free app from <a href=\"https:\/\/sqlitebrowser.org\">https:\/\/sqlitebrowser.org<\/a><\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Step 1: Move your Excel data into SQLite<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Create a file called <code>load.py<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import sqlite3\nimport pandas as pd\n\n# Read the big Excel file one time\ndf = pd.read_excel(\"sales.xlsx\")\n\n# Connect to the database file. If it does not exist, SQLite creates it.\nconn = sqlite3.connect(\"sales.db\")\n\n# Save the DataFrame as a table called \"sales\"\ndf.to_sql(\"sales\", conn, if_exists=\"replace\", index=False)\n\nconn.close()\nprint(f\"Loaded {len(df)} rows into sales.db\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Run it with <code>python load.py<\/code>. You now have a file called <code>sales.db<\/code>. It is often much smaller than the Excel file, and it opens in a moment.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><code>if_exists=\"replace\"<\/code> means &#8220;if the table already exists, throw it away and build it again&#8221;. Later, when you add new data, use <code>if_exists=\"append\"<\/code> to add rows at the end.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 2: Ask questions with SQL<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>SQL<\/strong> is the language we use to ask a database for data. It reads almost like English. The most common command is <code>SELECT<\/code>, which means &#8220;show me&#8221;.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Create <code>report.py<\/code>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import sqlite3\nimport pandas as pd\n\nconn = sqlite3.connect(\"sales.db\")\n\n# Show the first 10 rows\nfirst_rows = pd.read_sql(\"SELECT * FROM sales LIMIT 10\", conn)\nprint(first_rows)\n\n# Total sales per region, biggest first\nquery = \"\"\"\n    SELECT region,\n           COUNT(*)    AS orders,\n           SUM(amount) AS total_amount\n    FROM sales\n    GROUP BY region\n    ORDER BY total_amount DESC\n\"\"\"\nby_region = pd.read_sql(query, conn)\nprint(by_region)\n\nconn.close()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><code>GROUP BY<\/code> works like a PivotTable. It puts rows with the same region together, then <code>COUNT<\/code> and <code>SUM<\/code> do the maths for each group.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 3: Filter safely with parameters<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Often you want only one region or one month. Do not glue the value into the SQL text yourself. Use a <code>?<\/code> placeholder and pass the value separately. This is safer and avoids errors with special characters:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import sqlite3\nimport pandas as pd\n\nconn = sqlite3.connect(\"sales.db\")\n\nquery = \"\"\"\n    SELECT product, SUM(amount) AS total_amount\n    FROM sales\n    WHERE region = ?\n    GROUP BY product\n    ORDER BY total_amount DESC\n\"\"\"\nnorth = pd.read_sql(query, conn, params=(\"North\",))\nnorth.to_excel(\"north_by_product.xlsx\", index=False)\n\nconn.close()\nprint(\"Saved north_by_product.xlsx\")<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Now you email a small file with only the answer, not the whole 50 MB of data. \ud83c\udf89<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 4: Make searches faster with an index<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">An <strong>index<\/strong> 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:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>import sqlite3\n\nconn = sqlite3.connect(\"sales.db\")\nconn.execute(\"CREATE INDEX IF NOT EXISTS idx_sales_region ON sales(region)\")\nconn.commit()\nconn.close()<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If you load with <code>if_exists=\"replace\"<\/code>, the table is rebuilt, so run this again after each full load.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step 5: Look inside with DB Browser for SQLite<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Not everyone on your team uses Python. That is fine. Install DB Browser for SQLite and click <strong>Open Database<\/strong> to open <code>sales.db<\/code>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The <strong>Browse Data<\/strong> tab shows the table, a bit like Excel. You can filter each column.<\/li>\n\n\n<li>The <strong>Execute SQL<\/strong> tab lets you type a query like the ones above and press <code>Ctrl + Enter<\/code> to run it.<\/li>\n\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Be careful in the Browse Data tab: changes are saved only when you click <strong>Write Changes<\/strong>. If you only want to look, click <strong>Revert Changes<\/strong> before closing.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Common problems<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><code>database is locked<\/code>.<\/strong> \u2192 Another program, like DB Browser, has unsaved changes. Click <strong>Write Changes<\/strong> or <strong>Revert Changes<\/strong> there, then run your script again.<\/li>\n\n\n<li><strong><code>no such table: sales<\/code>.<\/strong> \u2192 You are connected to a different file. Use the full path, like <code>sqlite3.connect(r\"C:\\Reports\\sales.db\")<\/code>.<\/li>\n\n\n<li><strong>Dates sort in a strange order.<\/strong> \u2192 Store them as text in <code>YYYY-MM-DD<\/code> format. Before <code>to_sql<\/code>, run <code>df[\"date\"] = pd.to_datetime(df[\"date\"]).dt.strftime(\"%Y-%m-%d\")<\/code>.<\/li>\n\n\n<li><strong>Duplicate rows after many loads.<\/strong> \u2192 You used <code>append<\/code> with the same file twice. Load only new data, or use <code>replace<\/code> for a full reload.<\/li>\n\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Wrap-up<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">You moved a heavy spreadsheet into one light database file. Now you ask it questions in seconds and share only the answers.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Next, try connecting this with Task Scheduler: load the new data into <code>sales.db<\/code> every morning, and have your report ready before you arrive. Happy querying!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Keep your big data in one small SQLite database file and ask it questions with Python, pandas and simple SQL.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[5,13,12,19,18],"class_list":["post-48","post","type-post","status-publish","format-standard","hentry","category-uncategorized","tag-excel","tag-pandas","tag-python","tag-sql","tag-sqlite"],"_links":{"self":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/48","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/comments?post=48"}],"version-history":[{"count":1,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/48\/revisions"}],"predecessor-version":[{"id":85,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/posts\/48\/revisions\/85"}],"wp:attachment":[{"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/media?parent=48"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/categories?post=48"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fsaeed.blog\/index.php\/wp-json\/wp\/v2\/tags?post=48"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}