Python code: SQLite from scratch (create, insert, query, update, delete)

Python code: SQLite from scratch (create, insert, query, update, delete)

When the stock ledger lives scattered across loose files — one spreadsheet per month, a copy on the counter computer, another one sitting in the accountant mail — the real problem is not technology: it is that nobody can answer a simple question with confidence. What the warehouse is worth today. What price the drill went for last week. How many rolls of insulating tape are genuinely left. Every copy gives its own answer and none of them is in charge. This program shows, in seven steps you can watch on screen, how that same stock ledger is kept in one SQLite database: a single file, with a single version of the truth, where a price is corrected in one place and the total works itself out. It is written for the owner of a small business, for the accountant who receives boxes of receipts and for the storekeeper who counts and writes things down; you do not need to know how to program in order to follow what is happening.

No database engine to install, no licence to pay and no internet connection required. Python ships SQLite inside, and this ZIP ships everything else.

What the ZIP contains

What the business gains from a database instead of loose files

A spreadsheet is a photograph: it is good for whatever is visible in the picture and for nothing else. A database is a living file where each product exists once and where the rules always hold. Look at what changes day to day. First, the rival copies disappear: the price of the wire is one number, not whatever the salesperson remembers. Second, the file blocks impossible data: the program defines the code as the key, so two products cannot share the same code, no matter how many people work at the same time. Third, correcting a price does not force anyone to recompute anything by hand, because the value of each line is worked out at the moment of the query. And fourth, the total stock value and the subtotals by category are a question, not a formula somebody pasted into the next column that breaks the moment a row is inserted.

There is one more advantage that only shows up on a bad day: the database is changed inside a block that is committed whole or undone whole. If the program fails halfway through loading, you are not left with half a stock ledger on file: either everything went in or nothing did. In a spreadsheet, a power cut in the middle of a paste leaves a file nobody can trust.

What the program does

1. Creates the database and the products table with its data types, the first time it runs.
2. Reads the eight sample products and inserts them one by one, using a parameterized query.
3. Queries the whole table ordered by code and builds the listing with the value of each line.
4. Updates the price of the wire and shows the price before and the price after.
5. Deletes the insulating tape and confirms how many products are left in the table.
6. Works out the inventory value by category and checks that the sum of the categories matches the total.
7. Closes the connection and confirms: had any step failed, the database would have stayed as it was.

How to run it

Install Python 3.11 or newer from the official site and tick the box that adds Python to the system. Unzip the archive into a comfortable folder, open the terminal in that folder and type python sqlite_desde_cero.py. There is nothing else to install: the database ships inside Python and every other tool comes from the standard library. If the python command does not answer, try py sqlite_desde_cero.py.

The code, explained

The program fits on a screen and a half and reads from top to bottom. These are the five pieces worth understanding, because they are the ones that come back in any stock automation later on.

One: where the tools come from. Four imports and nothing more. The second one is the one that matters for the business, because it means the database already ships with Python and your stock backup is a file you copy the way you copy a photograph.

import csv                                         # csv: reads the sample products from a text file
import sqlite3                                     # sqlite3: the database ships inside Python, nothing to install
from decimal import Decimal, ROUND_HALF_UP          # Decimal: money is never computed with binary decimals (float)
from pathlib import Path                            # pathlib: paths that work on Windows, Linux and Mac

Two: the database is opened inside a contract. The with block opens the connection and, on the way out, commits the pending changes. If anything blows up along the way, it undoes the whole thing and leaves the file as it was. For an owner, that is the difference between a stock ledger you can trust and one that is half done.

    # with sqlite3.connect(...): opens the database and commits pending changes on exit
    with sqlite3.connect(RUTA_DB) as conexion:
        cursor = conexion.cursor()

Three: the data goes in through question marks. Here is the most important decision in the program and it is almost never explained in tutorials. The text of the query is written once, with one question mark for each value that will come in; the values travel separately, as an ordered list. The query is never built by pasting text together. The business reason is simple: a supplier name, a product reference or a note on an invoice can carry quotation marks or any other sign, and if that text were pasted into the query the engine would read it as an order rather than as data. With parameters the engine always knows what is the order and what is the data, and on top of that the same query runs many times without being parsed again, which is exactly what you need when loading hundreds of references.

        sql_insert = ("INSERT INTO productos (codigo, producto, categoria, precio, "
                      "existencias) VALUES (?, ?, ?, ?, ?)")
        mostrar_sql(sql_insert)
        for producto in productos:
            cursor.execute(sql_insert, (
                producto["code"], producto["product"],
                producto["category"], int(producto["price"]),
                int(producto["stock"]),
            ))

Four: the program shows the SQL it is about to run. This function looks like decoration and it is half the lesson: on screen you read the order in the language of databases, just before it runs. By the end of the session you already recognise the handful of statements of the trade, and they are the same ones used by any serious stock system.

def mostrar_sql(sql: str) -> None:
    """Shows the SQL statement about to run: reading the SQL is half the lesson."""
    print(f"SQL: {sql}")

Five: the business question, in one line. Counting the products and adding up the value of the warehouse grouped by category, sorted from largest to smallest, is exactly what an owner wants to see at month end. In a spreadsheet that is a pivot table you rebuild every time; here it is a single instruction that always reads the data as it stands.

        sql_grupo = ("SELECT categoria, COUNT(*) AS articulos, "
                     "SUM(precio * existencias) AS valor FROM productos "
                     "GROUP BY categoria ORDER BY valor DESC")

What you will see on screen

The output is in English, with the numbers in the format of the trade. A slice of the query and of the product listing:

SQL: INSERT INTO productos (codigo, producto, categoria, precio, existencias) VALUES (?, ?, ?, ?, ?)
Products inserted: 8
----------------------------------------------------------------------------------------------
Code    Product                      Category               Price       Stock             Value
----------------------------------------------------------------------------------------------
E01     THHN 12 AWG wire (meter)     Electrical          3,600.00         500      1,800,000.00
E02     Breaker 20 A                 Electrical         42,800.00          24      1,027,200.00
E03     Black insulating tape        Electrical          5,900.00         200      1,180,000.00
H01     650 W hammer drill           Tools             289,900.00          12      3,478,800.00
H02     16 oz ball hammer            Tools              45,900.00          40      1,836,000.00
H03     10 in adjustable wrench      Tools              62,900.00          18      1,132,200.00
P01     PVC pipe 1/2 in x 3 m        Plumbing           18,900.00         120      2,268,000.00
P02     1/2 in shut-off valve        Plumbing           34,500.00          35      1,207,500.00
----------------------------------------------------------------------------------------------
Rows queried: 8

And the closing block, with the figure the owner actually takes to the meeting:

  Products in the table: 7
  Total inventory value: 12,899,700.00
  File written: salida/tienda.db

Common mistakes and tips

• Running the program twice in a row makes no mess: it deletes the earlier database and builds it again, so the output is always the same and can be compared.
• If you edit the products file, respect the commas and the header row. One extra space in a column name and the load comes up empty.
• The database file does not open on a double click like a spreadsheet: you need a tool that speaks the language of queries, and that is precisely what the program does.
• Do not move the program halfway through: it looks for its data next to itself, so if you want to run it from another folder, move the whole package.
• Before trying changes, copy the database file into another folder. Copying a single file is all the backup you need.
• Prices are stored as whole units, with no binary cents, and the value of each line is worked out at query time. That way the total never drags a cent of difference.

When this is no longer enough

This program is the right starting point once you want the stock ledger to live in one place and you have lost your fear of the terminal. But a moment comes when the business asks for more: several people working at once, invoicing, purchasing, reports the accountant can print without anybody help, automatic backups and buttons instead of lines of text. That is when a program built for it — such as Kardex Tauro, which is free and helps you bring the stock ledger into order — becomes the sensible choice: underneath it is still an SQLite database, only you no longer have to write it yourself. Until that moment arrives, this ZIP and Kardex Tauro share the same idea at heart: the data of the business is stored once, and stored properly.

Download the package, run it once and read the screen calmly. It is seven steps, and at the end your stock ledger already lives in a single database.