Technology · PRACTICAL GUIDE
Python CSV loses None versus empty strings? Check the runtime and wire contract
A quoting constant can exist before its reader behavior works. Twenty local controls distinguish recoverable parsing trouble, irreversible export loss and a narrow text-or-null fallback.
On CPython3.12.14, writing [None, ""] with csv.QUOTE_NOTNULL produced an unquoted empty field followed by a quoted empty field. Reading that output with the same option returned two empty strings. The constant existed, but the deployed reader did not preserve the distinction.
Start with the original bytes and the producer’s declared null rules. If an earlier export already gave missing and intentionally empty values the same representation, changing reader options cannot reconstruct which was which. Preserve the file and re-export from the original data when that distinction matters.
1. Find the boundary where information disappears
Keep the source file unchanged. Inspect a small non-sensitive fixture with repr(), so commas, quotes and record endings are visible. A default csv.writer roundtrip of [None, ""] returned ["", ""] in our test; that is export loss. A NOTNULL writer retained two different wire fields, but our 3.12 reader then collapsed them; that is a parser compatibility boundary.
Do not replace every parsed empty string with None as a repair. In the tested two-field fixture, that turned both fields into None, erasing the intentional empty value. An unescaped sentinel also collides: encoding None as <NULL> made a real text value <NULL> indistinguishable from it.
2. Probe writer and reader, including one-column rows
Print sys.version, then write and read a fixture containing None, an empty string and "001" using the exact options deployed in production. Python’s 3.12 reference explicitly warns that QUOTE_NOTNULL and QUOTE_STRINGS do not affect reader behavior; the relevant reader change is documented for 3.13. We executed 3.12.14 only.
Also test a row containing only None. Our QUOTE_NOTNULL writer raised csv.Error: single empty field record must be quoted. A row containing one empty string read back as [""], while a blank record read as []. Quoting the null just to silence the error would encode an empty text value under a quote-sensitive null contract.
3. Choose an agreed format before transforming data
For a compatible modern reader, the documented QUOTE_NOTNULL contract maps an unquoted empty field to None and keeps other fields as strings. It does not restore integers or arbitrary Python objects. QUOTE_STRINGS has numeric conversion rules of its own; it is not a general object serializer. Verify all supported row shapes on both actual endpoints before adopting either.
PostgreSQL COPY CSV illustrates why the receiving contract matters: by default, unquoted empty fields represent NULL and quoted empty fields represent empty text; FORCE_NULL and FORCE_NOT_NULL can alter interpretation. That is PostgreSQL’s documented behavior, not a universal CSV guarantee. We did not execute a database or Python3.13 integration test.
4. A narrow fallback when you control both endpoints
For a legacy exchange that permits only str or None, our tested application layer encodes None as the literal field N and every string as S followed by its complete original content. Thus empty text becomes S, text N becomes SN, and a real <NULL> string becomes S<NULL>. Pass these fields through ordinary csv.writer; commas, quotes and embedded newlines remain the CSV layer’s job.
Decode only the exact field N as None. For a field starting with S, remove exactly its first character; reject every other field as a malformed tag. Reject non-string, non-None inputs before writing. This is a new, explicitly agreed application format, so never apply it blindly to existing ordinary CSV or assume COPY, pandas or a spreadsheet understands the tags.
5. Define completion as values, types and row shape
Our 20 asserted controls covered the two loss boundaries, the single-null writer exception, blank versus one-empty-field records, empty-to-None and sentinel collisions, and a 13-value tagged matrix. That matrix included None, empty text, "001", "0", literal null/tag-looking strings, a comma, a quote, an embedded newline and Unicode. Both values and types matched after the tagged roundtrip.
Further controls verified single-null, single-empty and two-null rows, rejection of integers and booleans, and rejection of malformed or empty raw tags. This demonstrates the stated text-or-null contract on CPython3.12.14/Linux. It does not certify other runtimes or downstream software. Keep a representative fixture at every boundary, and re-export rather than guess when the original distinction is already gone.
