# 使用SQLAlchemy ORM操作数据库
from sqlalchemy import create_engine, Column, Integer, String, DateTime, Text, ForeignKey, text
from sqlalchemy.orm import declarative_base, sessionmaker, relationship

Base = declarative_base()


class User(Base):
    __tablename__ = 't_user'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    nickname = Column(String(255), nullable=False)
    openid = Column(String(255), nullable=False)
    status = Column(Integer, nullable=False)
    avatar = Column(Text, nullable=False)
    can_download_count = Column(Integer, nullable=False)
    create_time = Column(DateTime, nullable=False)
    update_time = Column(DateTime, nullable=False)
    operator = Column(Integer, nullable=False)

    def __repr__(self):
        # 返回User对象的字符串表示
        return f"User(id={self.id}, name={self.name}, nickname={self.nickname}, openid={self.openid}, status={self.status}, avatar={self.avatar}, can_download_count={self.can_download_count}, create_time={self.create_time}, update_time={self.update_time}, operator={self.operator})"


engine = create_engine(
    'mysql+pymysql://root:123456@localhost:3306/demo?charset=utf8mb4', echo=True, pool_size=20)
Base.metadata.create_all(engine)

# 操作数据
Session = sessionmaker(bind=engine)
session = Session()

# 插入数据
user = User(name='test', nickname='test', openid='123456',
            status=1, avatar='https://example.com/avatar.jpg')
# session.add(user)
# session.commit()

# 查询全部数据
user = session.query(User).all()
# print(user)

# 执行原生sql
result = session.execute(text("SELECT * FROM t_user"))
for row in result:
    print(row)

# 更新
user = session.query(User).filter(User.id == 1).first()
if user:
    user.nickname = 'test1'
    session.commit()
else:
    print("User not found")

# 删除
user = session.query(User).filter(User.id == 1).first()
if user:
    session.delete(user)
    session.commit()
else:
    print("User not found")

# 事务管理
try:
    with session.begin():
        # 执行一些数据库操作
        user1 = User(name='Tom', nickname='Tommy', openid='654321',
                     status=1, avatar='https://example.com/avatar2.jpg')
        session.add(user1)
        raise Exception("Simulated error")  # 模拟错误，触发回滚
except Exception as e:
    # 处理异常
    print(f"Transaction failed: {e}")