一 介绍
SQLAlchemy是Python编程语言下的一款ORM框架,该框架建立在数据库API之上,使用关系对象映射进行数据库操作,简言之便是:将对象转换成SQL,然后使用数据API执行SQL并获取执行结果。
1、安装
pip3 install sqlalchemy
2、架构与流程
#1、使用者通过ORM对象提交命令 #2、将命令交给SQLAlchemy Core(Schema/Types SQL Expression Language)转换成SQL #3、使用 Engine/ConnectionPooling/Dialect 进行数据库操作 #3.1、匹配使用者事先配置好的egine #3.2、egine从连接池中取出一个链接 #3.3、基于该链接通过Dialect调用DB API,将SQL转交给它去执行
!!!上述流程分析,可以大致分为两个阶段!!!:
#第一个阶段(流程1-2):将SQLAlchemy的对象换成可执行的sql语句 #第二个阶段(流程3):将sql语句交给数据库执行
如果我们不依赖于SQLAlchemy的转换而自己写好sql语句,那是不是意味着可以直接从第二个阶段开始执行了,事实上正是如此,我们完全可以只用SQLAlchemy执行纯sql语句,如下
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
3、DB API
SQLAlchemy本身无法操作数据库,其必须以来pymsql等第三方插件,Dialect用于和数据API进行交流,根据配置文件的不同调用不同的数据库API,从而实现对数据库的操作,如:
#1、MySQL-Python mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> #2、pymysql mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] #3、MySQL-Connector mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname> #4、cx_Oracle oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]
更多详见:http://docs.sqlalchemy.org/en/latest/dialects/index.html
二 创建表
ORM中:
#类===>表 #对象==>表中的一行记录
四张表:业务线,服务,用户,角色,利用ORM创建出它们,并建立好它们直接的关系
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
注:设置外键的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])
三 增删改查
表结构
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
增
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
删
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
改
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
查
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
四 其他查询相关
一 准备表和数据
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
二 条件、通配符、limit、排序、分组、连表、组合
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
三 子查询
有三种形式的子查询,注意:子查询的sql必须用括号包起来,尤其在形式三中需要注意这一点
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
五 正查、反查
一 表修改
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship egine=create_engine('mysql+pymysql://root@127.0.0.1:3306/db1?charset=utf8',max_overflow=5) Base=declarative_base() class Dep(Base): __tablename__='dep' id=Column(Integer,primary_key=True,autoincrement=True) dname=Column(String(64),nullable=False,index=True) class Emp(Base): __tablename__='emp' id=Column(Integer,primary_key=True,autoincrement=True) ename=Column(String(32),nullable=False,index=True) dep_id=Column(Integer,ForeignKey('dep.id')) #在ForeignKey所在的类内添加relationship的字段,注意: #1:Dep是类名 #2:depart字段不会再数据库表中生成字段 #3:depart用于Emp表查询Dep表(正向查询),而xxoo用于Dep表查询Emp表(反向查询), depart=relationship('Dep',backref='xxoo') def init_db(): Base.metadata.create_all(egine) def drop_db(): Base.metadata.drop_all(egine) drop_db() init_db() Session=sessionmaker(bind=egine) session=Session() # 准备数据 session.add_all([ Dep(dname='技术'), Dep(dname='销售'), Dep(dname='运营'), Dep(dname='人事'), ]) session.add_all([ Emp(ename='林海峰',dep_id=1), Emp(ename='李杰',dep_id=1), Emp(ename='武配齐',dep_id=1), Emp(ename='元昊',dep_id=2), Emp(ename='李钢弹',dep_id=3), Emp(ename='张二丫',dep_id=4), Emp(ename='李坦克',dep_id=2), Emp(ename='王大炮',dep_id=4), Emp(ename='牛榴弹',dep_id=3) ]) session.commit()
二 标准连表查询
# 示例:查询员工名与其部门名 res=session.query(Emp.ename,Dep.dname).join(Dep) #迭代器 for row in res: print(row[0],row[1]) #等同于print(row.ename,row.dname)
三 基于relationship的正查、反查
#SQLAlchemy的relationship在内部帮我们做好表的链接 #查询员工名与其部门名(正向查) res=session.query(Emp) for row in res: print(row.ename,row.id,row.depart.dname) #查询部门名以及该部门下的员工(反向查) res=session.query(Dep) for row in res: # print(row.dname,row.xxoo) print(row.dname,[r.ename for r in row.xxoo])