sqlalchemy orm 方式做mysql处理

创建数据库learn，并在库中创建表ntbl

CREATE TABLE `ntbl` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) COLLATE utf8_bin NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_bin

实现代码，需要把mysql的用户名、密码、IP、端口做相应修改

# -*- coding: utf-8 -*-
from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import as_declarative, declared_attr

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, Session

# 1、创建 mysql 连接池
sql_a_uri = "mysql+pymysql://username:password@127.0.0.1:3306/learn?charset=utf8mb4"
mysql_engine = create_engine(sql_a_uri , pool_size=2, pool_pre_ping=True)
mysql_session = sessionmaker(autocommit=False, autoflush=False, bind=mysql_engine)

# 2.1、创建基类
@as_declarative()
class TBLBase:
    __name__: str 

    # 根据类名字，自动生成 __tablename__ 属性
    @declared_attr
    def __tablename__(cls) -> str:
        return cls.__name__.lower()

# 2.2、基于基类创建表对应的对象
class Ntbl(TBLBase) :
    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String(45), nullable=False)
    age = Column(Integer, nullable=False)

# 3.1、创建增删改查基类
class CRUDBase() :
    def add_one(self, one_data ) :
        with mysql_session() as db :
            db.add(one_data)
            db.commit()

# 3.2、创建基于此表的增删改查处理类
class CRUDNtbl(CRUDBase) :
    pass


# 4、测试
if __name__ == "__main__" :
    one_ntbl = Ntbl(name="name02", age=10)

    crud_ntbl = CRUDNtbl()
    crud_ntbl.add_one(one_ntbl)