pysqlitecipher 0.11

Ligth weigth and easy to use sqlite wrapper with built in encryption system.

pysqlitecipher

Ligth weigth and easy to use sqlite wrapper with built in encryption system.

Features

1. Create table with the help of scheme in the form of list.
2. Easily insert data elements in table by just passing a list of values.
3. Built in encryption system , just set password and pass make table secure = True while creating table then all the data insert , updated , deleted , retreived will be encrypted or decrypted automatically on the fly with the help if cryptography modules fernet tech.

How to use

Intialise object

from pysqlitecipher import sqlitewrapper

# make the object
obj = sqlitewrapper.SqliteCipher(dataBasePath="pysqlitecipher.db" , checkSameThread=False , password=None)

Parameters -

1. dataBasePath - it the absolute path to the data base i.e path + database name itself

2. checkSameThread - value used to initialse the sql connector , make it True if you are working on multi threaded application and don't want diff thread to mess with your data base.

3. password - Here you need to pass the password you want to use which will be used to encrypt the data base.

• Note password is necessary even if you don't want encryption in your data base.

• Make sure you use a password with at least 12 digit with a mix of numbers , chars and special chars for better security.

Process that occur automatically after object creation

1. Sql connection obj is intialised

2. A table - tableNames is created with following schema

   colName________Data Type

tableName______TEXT
secured________INT

• This table is to store and maintain records of which table as been created in data base because when using encryption on table name "Hello" it will yield diff strings so record is needed to be maintained.

• secured is 1 if table was created with make secure parameter=True else 0

• As tableNames table is created so its value is inserted into itself with secure parameter = 0

3. A table - authenticationTable is created with the following schema

   colName_________Data Type

SHA512_pass_____TEXT
encryptedKey____TEXT

• SHA512_pass is used to store the value obtained after applying SHA512 algo to your password for authenticating in the future.

• encryptedKey is used to store the random key generated by first time init of the data base by Fernet in cryptography module.

• Key generated is then encrypted using onetimepad with the SHA256 value of your password and is stored in encryptedKey.

• This Key is then retreived from data base , decrypted and used to init Fernet module which handles all the encryption on next connection.

• As authenticationTable table is created so its value is inserted into tableNames with secure parameter = 0

Note - So you cannot create table with names = authenticationTable , tableNames

Methods

1. Create Table

obj.createTable(tableName , colList , makeSecure=False , commit=True)

Call this method if you want to create a new table in data base =

Parameters -

1. tableName - name of the table you want to create

2. colList - schema in the list form

colList = [
            [colname , datatype] , 
            [colname2 , datatype] , 
          ]       

• Where colName is the name of the column

• Datatype is the type of data you want to store in column

• datatype can be -

TEXT - for text / string data type
REAL - for float numbers
INT - for simple integer numbers
JSON - for JSON Strings
LIST - for python list type
BLOB - for binary data

# Example = 
colList  = [
            ["rollno" , "INT"],
            ["name" , "TEXT"],
           ]

3. makeSecure - Bool value , True if you want to encrypt data stored in this table , False if you don't. Cannot be changed ones the table is created.

4. commit - if you want to commit the changes to the data base.

2. Insert Data into table

obj.insertIntoTable(tableName , insertList , commit = True)

Call this method if you want to insert some data into already created table.

Parameters -

1. tableName - name of the table in which you want to insert the data

2. insertList - list of the data you want to insert

consider this table test

colName     DataType
rollno      INT
name        TEXT

And You want to insert 1 in rollno and john in name , then the insert list will be - 

insertList = [1 , "john"]

3. commit - if you want to commit the changes

3. Get Data from table

obj.getDataFromTable(tableName , raiseConversionError = True , omitID = False)

Call this method if you want to get data from a table

Parameters -

1. tableName - name of the table from which you want to retrive data

2. raiseConversionError - bool Value

• as after encryption every data is converted to text form (Expect binary), so on the time of getting data from table it needs to be converted back to its original form. sometimes module may encounter error such as in case string in int data type col. so if the raiseConversionError is True then a error will be raised upon encountering such error else the TEXT from of data is returned

3. omitID - bool value

• when ever you insert some data a ID col is automatically maintained which is to update and delete values. but if you don't want that ID col to be returned while getting data from table, make omitID = True

4. Delete Data in Table

obj.deleteDataInTable(tableName , iDValue , commit = True , raiseError = True , updateId = True)

Call this method if you want to delete some row from a table

Parameters -

1. tableName - name of the table from which you want to delete a row

2. iDValue - ID value of the row which you want to delete , it is automatically maintained col and you can find ID of a row by calling method below

obj.getDataFromTable(tableName , raiseConversionError = True , omitID = False)

3. commit - if you want to commit the changes.

4. raiseError - if True , then if ID is not found, error will be raised.

5. updateId - if True , then if you delete a row from btw lets say 50 from row of 1 to 100 , then ID will be rearranged to fill the gap i.e now row will be 1 to 99 intead of 1 to 100 and 50 ID number missing. But note though this process takes some time. So if you are deleting many values it is good to make this False and instead call updateId function at the end

obj.updateIDs(tableName , commit = True)

5. Update Data in Table

obj.updateInTable(tableName , iDValue , colName , colValue , commit = True , raiseError = True)

Call this method if you want to update a value in row

Parameters -

1. tableName - name of the table from which you want to delete a row

2. iDValue - ID value of the row which you want to update data in , it is automatically maintained col and you can find ID of a row by calling method below

obj.getDataFromTable(tableName , raiseConversionError = True , omitID = False)

3. colName - name of the column you want to change value in row

4. colValue - new value to be inserted into colName col of row with ID = iDValue

5. commit - if you want to commit the changes.

6. raiseError - if True , then if ID is not found, error will be raised.

6. Change Password

obj.changePassword(newPass)

Pass the new password here and the password will be changed