Sort Large CSV Files by Integer Column with Python and SQLite

As a CSV grows, sorted(list(csv.reader(...))) can consume substantial memory. Sorting raw text can put 10 before 2 and split quoted fields containing newlines.

This beginner-to-intermediate tutorial reads an unsorted CSV into a temporary SQLite database, then writes a new file ordered by an integer column. The existing streaming large CSV tutorial covers row-by-row tasks such as counting; this follow-up handles global sorting, which needs information from the whole input.

1. Method: integer keys with stable ties

1. Parse records with csv.reader, not by splitting physical lines.
2. Store an integer sort key, an input sequence number seq, and the complete field list.
3. Run ORDER BY sort_key, seq and stream the result without fetchall().
4. Finish a temporary output before publishing it at a new path; keep any existing output.

SQLite’s ORDER BY documentation explains how successive expressions break ties. The second key, seq, explicitly preserves input order for equal integer keys instead of relying on incidental database order.

Input policy: UTF-8, comma-separated, double-quoted CSV, with an optional initial BOM. Require a nonempty header, nonblank unique column names, and matching field counts. Match the key column name exactly. Keys must be ASCII integers with an optional sign, between -2**63 and 2**63-1; whitespace, decimals, and missing values are rejected. 02 and 2 compare equally, but retain their original field text.

2. Setup and test data

Use Linux, a writable local filesystem, and Python 3.9 or newer with sqlite3 available. No pip packages or database server are needed. Examples were tested on Python 3.11.4 and SQLite 3.41.2. Commands work in bash or zsh; keep all steps in the same fresh directory and choose a different name for another run.

python3 --version
python3 -c 'import sqlite3; print(sqlite3.sqlite_version)'
mkdir csv-sort-demo
cd csv-sort-demo
mkdir scratch
cat > jobs.csv <<'CSV'
id,priority,note
A,10,"needs, review"
B,2,"line one
line two"
C,2,ready
D,-1,first
CSV

Save the next section as sort_csv.py, or download the complete sorting script. Use completed ordinary input files that do not change during the run. The output parent and the directory passed to --work-dir must already exist.

3. Complete implementation: stream into SQLite and back out

"""Sort UTF-8 CSV by one signed 64-bit integer column. Python 3.9+."""
import argparse
from contextlib import closing
import csv
import json
import os
from pathlib import Path
import re
import sqlite3
import sys
import tempfile


def records(reader, key_index, width):
    for seq, row in enumerate(reader, 1):
        if len(row) != width:
            raise ValueError(f"record {seq}: expected {width} fields, got {len(row)}")
        text = row[key_index]
        if not re.fullmatch(r"[+-]?[0-9]+", text):
            raise ValueError(f"record {seq}: key must be an integer, got {text!r}")
        number = int(text)
        if not -(2**63) <= number < 2**63:
            raise ValueError(f"record {seq}: key outside signed 64-bit range")
        yield seq, number, json.dumps(row, ensure_ascii=True)


def sort_csv(source, output, key, work_dir):
    if source.resolve() == output.resolve():
        raise ValueError("input and output must differ")
    if os.path.lexists(output):
        raise FileExistsError(f"output already exists: {output}")
    staged = None
    try:
        with tempfile.TemporaryDirectory(prefix="csv-sort-", dir=work_dir) as tmp:
            with closing(sqlite3.connect(str(Path(tmp) / "rows.sqlite"))) as con:
                con.execute("PRAGMA temp_store=FILE")
                con.execute("PRAGMA cache_size=-8192")
                con.execute("CREATE TABLE items (seq INTEGER PRIMARY KEY, "
                            "sort_key INTEGER NOT NULL, payload TEXT NOT NULL)")
                with source.open(encoding="utf-8-sig", newline="") as file:
                    reader = csv.reader(file, strict=True)
                    header = next(reader, None)
                    if not header or any(not name.strip() for name in header):
                        raise ValueError("a nonempty header is required")
                    if len(set(header)) != len(header):
                        raise ValueError("duplicate header names")
                    if key not in header:
                        raise ValueError(f"missing key column: {key!r}")
                    with con:
                        con.executemany(
                            "INSERT INTO items VALUES (?, ?, ?)",
                            records(reader, header.index(key), len(header)),
                        )
                count = con.execute("SELECT count(*) FROM items").fetchone()[0]
                with tempfile.NamedTemporaryFile(
                    mode="w", encoding="utf-8", newline="", delete=False,
                    prefix=".csv-sort-", suffix=".tmp", dir=output.parent,
                ) as file:
                    staged = Path(file.name)
                    writer = csv.writer(file, lineterminator="\n")
                    writer.writerow(header)
                    for (payload,) in con.execute(
                        "SELECT payload FROM items ORDER BY sort_key, seq"
                    ):
                        writer.writerow(json.loads(payload))
        # Same-directory hard link: publish a complete file without overwriting.
        os.link(staged, output)
        return count
    finally:
        if staged is not None:
            staged.unlink(missing_ok=True)


def main():
    parser = argparse.ArgumentParser(description=__doc__, allow_abbrev=False)
    parser.add_argument("input", type=Path)
    parser.add_argument("output", type=Path, help="new file in an existing directory")
    parser.add_argument("--key", required=True, help="exact integer column name")
    parser.add_argument("--work-dir", type=Path, help="existing SQLite workspace parent")
    args = parser.parse_args()
    csv.field_size_limit(1024 * 1024)
    try:
        count = sort_csv(args.input, args.output, args.key, args.work_dir)
    except (OSError, ValueError, csv.Error, sqlite3.Error) as exc:
        print(f"ERROR: {exc}", file=sys.stderr)
        return 1
    print(f"OK: rows={count} key={args.key} output={args.output}")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())

The Python sqlite3 documentation covers disk databases, parameter placeholders, and cursor iteration. The script uses fixed SQL and bound values; the user-provided column name only selects a CSV field position and is never interpolated into SQL.

The Python CSV documentation recommends opening CSV files with newline="". Parsing preserves embedded commas, quotes, and newlines, but writing regenerates CSV quoting and record separators: field values are preserved, not original bytes. Each field is limited to 1 MiB characters, including fields in the header.

TemporaryDirectory manages the database workspace, and the connection closes explicitly before cleanup. The staged output lives beside the destination. After writing and closing it, os.link() creates the final name without replacing an existing file. This requires a local filesystem supporting hard links and does not guarantee power-loss durability. Normal returns and ordinary exceptions clean up temporary files; forced termination or power loss can leave remnants to inspect manually.

4. Run and validate

1. Show help, then sort:

python3 sort_csv.py --help
python3 sort_csv.py jobs.csv sorted.csv --key priority --work-dir scratch

Expected output:

OK: rows=4 key=priority output=sorted.csv

2. Check ordering and fields with a CSV parser; do not treat wc -l as a record count:

python3 - <<'PY_CHECK'
import csv
from pathlib import Path
with open("sorted.csv", encoding="utf-8", newline="") as file:
    rows = list(csv.DictReader(file))
assert [row["id"] for row in rows] == ["D", "B", "C", "A"]
assert rows[1]["note"] == "line one\nline two"
assert rows[-1]["note"] == "needs, review"
assert not list(Path("scratch").iterdir())
assert not list(Path(".").glob(".csv-sort-*.tmp"))
print("OK: numeric order, stable ties, intact CSV, cleaned workspace")
PY_CHECK

Expect OK: numeric order, stable ties, intact CSV, cleaned workspace. IDs are D, B, C, A, rather than the text-key order D, A, B, C. B and C both have key 2, so they retain input order. A list is fine for this small assertion; the actual sorting script does not retain every record in a Python list.

5. Verify failure paths

Run in the same directory, keeping the sorted.csv created above:

printf 'id,priority\nX,not-a-number\n' > bad.csv
if python3 sort_csv.py bad.csv rejected.csv --key priority --work-dir scratch; then
  printf 'UNEXPECTED SUCCESS\n'
else
  printf 'STOP: invalid key\n'
fi
test ! -e rejected.csv && printf 'OK: no output published\n'
if python3 sort_csv.py jobs.csv sorted.csv --key priority --work-dir scratch; then
  printf 'UNEXPECTED OVERWRITE\n'
else
  printf 'OK: existing output kept\n'
fi

The invalid key reports record 1: key must be an integer on stderr, returns 1, and does not create rejected.csv. The repeated sort reports output already exists and leaves the existing sorted.csv unchanged. Success returns 0; CLI argument errors return 2.

An automated job should consume output only after exit status 0. Validation covered negative and signed integers, leading zeros, 64-bit boundaries, ties, Chinese text, BOM, CRLF, multiline fields, empty input, header-only input, duplicate headers, missing columns, incorrect widths, invalid UTF-8, broken quoting, existing output, and workspace cleanup. A separate run checked complete ordering and counts for 100,000 reverse-ordered records.

6. Disk usage and troubleshooting

A disk database does not impose a fixed total memory limit. temp_store=FILE requests file storage for SQLite temporary data, but SQLite still caches data. cache_size=-8192 suggests a page-cache size; it is not an 8 MiB process memory cap. The SQLite temporary-file documentation describes additional sorting storage, and the PRAGMA reference explains compile-time influences. Maximum record size, parser allocations, and sorter overhead also affect memory.

--work-dir selects the main database workspace only; it does not necessarily relocate every SQLite internal temporary file. Budget for the input, database, sorting scratch space, and new output together: no fixed size multiplier fits every CSV. Use the Python disk-space preflight tutorial before a large run. If you need duplicate-ID detection rather than sorting, follow the CSV duplicate-ID tutorial.

Summary

Parse CSV records incrementally, let SQLite handle global sorting, and use an integer key plus an input sequence number for stable results. Validate fields and failure exits with a small fixture before processing real files; reserve scratch space and keep the original input. This reduces the need to retain all records in Python, without promising constant memory or disk usage.