SQLAlchemy ORM 内部组件结构图及其说明：

组成部分：

• Engine，框架的引擎
• Connection Pooling ，数据库连接池
• Dialect，选择连接数据库的DB API种类
• Schema/Types，架构和类型
• SQL Exprression Language，SQL表达式语言，将创建的类翻译为SQl语言

SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作：

基于pymysql连接 
    mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]

Python 基于SQLAlchemy 初始化表结构

import datetime 
from sqlalchemy import create_engine 
from sqlalchemy.ext.declarative import declarative_base 
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime 
from sqlalchemy.orm import relationship, sessionmaker 
 
Base = declarative_base() 
engine = create_engine( 
    "mysql+pymysql://root:123456@127.0.0.1:3306/hotel", 
    max_overflow=0,  # 超过连接池大小外最多创建的连接 
    pool_size=5,  # 连接池大小 
    pool_timeout=30,  # 池中没有线程最多等待的时间，否则报错 
    pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置） 
) 
Session = sessionmaker(engine) 
 
# ##################### 单表示例 ######################### 
class Users(Base): 
    __tablename__ = 'users' 
 
    id = Column(Integer, primary_key=True) 
    name = Column(String(32), index=True) 
    age = Column(Integer, default=18) 
    email = Column(String(32), unique=True) 
    ctime = Column(DateTime, default=datetime.datetime.now) 
    extra = Column(Text, nullable=True) 
 
    __table_args__ = ( 
        # UniqueConstraint('id', 'name', name='uix_id_name'), 
        # Index('ix_id_name', 'name', 'extra'), 
    ) 
 
 
class Hosts(Base): 
    __tablename__ = 'hosts' 
 
    id = Column(Integer, primary_key=True) 
    name = Column(String(32), index=True) 
    ctime = Column(DateTime, default=datetime.datetime.now) 
 
 
# ##################### 一对多示例 ######################### 
class Hobby(Base): 
    __tablename__ = 'hobby' 
    id = Column(Integer, primary_key=True) 
    caption = Column(String(50), default='篮球') 
 
 
class Person(Base): 
    __tablename__ = 'person' 
    nid = Column(Integer, primary_key=True) 
    name = Column(String(32), index=True, nullable=True) 
    hobby_id = Column(Integer, ForeignKey("hobby.id")) 
 
    # 与生成表结构无关，仅用于查询方便 
    hobby = relationship("Hobby", backref='pers') 
 
 
# ##################### 多对多示例 ######################### 
 
class Server2Group(Base): 
    __tablename__ = 'server2group' 
    id = Column(Integer, primary_key=True, autoincrement=True) 
    server_id = Column(Integer, ForeignKey('server.id')) 
    group_id = Column(Integer, ForeignKey('group.id')) 
 
 
class Group(Base): 
    __tablename__ = 'group' 
    id = Column(Integer, primary_key=True) 
    name = Column(String(64), unique=True, nullable=False) 
 
    # 与生成表结构无关，仅用于查询方便 
    servers = relationship('Server', secondary='server2group', backref='groups') 
 
 
class Server(Base): 
    __tablename__ = 'server' 
 
    id = Column(Integer, primary_key=True, autoincrement=True) 
    hostname = Column(String(64), unique=True, nullable=False) 
 
 
def init_db(): 
    Base.metadata.create_all(engine) 
 
 
def drop_db(): 
    Base.metadata.drop_all(engine) 
 
 
if __name__ == '__main__': 
    drop_db() 
    init_db() 

Python 基于SQLAlchemy 实现对表基础操作:增删改查

from orm import Users, Hosts, Session 
# 原生SQL查询 
from sqlalchemy.sql import text 
 
# 全局session 实例化 
session = Session() 
 
 
# 简单插入 
def insert(): 
    # ############# 执行ORM操作 ############# 
    obj1 = Users(name="alex1") 
    session.add(obj1) 
    # 提交事务 
    session.commit() 
 
 
# 批量插入 
def batch_insert(): 
    session.add_all([ 
        Users(name="wupeiqi"), 
        Users(name="alex"), 
        Hosts(name="c1.com"), 
    ]) 
    session.commit() 
 
 
# 简单条件删除 
def delete(): 
    session.query(Users).filter(Users.id > 2).delete() 
    session.commit() 
 
 
# 简单条件更新 
def update(): 
    session.query(Users).filter(Users.id > 0).update({"name": "099"}) 
    session.query(Users).filter(Users.id > 0).update({Users.name: Users.name + "099"}, synchronize_session=False) 
    session.query(Users).filter(Users.id > 0).update({"age": Users.age + 1}, synchronize_session="evaluate") 
    session.commit() 
 
 
# 简单查询 
def select(): 
    r1 = session.query(Users).all() 
    print(r1) 
    r2 = session.query(Users.name.label('xx'), Users.age).all() 
    print(r2) 
    r3 = session.query(Users).filter(Users.name == "alex").all() 
    print(r3) 
    r4 = session.query(Users).filter_by(name='alex').all() 
    print(r4) 
    r5 = session.query(Users).filter_by(name='alex').first() 
    print(r5) 
    r6 = session.query(Users).filter(text("id<:value and name=:name")).params(value=224, name='fred').order_by( 
        Users.id).all() 
    print(r6) 
    r7 = session.query(Users).from_statement(text("SELECT * FROM users where name=:name")).params(name='ed').all() 
    print(r7) 
 
 
# 全局session 关闭 
session.close() 
 
if __name__ == '__main__': 
    # insert() 
    # batch_insert() 
    # delete() 
    # update() 
    select()