KnowHow

import sqlalchemy
import sqlalchemy.ext.declarative
import sqlalchemy.orm


Base = sqlalchemy.ext.declarative.declarative_base()
class Person(Base):
    __tablename__ = 'persons'
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
    name = sqlalchemy.Column(sqlalchemy.String(14))


class Dao_Person(object):
    def __init__(self, engine):
        self._engine = engine
        self_Base = Base
        # self._Person = Person
        self_Base.metadata.create_all(engine)
        Session = sqlalchemy.orm.sessionmaker(bind=engine)
        self._session = Session()

    def __del__(self):
        print('--- end of dao ---')

    def insert_data(self, user_name):
        person = Person(name=user_name)
        self._session.add(person)
        self._session.commit()
        del person

    def update_data(self, pre_name, change_name):
        person = self._session.query(Person).filter_by(name=pre_name).first()
        if person is not None:
            person.name = change_name
            self._session.add(person)
            self._session.commit()
        else:
            print('*update -> There is no name: ', pre_name)
        del person

    def delete_data(self, user_name):
        person = self._session.query(Person).filter_by(name=user_name).first()
        if person is not None:
            self._session.delete(person)
            self._session.commit()
        else:
            print('*delete -> There is no name: ', user_name)
        del person

    def select_all(self):
        persons = self._session.query(Person).all()
        for person in persons:
            print(person.id, person.name)
        del person


if __name__ == '__main__':
    user = 'root'         # ユーザ名
    password = ''         # パスワード
    host = 'localhost'    # ホスト名 or IP
    port = 3306           # ポート
    db_name = 'test_mysql_database2'       # データベース
    mysql_url = f'mysql+pymysql://{user}:{password}@{host}:{port}/{db_name}?charset=utf8'
    engine = sqlalchemy.create_engine(mysql_url)
    # engine = sqlalchemy.create_engine('sqlite:///:memory:', echo=True)
    # engine = sqlalchemy.create_engine('sqlite:///test_sqlite2', echo=True)
    dao = Dao_Person(engine)
    dao.select_all()
    print('---  first data ---')

    user_list = ['Mike', 'Nancy', 'Jun', 'Nobu']
    user_list =['Mike']
    flg = True
    if flg:
        for user in user_list:
            dao.insert_data(user)
        dao.select_all()
        print('---  insert ---')

    flg = True
    if flg:
        dao.update_data(user_list[0], 'Michel')
        dao.select_all()
        print('---  update ---')

    flg = True
    if flg:
        dao.delete_data('Michel')
        dao.select_all()
        print('---  delete ---')

    del dao