Do SQLite CRUD operation via JSON object
Project description
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:
[{
"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
andtimestamp
fields in each table, these will be auto-populated - Each table should have at most one
primary_key
, you may have one optionalforeign_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:
from SQLiteAsJSON import ManageDB db = ManageDB('my_databse.db', 'db_config.json')
-
The default
check_same_thread
option forSQLite
isTrue
. You can set itFalse
as:db = ManageDB('my_databse.db', 'db_config.json', False)
Create table
- Table can be created by calling
db.create_table()
- Example:
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:
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])
orjson.dumps({dict})
Search data
-
Pass table name w/ optional search condition as:
db.search_data(<table_name>, <optional_search_condition>)
-
Example:
db.search_data('my_table') db.search_data('my_table', {"id":"55bd5301b331439fae2ba8572942ded5"})
-
Multiple search conditions can be passed as:
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 toOR
as: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:
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:
db.delete_data('my_table', '55bd5301b331439fae2ba8572942ded5')
-
Returns: Success message(dict): If the delete operation was successful else none
With Love,
Sajjal
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.
Source Distribution
Built Distributions
Hashes for SQLiteAsJSON-1.0.4-py3-none-any.whl
Algorithm | Hash digest | |
---|---|---|
SHA256 | 83d4f8e3c9ebaf144e90b5c2f5e21fbb52ed34efbfc5194063255a0f8365aa7f |
|
MD5 | e37a4a3511e73722298b3665813a1de4 |
|
BLAKE2b-256 | a40abec6a038ee6ee31eaffe33c553c85af11c59c2f1791b6a361b62846a07a3 |