Skip to main content

Do SQLite CRUD operation via JSON object

Project description

GitHub stars GitHub issues GitHub language count GitHub top language GitHub repo size

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 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:

    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:

    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]) or json.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 to OR 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

SQLiteAsJSON-1.0.4.tar.gz (7.4 kB view hashes)

Uploaded Source

Built Distributions

SQLiteAsJSON-1.0.4-py3.8.egg (10.2 kB view hashes)

Uploaded Source

SQLiteAsJSON-1.0.4-py3-none-any.whl (6.9 kB view hashes)

Uploaded Python 3

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page