使用sqlalchemy操作数据库

sqlalchemy是一种常用的ORM（Object-Relational Mapping对象关系映射）框架.

from sqlalchemy import create_engine, Column, Integer, String, DateTime, Float, Date, BLOB, func, ForeignKey, extract, and_, or_, text
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
 
#创建基类
ModelBase = declarative_base()
 
#创建映射表
class User(ModelBase):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True, autoincrement=True)自增主键
    name = Column(String(80))
    createtime = Column(Datetime, index=True)

class SQLUtil:
    #使用contextmanager模块创建一个上下文管理器
    @contextmanager
    def session_scope(self):
        session = self.Session()
        try:
            yield session
            session.commit()
        except:
            session.rollback()
            raise
        finally:
            session.close()
    
    #初始化 创建引擎，建表，生成数据库连接对象
    def __init__(self, update_or_not=False):
        url = 'mysql+pymysql://user:pas@ip:port/db?charset=utf8mb4'
        engine = create_engine(
            url, pool_size=5, max_overflow=0, pool_pre_ping=True,
            echo=True)  # echo=True for debug
        ModelBase.metadata.create_all(engine)
        self.Session = sessionmaker(bind=engine, autoflush=False)

    #操作数据
    def get_user_name(self, name):
        with self.session_scope() as sess:
            info = sess.query(User).all()  #查询; sess.add 插入。         
           

参考SQLALchemy1.4文档：https://www.osgeo.cn/sqlalchemy/orm/tutorial.html

Flask-SQLAlchemy文档：http://www.pythondoc.com/flask-sqlalchemy/quickstart.html