database.py

from PyQt5.QtSql import QSqlDatabase, QSqlQuery

class Database():
    def __init__(self, db_name):
        self.db = QSqlDatabase.addDatabase('QSQLITE')
        self.db.setDatabaseName(db_name) 
        self.db.open()

    def create_table(self):
        query = QSqlQuery()

        query.exec('''CREATE TABLE IF NOT EXISTS users(
                   id INTEGER PRIMARY KEY,
                   username TEXT NOT NULL,
                   email TEXT NOT NULL UNIQUE,
                   password TEXT NOT NULL)''')
          
        query.exec('''CREATE TABLE expenses(
                    id INTEGER PRIMARY KEY,
                    user_id INTEGER,
                    date TEXT,
                    amount REAL,
                    category TEXT,
                    description TEXT
                   )
                ''')  
                     

    def register_user(self, username,email,password):
        query = QSqlQuery()

        try:

            query.prepare('INSERT INTO users(username,email,password) VALUES(?,?,?)')

        except Exception as e:
            print(f'Error: {e}')

    

        query.addBindValue(username)
        query.addBindValue(email)
        query.addBindValue(password)

        return query.exec()
    
    def login_user(self, username, password):
        query = QSqlQuery()
        query.prepare('SELECT * FROM users WHERE username=? AND password=?')

        query.addBindValue(username)
        query.addBindValue(password)
        query.exec()
        query.next()

        #if query.next():
        return query.value(0) if query.isValid() else None
        # else: #if query.isValid() else None
        #     return None
    
    def get_userId(self, username):
        query = QSqlQuery()
        query.exec('SELECT id FROM users WHERE username=?')

        query.addBindValue(username)
        query.exec()
        query.next()

        return query.value(0)
    
    def add_expense(self, user_id, date, amount, category, description):
        query = QSqlQuery(self.db)
        query.prepare('INSERT INTO expenses  (user_id, date, amount, category, description) VALUES (?,?,?,?,?)')

        query.addBindValue(user_id)
        query.addBindValue(date)
        query.addBindValue(amount)
        query.addBindValue(category)
        query.addBindValue(description)
        #return query.exec()
        # query.exec_('PRAGMA table_info(expenses)')
        # while query.next():
        #     print(query.value(1), query.value(2))

        if not query.exec_():
            print(f'An Error Ocurred: {query.lastError().text()}')
            return False
        return True
    
        
    
    def get_expenses(self, user_id):
        query = QSqlQuery()
        query.prepare('SELECT * FROM expenses  WHERE user_id=?')

        query.addBindValue(user_id)
        query.exec()

        expenses = []
        #row_id = 1

        while query.next():
            expense = {
                'id': query.value(0),
                'user_id': query.value(1),
                'date': query.value(2),
                'amount': query.value(3),
                'category': query.value(4),
                'description': query.value(5)
            }
            expenses.append(expense)
            #expenses.append([row_id, query.value(0), query.value(1), query.value(2), query.value(3), query.value(4)])
           # row_id += 1
        return expenses
    
    def delete_expense(self, id):
        query = QSqlQuery()
        query.prepare('DELETE FROM expenses WHERE id=?')

        query.addBindValue(id)
        if not query.exec():
            return Exception(query.lastError().text())
        
    def recent_expense(self):
        query = QSqlQuery()
        query.exec('SELECT * FROM expenses ORDER BY date DESC LIMIT 1')

        if query.next():
            recent_expense = {
                'date': query.value(0),
                'amount':query.value(1),
            }
            print(recent_expense)
        else:
            print('NO ExPENSE found')

    def check_email(self, email):
        query = QSqlQuery()

        query.prepare('SELECT * FROM users WHERE email = ?')
        query.addBindValue(email)
        if not query.exec():
            return Exception(query.lastError().text())

        # if query.next():
        #     raise InterruptedError
        # else:
        #     pass

    # def get_username(self, user_id):
    #     query = QSqlQuery(self.db)
    #     query.prepare('SELECT username FROM users WHERE id =?')
    #     query.addBindValue(user_id)
    #     query.exec()
    #     query.next()
    #     return query.value(0)
    
    # def get_userProfile(self, user_id):
    #     query = QSqlQuery(self.db)
    #     query.prepare('SELECT username, email, password FROM users WHERE id=?')
    #     query.addBindValue(user_id)
    #     query.exec()
    #     query.next()
    #     return query.value(0), query.value(1), query.value(2)