KnowHow

import sqlite3


class dao(object):
    def __init__(self, dbname: str):
        self.db_name = dbname
        self._conn = None
        self._curs = None
        self._conn_db()

    def __del__(self):
        self._close_db()

    def _conn_db(self):
        self._conn = sqlite3.connect(self.db_name)
        self._curs = self._conn.cursor()

    def _close_db(self):
        self._curs.close()
        self._conn.close()
        print('--- close ' + self.db_name + ' database ---')

    def create_table(self, table_name):
        if self._conn is None:
            self._conn_db()
        self._curs.execute(
            'CREATE TABLE IF NOT EXISTS ' + table_name + '(id INTEGER PRIMARY KEY AUTOINCREMENT, name STRING)')
        self._conn.commit()

    def insert(self, name):
        if self._conn is None:
            self._conn_db()
        self._curs.execute(
            'INSERT INTO persons(name) values ("' + name + '")'
        )
        self._conn.commit()

    def update(self, table_name, pre_name, ch_name):
        if self._conn is None:
            self._conn_db()
        self._curs.execute(
            'UPDATE ' + table_name + ' set name = "' + ch_name + '" WHERE name = "' + pre_name + '"')
        self._conn.commit()

    def delete_item(self, table_name, del_item):
        if self._conn is None:
            self._conn_db()
        self._curs.execute(
            'DELETE FROM ' + table_name + ' WHERE name = "' + del_item + '"')
        self._conn.commit()

    def select_all(self, table_name):
        if self._conn is None:
            self._conn_db()
        self._curs.execute('SELECT * FROM ' + table_name)
        print(self._curs.fetchall())



if __name__ == '__main__':
    db_name= 'test_sqlite_3.db'
    table_name = 'persons'
    db = dao(db_name)

    # test to create table
    db.create_table(table_name)

    # test to insert
    # user_names = ['Mike', 'Nancy' , 'Rick', 'Jun', 'Nob']
    # for name in user_names:
    #     db.insert(name)

    # test to update
    # db.select_all(table_name)
    # db.update(table_name, pre_name='Mike', ch_name='Michel')

    # delete test
    # db.select_all(table_name)
    # db.delete_item(table_name, del_item='Michel')


    db.select_all(table_name)

    del(db)