ORM
ORM，对象关系映射。使用面向对象的方式来操作数据库。
关系模型和Python对象之间的映射
table -> class 表映射为类
row -> object 行映射为实例
column -> property 字段映射为(实例)属性举例：
student表，字段为id int, name varchar, age int
映射到Python为：
class Student:
id = ?某类型字段(使用一个类，来描述字段。因为字段可能很复杂)
name = ?某类型字段
age = ?某类型字段
最终得到实例：
class Student:
def __init__(self):
self.id = ?
self.name = ?
self.age = ? 实例属性，表示每一行SQLAlchemy
SQLAlchemy 是一个ORM框架，大量使用了元编程。
安装
$ pip install sqlalchemy文档
https://docs.sqlalchemy.org/en/13/
http://docs.sqlalchemy.org/en/latest/
查看版本：
import sqlalchemy
print(sqlalchemy.__version__) # 1.3.13开发
SQLAlchemy内部使用了连接池
创建连接
数据库连接，由引擎完成（解决方言，并返回一个连接）。
from sqlalchemy import create_engine
# mysqldb的连接
#连接字符串：mysql + mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
engine = sqlalchemy.create_engine("mysql+mysqldb://root:hanna@192.168.2.100:3306/magedu")
# pymysql的连接
# mysq + pymysql://<user>:<password>@<host>[:<port>]/<dbname>[?options]
engine = sqlalchemy.create_engine("mysql+mysqldb://root:hanna@192.168.2.100:3306/magedu")
engine = sqlalchemy.create_engine("mysql+mysqldb://root:hanna@192.168.2.100:3306/magedu",echo=True) # 引擎是否打印执行语句，调试时可打开以提供便利比如，使用pymysql。注意，需要事先安装pymysql。
from sqlalchemy import create_engine
connstr = "mysql+pymysql://root:hanna@192.168.2.100:3306/school"
engine = create_engine(connstr,echo=True)Declare a Mapping 创建映射
创建基类
from sqlalchemy.ext.declarative import declarative_base
# 创建基类，便于实体类继承
Base = declarative_base() # 基类，源码定义中它的元类已经修改过，已经添加了功能然后就可以构建子类了
创建实体类
student表
CREATE TABLE student (
id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
age INTEGER,
PRIMARY KEY (id)
)实体表已经生成，接下来需要使用程序语言去描述。注意定义时需要和实体表的字段定义时的约束一致。
from sqlalchemy import Column,Integer,String# 创建实体类class Student(Base): __tablename__ = 'student' # 指定表名 # 定义属性对应字段 id = Column(Integer,primary_key=True) name = Column(String(64)) age = Column(Integer) # 第一参数是字段名，如果和属性名不一致，则一定指定 # age = Column('Age',Integer) def __repr__(self): return "{} id={} name={} age={}".format( self.__class__.__name__, self.id, self.name, self.age )# 实例化：s = Student(name='tom')s.age=20print(s) # Student id=None name=tom age=20创建表
可以创建由程序定义的表，或者删除表。
实例化
from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,Stringconnstr = "mysql+pymysql://root:hanna@192.168.2.100:3306/school"engine = create_engine(connstr,echo=True)Base = declarative_base()class Student(Base): __tablename__ = 'student' id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64)) age = Column(Integer) def __repr__(self): return "{} id={} name={},age={}".format( self.__class__.__name__, self.id, self.name, self.age )s = Student(name='tom')s.age=20print(s)Base.metadata.create_all(bind=engine) # Base.metadata.drop_all(bind=engine)# # 部分输出：#CREATE TABLE student (# id INTEGER NOT NULL AUTO_INCREMENT, # name VARCHAR(64), # age INTEGER, # PRIMARY KEY (id)#) 可以通过检查语句，来检查自己的类是否写得准确# 2020-02-11 21:17:02,118 INFO sqlalchemy.engine.base.Engine {}# 2020-02-11 21:17:02,124 INFO sqlalchemy.engine.base.Engine COMMIT生产环境很少这样创建表，一般是系统上线时，由sql脚本生成，或者由模型生成表。
生产环境很少删除表，宁可废弃也不删除。
创建会话session
在一个会话中操作数据库，会话建立在连接上，连接被引擎管理。
from sqlalchemy.orm import sessionmaker# 创建sessionSession = sessionmaker(bind=engine) # 创建了会话的Session类，返回类session = Session() # Session类会话的实例化session对象线程不安全，因此不同线程使用不同的session对象。
Session类和engine都是线程安全的，因此使用一个即可。
CRUD操作-增加
from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column,Integer,Stringfrom sqlalchemy.orm import sessionmakerconnstr = "mysql+pymysql://root:hanna@192.168.2.100:3306/school"engine = create_engine(connstr,echo=True)Base = declarative_base()class Student(Base): __tablename__ = 'student' id = Column(Integer,primary_key=True,autoincrement=True) name = Column(String(64)) age = Column(Integer) def __repr__(self): return "{} id={} name={},age={}".format( self.__class__.__name__, self.id, self.name, self.age )s = Student(name='tom')s.age=20#Base.metadata.create_all(bind=engine)Session = sessionmaker(bind=engine)session = Session()session.add(s) # id是自增，因此未定义id，由数据库自动生成print(s) # Student id=None name=tom,age=20session.commit() # 提交到数据库中，执行更改print(s) # Student id=1 name=tom,age=20##s.age = 30session.add(s) # 同一个session，最终生效的是最后一个修改的值# 注意：可以先session.add(s)，然后再s.age=30，不过习惯上推荐最后addtry: session.add_all([s]) # 可同时增加多个session实例，比如add_all([s1,s2]) print(s) session.commit() print(s) # 提交后，数据库将生成id并返回到s中except: session.rollback() raise简单查询
使用query方法，返回一个Query对象
students = session.query(Student) # 无条件查询，查询Student的表，相当于select * from studentfor student in students: print(student)# Student id=1 name=tom,age=20# Student id=2 name=tom,age=20# Student id=3 name=tom,age=20student = session.query(Student).get(2) # 通过主键查询print(student)# Student id=2 name=tom,age=20query方法将实体类传入，返回类的对象(query对象)为可迭代对象。此时并不执行查询。而需要迭代它，它才会执行SQL来查询数据库，封装数据到指定类的实例中。
get方法使用主键查询，返回一条传入类的一个实例，相当于立即查询。
改
必须先从数据库中查出结果，然后修改，再提交更改。
student = session.query(Student).get(2)print(student)student.name = 'sam'student.age = 30 # # 实际执行了update语句，通过状态可判断update还是insert，因为已经存在id了。print(student)session.add(student)session.commit()删除
编写如下程序来删除数据，会发生什么？
try: student = Student(id=2,name="sam",age=30) session.delete(student) session.commit()except Exception as e: session.rollback() print(e) # Instance '<Student at 0x1393e1460b8>' is not persisted会产生一个异常，未持久异常。
Instance '<Student at 0x1393e1460b8>' is not persisted状态**
每一个实体，都有一个状态属性_sa_instance_stat，其类型是sqlalchemy.orm.state.InstanceState，可以使用sqlalchemy.inspect(entity)函数查看状态。
常见的状态如下：
状态 | 说明 |
transient | 实体类尚未加到到session中，同时并没有保存到数据库中。(没有add()，临时的) |
pending | transiet的实体被add()到session中，状态切换到pending。但它还没有被flush到数据库中。(没有commit()，挂起的) |
persistent | session中的实体对象对应着数据库中的真实记录。pending状态在提交成功后，可以变成persis状态，或者查询成功返回的实体也是persis状态。 |
deleted | 实体被删除后且已经flush，但未commit完成。事务提交成功了，实体变成detached。事务失败，返回persistent状态。(已经delete()，没有commit()) |
detached | 删除成功的实体，进入到此状态. (已经delete(),已经commit()，脱离) |
新建一个实体，状态是transient，临时的。
一旦add()后，transient -> pending
成功commit()后，pending -> persistent
persistent状态的实体删除后，flush后但没有commit，persistent -> deleted
成功commit后，deleted -> detached
提交失败，还原到 persistent。
flush方法，是主动把改变应用到数据库中去。
删除、修改操作，需要对应一个真实的记录。所以要求实体对象是persistent状态。
import sqlalchemyfrom sqlalchemy import create_engine,Column,Integer,Stringfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmakerconnstr = "{}://{}:{}@{}:{}/{}".format( 'mysql+pymysql','root','hanna', '192.168.2.100',3306,'school')engine = create_engine(connstr, echo=True)Base = declarative_base()# 创建实体类class Student(Base): __tablename__ = 'student' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), nullable=False) age = Column(Integer) def __repr__(self): return "<{} id={} name={} age={}>".format( self.__class__.__name__,self.id,self.name,self.age )Session = sessionmaker(bind=engine)session = Session()from sqlalchemy.orm.state import InstanceStatedef getstate(entity, i): insp = sqlalchemy.inspect(entity) state = "sessionid={}, attached={}\ntransient={},persistent={}\npending={},deleted={},detached={}".format( insp.session_id, insp._attached, insp.transient, insp.persistent, insp.pending, insp.deleted, insp.detached ) print(i, state) # #1 sessionid=1, attached=True #transient=False,persistent=True #pending=False,deleted=False,detached=False print(insp.key) # (<class '__main__.Student'>, (2,), None) print('-'*30)student = session.query(Student).get(2)getstate(student,1) # persistent###import sqlalchemyfrom sqlalchemy import create_engine,Column,Integer,Stringfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmakerconnstr = "{}://{}:{}@{}:{}/{}".format( 'mysql+pymysql','root','hanna', '192.168.2.100',3306,'school')engine = create_engine(connstr, echo=True)Base = declarative_base()# 创建实体类class Student(Base): __tablename__ = 'student' id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), nullable=False) age = Column(Integer) def __repr__(self): return "<{} id={} name={} age={}>".format( self.__class__.__name__,self.id,self.name,self.age )Session = sessionmaker(bind=engine)session = Session()from sqlalchemy.orm.state import InstanceStatedef getstate(entity, i): insp = sqlalchemy.inspect(entity) state = "sessionid={}, attached={}\ntransient={},persistent={}\npending={},deleted={},detached={}".format( insp.session_id, insp._attached, # 附加到会话中的状态 insp.transient, insp.persistent, insp.pending, insp.deleted, insp.detached ) print(i, state) print(insp.key) print('-'*30)student = session.query(Student).get(2)getstate(student,1) # persistenttry: student = Student(id=2,name="sam",age=30) getstate(student, 2) # transient=True student = Student(name="sammy",age=30) getstate(student, 3) # transient=True session.add(student) # add之后，变成pending getstate(student, 4) # pending=True # session.delete(student) # 删除的前提是persistent # getstate(student, 5) session.commit() getstate(student, 6) # persistent=Trueexcept Exception as e: session.rollback() print(e)student = session.query(Student).get(2)getstate(student,10) # persistent=Truetry: session.delete(student) getstate(student, 11) # persistent=True session.flush() getstate(student, 12) # deleted=True session.commit() getstate(student, 13) # detached=Trueexcept Exception as e: session.rollback() print(e)复杂查询
实体类
import sqlalchemyfrom sqlalchemy import create_engine,Column,Integer,String, Date, Enum, ForeignKeyfrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import sessionmakerimport enumconnstr = "{}://{}:{}@{}:{}/{}".format( 'mysql+pymysql','root','hanna', '192.168.2.100',3306,'test')engine = create_engine(connstr, echo=True)Base = declarative_base()Session = sessionmaker(bind=engine)session = Session()class MyEnum(enum.Enum): M = 'M' F = 'F'class Employee(Base): __tablename__ = 'employees' emp_no = Column(Integer, primary_key=True) birth_date = Column(Date, nullable=False) first_name = Column(String(14),nullable=False) last_name = Column(String(16),nullable=False) gender = Column(Enum(MyEnum),nullable=False) hire_date = Column(Date, nullable=False) def __repr__(self): return "{} no={} name={} {} gender{}".format( self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value )# 打印函数def show(emps): for x in emps: print(x) print('~~~~~~~~~~',end='\n\n')# 简单条件查询emps = session.query(Employee).filter(Employee.emp_no > 10015)show(emps)# 与或非from sqlalchemy import or_, and_, not_emps = session.query(Employee).filter(Employee.emp_no > 10015).filter(Employee.gender == MyEnum.F)show(emps)emps = session.query(Employee).filter(and_(Employee.emp_no > 10015,Employee.gender == MyEnum.M))show(emps)# & 一定要注意&符号两边表达式都要加括号emps = session.query(Employee).filter((Employee.emp_no > 10015) & (Employee.gender == MyEnum.M))show(emps)# OR条件emps = session.query(Employee).filter((Employee.emp_no > 10018) | (Employee.emp_no < 10003))show(emps)emps = session.query(Employee).filter(or_(Employee.emp_no > 10018,Employee.emp_no < 10003))show(emps)# Notemps = session.query(Employee).filter(~(Employee.emp_no < 10018))show(emps)emps = session.query(Employee).filter(not_(Employee.emp_no<10018))show(emps)# inemplist = [10010,10015,10018]emps = session.query(Employee).filter(Employee.emp_no.in_(emplist))show(emps)# not inemps = session.query(Employee).filter(Employee.emp_no.notin_(emplist))show(emps)# like 可忽略大小写匹配emps = session.query(Employee).filter(Employee.last_name.like('P%'))show(emps)排序
# 升序emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no)emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.asc())show(emps)# 降序emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.desc())show(emps)# 多列排序emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.last_name).order_by(Employee.emp_no.desc())show(emps)分页
# 分页emps = session.query(Employee).limit(4)show(emps) # 此处是执行sqlemps = session.query(Employee).limit(4).offset(18)show(emps)上述过程中，emps仅仅是SQL的一种表现形式，而没有实际执行SQL。如果需要执行SQL，则需要消费者方法。
消费者方法
消费者方法调用后，Query对象(可迭代)就转换成了一个容器。
# 总行数emps = session.query(Employee)print(len(list(emps))) # 返回大量的结果集， 然后转换listprint(emps.count()) # 聚合函数count(*)的查询# 取所有数据print(emps.all()) # 返回列表，查不到则返回空列表。此处会执行SQL，为消费者方法。# 取首行print(emps.first()) # 返回首行，查不到则返回None# 有且只能有一行#print(emps.one()) # 如果查询结果是多行则抛异常print(emps.limit(1).one())# 删除 delete by querysession.query(Employee).filter(Employee.emp_no > 10018).delete()# session.commit() # 提交删除聚合、分组
# 聚合函数# count,可应用于计算总行数 from sqlalchemy import funcquery = session.query(func.count(Employee.emp_no))print(query.one()) # 只能有一行结果 (20,)print(query.scalar()) # 取one() 且返回元组的第一个元素 20# max/min/avg，query对象.消费者方法scalar返回值print(session.query(func.max(Employee.emp_no)).scalar())print(session.query(func.min(Employee.emp_no)).scalar())print(session.query(func.avg(Employee.emp_no)).scalar())# 分组print(session.query(Employee.gender,func.count(Employee.emp_no)).group_by(Employee.gender).all())关联查询
提示：多表关联进行查询。如果两张表对应的关系过多(多对多关系)，可借助第三张表做映射。
CREATE TABLE 'departments' (`dept_no` char(4) NOT NULL,`dept_name` varchar(40) NOT NULL,PRIMARY KEY(`dept_no`),UNIQUE KEY `dept_name` (`dept_name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `employees` (`emp_no` int(11) NOT NULL,`birth_date` date NOT NULL,`first_name` varchar(14) NOT NULL,`last_name` varchar(16) NOT NULL,`gender` enum('M','F') NOT NULL,`hire_date` date NOT NULL,PRIMARY KEY(`emp_no`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `dept_emp` (`emp_no` int(11) NOT NULL, // 外键约束，关联到emp_no`dept_no` char(4) NOT NULL, // 外键约束，关联到dept_no`from_date` date NOT NULL,`to_date` date NOT NULL,PRIMARY KEY(`emp_no`,`dept_no`), // 联合主键KEY `dept_no` (`dept_no`),CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY(`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY(`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE,) ENGINE=InnoDB DEFAULT CHARSET=utf8;从语句看出员工、部门之间的关系是多对多关系。
先把这些表的Model类和字段属性建立起来。
# 创建实体类class Employee(Base): __tablename__ = 'employees' emp_no = Column(Integer, primary_key=True) birth_date = Column(Date, nullable=False) first_name = Column(String(14), nullable=False) last_name = Column(String(16), nullable=False) gender = Column(Enum(MyEnum), nullable=False) hire_date = Column(Date, nullable=False) def __repr__(self): return "{} no={} name={} {} gender{}".format( self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value )class Department(Base): __tablename__ = 'departments' dept_no = Column(String(4),primary_key=True) dept_name = Column(String(40), nullable=False,unique=True) def __repr__(self): return "{} no={} name={}".format( type(self).__name__,self.dept_no,self.dept_name )class Dept_emp(Base): __tablename__ = "dept_emp" emp_no = Column(Integer,ForeignKey('employees.emp_no',ondelete='CASCADE'),primary_key=True) dept_no = Column(String(4),ForeignKey('departments.dept_no',ondelete='CASCADE'),primary_key=True) from_date = Column(Date, nullable=False) to_date = Column(Date, nullable=False) def __repr__(self): return "{} empno={} deptno={}".format( type(self).__name__,self.emp_no,self.dept_no )ForeignKey('departments.dept_no',ondelete='CASCADE')定义外键约束，且级联删除。
需求：查询10010员工的所在的部门编号，仅所需查员工信息表和员工部门表，而无需部门信息表
1、使用隐式内连接
results = session.query(Employee, Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() # 执行SQL，立刻消费show(results) # 打印数据这种方式产生隐式(即没有使用join)连接的语句：
SELECT *FROM employees, dept_emp WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s2、使用join(明显join)
# 查询10010员工所在的部门的编号# 第一种写法results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == 10010).all()# SELECT employees.emp_no AS employees_emp_no, employees.birth_date AS employees_birth_date, employees.first_name AS employees_first_name, employees.last_name AS employees_last_name, employees.gender AS employees_gender, employees.hire_date AS employees_hire_date FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no WHERE employees.emp_no = %(emp_no_1)s# 第二种写法results = session.query(Employee).join(Dept_emp,Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all()# SELECT employees.emp_no AS employees_emp_no, employees.birth_date AS employees_birth_date, employees.first_name AS employees_first_name, employees.last_name AS employees_last_name, employees.gender AS employees_gender, employees.hire_date AS employees_hire_date FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no WHERE employees.emp_no = %(emp_no_1)sprint(results)# [Employee no=10010 name=Duangkaew Piveteau genderF]这两种写法，返回只有一行数据，为什么？
原因在于query(Employee)这个只能返回一个实体对象中去，为了解决此问题，需要修改实体类Employee,增加属性用于存放部门信息。
也就是说，print(results)时，仅仅打印self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value，而这条数据只有一条。
因此需要增加Employee到departments的关系。
sqlalchemy.orm.relationship实体类名字符串。
from sqlalchemy.orm import relationshipclass Employee(Base): __tablename__ = 'employees' emp_no = Column(Integer, primary_key=True) birth_date = Column(Date, nullable=False) first_name = Column(String(14), nullable=False) last_name = Column(String(16), nullable=False) gender = Column(Enum(MyEnum), nullable=False) hire_date = Column(Date, nullable=False) dept_emps = relationship('Dept_emp') # def __repr__(self): return "{} no={} name={} {} gender{} {}".format( self.__class__.__name__,self.emp_no,self.first_name,self.last_name,self.gender.value,self.dept_emps )[Employee no=10010 name=Duangkaew Piveteau genderF [Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]]查询信息
# 查询10010员工所在的部门编号# 第一种results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010)# 第二种results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010)# 第三种results = session.query(Employee).join(Dept_emp,(Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010))第一种方法join(Demp_emp)中没有等值条件，会自动生成一个等值条件如果后面有filter，哪怕是filter(Employee.emp_no == Dept_emp.emp_no)，这个条件会在where中出现。第一种这种自动增加join的等值条件的方式不好，不要这么写。
第二种方法在join中增加了等值条件，阻止了自动的等值条件的生成，这种方式推荐。
第三种方法类似第二种，也可以。
总结
在开发中，一般都会采用ORM框架，这样就可以使用对象操作表。
如果表映射的类，使用Column的描述器定义类属性，使用ForeginKey来定义外键约束。
如果在一个对象中，想查看其他表对应的对象的内容，则使用relationship来定义关系。
是否使用外键？
1.使用。能使数据保证完整性和一致性。
2.不使用。开发难度增大，大数据的时候影响插入性能。应该在业务层上，保证事务的正常执行，保证数据的一致性。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
