=============
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 btree+ 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 by index
++++++++++++++++++++++++++++++
Retrieve the target record using the index name.
::
index_schema = database.get_index_schema_by_name("index_name")
index_key = {
"column1': 1,
"column2": "str",
}
for rowid_record in database.filter_by_index(index_schema, index_key):
(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
-------------
Reference for reading and writing the source code.
Documents on the web
++++++++++++++++++++++
- https://www.sqlite.org/fileformat2.html
- https://fly.io/blog/sqlite-internals-btree/
Repository
++++++++++++++++++++++
- https://github.com/alicebob/sqlittle
Book
++++++++++++++++++++++
- Alex Petrov, A Deep Dive into How Distributed Data Systems Work, O'Reilly Media, Inc. 2019 (chapter 3,4)
Raw data
{
"_id": null,
"home_page": "https://github.com/nakagami/sqliteio/",
"name": "sqliteio",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "SQLite3",
"author": "Hajime Nakagami",
"author_email": "nakagami@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/06/eb/fa9b1c49487ecdf4d94d3cc078a9885d6a1c516d0459061fc832b5047c06/sqliteio-0.2.0.tar.gz",
"platform": null,
"description": "=============\nsqliteio\n=============\n\nsqliteio is a library for reading and writing SQLite3 file with its own API.\n\nI have tested it with recent CPython and MicroPython.\nWorking with MicroPython is one of the key motivations.\n\nIt can be used with CPython.\nBut since it is written in pure python, it is too slow.\nAnd has limited functionality.\nSo there is no advantage to using it with CPython.\n\nI started this project to learn about sqlite3 data structures.\nI hope this is helpful for those learning about sqlite3's btree+ structure.\n\nExamples\n-------------\n\nOpen & Close\n++++++++++++++++++++++++++++++\n\nEverything starts with open() and ends with close()\n\n::\n\n import sqliteio\n \n database = sqliteio.open('/path/to/db_name.sqlite')\n \n (... operations to database)\n \n database.close()\n\nopen() as read only file.\n\n::\n\n f = open(\"/path/to/db_name.sqlite\", \"rb\")\n database = sqliteio.open(f)\n\nYou can also open() with a BytesIO instance or something byte stream generator.\n\n::\n\n with open(\"/path/to/db_name.sqlite\", \"rb\") as f:\n bytesio = io.BytesIO(f.read())\n database = sqliteio.open(bytesio)\n\nFetch all records\n++++++++++++++++++++++++++++++\n\nRetrieve all data in a table.\n\n::\n\n for rowid_record in database.fetch_all(\"table_name\"):\n (rowid, r) = rowid_record\n print(rowid) # print rowid\n print(r) # print record dict\n\nGet by rowid\n++++++++++++++++++++++++++++++\n\nRetrieve a record using rowid.\n\nReturns None if there is no record for the rowid.\n\n::\n\n rowid_record = database.get_by_rowid(\"table_name\", 10)\n assert rowid_record[0] == 10\n print(r) # print record dict\n\n\nGet by Primary Key\n++++++++++++++++++++++++++++++\n\nRetrieve a record using Primary Key.\n\nReturns None if there is no record for the primary key.\n\n::\n\n rowid, r = database.get_by_pk(\"table_name\", 10)\n assert rowid == 10\n print(r)\n\n\nFilter by index\n++++++++++++++++++++++++++++++\n\nRetrieve the target record using the index name.\n\n::\n\n index_schema = database.get_index_schema_by_name(\"index_name\")\n index_key = {\n \"column1': 1,\n \"column2\": \"str\",\n }\n for rowid_record in database.filter_by_index(index_schema, index_key):\n (rowid, r) = rowid_record\n print(rowid) # print rowid\n print(r) # print record dict\n\nInsert\n++++++++++++++++++++++++++++++\n\nInsert using dictionary list data.\n\n::\n\n r1 = {\n 'pk_column': None,\n 'column1': 1,\n 'column2': 'string1',\n }\n r2 = {\n 'pk_column': None,\n 'column1': 2,\n 'column2': 'string2',\n }\n \n database.insert(\"table_name\", [r1, r2])\n\nDelete\n++++++++++++++++++++++++++++++\n\nDeletion by rowid.\nIn other words, sqliteio only has the ability to delete one row at a time.\n\n::\n\n database.delete_by_rowid(\"test_table\", 1)\n\n\nUpdate\n++++++++++++++++++++++++++++++\n\nUpdate by rowid.\nIn other words, sqliteio only has the ability to update one row at a time.\n\n::\n\n update_data = {\n 'column1': 10,\n 'column2': 'new_data',\n }\n database.update_by_rowid(\"test_table\", 1, update_data)\n\n\nCommit & Rollback\n++++++++++++++++++++++++++++++\n\nWith Insert, Delete and Update, only the data in memory can be changed and reflected in the file with commit().\nTo discard changes, use rollback().\n\n::\n\n database.commit()\n\n::\n\n database.rollback()\n\n\nReference\n-------------\n\nReference for reading and writing the source code.\n\nDocuments on the web\n++++++++++++++++++++++\n\n- https://www.sqlite.org/fileformat2.html\n- https://fly.io/blog/sqlite-internals-btree/\n\nRepository\n++++++++++++++++++++++\n\n- https://github.com/alicebob/sqlittle\n\nBook\n++++++++++++++++++++++\n\n- Alex Petrov, A Deep Dive into How Distributed Data Systems Work, O'Reilly Media, Inc. 2019 (chapter 3,4)\n\n\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "SQLite3 I/O library",
"version": "0.2.0",
"split_keywords": [
"sqlite3"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "06ebfa9b1c49487ecdf4d94d3cc078a9885d6a1c516d0459061fc832b5047c06",
"md5": "72715b72a1fbb404f835117df75abca0",
"sha256": "0685f551a05b2f35616bce90c631ee21c58a89664d7c99720da852ed63cab52a"
},
"downloads": -1,
"filename": "sqliteio-0.2.0.tar.gz",
"has_sig": false,
"md5_digest": "72715b72a1fbb404f835117df75abca0",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 19016,
"upload_time": "2023-04-09T01:47:19",
"upload_time_iso_8601": "2023-04-09T01:47:19.247759Z",
"url": "https://files.pythonhosted.org/packages/06/eb/fa9b1c49487ecdf4d94d3cc078a9885d6a1c516d0459061fc832b5047c06/sqliteio-0.2.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2023-04-09 01:47:19",
"github": true,
"gitlab": false,
"bitbucket": false,
"github_user": "nakagami",
"github_project": "sqliteio",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqliteio"
}
JSON
