KnowHow

import mysql.connector
import mysql


class dao_mysql(object):
    def __init__(self, database_name, hostname='127.0.0.1', user='root'):
        self._hostname = hostname
        self._user = user
        self._database_name = database_name
        self._conn = None
        self._curs = None
        self._create_database()
        self._conn_db()

    def __del__(self):
        self._close_db()
        print('clear dao_mysql instance')

    def __str__(self):
        print('check print')
        print('hostname ', self._hostname)
        print('user name', self._user)
        print('database ', self._database_name)
        return ''

    def _conn_db(self):
        self._conn = mysql.connector.connect(host=self._hostname,
                                             user=self._user,
                                             database=self._database_name)
        self._curs = self._conn.cursor()

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

    def _create_database(self):
        self._conn = mysql.connector.connect(host=self._hostname,
                                             user=self._user)
        self._curs = self._conn.cursor()
        self._curs.execute(
            'CREATE DATABASE IF NOT EXISTS ' + self._database_name
        )
        self._conn.commit()
        self._curs.close()
        self._conn.close()

    def create_table(self, table_name):
        self._curs.execute(
            'CREATE TABLE IF NOT EXISTS ' + table_name + '('
            'id int NOT NULL AUTO_INCREMENT,'
            'name varchar(14) NOT NULL,'
            'PRIMARY KEY(id))')
        self._conn.commit()
        print('* createv' + table_name + ' talbe ')

    def insert_name(self, table_name, name):
        self._curs.execute('INSERT INTO ' + table_name + '(name) values("' + name + '")')
        self._conn.commit()
        print('* insert data ')

    def update_name(self, table_name, new_name, pre_name):
        self._curs.execute('UPDATE '+ table_name +' set name = "' + new_name + '" WHERE name = "' + pre_name + '"')
        self._conn.commit()
        print('* update data')

    def delete_name(self, table_name, name):
        self._curs.execute('DELETE FROM ' + table_name + ' WHERE name = "' + name + '"')
        self._conn.commit()
        print('* delete data')

    def select_all(self, table_name):
        self._curs.execute('SELECT * FROM ' + table_name)
        for row in self._curs:
            print(row)


if __name__ == '__main__':
    database_name = 'test_mysql_db1'
    table_name = 'persons'
    db = dao_mysql(database_name)
    print(db)

    # crate table
    db.create_table(table_name)

    # test insert
    test_flg = False
    if test_flg:
        user_names = ['Mike', 'Nancy', 'Jun', 'Nobu']
        for user in user_names:
            db.insert_name(table_name, user)

    # test update
    test_flg = False
    if test_flg:
        db.select_all(table_name)
        print('-update-')
        db.update_name(table_name, new_name='Michel', pre_name='Mike')

    # test delete
    test_flg = True
    if test_flg:
        db.select_all(table_name)
        print('-delete-')
        db.delete_name(table_name, 'Michel')

    # select
    db.select_all(table_name)

    del db