Python-ORM

1、ORM

　　ORM：对象关系映射，对象和关系之间的映射，使用面向对象的方式来操作数据库

　　关系模型和 Python对象之间的映射

　　table ===> class , 表映射为类

　　row ===> object , 行映射为实例

　　column ===> property, 字段映射为属性

　　举例：

　　有表student，字段为 id int, name varchar, age int

　　映射到python中：

 1 class Student：
 2     id = ？某类型字段
 3     name = ？某类型字段
 4     age = ？某类型字段
 5 最终得到实例：
 6 
 7 class Student：
 8     def __init__（self）：
 9         self.id  =?
10         self.name = ?
11         self.age = ?

2、SQLAlchemy：

　　SQLAlchemy 是一个ORM框架

　　安装： $ pip install sqlachemy

　　文档：https://docs.sqlalchemy.org/en/latest/

　　查看版本：　　　　

import sqlalchemy
print(sqlalchemy.__version__)

　　开发：SQLAlchemy 内部使用了连接池

3、创建连接池　　

　　数据库连接的事情，交给引擎

　　　　dialect + driver：// username:password@host：port/ database

　　　　方言（dialect）：支持mysql， oracle等

　　　　驱动（dirver）：封装好的驱动，此处 pymysql， mysqldb只支持 python2

　　mysqldb的链接

1 mysql + mysqldb://<user>:<password>@<host>[:<posrt>]/<dbname>
2 enging = sqlalchemy,create_engine('mysql +mysqldb://root:123456@127.0.0.1:3306/test')

　　pymysql的链接(echo=True 表示开启debug功能，打印一些调试信息）

enging = create_engine('mysql+pymysql://root:123456@127.0.0.1:3306/testt'，echo=True)

　　注意：创建引擎并不会马上连接数据库，直到让数据库执行任务时才连接

4、Declare a Mapping 创建映射

 1 from sqlalchemy import create_engine,Column, Integer, String
 2 from sqlalchemy.ext.declarative import  declarative_base
 3 
 4 enging = create_engine('mysql+pymysql://root:123456@192.168.112.111:3306/testt', echo=True)
 5 
 6 
 7 # 创建 基类，所有的实体类必须继承此基类
 8 Base = declarative_base()
 9 
10 # 创建实体类
11 class Student(Base):
12     # 指定表名(必须指定）
13     __tablename__ = 'student'
14     # 定义类属性 ====>  对应字段，如果 属性 标识符(如 age） 跟字段名一致，则，Column中不需要指定，否则，根据需要指定
15     id = Column(Integer, primary_key=True, autoincrement=True)
16     name = Column(String(12), nullable=True)
17     age = Column(Integer)
18 
19     def __repr__(self):
20         return '{} id={} name={} age={}'.format(self.__class__.name, self.id,self.name, self.age)
21 
22 # 查看表结构
23 print(Student)
24 print(repr(Student.__table__))
25 '''
26 <class '__main__.Student'>
27 Table('student', MetaData(bind=None), Column('id', Integer(), table=<student>, primary_key=True, nullable=False), Column('name', String(length=12), table=<student>), Column('age', Integer(), table=<student>), schema=None)
28 '''

22 # 实例化 ===>  新建一行，对应 row
23 s = Student(name='tom')
24 s.age = 20 # 这里使用了描述性，因为Column是类，并实例化为一个实体类的属性
25 # print(s.age, s.name)
26 
27 # 创建表
28 # 可以使用SQLAlchemy 来创建表，删除表
29 # 删除继承自Base的所有表，所有的表，都是 归 Base 基类管理的
30 Base.metadata.drop_all(enging)
31 # 创建继承自Base的所有的表
32 Base.metadata.create_all(enging)
33 
34 '''
35 执行结果： 部分 echo=True 的信息：可以看到执行后的SQL 语句是否是自己想要的。
36 CREATE TABLE student (
37     id INTEGER NOT NULL AUTO_INCREMENT, 
38     name VARCHAR(12), 
39     age INTEGER, 
40     PRIMARY KEY (id)
41 )
42 
43 
44 2018-11-12 10:24:38,679 INFO sqlalchemy.engine.base.Engine {}
45 2018-11-12 10:24:38,740 INFO sqlalchemy.engine.base.Engine COMMIT
46 '''

　　注意：

　　　　生产环境总很少这样创建表，都是系统上线的时候由脚本生成。

　　　　生成环境很少删除表，宁可废弃，都不删除。

　　　　如果数据库中有表，只是对此表操作，实体类，字段的属性的定义事实上是可以省略的，为了方便阅读，还是写上基本的，如唯一键等，可以不写。

1 # 创建会话 session
2 # 在一个会话中操作数据库，会话建立在连接 上，连接被 引擎管理。
3 # 当第一次使用数据库时，从引擎维护的链接池中获取一个连接使用
4 Session = sessionmaker(bind=engine) # 返回一个 Session 类
5 session = Session() # 实例化一个session
6 # 依然在第一次使用时连接数据库
7 session 对象线程不安全，所以不同线程应该使用不同的session对象
8 Session 类和engine 有一个就行了

5、CRUD操作

　　增：

 1 # 增
 2 '''
 3 add() 增加一个对象
 4 add_all() 可迭代对象，元素是对象
 5 '''
 6 
 7 session.add(s)
 8 session.commit()
 9 
10 # 最好使用 try ，出现问题，一般都要回滚
11 try:
12     session.add_all([s]) # 如果多个实例，则使用 add_all
13     session.commit() # 提交 不成功。
14     print('=====================================')
15 except Exception as e:
16     session.rollback()
17     raise
18 # add_all() 方法 不会提交成功，是s 第一次提交之后，s的主键有了值，
19 # 所以，只要 s 没有修改过，就认为没有改动，
20 try:
21     s.name = 'lucy' # 这里 是 对之前的s 做了update操作，所以再次提交，就可以提交成功
22     session.add_all([s]) # 如果多个实例，则使用 add_all
23     session.commit() # 提交 不成功。
24 except Exception as e:
25     session.rollback()
26     raise

　　s 主键没有值，就是新增；主键有值，就是找到主键对应的记录修改，因为第一次提交后，通过打印发现 s.id 是有值的

　　简单查询：　　

1 # 使用 query() 方法，返回一个Query 对象
2 students= session.query(Student) # SELECT * FROM student
3 print('===', students) # 打印 发现只是一个SQL语句，并不是查询到的结果
4 for student in students:
5     print(student)
6 
7 # SELECT * FROM student where id = 2
8 student = session.query(Student).get(2) # 通过主键查询
9 print('---', student)

　　query 方法将实体类传入，返回类的对象可迭代对象，这时候不必查询，迭代它就执行SQL来查询数据库

　　封装数据到指定类的实例

　　get 方法使用主键查询，返回一条传入类的一个实例。

　　改：

1 student = session.query(Student).get(2)
2 print('========', student)
3 student.name = 'sam1'
4 student.age = 30
5 print('========',student)
6 session.add(student)
7 session.commit()

　　先查回来，在修改，再提交修改。

　　删：

1 # 删除
2 try:
3     student = Student(id=2, name='sam1',age=30)
4     session.delete(student)
5     session.commit()
6 except Exception as e:
7     session.rollback()
8     print(e)

　　但是，这是Student实体类实例化出的新的一个实例，虽然表中可能有此row，但是此时，删除的是这个新实例

　　，而这个实例并没有持久化（commit），所以报错：
　　　　　　　　　　　Instance '<Student at 0x38bfd30>' is not persisted

　　状态***

　　　　每一个实体，都有一个状态属性 _sa_instance_state，其类型是sqlalchemy.orm.state.instanceState

　　　　可以使用 sqlalchemy.inspect（entity）函数查看状态。

　　　　常见的状态值 transient（临时的），pending（预备好的），persistent（持久的），deleted（删除的），detached（分裂的）。

　　　　新建一个实体，状态是 transient 临时的

　　　　一旦add() 后，从transient 变成 pending状态

　　　　成功commit 后，变成persistent

　　　　成功查询返回的实体对象，也是persistent状态

　　　　persistent 状态的实体，修改依然是persisitent状态

　　　　persistent状态的实体，删除后，flush后但没有commit，就变成deleted状态，成功提交，变成detached状态

　　　　提交失败，还原到persistent状态，flush方法，主动把改变应用到数据库中。

　　删除，修改操作，需要对应一个真实的记录，所以要求实体对象是persistent状态

　　测试1：

 1 from sqlalchemy import create_engine,Column, Integer, String
 2 from sqlalchemy.orm import sessionmaker
 3 from sqlalchemy.ext.declarative import  declarative_base
 4 import sqlalchemy
 5 
 6 engine = create_engine('mysql+pymysql://root:123456@192.168.112.111:3306/test', echo=True)
 7 
 8 # 创建 基类，所有的实体类必须继承此基类
 9 Base = declarative_base()
10 
11 # 创建实体类
12 class Student(Base):
13     # 指定表名(必须指定）
14     __tablename__ = 'student'
15     # 定义类属性 ====>  对应字段
16     id = Column(Integer, primary_key=True, autoincrement=True)
17     name = Column(String(12), nullable=True)
18     age = Column(Integer)
19 
20     def __repr__(self):
21         return '{} id={} name={} age={}'.format(self.__class__.__name__, self.id,self.name, self.age)
22 
23 Session = sessionmaker(bind=engine)
24 session = Session()
25 
26 from sqlalchemy.orm.state import InstanceState
27 def getstate(entity,i):
28     insp = sqlalchemy.inspect(entity)
29     state = 'session={}, attache={}\n' \
30             'transient={}, persistent={}\n' \
31             'pending={}, deleted={}, detached={}'.format(
32         insp.session_id,
33         insp._attached,
34         insp.transient,
35         insp.persistent,
36         insp.pending,
37         insp.deleted,
38         insp.detached)
39     print(i, state)
40     print(insp.key)
41 
42 student = session.query(Student).get(2)
43 getstate(student, 1)
44 
45 try:
46     student = Student(id=2,name='sam', age=30)
47     getstate(student, 2)
48 
49     student = Student(name='sammy', age=30)
50     getstate(student, 3)
51 
52     session.add(student)
53     getstate(student, 4)
54 
55     session.commit()
56     getstate(student, 5)
57 
58 except Exception as e:
59     session.rollback()
60     print(e)

　　结果：

 1 1 session=1, attache=True
 2 transient=False, persistent=True
 3 pending=False, deleted=False, detached=False
 4 (<class '__main__.Student'>, (2,), None)
 5 2 session=None, attache=False
 6 transient=True, persistent=False
 7 pending=False, deleted=False, detached=False
 8 None
 9 3 session=None, attache=False
10 transient=True, persistent=False
11 pending=False, deleted=False, detached=False
12 None
13 4 session=1, attache=True
14 transient=False, persistent=False
15 pending=True, deleted=False, detached=False
16 None
17 5 session=1, attache=True
18 transient=False, persistent=True
19 pending=False, deleted=False, detached=False
20 (<class '__main__.Student'>, (6,), None)

结果

　　测试 2：

 1 student = session.query(Student).get(3)
 2 print(student)
 3 getstate(student, 1)
 4 
 5 try:
 6     # 删除的前提是 persistent
 7     session.delete(student)
 8     getstate(student, 2) # 没有flush，还是persistent
 9     session.flush()
10     getstate(student, 3)
11     session.commit()
12     getstate(student, 4)
13 except Exception as e:
14     session.rollback()
15     print(e)

　　结果2：

 1 D:\python3.7\python.exe E:/code_pycharm/test_in_class/tt17.py
 2 Student id=3 name=tom age=20
 3 1 session=1, attache=True
 4 transient=False, persistent=True
 5 pending=False, deleted=False, detached=False
 6 (<class '__main__.Student'>, (3,), None)
 7 2 session=1, attache=True
 8 transient=False, persistent=True
 9 pending=False, deleted=False, detached=False
10 (<class '__main__.Student'>, (3,), None)
11 3 session=1, attache=True
12 transient=False, persistent=False
13 pending=False, deleted=True, detached=False
14 (<class '__main__.Student'>, (3,), None)
15 4 session=None, attache=False
16 transient=False, persistent=False
17 pending=False, deleted=False, detached=True
18 (<class '__main__.Student'>, (3,), None)
19 
20 Process finished with exit code 0

结果2

　　复杂查询：

  1 from sqlalchemy import create_engine,Column, Integer, String, Date
  2 from sqlalchemy import  Enum, ForeignKey, create_engine
  3 from sqlalchemy.orm import sessionmaker
  4 from sqlalchemy.ext.declarative import  declarative_base
  5 import sqlalchemy
  6 
  7 import enum # 这里使用的是python 提供的 枚举，sqlalchemy 提供的enum 不能使用
  8 
  9 class MyEnum(enum.Enum):
 10     M = 'M'
 11     F = 'F'
 12 
 13 
 14 engine = create_engine('mysql+pymysql://root:123456@192.168.112.111:3306/test', echo =True)
 15 Session = sessionmaker(bind=engine)
 16 session = Session()
 17 
 18 Base = declarative_base()
 19 
 20 class Employee(Base):
 21     __tablename__ = 'employees'
 22     emp_no = Column(Integer, primary_key=True)
 23     birth_date = Column()
 24     first_name = Column()
 25     last_name = Column()
 26     gender = Column(Enum(MyEnum))
 27     hire_date = Column(Date)
 28 
 29     def __repr__(self):
 30         return "{} no={} name='{} {}' !!!gender={}  gender={}".format(
 31             self.__class__.__name__,self.emp_no, self.first_name,self.last_name,
 32             self.gender,
 33             self.gender.value
 34         )
 35 
 36 # 打印函数
 37 def show(emps):
 38     for x in emps:
 39         print(x)
 40     print('======'*15, end='\n\n')
 41 
 42 # 简单条件查询
 43 emps = session.query(Employee).filter(Employee.emp_no > 10015)
 44 show(emps)
 45 
 46 # 与或非
 47 from sqlalchemy import or_, and_, not_， 其他的是自带的，不需要导入
 48 # AND 条件
 49 # 方式 1： 多级filter 表示 AND，注意一点，这是Python代码，所以不能以SQL 语句的条件判断符 表示，例如 =，<=>, <>
 50 emps = session.query(Employee).filter(Employee.emp_no > 10015).filter(Employee.gender ==MyEnum.F)
 51 show(emps)
 52 # 方式 2：使用 模块提供 and_
 53 emps = session.query(Employee).filter(and_(Employee.emp_no > 10015, Employee.gender == MyEnum.F))
 54 show(emps)
 55 # 方式 3： 使用 & 符号, 但是注意，& 两边的条件，要用括号，负责可能出现问题。
 56 emps = session.query(Employee).filter((Employee.emp_no >10015) & (Employee.gender == MyEnum.F))
 57 show(emps)
 58 
 59 # OR 条件
 60 # 方式 1： | 符号，注意括号
 61 emps = session.query(Employee).filter((Employee.emp_no > 10018) | (Employee.emp_no < 10003))
 62 show(emps)
 63 # 方式 2： or_
 64 emps = session.query(Employee).filter(or_(Employee.emp_no > 10018, Employee.emp_no < 10003))
 65 show(emps)
 66 
 67 # NOT 条件
 68 # 方式 1：not_
 69 emps = session.query(Employee).filter(not_(Employee.emp_no < 10018))
 70 show(emps)
 71 # 方式 2： ~
 72 emps = session.query(Employee).filter(~(Employee.emp_no < 10018))
 73 show(emps)
 74 
 75 # in 操作
 76 emplist = [10010, 10012, 10018, 1000000000]
 77 emps = session.query(Employee).filter(Employee.emp_no.in_(emplist))
 78 show(emps)
 79 
 80 # not in 操作
 81 emps = session.query(Employee).filter(Employee.emp_no.notin_(emplist))
 82 show(emps)
 83 
 84 # like
 85 emps = session.query(Employee).filter(Employee.last_name.like('P%'))
 86 show(emps)
 87 # not like
 88 emps = session.query(Employee).filter(Employee.last_name.notlike('P%'))
 89 show(emps)
 90 
 91 # ilike :不区分大小写
 92 
 93 # 排序
 94 # 升序
 95 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no)
 96 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.asc())
 97 show(emps)
 98 # 降序
 99 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.desc())
100 show(emps)
101 
102 # 多列排序 :select * from Employee where emp_no > 10010 order by first_name, last_name desc
103 emps = session.query(Employee).filter(Employee.emp_no > 10010
104                                       ).order_by(Employee.last_name).order_by(Employee.last_name.desc())
105 show(emps)
106 
107 #  分页
108 emps = session.query(Employee).limit(4)
109 show(emps)
110 emps = session.query(Employee).limit(4).offset(3) # 位移 3 个，显示 4 个
111 show(emps)

　　　结果：

  1 D:\python3.7\python.exe E:/code_pycharm/test_in_class/tt17.py
  2 2018-11-12 16:00:20,499 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
  3 2018-11-12 16:00:20,500 INFO sqlalchemy.engine.base.Engine {}
  4 2018-11-12 16:00:20,501 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
  5 2018-11-12 16:00:20,501 INFO sqlalchemy.engine.base.Engine {}
  6 2018-11-12 16:00:20,503 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
  7 2018-11-12 16:00:20,503 INFO sqlalchemy.engine.base.Engine {}
  8 2018-11-12 16:00:20,506 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
  9 2018-11-12 16:00:20,506 INFO sqlalchemy.engine.base.Engine {}
 10 2018-11-12 16:00:20,508 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
 11 2018-11-12 16:00:20,508 INFO sqlalchemy.engine.base.Engine {}
 12 2018-11-12 16:00:20,509 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
 13 2018-11-12 16:00:20,509 INFO sqlalchemy.engine.base.Engine {}
 14 2018-11-12 16:00:20,510 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
 15 2018-11-12 16:00:20,510 INFO sqlalchemy.engine.base.Engine {}
 16 2018-11-12 16:00:20,512 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
 17 2018-11-12 16:00:20,513 INFO sqlalchemy.engine.base.Engine 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 
 18 FROM employees 
 19 WHERE employees.emp_no > %(emp_no_1)s
 20 2018-11-12 16:00:20,513 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015}
 21 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M  gender=M
 22 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
 23 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
 24 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
 25 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
 26 ==========================================================================================
 27 
 28 2018-11-12 16:00:20,516 INFO sqlalchemy.engine.base.Engine 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 
 29 FROM employees 
 30 WHERE employees.emp_no > %(emp_no_1)s AND employees.gender = %(gender_1)s
 31 2018-11-12 16:00:20,516 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'gender_1': 'F'}
 32 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
 33 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
 34 ==========================================================================================
 35 
 36 2018-11-12 16:00:20,519 INFO sqlalchemy.engine.base.Engine 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 
 37 FROM employees 
 38 WHERE employees.emp_no > %(emp_no_1)s AND employees.gender = %(gender_1)s
 39 2018-11-12 16:00:20,519 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'gender_1': 'F'}
 40 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
 41 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
 42 ==========================================================================================
 43 
 44 2018-11-12 16:00:20,521 INFO sqlalchemy.engine.base.Engine 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 
 45 FROM employees 
 46 WHERE employees.emp_no > %(emp_no_1)s AND employees.gender = %(gender_1)s
 47 2018-11-12 16:00:20,521 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'gender_1': 'F'}
 48 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
 49 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
 50 ==========================================================================================
 51 
 52 2018-11-12 16:00:20,523 INFO sqlalchemy.engine.base.Engine 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 
 53 FROM employees 
 54 WHERE employees.emp_no > %(emp_no_1)s OR employees.emp_no < %(emp_no_2)s
 55 2018-11-12 16:00:20,523 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018, 'emp_no_2': 10003}
 56 Employee no=10001 name='Georgi Facello' !!!gender=MyEnum.M  gender=M
 57 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F  gender=F
 58 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
 59 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
 60 ==========================================================================================
 61 
 62 2018-11-12 16:00:20,526 INFO sqlalchemy.engine.base.Engine 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 
 63 FROM employees 
 64 WHERE employees.emp_no > %(emp_no_1)s OR employees.emp_no < %(emp_no_2)s
 65 2018-11-12 16:00:20,526 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018, 'emp_no_2': 10003}
 66 Employee no=10001 name='Georgi Facello' !!!gender=MyEnum.M  gender=M
 67 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F  gender=F
 68 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
 69 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
 70 ==========================================================================================
 71 
 72 2018-11-12 16:00:20,527 INFO sqlalchemy.engine.base.Engine 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 
 73 FROM employees 
 74 WHERE employees.emp_no >= %(emp_no_1)s
 75 2018-11-12 16:00:20,528 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018}
 76 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
 77 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
 78 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
 79 ==========================================================================================
 80 
 81 2018-11-12 16:00:20,530 INFO sqlalchemy.engine.base.Engine 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 
 82 FROM employees 
 83 WHERE employees.emp_no >= %(emp_no_1)s
 84 2018-11-12 16:00:20,530 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018}
 85 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
 86 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
 87 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
 88 ==========================================================================================
 89 
 90 2018-11-12 16:00:20,532 INFO sqlalchemy.engine.base.Engine 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 
 91 FROM employees 
 92 WHERE employees.emp_no IN (%(emp_no_1)s, %(emp_no_2)s, %(emp_no_3)s, %(emp_no_4)s)
 93 2018-11-12 16:00:20,533 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010, 'emp_no_2': 10012, 'emp_no_3': 10018, 'emp_no_4': 1000000000}
 94 Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F
 95 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M  gender=M
 96 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
 97 ==========================================================================================
 98 
 99 2018-11-12 16:00:20,535 INFO sqlalchemy.engine.base.Engine 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 
100 FROM employees 
101 WHERE employees.emp_no NOT IN (%(emp_no_1)s, %(emp_no_2)s, %(emp_no_3)s, %(emp_no_4)s)
102 2018-11-12 16:00:20,535 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010, 'emp_no_2': 10012, 'emp_no_3': 10018, 'emp_no_4': 1000000000}
103 Employee no=10001 name='Georgi Facello' !!!gender=MyEnum.M  gender=M
104 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F  gender=F
105 Employee no=10003 name='Parto Bamford' !!!gender=MyEnum.M  gender=M
106 Employee no=10004 name='Chirstian Koblick' !!!gender=MyEnum.M  gender=M
107 Employee no=10005 name='Kyoichi Maliniak' !!!gender=MyEnum.M  gender=M
108 Employee no=10006 name='Anneke Preusig' !!!gender=MyEnum.F  gender=F
109 Employee no=10007 name='Tzvetan Zielinski' !!!gender=MyEnum.F  gender=F
110 Employee no=10008 name='Saniya Kalloufi' !!!gender=MyEnum.M  gender=M
111 Employee no=10009 name='Sumant Peac' !!!gender=MyEnum.F  gender=F
112 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F  gender=F
113 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M  gender=M
114 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M  gender=M
115 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M  gender=M
116 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M  gender=M
117 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
118 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
119 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
120 ==========================================================================================
121 
122 2018-11-12 16:00:20,538 INFO sqlalchemy.engine.base.Engine 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 
123 FROM employees 
124 WHERE employees.last_name LIKE %(last_name_1)s
125 2018-11-12 16:00:20,538 INFO sqlalchemy.engine.base.Engine {'last_name_1': 'P%'}
126 Employee no=10006 name='Anneke Preusig' !!!gender=MyEnum.F  gender=F
127 Employee no=10009 name='Sumant Peac' !!!gender=MyEnum.F  gender=F
128 Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F
129 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
130 ==========================================================================================
131 
132 2018-11-12 16:00:20,540 INFO sqlalchemy.engine.base.Engine 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 
133 FROM employees 
134 WHERE employees.last_name NOT LIKE %(last_name_1)s
135 2018-11-12 16:00:20,540 INFO sqlalchemy.engine.base.Engine {'last_name_1': 'P%'}
136 Employee no=10001 name='Georgi Facello' !!!gender=MyEnum.M  gender=M
137 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F  gender=F
138 Employee no=10003 name='Parto Bamford' !!!gender=MyEnum.M  gender=M
139 Employee no=10004 name='Chirstian Koblick' !!!gender=MyEnum.M  gender=M
140 Employee no=10005 name='Kyoichi Maliniak' !!!gender=MyEnum.M  gender=M
141 Employee no=10007 name='Tzvetan Zielinski' !!!gender=MyEnum.F  gender=F
142 Employee no=10008 name='Saniya Kalloufi' !!!gender=MyEnum.M  gender=M
143 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F  gender=F
144 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M  gender=M
145 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M  gender=M
146 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M  gender=M
147 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M  gender=M
148 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M  gender=M
149 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
150 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
151 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
152 ==========================================================================================
153 
154 2018-11-12 16:00:20,543 INFO sqlalchemy.engine.base.Engine 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 
155 FROM employees 
156 WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.emp_no ASC
157 2018-11-12 16:00:20,543 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
158 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F  gender=F
159 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M  gender=M
160 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M  gender=M
161 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M  gender=M
162 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M  gender=M
163 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M  gender=M
164 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
165 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
166 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
167 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
168 ==========================================================================================
169 
170 2018-11-12 16:00:20,546 INFO sqlalchemy.engine.base.Engine 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 
171 FROM employees 
172 WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.emp_no DESC
173 2018-11-12 16:00:20,546 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
174 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
175 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
176 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
177 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
178 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M  gender=M
179 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M  gender=M
180 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M  gender=M
181 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M  gender=M
182 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M  gender=M
183 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F  gender=F
184 ==========================================================================================
185 
186 2018-11-12 16:00:20,548 INFO sqlalchemy.engine.base.Engine 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 
187 FROM employees 
188 WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.last_name, employees.last_name DESC
189 2018-11-12 16:00:20,549 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
190 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F  gender=F
191 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M  gender=M
192 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M  gender=M
193 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M  gender=M
194 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M
195 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M  gender=M
196 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
197 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F  gender=F
198 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M  gender=M
199 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M
200 ==========================================================================================
201 
202 2018-11-12 16:00:20,551 INFO sqlalchemy.engine.base.Engine 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 
203 FROM employees 
204  LIMIT %(param_1)s
205 2018-11-12 16:00:20,551 INFO sqlalchemy.engine.base.Engine {'param_1': 4}
206 Employee no=10001 name='Georgi Facello' !!!gender=MyEnum.M  gender=M
207 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F  gender=F
208 Employee no=10003 name='Parto Bamford' !!!gender=MyEnum.M  gender=M
209 Employee no=10004 name='Chirstian Koblick' !!!gender=MyEnum.M  gender=M
210 ==========================================================================================
211 
212 2018-11-12 16:00:20,553 INFO sqlalchemy.engine.base.Engine 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 
213 FROM employees 
214  LIMIT %(param_1)s, %(param_2)s
215 2018-11-12 16:00:20,553 INFO sqlalchemy.engine.base.Engine {'param_1': 3, 'param_2': 4}
216 Employee no=10004 name='Chirstian Koblick' !!!gender=MyEnum.M  gender=M
217 Employee no=10005 name='Kyoichi Maliniak' !!!gender=MyEnum.M  gender=M
218 Employee no=10006 name='Anneke Preusig' !!!gender=MyEnum.F  gender=F
219 Employee no=10007 name='Tzvetan Zielinski' !!!gender=MyEnum.F  gender=F
220 ==========================================================================================
221 
222 
223 Process finished with exit code 0

结果

 1 # 消费者方法：
 2 # 消费者方法调用后，Query 对象（可迭代） 就转换成了一个容器
 3 
 4 # 总行数
 5 emps = session.query(Employee).filter(Employee.emp_no >10017)
 6 
 7 print(len(list(emps))) # 返回大量的结果，用list查看，求len
 8 print(emps.count())# 聚合函数count(*) 的查询
 9 
10 # 去所有数据：
11 print(emps.all()) # 返回列表，查不到返回空列表
12 
13 # 取首行
14 print(emps.first())# 查不到，返回None
15 
16 # 有且只能有一行
17 # print(emps.one()) # 查询 结构多行，抛异常
18 print(emps.limit(1).one())
19 
20 # 删除 delete by query
21 s = session.query(Employee).filter(Employee.emp_no > 10018).delete()
22 print(s) # 返回删除的行数
23 ## 删除后，需要提交，才能生效。 session.commit()

　　结果：

1 3
2 3
3 [Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F, Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M  gender=M, Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M  gender=M]
4 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
5 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F  gender=F
6 2

结果

 1 # 聚合 分组
 2 # 聚合函数
 3 from sqlalchemy import func
 4 query = session.query(func.count(Employee.emp_no))
 5 print(query)
 6 print(query.one()) # 只能有一行结果
 7 print(query.scalar())  # 取 one() 返回元组的第一个元素
 8 
 9 # max  min  avg
10 print(session.query(func.max(Employee.emp_no)).scalar())
11 print(session.query(func.min(Employee.emp_no)).scalar())
12 print(session.query(func.avg(Employee.emp_no)).scalar())
13 
14 print('======'*15, end='\n\n')
15 # 分组
16 print(session.query(Employee.gender,
17     func.count(Employee.emp_no)).group_by(Employee.gender).all())

　　结果：

 1 D:\python3.7\python.exe E:/code_pycharm/test_in_class/tt17.py
 2 SELECT count(employees.emp_no) AS count_1 
 3 FROM employees
 4 2018-11-12 16:18:22,426 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
 5 2018-11-12 16:18:22,426 INFO sqlalchemy.engine.base.Engine {}
 6 2018-11-12 16:18:22,428 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
 7 2018-11-12 16:18:22,428 INFO sqlalchemy.engine.base.Engine {}
 8 2018-11-12 16:18:22,430 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
 9 2018-11-12 16:18:22,430 INFO sqlalchemy.engine.base.Engine {}
10 2018-11-12 16:18:22,431 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
11 2018-11-12 16:18:22,431 INFO sqlalchemy.engine.base.Engine {}
12 2018-11-12 16:18:22,433 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
13 2018-11-12 16:18:22,433 INFO sqlalchemy.engine.base.Engine {}
14 2018-11-12 16:18:22,434 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
15 2018-11-12 16:18:22,434 INFO sqlalchemy.engine.base.Engine {}
16 2018-11-12 16:18:22,435 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
17 2018-11-12 16:18:22,435 INFO sqlalchemy.engine.base.Engine {}
18 2018-11-12 16:18:22,436 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
19 2018-11-12 16:18:22,437 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 
20 FROM employees
21 2018-11-12 16:18:22,437 INFO sqlalchemy.engine.base.Engine {}
22 (20,)
23 2018-11-12 16:18:22,439 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 
24 FROM employees
25 2018-11-12 16:18:22,439 INFO sqlalchemy.engine.base.Engine {}
26 20
27 2018-11-12 16:18:22,440 INFO sqlalchemy.engine.base.Engine SELECT max(employees.emp_no) AS max_1 
28 FROM employees
29 2018-11-12 16:18:22,440 INFO sqlalchemy.engine.base.Engine {}
30 10020
31 2018-11-12 16:18:22,441 INFO sqlalchemy.engine.base.Engine SELECT min(employees.emp_no) AS min_1 
32 FROM employees
33 2018-11-12 16:18:22,441 INFO sqlalchemy.engine.base.Engine {}
34 10001
35 2018-11-12 16:18:22,443 INFO sqlalchemy.engine.base.Engine SELECT avg(employees.emp_no) AS avg_1 
36 FROM employees
37 2018-11-12 16:18:22,443 INFO sqlalchemy.engine.base.Engine {}
38 10010.5000
39 ==========================================================================================
40 
41 2018-11-12 16:18:22,445 INFO sqlalchemy.engine.base.Engine SELECT employees.gender AS employees_gender, count(employees.emp_no) AS count_1 
42 FROM employees GROUP BY employees.gender
43 2018-11-12 16:18:22,445 INFO sqlalchemy.engine.base.Engine {}
44 [(<MyEnum.M: 'M'>, 12), (<MyEnum.F: 'F'>, 8)]
45 
46 Process finished with exit code 0

结果

　　关联查询：三张表

 1 CREATE TABLE `employees` (
 2   `emp_no` int(11) NOT NULL,
 3   `birth_date` date NOT NULL,
 4   `first_name` varchar(14) NOT NULL,
 5   `last_name` varchar(16) NOT NULL,
 6   `gender` enum('M','F') NOT NULL,
 7   `hire_date` date NOT NULL,
 8   PRIMARY KEY (`emp_no`)
 9 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
10 
11 
12 
13 CREATE TABLE `departments` (
14   `dept_no` char(4) NOT NULL,
15   `dept_name` varchar(40) NOT NULL,
16   PRIMARY KEY (`dept_no`),
17   UNIQUE KEY `dept_name` (`dept_name`)
18 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
19 
20 
21 
22 CREATE TABLE `dept_emp` (
23   `emp_no` int(11) NOT NULL,
24   `dept_no` char(4) NOT NULL,
25   `from_date` date NOT NULL,
26   `to_date` date NOT NULL,
27   PRIMARY KEY (`emp_no`,`dept_no`),
28   KEY `dept_no` (`dept_no`),
29   CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,
30   CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) REFERENCES `departments` (`dept_no`) ON DELETE CASCADE
31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　dept_emp的设计表：

　　　　外键：

　　从语句看出，员工和部门之间的关系是多对多的关系，先把这些表的Model类和字段属性建立起来。

　　需求，查询10010员工 所在的部门编号及员工信息

 1 from sqlalchemy import create_engine,Column, Integer, String, Date
 2 from sqlalchemy import  Enum, ForeignKey, create_engine
 3 from sqlalchemy.orm import sessionmaker
 4 from sqlalchemy.ext.declarative import  declarative_base
 5 import sqlalchemy
 6 
 7 import enum # 这里使用的是python 提供的 枚举，sqlalchemy 提供的enum 不能使用
 8 
 9 class MyEnum(enum.Enum):
10     M = 'M'
11     F = 'F'
12 
13 
14 engine = create_engine('mysql+pymysql://root:123456@192.168.112.111:3306/test', echo=True)
15 Session = sessionmaker(bind=engine)
16 session = Session()
17 
18 Base = declarative_base()
19 
20 class Employee(Base):
21     __tablename__ = 'employees'
22     emp_no = Column(Integer, primary_key=True)
23     birth_date = Column()
24     first_name = Column()
25     last_name = Column()
26     gender = Column(Enum(MyEnum))
27     hire_date = Column(Date)
28 
29     def __repr__(self):
30         return "{} no={} name='{} {}' !!!gender={}  gender={}".format(
31             self.__class__.__name__,self.emp_no, self.first_name,self.last_name,
32             self.gender,
33             self.gender.value
34         )
35 
36 class Department(Base):
37     __tablename__ = 'departments'
38     dept_no = Column(primary_key=True)
39     dept_name = Column(unique=True)
40 
41     def __repr__(self):
42         return '{} no={} name={}'.format(
43             type(self).__name__,self.dept_no,self.dept_name
44         )
45 
46 class Dept_emp(Base):
47     __tablename__ = 'dept_emp'
48 
49     emp_no = Column(Integer, ForeignKey('employees.emp_no',ondelete='CASCADE'),primary_key=True)
50     dept_no = Column(ForeignKey('departments.dept_no',ondelete='CASCADE'),primary_key=True)
51     from_date = Column()
52     to_date = Column()
53 
54     def __repr__(self):
55         return '{} empno={} deptno={}'.format(
56             type(self).__name__,self.emp_no,self.dept_no)
57 
58 def show(emps):
59     for x in emps:
60         print(x)
61     print('======'*15, end='\n\n')
62 
63 '''
64 需求，查询10010员工 所在的部门编号及员工信息
65 '''
66 
67 # 1、使用隐式内连接
68 # 查询10010员工的所在部门编号及员工信息
69 results = session.query(Employee, Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all()
70 print(results)
71 show(results)
72 
73 # 2、使用 join
74 # 写法 1： 默认是 inner join
75 results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no ==10010).all()
76 print(results)
77 show(results)
78 '''
79 可以看到默认会添加 ON 条件，但是可能会不准确，这里利用主键，所以是所需结果
80 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no 
81 WHERE employees.emp_no = %(emp_no_1)s
82 '''
83 # 写法 2：手动提供条件
84 results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no ==10010).all()
85 print(results)
86 show(results)

　　结果：

 1 D:\python3.7\python.exe E:/code_pycharm/test_in_class/tt17.py
 2 2018-11-12 17:25:26,515 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
 3 2018-11-12 17:25:26,515 INFO sqlalchemy.engine.base.Engine {}
 4 2018-11-12 17:25:26,517 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
 5 2018-11-12 17:25:26,517 INFO sqlalchemy.engine.base.Engine {}
 6 2018-11-12 17:25:26,519 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
 7 2018-11-12 17:25:26,519 INFO sqlalchemy.engine.base.Engine {}
 8 2018-11-12 17:25:26,522 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
 9 2018-11-12 17:25:26,522 INFO sqlalchemy.engine.base.Engine {}
10 2018-11-12 17:25:26,524 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
11 2018-11-12 17:25:26,524 INFO sqlalchemy.engine.base.Engine {}
12 2018-11-12 17:25:26,525 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
13 2018-11-12 17:25:26,525 INFO sqlalchemy.engine.base.Engine {}
14 2018-11-12 17:25:26,526 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
15 2018-11-12 17:25:26,526 INFO sqlalchemy.engine.base.Engine {}
16 2018-11-12 17:25:26,528 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
17 2018-11-12 17:25:26,529 INFO sqlalchemy.engine.base.Engine 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, dept_emp.emp_no AS dept_emp_emp_no, dept_emp.dept_no AS dept_emp_dept_no, dept_emp.from_date AS dept_emp_from_date, dept_emp.to_date AS dept_emp_to_date 
18 FROM employees, dept_emp 
19 WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s
20 2018-11-12 17:25:26,529 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
21 [(Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F, Dept_emp empno=10010 deptno=d004), (Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F, Dept_emp empno=10010 deptno=d006)]
22 (Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F, Dept_emp empno=10010 deptno=d004)
23 (Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F, Dept_emp empno=10010 deptno=d006)
24 ==========================================================================================
25 
26 2018-11-12 17:25:26,532 INFO sqlalchemy.engine.base.Engine 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 
27 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no 
28 WHERE employees.emp_no = %(emp_no_1)s
29 2018-11-12 17:25:26,532 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
30 [Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F]
31 Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F
32 ==========================================================================================
33 
34 2018-11-12 17:25:26,534 INFO sqlalchemy.engine.base.Engine 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 
35 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no 
36 WHERE employees.emp_no = %(emp_no_1)s
37 2018-11-12 17:25:26,534 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
38 [Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F]
39 Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F  gender=F
40 ==========================================================================================
41 
42 
43 Process finished with exit code 0

View Code

　　从表中看出，10010员工对应两个部门，本应显示两个，而且也没有部门信息，像第一种隐式方式，但是使用 join 方式只显示一个

　　是因为：results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no ==10010).all()

　　只返回一个实体对象，解决方法有两种，

　　　　一是，像隐式那样，将两个实体都传进去

　　　　二是，使用sqlalchemy.orm.relationship( 实体类名字符串）

　　测试第二种方法：

 1 from sqlalchemy import create_engine,Column, Integer, String, Date
 2 from sqlalchemy import  Enum, ForeignKey, create_engine
 3 from sqlalchemy.orm import sessionmaker, relationship
 4 from sqlalchemy.ext.declarative import  declarative_base
 5 import sqlalchemy
 6 
 7 import enum # 这里使用的是python 提供的 枚举，sqlalchemy 提供的enum 不能使用
 8 
 9 class MyEnum(enum.Enum):
10     M = 'M'
11     F = 'F'
12 
13 
14 engine = create_engine('mysql+pymysql://root:123456@192.168.112.111:3306/test', echo=True)
15 Session = sessionmaker(bind=engine)
16 session = Session()
17 
18 Base = declarative_base()
19 
20 class Employee(Base):
21     __tablename__ = 'employees'
22     emp_no = Column(Integer, primary_key=True)
23     birth_date = Column()
24     first_name = Column()
25     last_name = Column()
26     gender = Column(Enum(MyEnum))
27     hire_date = Column(Date)
28 
29     # 因为从员工表获取 找部门，所以，关系，放在此处
30     departements = relationship('Dept_emp') # 关系，只是从一张表找另一张表用，并不是字段
31 
32     def __repr__(self):
33         return "{} no={} name='{} {}'gender={} deps={}".format(
34             self.__class__.__name__,self.emp_no, self.first_name,self.last_name,
35             self.gender.value,
36             self.departements
37         )
38 
39 class Department(Base):
40     __tablename__ = 'departments'
41     dept_no = Column(primary_key=True)
42     dept_name = Column(unique=True)
43 
44     def __repr__(self):
45         return '{} no={} name={}'.format(
46             type(self).__name__,self.dept_no,self.dept_name
47         )
48 
49 class Dept_emp(Base):
50     __tablename__ = 'dept_emp'
51 
52     emp_no = Column(Integer, ForeignKey('employees.emp_no',ondelete='CASCADE'),primary_key=True)
53     dept_no = Column(ForeignKey('departments.dept_no',ondelete='CASCADE'),primary_key=True)
54     from_date = Column()
55     to_date = Column()
56 
57     def __repr__(self):
58         return '{} empno={} deptno={}'.format(
59             type(self).__name__,self.emp_no,self.dept_no)
60 
61 def show(emps):
62     for x in emps:
63         print(x)
64     print('======'*15, end='\n\n')
65 
66 '''
67 需求，查询10010员工 所在的部门编号及员工信息
68 '''
69 
70 # 第一种,因为没有提供等值条件，所以会自动生成join 条件，不推荐
71 results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010)
72 print(results.all())
73 
74 # 第二种
75 results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no ==10010)
76 print(results.all())
77 
78 # 第三种
79 results = session.query(Employee).join(Dept_emp, (Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010))
80 print(results.all())

　　结果：

 1 D:\python3.7\python.exe E:/code_pycharm/test_in_class/tt17.py
 2 2018-11-12 17:49:18,365 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
 3 2018-11-12 17:49:18,365 INFO sqlalchemy.engine.base.Engine {}
 4 2018-11-12 17:49:18,367 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
 5 2018-11-12 17:49:18,367 INFO sqlalchemy.engine.base.Engine {}
 6 2018-11-12 17:49:18,368 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
 7 2018-11-12 17:49:18,369 INFO sqlalchemy.engine.base.Engine {}
 8 2018-11-12 17:49:18,370 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
 9 2018-11-12 17:49:18,370 INFO sqlalchemy.engine.base.Engine {}
10 2018-11-12 17:49:18,372 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
11 2018-11-12 17:49:18,372 INFO sqlalchemy.engine.base.Engine {}
12 2018-11-12 17:49:18,373 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
13 2018-11-12 17:49:18,373 INFO sqlalchemy.engine.base.Engine {}
14 2018-11-12 17:49:18,374 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
15 2018-11-12 17:49:18,374 INFO sqlalchemy.engine.base.Engine {}
16 2018-11-12 17:49:18,376 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
17 2018-11-12 17:49:18,377 INFO sqlalchemy.engine.base.Engine 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 
18 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no 
19 WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s
20 2018-11-12 17:49:18,377 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
21 2018-11-12 17:49:18,380 INFO sqlalchemy.engine.base.Engine SELECT dept_emp.emp_no AS dept_emp_emp_no, dept_emp.dept_no AS dept_emp_dept_no, dept_emp.from_date AS dept_emp_from_date, dept_emp.to_date AS dept_emp_to_date 
22 FROM dept_emp 
23 WHERE %(param_1)s = dept_emp.emp_no
24 2018-11-12 17:49:18,380 INFO sqlalchemy.engine.base.Engine {'param_1': 10010}
25 [Employee no=10010 name='Duangkaew Piveteau'gender=F deps=[Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]]
26 2018-11-12 17:49:18,382 INFO sqlalchemy.engine.base.Engine 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 
27 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no 
28 WHERE employees.emp_no = %(emp_no_1)s
29 2018-11-12 17:49:18,382 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
30 2018-11-12 17:49:18,384 INFO sqlalchemy.engine.base.Engine SELECT dept_emp.emp_no AS dept_emp_emp_no, dept_emp.dept_no AS dept_emp_dept_no, dept_emp.from_date AS dept_emp_from_date, dept_emp.to_date AS dept_emp_to_date 
31 FROM dept_emp 
32 WHERE %(param_1)s = dept_emp.emp_no
33 2018-11-12 17:49:18,384 INFO sqlalchemy.engine.base.Engine {'param_1': 10010}
34 [Employee no=10010 name='Duangkaew Piveteau'gender=F deps=[Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]]
35 2018-11-12 17:49:18,386 INFO sqlalchemy.engine.base.Engine 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 
36 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s
37 2018-11-12 17:49:18,386 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}
38 2018-11-12 17:49:18,387 INFO sqlalchemy.engine.base.Engine SELECT dept_emp.emp_no AS dept_emp_emp_no, dept_emp.dept_no AS dept_emp_dept_no, dept_emp.from_date AS dept_emp_from_date, dept_emp.to_date AS dept_emp_to_date 
39 FROM dept_emp 
40 WHERE %(param_1)s = dept_emp.emp_no
41 2018-11-12 17:49:18,387 INFO sqlalchemy.engine.base.Engine {'param_1': 10010}
42 [Employee no=10010 name='Duangkaew Piveteau'gender=F deps=[Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]]
43 
44 Process finished with exit code 0

View Code

[Employee no=10010 name='Duangkaew Piveteau'gender=F deps=[Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]]

　可以看出：　一个emp_no 分配两个 dep_no

注意：

1 results = session.query(Employee).join(Dept_emp, (Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010))
2 print(results.all())
3 for x in results:
4     print(x)
5     print(x.emp_no)
6     print(x.departements)

结果：

1 D:\python3.7\python.exe E:/code_pycharm/test_in_class/tt17.py
2 [Employee no=10010 name='Duangkaew Piveteau'gender=F deps=[Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]]
3 Employee no=10010 name='Duangkaew Piveteau'gender=F deps=[Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]
4 10010
5 [Dept_emp empno=10010 deptno=d004, Dept_emp empno=10010 deptno=d006]
6 
7 Process finished with exit code 0

　　可以看出，只要不访问 departements 属性，就不会差 dept_emp这张表

posted @ 2018-11-12 11:35 JerryZao 阅读(291) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

JerryZao

为什么坚持，想一想当初，每天进步一点点！

Python-ORM

公告