Socket
Socket
Sign inDemoInstall

SQLiteAsJSON

Package Overview
Dependencies
0
Maintainers
1
Alerts
File Explorer

Install Socket

Protect your apps from supply chain attacks

Install

SQLiteAsJSON

Do SQLite CRUD operation via JSON object

    1.0.4

Maintainers
1

Readme

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

Keywords

FAQs


Did you know?

Socket installs a GitHub app to automatically flag issues on every pull request and report the health of your dependencies. Find out what is inside your node modules and prevent malicious activity before you update the dependencies.

Install

Related posts

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap

Stay in touch

Get open source security insights delivered straight into your inbox.


  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc