[](https://github.com/Sajjal/SQLite_As_JSON/stargazers)
[](https://github.com/Sajjal/SQLite_As_JSON/issues)
# SQLite As JSON
A Python helper package to do SQLite CRUD operation via JSON object. This package is developed using Python 3 with no external dependencies.
---
## Background (_Why this package was developed?_)
I'm working on another Python project that requires me to store a very minimal amount of data so I decided to use SQLite as a database. I feel that it is very easy to make typos and errors while creating multiple tables and doing some CRUD operations. Therefore, I created a separate helper Class that takes in a JSON object and parses it to create tables and do CRUD operations according to the instruction defined in that JSON object. It significantly helped to minimize errors. I thought it could be useful to others too and here it is.
## Installation:
### Download Package:
- `pip install SQLiteAsJSON`
### Setup
- Create table schema on **db_config.json** file as:
```JSON
[{
"table_name": "my_table",
"fields": [
{ "name": "id", "type": "char", "length": "50", "null": 0 },
{ "name": "timestamp", "type": "char", "length": "20", "null": 0 },
{ "name": "email", "type": "char", "length": "50", "null": 0 },
{ "name": "password", "type": "char", "length": "50", "null": 0 },
{ "name": "personID", "type": "char", "length": "50", "null": 0 }
],
"config": {
"primary_key": "id",
"foreign_key": {
"field": "personID",
"reference_table": "persons",
"reference_table_field": "id" }
}
},
{
"table_name": "persons",
"fields": [
{ "name": "id", "type": "char", "length": "50", "null": 0 },
{ "name": "timestamp", "type": "char", "length": "20", "null": 0 },
{ "name": "first_name", "type": "char", "length": "20", "null": 0 },
{ "name": "last_name", "type": "char", "length": "20", "null": 1 },
{ "name": "address", "type": "char", "length": "100", "null": 1 }
],
"config": {
"primary_key": "id"
}
}]
```
- You can add more than one table
- You must have `id` and `timestamp` fields in each table, these will be auto-populated
- Each table should have at most one `primary_key`, you may have one optional `foreign_key` per table
- If you want the field to be `NOT NULL` pass `"null" : 0` else pass `"null" : 1`
---
#### Initialize:
- Instantiate Class object as: `db = ManageDB(<database name>, <path to db_config.json>)`
- Example:
```python
from SQLiteAsJSON import ManageDB
db = ManageDB('my_databse.db', 'db_config.json')
```
- The default `check_same_thread` option for `SQLite` is `True`. You can set it `False` as:
```python
db = ManageDB('my_databse.db', 'db_config.json', False)
```
#### Create table
- Table can be created by calling `db.create_table()`
- Example:
```python
db.create_table()
```
- Returns: Success message(dict): If the table creation was successful else none
#### Insert data
- Pass **table name** and **data to insert** as: `db.insert_data(<table_name>, <data_to_insert>)`
- Example:
```python
db.insert_data('my_table', {"email": 'a@b.com', "password": 'password', "personID":'1'})
```
- Returns: Success message(dict): If the insertion was successful else none
- SQLite does not supports boolean data types, it is recommended to use 1 for True and 0 for False
- To insert **_Array (List)_** or **Object (dict)**, first stringify it using `json.dumps([List])` or `json.dumps({dict})`
#### Search data
- Pass **table name** w/ **optional search condition** as: `db.search_data(<table_name>, <optional_search_condition>)`
- Example:
```python
db.search_data('my_table')
db.search_data('my_table', {"id":"55bd5301b331439fae2ba8572942ded5"})
```
- Multiple search conditions can be passed as:
```python
db.search_data('my_table', {"email":"a@b.com", "personID":"1"})
```
- Multiple search conditions will be joined by `AND` operator by default. It can be changed to `OR` as:
```python
db.search_data('my_table', {"email":"a@b.com", "personID":"1"}, 'OR')
```
- Returns: Search results (dict): Search results as a JSON object
#### Update data
- Pass **table name**, **row id** and **data to update** as: `db.update_data(<table_name>, <row_id>, <data_to_update>)`
- Example:
```python
db.update_data('my_table', '55bd5301b331439fae2ba8572942ded5', {
"email:abc@example.com","password":"hello_world"
})
```
- Returns: Success message(dict): If the update was successful else none
#### Delete data
- Pass **table name** and **row id** as: `db.delete_data(<table_name>, <row_id>)`
- Example:
```python
db.delete_data('my_table', '55bd5301b331439fae2ba8572942ded5')
```
- Returns: Success message(dict): If the delete operation was successful else none
---
With Love,
**Sajjal**
Raw data
{
"_id": null,
"home_page": "https://github.com/Sajjal/SQLite_As_JSON",
"name": "SQLiteAsJSON",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "sqlite as json,sql json,sqlite orm,sqlite nosql,sqlite json,json sqlite",
"author": "Sajjal Neupane (https://mrsajjal.com)",
"author_email": "",
"download_url": "https://files.pythonhosted.org/packages/e3/1e/14fff21ddb34d899f65f4f9de52bdbbec5632be666df9bf3bf68fc85492a/SQLiteAsJSON-1.0.4.tar.gz",
"platform": "",
"description": "\n[](https://github.com/Sajjal/SQLite_As_JSON/stargazers)\n[](https://github.com/Sajjal/SQLite_As_JSON/issues)\n\n\n\n\n# SQLite As JSON\n\nA Python helper package to do SQLite CRUD operation via JSON object. This package is developed using Python 3 with no external dependencies.\n\n---\n\n## Background (_Why this package was developed?_)\n\nI'm working on another Python project that requires me to store a very minimal amount of data so I decided to use SQLite as a database. I feel that it is very easy to make typos and errors while creating multiple tables and doing some CRUD operations. Therefore, I created a separate helper Class that takes in a JSON object and parses it to create tables and do CRUD operations according to the instruction defined in that JSON object. It significantly helped to minimize errors. I thought it could be useful to others too and here it is.\n\n## Installation:\n\n### Download Package:\n\n- `pip install SQLiteAsJSON`\n\n### Setup\n\n- Create table schema on **db_config.json** file as:\n\n```JSON\n[{\n \"table_name\": \"my_table\",\n \"fields\": [\n { \"name\": \"id\", \"type\": \"char\", \"length\": \"50\", \"null\": 0 },\n { \"name\": \"timestamp\", \"type\": \"char\", \"length\": \"20\", \"null\": 0 },\n { \"name\": \"email\", \"type\": \"char\", \"length\": \"50\", \"null\": 0 },\n { \"name\": \"password\", \"type\": \"char\", \"length\": \"50\", \"null\": 0 },\n { \"name\": \"personID\", \"type\": \"char\", \"length\": \"50\", \"null\": 0 }\n ],\n \"config\": {\n \"primary_key\": \"id\",\n \"foreign_key\": {\n \"field\": \"personID\",\n \"reference_table\": \"persons\",\n \"reference_table_field\": \"id\" }\n }\n },\n\n {\n \"table_name\": \"persons\",\n \"fields\": [\n { \"name\": \"id\", \"type\": \"char\", \"length\": \"50\", \"null\": 0 },\n { \"name\": \"timestamp\", \"type\": \"char\", \"length\": \"20\", \"null\": 0 },\n { \"name\": \"first_name\", \"type\": \"char\", \"length\": \"20\", \"null\": 0 },\n { \"name\": \"last_name\", \"type\": \"char\", \"length\": \"20\", \"null\": 1 },\n { \"name\": \"address\", \"type\": \"char\", \"length\": \"100\", \"null\": 1 }\n ],\n \"config\": {\n \"primary_key\": \"id\"\n }\n }]\n```\n\n- You can add more than one table\n- You must have `id` and `timestamp` fields in each table, these will be auto-populated\n- Each table should have at most one `primary_key`, you may have one optional `foreign_key` per table\n- If you want the field to be `NOT NULL` pass `\"null\" : 0` else pass `\"null\" : 1`\n\n---\n\n#### Initialize:\n\n- Instantiate Class object as: `db = ManageDB(<database name>, <path to db_config.json>)`\n- Example:\n\n ```python\n from SQLiteAsJSON import ManageDB\n\n db = ManageDB('my_databse.db', 'db_config.json')\n ```\n\n- The default `check_same_thread` option for `SQLite` is `True`. You can set it `False` as:\n\n ```python\n db = ManageDB('my_databse.db', 'db_config.json', False)\n ```\n\n#### Create table\n\n- Table can be created by calling `db.create_table()`\n- Example:\n ```python\n db.create_table()\n ```\n- Returns: Success message(dict): If the table creation was successful else none\n\n#### Insert data\n\n- Pass **table name** and **data to insert** as: `db.insert_data(<table_name>, <data_to_insert>)`\n- Example:\n\n ```python\n db.insert_data('my_table', {\"email\": 'a@b.com', \"password\": 'password', \"personID\":'1'})\n ```\n\n- Returns: Success message(dict): If the insertion was successful else none\n- SQLite does not supports boolean data types, it is recommended to use 1 for True and 0 for False\n- To insert **_Array (List)_** or **Object (dict)**, first stringify it using `json.dumps([List])` or `json.dumps({dict})`\n\n#### Search data\n\n- Pass **table name** w/ **optional search condition** as: `db.search_data(<table_name>, <optional_search_condition>)`\n- Example:\n\n ```python\n db.search_data('my_table')\n db.search_data('my_table', {\"id\":\"55bd5301b331439fae2ba8572942ded5\"})\n ```\n\n- Multiple search conditions can be passed as:\n\n ```python\n db.search_data('my_table', {\"email\":\"a@b.com\", \"personID\":\"1\"})\n ```\n\n- Multiple search conditions will be joined by `AND` operator by default. It can be changed to `OR` as:\n\n ```python\n db.search_data('my_table', {\"email\":\"a@b.com\", \"personID\":\"1\"}, 'OR')\n ```\n\n- Returns: Search results (dict): Search results as a JSON object\n\n#### Update data\n\n- Pass **table name**, **row id** and **data to update** as: `db.update_data(<table_name>, <row_id>, <data_to_update>)`\n- Example:\n\n ```python\n db.update_data('my_table', '55bd5301b331439fae2ba8572942ded5', {\n \"email:abc@example.com\",\"password\":\"hello_world\"\n })\n ```\n\n- Returns: Success message(dict): If the update was successful else none\n\n#### Delete data\n\n- Pass **table name** and **row id** as: `db.delete_data(<table_name>, <row_id>)`\n- Example:\n\n ```python\n db.delete_data('my_table', '55bd5301b331439fae2ba8572942ded5')\n ```\n\n- Returns: Success message(dict): If the delete operation was successful else none\n\n---\n\nWith Love,\n\n**Sajjal**\n\n\n",
"bugtrack_url": null,
"license": "",
"summary": "Do SQLite CRUD operation via JSON object",
"version": "1.0.4",
"project_urls": {
"Homepage": "https://github.com/Sajjal/SQLite_As_JSON"
},
"split_keywords": [
"sqlite as json",
"sql json",
"sqlite orm",
"sqlite nosql",
"sqlite json",
"json sqlite"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "89113a5dcfaa0737b65fb9c101a966ce784c3d93bd00193a7f38421f98d6bd35",
"md5": "b60cafc96a2241a1ac4e35536bb9b4dd",
"sha256": "4a9d8e477c633d39fc3f92e57d97af567fd5fe31680a5fd4ca801a5a76b18abe"
},
"downloads": -1,
"filename": "SQLiteAsJSON-1.0.4-py3.8.egg",
"has_sig": false,
"md5_digest": "b60cafc96a2241a1ac4e35536bb9b4dd",
"packagetype": "bdist_egg",
"python_version": "1.0.4",
"requires_python": null,
"size": 10225,
"upload_time": "2021-10-26T16:29:03",
"upload_time_iso_8601": "2021-10-26T16:29:03.621710Z",
"url": "https://files.pythonhosted.org/packages/89/11/3a5dcfaa0737b65fb9c101a966ce784c3d93bd00193a7f38421f98d6bd35/SQLiteAsJSON-1.0.4-py3.8.egg",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "a40abec6a038ee6ee31eaffe33c553c85af11c59c2f1791b6a361b62846a07a3",
"md5": "e37a4a3511e73722298b3665813a1de4",
"sha256": "83d4f8e3c9ebaf144e90b5c2f5e21fbb52ed34efbfc5194063255a0f8365aa7f"
},
"downloads": -1,
"filename": "SQLiteAsJSON-1.0.4-py3-none-any.whl",
"has_sig": false,
"md5_digest": "e37a4a3511e73722298b3665813a1de4",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 6917,
"upload_time": "2021-10-26T16:29:02",
"upload_time_iso_8601": "2021-10-26T16:29:02.495005Z",
"url": "https://files.pythonhosted.org/packages/a4/0a/bec6a038ee6ee31eaffe33c553c85af11c59c2f1791b6a361b62846a07a3/SQLiteAsJSON-1.0.4-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "e31e14fff21ddb34d899f65f4f9de52bdbbec5632be666df9bf3bf68fc85492a",
"md5": "dbadf23cc7b39a30e81e0c964688ba5c",
"sha256": "7f2885d16ca3ec630e4d39d945e6227a5bbbbd00651e3ce4b575c994b293f2b8"
},
"downloads": -1,
"filename": "SQLiteAsJSON-1.0.4.tar.gz",
"has_sig": false,
"md5_digest": "dbadf23cc7b39a30e81e0c964688ba5c",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 7364,
"upload_time": "2021-10-26T16:29:05",
"upload_time_iso_8601": "2021-10-26T16:29:05.086889Z",
"url": "https://files.pythonhosted.org/packages/e3/1e/14fff21ddb34d899f65f4f9de52bdbbec5632be666df9bf3bf68fc85492a/SQLiteAsJSON-1.0.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2021-10-26 16:29:05",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "Sajjal",
"github_project": "SQLite_As_JSON",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "sqliteasjson"
}
JSON
