Dev SQLite DBMS

SQLite DataBase Management (SQLiteDBMS.py)

In this document ToDo Database will be referred to as TDDB

This site has almost all information on usage of SQLite in Python.

Download the SQLite DataBase browser to browse the todo database file. Database files are named (*.db)

IMPORTANT:

1. Every time there is a change to the values assigned to a todo, the database structure will change. So we will be using the first column of the database to check for version number of the database and open it only if it is the latest version and we must also change the TDDB default template ToDo.db to reflect our code.

Code Explanation

Importing the necessary modules:

import sqlite3
from pathlib import Path
import os

These are constants, with sqldba later being initialized as an object of class sqldb

CurrentDir = Path(os.path.dirname(__file__))
sqldba = None

SQLDB class:

This class has an sqlite DB connections variable and a variable to hold its cursor which helps us to read data. It has three functions:

1. InitiateConnection: starts the database
2. Commit: writes changes to the database
3. CloseConnection: delete the object after it has been used

sqlite3.connect takes path to database as argument

class sqldb:
    '''This is the main SQLite DBMS class'''
    __tododb = None
    dbCursor = None
    def InitiateConnection(self):
        self.__tododb = sqlite3.connect(os.path.join(CurrentDir,'Temp.db'))
        print("~Connected to the ToDo database successfully")
        self.dbCursor = self.__tododb.cursor()
    def Commit(self):
        self.__tododb.commit()
    def CloseConnection(self):
        self.__tododb.close()
        __tododb = None
        dbCursor = None

Database is read and put into python readable format:

the class object is initiated along with a connection and if a table does not exist, it creates it in the database (this happens when a wrong .db file is imported, so that the rest of the code works to find out that it has been imported wrongly and displays an error)

def InitToDoDB():
    global sqldba
    sqldba = sqldb()
    sqldba.InitiateConnection()
    sqldba.dbCursor.execute('''CREATE TABLE if not exists TODO (ID INT PRIMARY KEY NOT NULL, TASK TEXT NOT NULL, DONE INT NOT NULL, LISTNO INT NOT NULL);''')
    print("Todo table creation attempted, If not exists")
    sqldba.Commit()

Execute DB commands:

This functions is used to directly run string commands in the SQL query language using the dbcursor object and if the data is returned, it can be returned or else None is returned.

def ExecuteCommandOnToDoDB(command):
    sqldba.dbCursor.execute(command)
    sqldba.Commit()
    return sqldba.dbCursor.fetchall()

Close DB:

This function impletes the close connection of the DB Object.

def CloseToDoDB():
    print("~ToDoDB in sqlite3 has been closed")
    sqldba.CloseConnection()

ToDo Database Template:

ID	TASK	DONE	LISTNO
0	ToDoDB	31	-3
1	HOME	2	0
2	Clean Mess	0	0
3	SHOPPING	2	1
4	milk	1	1
5	carrot	0	1
6	ToDoDB	30	-3

This is the template todo database:

1. here the first and last line contain ToDoDB with list no. representing - version number
2. To signify groups, we used DONE value as 2
3. For tasks done or not we use DONE as 1 or 0 respectively and list numbers represent where each list or task belongs
4. The negative value of version number is so that it does not clash with group number while parsing, as the parser we created handles strings at a very basic level so as to minimize computational intensity.
5. as groups and tasks use done as 0,1 and 2, we decided to use 3 for ToDoDB and 1 and 0 as a suffix to signify start and end