# SQLite3 Database Construction Components
------------------------------------------
## TOC
1. [Abstract](#1-abstract)
2. [Get Started](#2-get-started)
2.1 [Install the Package](#21-install-the-package)
2.2 [Database Structure Definition Example](#22-database-structure-definition-example)
## 1. Abstract
--------------
The package provides Python abstractions for SQLite3 database structural components, like column, table and database.
With these abstractions you can conveniently define desired structure for an SQLite database including all necessary
constraints and data types. Yet you expected to possess some knowledge of `SQLite DB engine` in general and basic
familiarity with Python's `sqlite3` module API.
Components also provide tools to perform basic sanity checks and verification of underlying database physical storage
consistency against the defined database structure.
[[TOC]](#toc "Table Of Content")
## 2. Get Started
-----------------
### 2.1 Install the Package
-------------------------------------
You install the `sqlite-construct` package like any other regular Python package:
```terminal
pip install sqlite-construct
```
[[TOC]](#toc "Table Of Content")
### 2.2 Database Structure Definition Example
---------------------------------------------
```python
from datetime import datetime, timezone
import sqlite3
from sqlite_construct import (
SQLiteDBConnection,
SQLiteDBColumnDefinition,
SQLiteDBTableDefinition,
SQLiteDBDefinition,
SQLiteDBTriggerDefinition,
SQLiteDBTriggerProgStmtDefinition,
DBReference,
DB_SCHEME
)
# Define structure of the database
db_definition = SQLiteDBDefinition(
tables=[
SQLiteDBTableDefinition(
name="product",
columns=[
SQLiteDBColumnDefinition(name="id", type="INTEGER", constraint="PRIMARY KEY NOT NULL"),
SQLiteDBColumnDefinition(name="name", type="TEXT", constraint="NOT NULL"),
SQLiteDBColumnDefinition(name="sku_code", type="TEXT", constraint="NOT NULL UNIQUE"),
SQLiteDBColumnDefinition(name="timestamp", type="REAL", constraint="NOT NULL"),
],
),
SQLiteDBTableDefinition(
name="availability",
columns=[
SQLiteDBColumnDefinition(name="sku_code", type="TEXT", constraint="NOT NULL"),
SQLiteDBColumnDefinition(name="store_id", type="INTEGER", constraint="NOT NULL"),
SQLiteDBColumnDefinition(name="count", type="INTEGER", constraint="NOT NULL"),
SQLiteDBColumnDefinition(name="timestamp", type="REAL", constraint="NOT NULL"),
],
constraint=(
"PRIMARY KEY (sku_code,store_id),"
"FOREIGN KEY(sku_code) REFERENCES product (sku_code)"
" ON DELETE RESTRICT ON UPDATE RESTRICT"
)
)
],
triggers=[
# Prohibit changing fields of composite PK of the "availability" table even though it'd not violate
# uniqueness constraint for PK.
SQLiteDBTriggerDefinition(
name="before_update_availability",
timing="BEFORE",
action="UPDATE",
table_name="availability",
column_names=["sku_code", "store_id"],
prog_stmts=[
SQLiteDBTriggerProgStmtDefinition(body='SELECT RAISE(FAIL, "Attempt to update immutable field")')
],
)
]
)
# Create a DB reference object (optional)
db_ref=DBReference(scheme=DB_SCHEME.SQLITE3, path="app_db.sqlite3")
# Set up a connection to the database.
# NOTE. If the DB file does not exist the "sqlite3.connect()" auto-creates an empty DB file.
db_conn = sqlite3.connect(db_ref.dbname, factory=SQLiteDBConnection)
# Activate foreign keys support
db_conn.execute("PRAGMA foreign_keys = ON")
db_conn.commit()
db_conn.row_factory = sqlite3.Row
db_cursor = db_conn.cursor()
# Check the database
if db_definition.db_is_void(db_cursor=db_cursor):
# DB does not hold any schema objects and requires initialization.
db_definition.db_init(db_cursor=db_cursor)
else:
# Verify database structure
db_definition.db_verify(db_cursor=db_cursor)
# Do some operations on the database
# Prepare data
sql_template_insert = "INSERT INTO product (name,sku_code,timestamp) VALUES (:name,:sku_code,:timestamp)"
field_values1 = dict(name="product 1", sku_code="sku-code-product1", timestamp=datetime.now(timezone.utc).timestamp())
field_values2 = dict(name="product 2", sku_code="sku-code-product2", timestamp=datetime.now(timezone.utc).timestamp())
field_values3 = dict(name="product 3", sku_code="sku-code-product3", timestamp=datetime.now(timezone.utc).timestamp())
# Insert data into the database
db_cursor.executemany(sql_template_insert, (field_values1, field_values2, field_values3))
db_conn.commit()
# Fetch data from the database
db_cursor.execute("SELECT * FROM product")
for row in db_cursor.fetchall():
print(dict(**row))
# Close database
db_conn.close()
```
[[TOC]](#toc "Table Of Content")
Raw data
{
"_id": null,
"home_page": null,
"name": "sqlite-construct",
"maintainer": null,
"docs_url": null,
"requires_python": ">=3.11",
"maintainer_email": null,
"keywords": "sqlite, database",
"author": "ambient@mailbox.org",
"author_email": null,
"download_url": "https://files.pythonhosted.org/packages/f0/25/11a0fac7e5ee5cc639ec7393d6ac856e189e9b706cc1ff9ee927fbd69c48/sqlite_construct-0.1.0.tar.gz",
"platform": null,
"description": "# SQLite3 Database Construction Components\n\n------------------------------------------\n\n## TOC\n\n1. [Abstract](#1-abstract) \n2. [Get Started](#2-get-started) \n 2.1 [Install the Package](#21-install-the-package) \n 2.2 [Database Structure Definition Example](#22-database-structure-definition-example) \n\n\n## 1. Abstract\n\n--------------\n The package provides Python abstractions for SQLite3 database structural components, like column, table and database. \n With these abstractions you can conveniently define desired structure for an SQLite database including all necessary\nconstraints and data types. Yet you expected to possess some knowledge of `SQLite DB engine` in general and basic\nfamiliarity with Python's `sqlite3` module API.\n\n Components also provide tools to perform basic sanity checks and verification of underlying database physical storage\nconsistency against the defined database structure.\n\n[[TOC]](#toc \"Table Of Content\")\n\n\n## 2. Get Started\n\n-----------------\n\n\n### 2.1 Install the Package\n\n-------------------------------------\n You install the `sqlite-construct` package like any other regular Python package:\n\n```terminal\npip install sqlite-construct\n```\n\n[[TOC]](#toc \"Table Of Content\")\n\n\n### 2.2 Database Structure Definition Example\n\n---------------------------------------------\n\n```python\nfrom datetime import datetime, timezone\nimport sqlite3\n\nfrom sqlite_construct import (\n SQLiteDBConnection,\n SQLiteDBColumnDefinition,\n SQLiteDBTableDefinition,\n SQLiteDBDefinition,\n SQLiteDBTriggerDefinition,\n SQLiteDBTriggerProgStmtDefinition,\n DBReference,\n DB_SCHEME\n)\n\n\n# Define structure of the database\ndb_definition = SQLiteDBDefinition(\n tables=[\n SQLiteDBTableDefinition(\n name=\"product\",\n columns=[\n SQLiteDBColumnDefinition(name=\"id\", type=\"INTEGER\", constraint=\"PRIMARY KEY NOT NULL\"),\n SQLiteDBColumnDefinition(name=\"name\", type=\"TEXT\", constraint=\"NOT NULL\"),\n SQLiteDBColumnDefinition(name=\"sku_code\", type=\"TEXT\", constraint=\"NOT NULL UNIQUE\"),\n SQLiteDBColumnDefinition(name=\"timestamp\", type=\"REAL\", constraint=\"NOT NULL\"),\n ],\n ),\n SQLiteDBTableDefinition(\n name=\"availability\",\n columns=[\n SQLiteDBColumnDefinition(name=\"sku_code\", type=\"TEXT\", constraint=\"NOT NULL\"),\n SQLiteDBColumnDefinition(name=\"store_id\", type=\"INTEGER\", constraint=\"NOT NULL\"),\n SQLiteDBColumnDefinition(name=\"count\", type=\"INTEGER\", constraint=\"NOT NULL\"),\n SQLiteDBColumnDefinition(name=\"timestamp\", type=\"REAL\", constraint=\"NOT NULL\"),\n ],\n constraint=(\n \"PRIMARY KEY (sku_code,store_id),\"\n \"FOREIGN KEY(sku_code) REFERENCES product (sku_code)\"\n \" ON DELETE RESTRICT ON UPDATE RESTRICT\"\n )\n )\n ],\n triggers=[\n # Prohibit changing fields of composite PK of the \"availability\" table even though it'd not violate\n # uniqueness constraint for PK.\n SQLiteDBTriggerDefinition(\n name=\"before_update_availability\",\n timing=\"BEFORE\",\n action=\"UPDATE\",\n table_name=\"availability\",\n column_names=[\"sku_code\", \"store_id\"],\n prog_stmts=[\n SQLiteDBTriggerProgStmtDefinition(body='SELECT RAISE(FAIL, \"Attempt to update immutable field\")')\n ],\n )\n ]\n)\n\n\n# Create a DB reference object (optional)\ndb_ref=DBReference(scheme=DB_SCHEME.SQLITE3, path=\"app_db.sqlite3\")\n\n# Set up a connection to the database.\n# NOTE. If the DB file does not exist the \"sqlite3.connect()\" auto-creates an empty DB file.\ndb_conn = sqlite3.connect(db_ref.dbname, factory=SQLiteDBConnection)\n# Activate foreign keys support\ndb_conn.execute(\"PRAGMA foreign_keys = ON\")\ndb_conn.commit()\ndb_conn.row_factory = sqlite3.Row\ndb_cursor = db_conn.cursor()\n\n# Check the database\nif db_definition.db_is_void(db_cursor=db_cursor):\n # DB does not hold any schema objects and requires initialization.\n db_definition.db_init(db_cursor=db_cursor)\nelse:\n # Verify database structure\n db_definition.db_verify(db_cursor=db_cursor)\n\n# Do some operations on the database\n\n# Prepare data\nsql_template_insert = \"INSERT INTO product (name,sku_code,timestamp) VALUES (:name,:sku_code,:timestamp)\"\n\nfield_values1 = dict(name=\"product 1\", sku_code=\"sku-code-product1\", timestamp=datetime.now(timezone.utc).timestamp())\nfield_values2 = dict(name=\"product 2\", sku_code=\"sku-code-product2\", timestamp=datetime.now(timezone.utc).timestamp())\nfield_values3 = dict(name=\"product 3\", sku_code=\"sku-code-product3\", timestamp=datetime.now(timezone.utc).timestamp())\n\n# Insert data into the database\ndb_cursor.executemany(sql_template_insert, (field_values1, field_values2, field_values3))\ndb_conn.commit()\n\n# Fetch data from the database\ndb_cursor.execute(\"SELECT * FROM product\")\nfor row in db_cursor.fetchall():\n print(dict(**row))\n\n# Close database\ndb_conn.close()\n```\n\n[[TOC]](#toc \"Table Of Content\")\n\n",
"bugtrack_url": null,
"license": "0BSD",
"summary": "SQLite3 database construction components",
"version": "0.1.0",
"project_urls": {
"Repository": "https://github.com/ambient5/sqlite-construct"
},
"split_keywords": [
"sqlite",
" database"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "3c75ecaa8d65e170f1838aa2883b0f20e3c742a220334166b36eefc5db9cf77f",
"md5": "54e95bf6add13a479f0701ba06e4f61e",
"sha256": "36d5cc881127d0229801299c4a0b74f1b0250f0626d8cb3130e0f953a1a72c82"
},
"downloads": -1,
"filename": "sqlite_construct-0.1.0-py3-none-any.whl",
"has_sig": false,
"md5_digest": "54e95bf6add13a479f0701ba06e4f61e",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.11",
"size": 7718,
"upload_time": "2025-08-01T01:25:20",
"upload_time_iso_8601": "2025-08-01T01:25:20.057908Z",
"url": "https://files.pythonhosted.org/packages/3c/75/ecaa8d65e170f1838aa2883b0f20e3c742a220334166b36eefc5db9cf77f/sqlite_construct-0.1.0-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": null,
"digests": {
"blake2b_256": "f02511a0fac7e5ee5cc639ec7393d6ac856e189e9b706cc1ff9ee927fbd69c48",
"md5": "5c960289d8826f45ee5b2d5957a2397d",
"sha256": "8355ad6506415ed117351141e7264a1540cd5192a4c7caf42bb51f422445b341"
},
"downloads": -1,
"filename": "sqlite_construct-0.1.0.tar.gz",
"has_sig": false,
"md5_digest": "5c960289d8826f45ee5b2d5957a2397d",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.11",
"size": 6761,
"upload_time": "2025-08-01T01:25:21",
"upload_time_iso_8601": "2025-08-01T01:25:21.494669Z",
"url": "https://files.pythonhosted.org/packages/f0/25/11a0fac7e5ee5cc639ec7393d6ac856e189e9b706cc1ff9ee927fbd69c48/sqlite_construct-0.1.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-08-01 01:25:21",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "ambient5",
"github_project": "sqlite-construct",
"github_not_found": true,
"lcname": "sqlite-construct"
}
JSON
