Writing null/zero (\x00, ^@) bytes to an sqlite3 DB

sqlite3nulls.py

import sqlite3


def init_db():
    TEST_DB_FILE = 'testdb.sqlite'
    db = sqlite3.connect(TEST_DB_FILE, isolation_level="EXCLUSIVE")
    c = db.execute("BEGIN")
    c.execute("SELECT count(name) FROM sqlite_master WHERE type='table' AND name='snapshot'")
    if c.fetchone()[0] == 0:
      # Using TEXT colums is not a good idea because SQL expressions will have undefined result per sqlite3 documentation:
      # https://www.sqlite.org/c3ref/bind_blob.html
      # "If a non-negative fourth parameter is provided to sqlite3_bind_text() or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter must be the byte offset where the NUL terminator would occur assuming the string were NUL terminated. If any NUL characters occur at byte offsets less than the value of the fourth parameter then the resulting string value will contain embedded NULs. The result of expressions involving strings with embedded NULs is undefined."
        db.execute("CREATE TABLE snapshot (handle TEXT PRIMARY KEY, data BLOB)")
        db.commit()
    return db

def write_data(db, key, data):
    db.execute("REPLACE INTO snapshot VALUES (?, ?)", (key, sqlite3.Binary(data)))


def main():
    db = init_db()
    write_data(db, 'somekey', b'about-to-truncate\x00this-will-be-trucated')
    db.commit()
    db.close()


if __name__ == '__main__':
    main()
    """
After running this there will be correct data seen in the resulting file:

$ strings testdb.sqlite 
SQLite format 3
tablesnapshotsnapshot
CREATE TABLE snapshot (handle TEXT PRIMARY KEY, data BLOB)/
indexsqlite_autoindex_snapshot_1snapshot
Zsomekeyabout-to-truncate
this-will-be-trucated
	somekey

# less output will show this ("^@" corresponds to the null byte, see https://en.wikipedia.org/wiki/C0_and_C1_control_codes)
^Csomekeyabout-to-truncate^@this-will-be-trucated

However, sqlite3 client will truncate the data:

$ sqlite3 testdb.sqlite 'select * from snapshot;'
somekey|about-to-truncate
    """