Skip to main content

sqliteio

sqliteio is a library for reading and writing SQLite3 file with its own API.

I have tested it with recent CPython and MicroPython. Working with MicroPython is one of the key motivations.

It can be used with CPython. But since it is written in pure python, it is too slow. And has limited functionality. So there is no advantage to using it with CPython.

I started this project to learn about sqlite3 data structures. I hope this is helpful for those learning about sqlite3’s B+ tree structure.

Examples

Open & Close

Everything starts with open() and ends with close()

import sqliteio

database = sqliteio.open('/path/to/db_name.sqlite')

(... operations to database)

database.close()

open() as read only file.

f = open("/path/to/db_name.sqlite", "rb")
database = sqliteio.open(f)

You can also open() with a BytesIO instance or something byte stream generator.

with open("/path/to/db_name.sqlite", "rb") as f:
    bytesio = io.BytesIO(f.read())
    database = sqliteio.open(bytesio)

Fetch all records

Retrieve all data in a table.

for rowid_record in database.fetch_all("table_name"):
    (rowid, r) = rowid_record
    print(rowid)    # print rowid
    print(r)        # print record dict

Get by rowid

Retrieve a record using rowid.

Returns None if there is no record for the rowid.

rowid_record = database.get_by_rowid("table_name", 10)
assert rowid_record[0] == 10
print(r)        # print record dict

Get by Primary Key

Retrieve a record using Primary Key.

Returns None if there is no record for the primary key.

rowid, r = database.get_by_pk("table_name", 10)
assert rowid == 10
print(r)

Filter

Retrieve the target records using a table name and conditions.

cond = {
    "column1': 1,
    "column2": "str",
}
for rowid_record in database.filter("test_table", cond):
    (rowid, r) = rowid_record
    print(rowid)    # print rowid
    print(r)        # print record dict

Insert

Insert using dictionary list data.

r1 = {
    'pk_column': None,
    'column1': 1,
    'column2': 'string1',
}
r2 = {
    'pk_column': None,
    'column1': 2,
    'column2': 'string2',
}

database.insert("table_name", [r1, r2])

Delete

Deletion by rowid. In other words, sqliteio only has the ability to delete one row at a time.

database.delete_by_rowid("test_table", 1)

Update

Update by rowid. In other words, sqliteio only has the ability to update one row at a time.

update_data = {
    'column1': 10,
    'column2': 'new_data',
}
database.update_by_rowid("test_table", 1, update_data)

Commit & Rollback

With Insert, Delete and Update, only the data in memory can be changed and reflected in the file with commit(). To discard changes, use rollback().

database.commit()
database.rollback()

Reference for development

Reference for reading and writing the source code.

Documents on the web

Repository

Book

  • Alex Petrov, A Deep Dive into How Distributed Data Systems Work, O’Reilly Media, Inc. 2019 (chapter 3,4)

Metadata

Release files for sqliteio 0.3.1

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for sqliteio 0.3.1
File Size Uploaded Provenance Actions
sqliteio-0.3.1.tar.gz 20.8 kB Without provenance Details for sqliteio-0.3.1.tar.gz

Release files / sqliteio-0.3.1.tar.gz

Download URL sqliteio-0.3.1.tar.gz
Size 20.8 kB
Tags Source
SHA-256 checksum
How to use checksums
1b2f035fdd0e03b392a9e6d927be01a6bd0e53eba0823af8e7888edbc378df90
BLAKE2b-256 checksum
How to use checksums
0453c62f30060f0fc81cdb0eba09f1abfaff4d83b16e7f525c8d98a2ca05ac45
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/7.0.0 CPython/3.14.4

Security for sqliteio 0.3.1

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page