一、介绍
SQLAlchemy是一个基于Python实现的ORM框架。该框架建立在 DB API之上，使用关系对象映射进行数据库操作，简言之便是：将类和对象转换成SQL，然后使用数据API执行SQL并获取执行结果。
pip3 install sqlalchemy
组成部分：
Engine，框架的引擎 Connection Pooling ，数据库连接池 Dialect，选择连接数据库的DB API种类 Schema/Types，架构和类型 SQL Exprression Language，SQL表达式语言
SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：
MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...] 更多：http://docs.sqlalchemy.org/en/latest/dialects/index.html
django中数据库的表如何反向生成models
python manage.py inspectdb > app/models.py
二、orm简单使用（能创建表，删除表，不能修改表）
models.py
import datetime from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index Base = declarative_base() class Users(Base): __tablename__ = 'users' # 数据库表名称 id = Column(Integer, primary_key=True) # id 主键 name = Column(String(32), index=True, nullable=False) # name列，索引，不可为空 email = Column(String(32), unique=True) #datetime.datetime.now不能加括号，加了括号，以后永远是当前时间 # 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', 'email'), #索引 ) def init_db(): """ 根据类创建数据库表 :return: """ engine = create_engine( # "什么数据库(mysql,orcal)+用什么去连接数据库(pymysql)://数据库用户名:密码@ip：端口/数据库名?charset=字符集" "mysql+pymysql://root:123@127.0.0.1:3306/day95?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） ) Base.metadata.create_all(engine) def drop_db(): """ 根据类删除数据库表 :return: """ engine = create_engine( "mysql+pymysql://root:123456@127.0.0.1:3306/aaa?charset=utf8", max_overflow=0, # 超过连接池大小外最多创建的连接 pool_size=5, # 连接池大小 pool_timeout=30, # 池中没有线程最多等待的时间，否则报错 pool_recycle=-1 # 多久之后对线程池中的线程进行一次连接的回收（重置） ) Base.metadata.drop_all(engine) if __name__ == '__main__': # drop_db() init_db()
orm1.py
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from models import Users #"mysql+pymysql://root@127.0.0.1:3306/aaa" engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/day95", max_overflow=0, pool_size=5) Connection = sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个Connection conn = Connection() ############# 执行ORM操作 ############# obj1 = Users(name="hank",age=18) # 创建一个对象 conn.add(obj1) # 必须提交事务才能生效 conn.commit() # 关闭session，其实是将连接放回连接池 conn.close()
三、基本操作（增删改查）
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from models import Users engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/day95", max_overflow=0, pool_size=5) Connection = sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个Connection conn = Connection() ############# 执行ORM操作 ############# # 1、单增 obj1 = Users(name="sean",age=35) # 创建一个对象 conn.add(obj1) # 2、多个增加 conn.add_all([ Users(name="bob",age=15), Users(name="paul",age=23), #Books(name="西游记") ]) # 3、改 conn.query(Users).update({'name':"sb","age":14}) # 把名字全部改成sb,年龄改为14 # 下面类似于django的F查询,这个后面必须配合synchronize_session # 如果是字符串就用False，如果是数字就不用操作 conn.query(Users).update({Users.name:Users.name + " is dsb"},synchronize_session=False) conn.query(Users).update({Users.age:Users.age + 1}) # 4、删 conn.query(Users).delete() #query里面填表名 # 5、查 # 查询所有,返回的是列表 r1 = conn.query(Users).all() # 查单个 r1 = conn.query(Users).first() # 查哪些字段，把name重命名为xx r1 = conn.query(Users.name.label("xx"),Users.age).first() print(r1.xx) # filter传的是表达式，filter_by传的是参数 r1 = conn.query(Users).filter(Users.name == "bob").first() r1 = conn.query(Users).filter(Users.name == "bob").update({"age":14}) # 返回的是影响的行数 r1 = conn.query(Users).filter_by(name="paul").first()
# 打印原生sql语句 r1 = conn.query(Users) print(r1) #增，删，改都要commit() conn.commit() # 关闭session，其实是将连接放回连接池 conn.close()
四、单表查询操作
from sqlalchemy.orm import sessionmaker from sqlalchemy import create_engine from models import Users engine = create_engine("mysql+pymysql://root:123@127.0.0.1:3306/day95", max_overflow=0, pool_size=5) Session = sessionmaker(bind=engine) # 每次执行数据库操作时，都需要创建一个Connection session = Session() # 1、条件 ret = session.query(Users).filter_by(name="bob").all() # all() 返回列表套对象 # ret = session.query(Users).filter_by(name="paul").first() # 2、表达式 ，and 条件连接 ret = session.query(Users).filter(Users.name == "bob",Users.age == 14).first() # 3、表达式的between 条件 顾头也顾尾 ret = session.query(Users).filter(Users.age.between(15, 25)).all() # 4、下划线in_ ret = session.query(Users).filter(Users.id.in_([12,14])).all() # 5、~非，除...外 ret = session.query(Users).filter(~Users.id.in_([12,14])).all() # 6、or_包裹的都是or条件，and_包裹的都是and条件，必须导包 from sqlalchemy import and_, or_ ret = session.query(Users).filter(or_(Users.id==12,Users.name=="hank")).all() ret = session.query(Users).filter(and_(Users.id==12,Users.name=="bob")).all() ret = session.query(Users).filter( or_( Users.id < 12, and_(Users.name == 'hank', Users.id == 14), )).all() # 7、模糊匹配 like查询 # 必须以b开头 ret = session.query(Users).filter(Users.name.like('b%')).all() # 第二个字母是b ret = session.query(Users).filter(Users.name.like('_b%')).all() # 不以b开头 ret = session.query(Users).filter(~Users.name.like('b%')).all() # 8、排序 # desc()从大到小排序，降序 ret = session.query(Users).filter(Users.id>1).order_by(Users.id.desc()).all() # asc()从小到大排序，升序 ret = session.query(Users).filter(Users.id>1).order_by(Users.id.asc()).all() # 多条件排序，先以年纪从大到小排，如果年龄相同，然后再以id从小到大排 ret = session.query(Users).filter(Users.id>1).order_by(Users.age.asc(),Users.id.asc()).all() # 9、分组查询 ret = session.query(Users).group_by(Users.name).all() # 按名字分组 from sqlalchemy import func # 选出组内成员年龄大于等于30的组 ret =session.query(Users).group_by(Users.name).having(func.min(Users.age)>=20).all() # 选出组内最小年龄要大于12的组，查询组内的最小年龄，最大年龄，年纪之和 ret = session.query( func.min(Users.age), func.max(Users.age), func.sum(Users.age), Users.name ).group_by(Users.name).having(func.min(Users.age)>12).all() print(ret)
五、表关系创建
一对多
# 一个人对应多个爱好
from sqlalchemy.orm import relationship
class Hobby(Base): __tablename__ = "hobby" id = Column(Integer,primary_key=True) catption = Column(String(50),default="双色球") class Person(Base): __tablename__ = "person" nid = Column(Integer,primary_key=True) name = Column(String(32)) # hobby指的是tablename而不是Hobby类名 hobby_id = Column(Integer, ForeignKey("hobby.id")) # 跟数据库无关，不会新增字段，只能用于快速链表操作 # relationship的第一个参数是类名，第二个参数backref是反向查询的字段 hobby = relationship("Hobby",backref="pers")
多对多
class Boy2Girl(Base): __tablename__ = 'boy2girl' id = Column(Integer, primary_key=True) girl_id = Column(Integer, ForeignKey('girl.id')) boy_id = Column(Integer, ForeignKey('boy.id')) # 一个男孩喜欢多个女孩，一个女孩也可以喜欢多个男孩 class Girl(Base): __tablename__ = "girl" id = Column(Integer,primary_key=True) name = Column(String(100),nullable=False) class Boy(Base): __tablename__ = "body" id = Column(Integer,primary_key=True) name = Column(String(100),nullable=False) # 与生成表结构无关，仅用于查询方便,放在哪个单表中都可以 # secondary="第三张表的表名" girl = relationship('Girl', secondary='boy2girl', backref='boys')
六、ORM操作
一对多
# 添加方式一：没有用到关联关系 session.add_all([ Hobby(caption="篮球"), Hobby(caption="下棋"), Person(name="tank",hobby_id=1), Person(name="sean",hobby_id=2), ]) # 添加方式二、用关联关系 person = Person(name="bob",hobby=Hobby(caption="相亲")) session.add(person) hobby = Hobby(caption="足球") hobby.pres = [Person(name="owen"),Person(name="july")] session.add(hobby) # 正向查询 pr = session.query(Person).filter(Person.name == "tank").first() print(pr.name) print(pr.hobby.caption) # hobby是外键字段 # 反向查询 v = session.query(Hobby).filter(Hobby.caption=="下棋").first() print(v.caption) print(v.pers) # 自己链表 # isouter默认为False，为inner join , 设置为True就是left join person_list = session.query(Person,Hobby).join(Hobby,Person.hobby_id==Hobby.id,isouter=True) print(person_list)
多对多
# 添加 session.add_all([ Boy(name="tank"), Boy(name="sean"), Girl(name="judy"), Girl(name="mary") ]) b2g = Boy2Girl(girl_id=2,boy_id=1) session.add(b2g) # 用关联关系添加 # 正向插入 boy = Boy(name="鲍勃") boy.girl=[Girl(name="小菊"),Girl(name="小苍")] session.add(boy) # 反向插入 girl = Girl(name="三上") girl.boys = [Boy(name="杰森"),Boy(name="杰瑞")] session.add(girl) # 使用relationship的关系 # 正向查 b = session.query(Boy).filter(Boy.name == "鲍勃").first() print(b.name) print(b.girl) # 反向查 g = session.query(Girl).filter(Girl.name == "三上").first() print(g.name) print(g.boys)