sqlalchemy basic

# SQLAlchemy==2.0.25

from sqlalchemy import create_engine, Column
from sqlalchemy.orm import sessionmaker, declarative_base
from sqlalchemy import String, Integer


class User(Base):
    __tablename__ = "user_account"

    id: int = Column(Integer, primary_key=True)
    name: str = Column(String(30))
    email: str = Column(String(30))

    def __repr__(self) -> str:
        return f"User(id={self.id!r}, name={self.name!r})"
        
        
if __name__ == '__main__':
    engine = create_engine("mysql+pymysql://<user>:<password>@<ip>/<db>")
    
    # create table, if change table column, u'd better take it manually 
    User.metadata.create_all(engine)

    Session = sessionmaker(bind=engine)
    session = Session()

    # add
    user1 = User(name='www', email='')
    session.add_all([user1])
    session.commit()
 
    # update
    q = session.query(User).filter_by(name="www").all()
    q[0].email = 'www@example.com'
    q = session.query(User).filter_by(name="www").first()
    q.email = 'www@example.com'
    session.commit()
    
    # query
    # query by condition
    users = session.query(User).filter_by(name="www").all()
    users = session.query(User).all()
    # turn obj to dict
    dict_results = [{column.name: getattr(row, column.name) for column in     row.__table__.columns} for row in users]
    
    # delete
    q = session.query(User).filter_by(name="www").first()
    session.delete(q)
    session.commit()