Python Forum

I made a class to manage a database using sqlite3, which seems to be working well, except when I restart the program the changes do not take place and I don't know what I'm doing wrong. I make the changes, commit, and then close the database. Here's the Database class:

import sqlite3 as sql


class Database:
    def __init__(self, tables, name):
        self.name = name+'.db'
        self.db = sql.connect(self.name)
        self.cursor = self.db.cursor()
        self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        db_tables = {table[0]: None for table in self.cursor.fetchall()}
        for table in db_tables:
            self.cursor.execute("SELECT * FROM %s" %table)
            db_tables[table] = [description[0] for description in self.cursor.description]
        for table, fields in tables.items():
            if table not in db_tables or len(tuple(set(fields).difference(set(db_tables[table]))) + tuple(set(db_tables[table]).difference(set(fields)))) > 0:
                self.delete_table(table, close=False)
                self.cursor.execute('''CREATE TABLE %s (%s)''' %(table, ", ".join(fields)))
        self.db.commit()
        self.db.close()

    def check_database_connection(self):
        try:
            self.cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
        except sql.ProgrammingError:
            self.db = sql.connect(self.name)
            self.cursor = self.db.cursor()

    def insert_column(self, table, values, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute(f"INSERT INTO {table}({','.join(list(values.keys()))}) VALUES({','.join(['?' for _ in range(len(values))])})", list(values.values()))
        self.db.commit()
        if close:
            self.db.close()

    def get_column(self, table, columns, filter='', limit='', limitOffset='', close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        if filter != '':
            filter = f" WHERE {filter[0]} ='{filter[1]}'"
        if limit != '':
            limit = f" LIMIT {str(limit)}"
            if limitOffset != '':
                limitOffset = f" OFFSET {str(limitOffset)}"
        self.cursor.execute(f"SELECT {', '.join(columns)} FROM {table}" + filter + limit + limitOffset)
        columns = self.cursor.fetchall()
        if close:
            self.db.close()
        return columns

    def delete_column(self, table, filter, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute(f"DELETE FROM {table} WHERE {filter[0]} = '{filter[1]}'")
        self.db.commit()
        if close:
            self.db.close()

    def delete_table(self, table, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute("DROP table IF EXISTS %s" %table)
        self.db.commit()
        if close:
            self.db.close()

    def update_column(self, table, columnInfo, close=True):
        self.check_database_connection()
        self.cursor = self.db.cursor()
        self.cursor.execute(f"UPDATE {table} SET {columnInfo[0]} = '{columnInfo[1]}' WHERE {columnInfo[2]} = '{columnInfo[3]}'")
        self.db.commit()
        if close:
            self.db.close()

Thank you in advance for any help.

Update: I believe the problem may lie in my long if statement failing, leading to all the tables being recreated. I'm going to look through it.

I've made the if statement the following: if table not in db_tables and it works now. For now, since I'm not going to be changing the columns, I'll leave my if statement at that.

SheeppOSU

SheeppOSU