Explain why CSV null round-trips can lose meaning
Also published in our Blogger archive.
Quick answer
The csv writer writes None as an empty string, which makes the value easier to serialize but not reversible.
Example
The example writes [None, ""], reads the row back, and asserts that both cells are "". The output demonstrates the lost distinction without relying on a database or external file.
import csv, io
buf = io.StringIO()
csv.writer(buf).writerow([None, ''])
row = next(csv.reader(io.StringIO(buf.getvalue())))
assert row == ['', '']
print(repr(row))Expected stdout:
['', '']Reading the result
A null sentinel can preserve meaning only if its collision behavior is defined. Alternatives include a separate presence column or a format that has a native null representation.
Choose a sentinel only after testing it against legitimate values such as an empty string, NULL, or None. A reversible interchange format needs a documented escape rule for any collision.
The assertion is intentionally about the reader result, where the ambiguity becomes operational. The writer output alone would not prove how a later CSV consumer observes it.
Sources
- Python csv module documentation
Prepared with AI assistance. The example uses synthetic data; its stated limits apply.