database-old.py

import sqlite3
import os
import sys
import json
current = os.path.dirname(os.path.realpath(__file__))
parent = os.path.dirname(current)
sys.path.append(parent)


class Database:
    def __init__(self, db):
        # creating database connection
        self.con = sqlite3.connect(db)
        self.cur = self.con.cursor()

        # SQL queries to create tables
        sql = """
        CREATE TABLE IF NOT EXISTS commands (
            id Integer PRIMARY KEY,
            url text,
            datewanted text,
            timewanted text,
            hoursba Integer,
            seats Integer,
            reservation text,
            rundate text,
            runtime text,
            runnow text,
            account text,
            nonstop text,
            duration Integer,
            proxy text,
            retry Integer,
            minidle Integer,
            maxidle Integer,
            checkonly text
        )
        """
        # cursor executions
        self.cur.execute(sql)
        sql = """
        CREATE TABLE IF NOT EXISTS checks (
            id Integer PRIMARY KEY,
            url text,
            startdate text,
            enddate text,
            seats Integer,
            nonstop text,
            proxy text
        )
        """
        self.cur.execute(sql)
        self.con.commit()

    def reservationValues(self):
        file = open("reservationlist.json", "r")
        listvalue = json.load(file)
        tmplist = [value for value in listvalue]
        tmplist.append("<Not Set>")
        setlist = set(tmplist)
        return sorted(list(setlist), key=str.casefold)

    def proxyValues(self):
        file = open("proxylist.json", "r")
        listvalue = json.load(file)
        tmplist = [value['profilename'] for value in listvalue]
        tmplist.append("<Not Set>")
        setlist = set(tmplist)
        return sorted(list(setlist), key=str.casefold)
    
    def profileValues(self):
        file = open("profilelist.json", "r")
        self.profilelist = json.load(file)
        return [value['email'] for value in self.profilelist]

    # Add Instructor record to the table
    def insertCommand(self, url, datewanted, timewanted, hoursba, seats, reservation, rundate, runtime, runnow, account, nonstop, duration, proxy, retry, minidle, maxidle, checkonly):
        self.cur.execute("INSERT INTO commands VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                         (url, datewanted, timewanted, hoursba, seats, reservation, rundate, runtime, runnow, account, nonstop, duration, proxy, retry, minidle, maxidle, checkonly))
        self.con.commit()


    # Display Instructor List from table
    def viewCommand(self):
        self.cur.execute("SELECT * FROM commands order by id desc")
        rows = self.cur.fetchall()
        dlist = []
        for row in rows:
            rowlist = list(row)
            rowlist.append(rowlist[1]) 
            rowlist[1] = rowlist[1].split("/")[-1]
            dlist.append(tuple(rowlist))
        return dlist

    # Delete Instructor Entry from table
    def removeCommand(self, comid):
        self.cur.execute("DELETE FROM commands WHERE id=?", (comid,))
        self.con.commit()

    # Edit Instructor Details in the table
    def updateCommand(self, comid, url, datewanted, timewanted, hoursba, seats, reservation, rundate, runtime, runnow, account, nonstop, duration, proxy, retry, minidle, maxidle, checkonly):
        sql_insert_query = """UPDATE commands SET url=?, datewanted=?, timewanted=?, hoursba=?, seats=?, reservation=?, rundate=?, runtime=?, runnow=?, account=?, nonstop=?, duration=?, proxy=?, retry=?, minidle=?, maxidle=?, checkonly=? WHERE id=?"""
        self.cur.execute(sql_insert_query, (url, datewanted, timewanted, hoursba, seats, reservation, rundate, runtime, runnow, account, nonstop, duration, proxy, retry, minidle, maxidle, checkonly, comid))
        self.con.commit()


    def insertCheck(self, url, startdate, enddate, seats, nonstop, proxy):
        self.cur.execute("INSERT INTO checks VALUES (NULL,?,?,?,?,?,?)",
                         (url, startdate, enddate, seats, nonstop, proxy))
        self.con.commit()

    # Display Instructor List from table
    def viewCheck(self):
        self.cur.execute("SELECT * FROM checks order by id desc")
        rows = self.cur.fetchall()
        dlist = []
        for row in rows:
            rowlist = list(row)
            rowlist.append(rowlist[1]) 
            rowlist[1] = rowlist[1].split("/")[-1]
            dlist.append(tuple(rowlist))
        return dlist

    # Delete Instructor Entry from table
    def removeCheck(self, comid):
        self.cur.execute("DELETE FROM checks WHERE id=?", (comid,))
        self.con.commit()

    # Edit Instructor Details in the table
    def updateCheck(self, comid, url, startdate, enddate, seats, nonstop, proxy):
        sql_insert_query = """UPDATE checks SET url=?, startdate=?, enddate=?, seats=?, nonstop=?, proxy=? WHERE id=?"""
        self.cur.execute(sql_insert_query, (url, startdate, enddate, seats, nonstop, proxy, comid))
        self.con.commit()