pythonorm_Python-ORM

最新推荐文章于 2024-03-27 10:31:40 发布

程序幻境画师

最新推荐文章于 2024-03-27 10:31:40 发布

阅读量265

点赞数

文章标签： pythonorm

本文链接：https://blog.csdn.net/weixin_42723849/article/details/112924063

版权

1、ORM

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

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

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

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

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

举例：

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

映射到python中：

1 classStudent：2 id =？某类型字段3 name =？某类型字段4 age =？某类型字段5 最终得到实例：6

7 classStudent：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/

查看版本：

importsqlalchemyprint(sqlalchemy.__version__)

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

3、创建连接池

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

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

方言(dialect)：支持mysql， oracle等

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

mysqldb的链接

1 mysql + mysqldb://:@[:]/

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 importcreate_engine,Column, Integer, String2 from sqlalchemy.ext.declarative importdeclarative_base3

4 enging = create_engine('mysql+pymysql://root:123456@192.168.112.111:3306/testt', echo=True)5

7 # 创建基类，所有的实体类必须继承此基类

8 Base =declarative_base()9

10 # 创建实体类

11 classStudent(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 27 Table('student', MetaData(bind=None), Column('id', Integer(), table=, primary_key=True, nullable=False), Column('name', String(length=12), table=), Column('age', Integer(), table=), schema=None)28 '''

22 # 实例化 ===> 新建一行，对应 row

23 s = Student(name='tom')24 s.age = 20 #这里使用了描述性，因为Column是类，并实例化为一个实体类的属性

25 #print(s.age, s.name)

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

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 COMMIT46 '''

注意：

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

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

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

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 '''

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 exceptException 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 exceptException 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 instudents: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 exceptException as e:7 session.rollback()8 print(e)

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

，而这个实例并没有持久化(commit)，所以报错：

Instance '' 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 importcreate_engine,Column, Integer, String2 from sqlalchemy.orm importsessionmaker3 from sqlalchemy.ext.declarative importdeclarative_base4 importsqlalchemy5

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 classStudent(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)

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 exceptException as e:59 session.rollback()60 print(e)

结果：

1 1 session=1, attache=True2 transient=False, persistent=True3 pending=False, deleted=False, detached=False4 (, (2,), None)5 2 session=None, attache=False6 transient=True, persistent=False7 pending=False, deleted=False, detached=False8 None9 3 session=None, attache=False10 transient=True, persistent=False11 pending=False, deleted=False, detached=False12 None13 4 session=1, attache=True14 transient=False, persistent=False15 pending=True, deleted=False, detached=False16 None17 5 session=1, attache=True18 transient=False, persistent=True19 pending=False, deleted=False, detached=False20 (, (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 exceptException as e:14 session.rollback()15 print(e)

结果2：

1 D:\python3.7\python.exe E:/code_pycharm/test_in_class/tt17.py2 Student id=3 name=tom age=20

3 1 session=1, attache=True4 transient=False, persistent=True5 pending=False, deleted=False, detached=False6 (, (3,), None)7 2 session=1, attache=True8 transient=False, persistent=True9 pending=False, deleted=False, detached=False10 (, (3,), None)11 3 session=1, attache=True12 transient=False, persistent=False13 pending=False, deleted=True, detached=False14 (, (3,), None)15 4 session=None, attache=False16 transient=False, persistent=False17 pending=False, deleted=False, detached=True18 (, (3,), None)19

20 Process finished with exit code 0

结果2

复杂查询：

1 from sqlalchemy importcreate_engine,Column, Integer, String, Date2 from sqlalchemy importEnum, ForeignKey, create_engine3 from sqlalchemy.orm importsessionmaker4 from sqlalchemy.ext.declarative importdeclarative_base5 importsqlalchemy6

7 import enum # 这里使用的是python 提供的枚举，sqlalchemy 提供的enum 不能使用

9 classMyEnum(enum.Enum):10 M = 'M'

11 F = 'F'

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 classEmployee(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.value34 )35

36 #打印函数

37 defshow(emps):38 for x inemps: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 :不区分大小写

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.py2 2018-11-12 16:00:20,499 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

3 2018-11-12 16:00:20,500INFO 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,501INFO sqlalchemy.engine.base.Engine {}6 2018-11-12 16:00:20,503INFO sqlalchemy.engine.base.Engine SELECT DATABASE()7 2018-11-12 16:00:20,503INFO 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,506INFO 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_111 2018-11-12 16:00:20,508INFO 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_113 2018-11-12 16:00:20,509INFO 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_115 2018-11-12 16:00:20,510INFO sqlalchemy.engine.base.Engine {}16 2018-11-12 16:00:20,512INFO sqlalchemy.engine.base.Engine BEGIN (implicit)17 2018-11-12 16:00:20,513INFO 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_date18 FROM employees19 WHERE employees.emp_no > %(emp_no_1)s20 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=M22 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F gender=F23 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F24 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M25 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M26 ==========================================================================================

28 2018-11-12 16:00:20,516INFO 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_date29 FROM employees30 WHERE employees.emp_no > %(emp_no_1)s AND employees.gender = %(gender_1)s31 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=F33 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F34 ==========================================================================================

36 2018-11-12 16:00:20,519INFO 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_date37 FROM employees38 WHERE employees.emp_no > %(emp_no_1)s AND employees.gender = %(gender_1)s39 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=F41 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F42 ==========================================================================================

44 2018-11-12 16:00:20,521INFO 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_date45 FROM employees46 WHERE employees.emp_no > %(emp_no_1)s AND employees.gender = %(gender_1)s47 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=F49 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F50 ==========================================================================================

52 2018-11-12 16:00:20,523INFO 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_date53 FROM employees54 WHERE employees.emp_no > %(emp_no_1)s OR employees.emp_no < %(emp_no_2)s55 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=M57 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F gender=F58 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M59 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M60 ==========================================================================================

62 2018-11-12 16:00:20,526INFO 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_date63 FROM employees64 WHERE employees.emp_no > %(emp_no_1)s OR employees.emp_no < %(emp_no_2)s65 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=M67 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F gender=F68 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M69 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M70 ==========================================================================================

72 2018-11-12 16:00:20,527INFO 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_date73 FROM employees74 WHERE employees.emp_no >= %(emp_no_1)s75 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=F77 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M78 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M79 ==========================================================================================

81 2018-11-12 16:00:20,530INFO 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_date82 FROM employees83 WHERE employees.emp_no >= %(emp_no_1)s84 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=F86 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M87 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M88 ==========================================================================================

90 2018-11-12 16:00:20,532INFO 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_date91 FROM employees92 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=F95 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M gender=M96 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F97 ==========================================================================================

99 2018-11-12 16:00:20,535INFO 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_date100 FROM employees101 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=M104 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F gender=F105 Employee no=10003 name='Parto Bamford' !!!gender=MyEnum.M gender=M106 Employee no=10004 name='Chirstian Koblick' !!!gender=MyEnum.M gender=M107 Employee no=10005 name='Kyoichi Maliniak' !!!gender=MyEnum.M gender=M108 Employee no=10006 name='Anneke Preusig' !!!gender=MyEnum.F gender=F109 Employee no=10007 name='Tzvetan Zielinski' !!!gender=MyEnum.F gender=F110 Employee no=10008 name='Saniya Kalloufi' !!!gender=MyEnum.M gender=M111 Employee no=10009 name='Sumant Peac' !!!gender=MyEnum.F gender=F112 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F gender=F113 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M gender=M114 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M gender=M115 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M gender=M116 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M gender=M117 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F gender=F118 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M119 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M120 ==========================================================================================

121

122 2018-11-12 16:00:20,538INFO 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_date123 FROM employees124 WHERE employees.last_name LIKE %(last_name_1)s125 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=F127 Employee no=10009 name='Sumant Peac' !!!gender=MyEnum.F gender=F128 Employee no=10010 name='Duangkaew Piveteau' !!!gender=MyEnum.F gender=F129 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F130 ==========================================================================================

131

132 2018-11-12 16:00:20,540INFO 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_date133 FROM employees134 WHERE employees.last_name NOT LIKE %(last_name_1)s135 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=M137 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F gender=F138 Employee no=10003 name='Parto Bamford' !!!gender=MyEnum.M gender=M139 Employee no=10004 name='Chirstian Koblick' !!!gender=MyEnum.M gender=M140 Employee no=10005 name='Kyoichi Maliniak' !!!gender=MyEnum.M gender=M141 Employee no=10007 name='Tzvetan Zielinski' !!!gender=MyEnum.F gender=F142 Employee no=10008 name='Saniya Kalloufi' !!!gender=MyEnum.M gender=M143 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F gender=F144 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M gender=M145 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M gender=M146 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M gender=M147 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M gender=M148 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M gender=M149 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F gender=F150 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M151 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M152 ==========================================================================================

153

154 2018-11-12 16:00:20,543INFO 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_date155 FROM employees156 WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.emp_no ASC157 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=F159 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M gender=M160 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M gender=M161 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M gender=M162 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M gender=M163 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M gender=M164 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F gender=F165 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F166 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M167 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M168 ==========================================================================================

169

170 2018-11-12 16:00:20,546INFO 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_date171 FROM employees172 WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.emp_no DESC173 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=M175 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M176 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F177 Employee no=10017 name='Cristinel Bouloucos' !!!gender=MyEnum.F gender=F178 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M gender=M179 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M gender=M180 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M gender=M181 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M gender=M182 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M gender=M183 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F gender=F184 ==========================================================================================

185

186 2018-11-12 16:00:20,548INFO 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_date187 FROM employees188 WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.last_name, employees.last_name DESC189 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=F191 Employee no=10012 name='Patricio Bridgland' !!!gender=MyEnum.M gender=M192 Employee no=10016 name='Kazuhito Cappelletti' !!!gender=MyEnum.M gender=M193 Employee no=10014 name='Berni Genin' !!!gender=MyEnum.M gender=M194 Employee no=10019 name='Lillian Haddadi' !!!gender=MyEnum.M gender=M195 Employee no=10015 name='Guoxiang Nooteboom' !!!gender=MyEnum.M gender=M196 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F197 Employee no=10011 name='Mary Sluis' !!!gender=MyEnum.F gender=F198 Employee no=10013 name='Eberhardt Terkki' !!!gender=MyEnum.M gender=M199 Employee no=10020 name='Mayuko Warwick' !!!gender=MyEnum.M gender=M200 ==========================================================================================

201

202 2018-11-12 16:00:20,551INFO 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_date203 FROM employees204 LIMIT %(param_1)s205 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=M207 Employee no=10002 name='Bezalel Simmel' !!!gender=MyEnum.F gender=F208 Employee no=10003 name='Parto Bamford' !!!gender=MyEnum.M gender=M209 Employee no=10004 name='Chirstian Koblick' !!!gender=MyEnum.M gender=M210 ==========================================================================================

211

212 2018-11-12 16:00:20,553INFO 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_date213 FROM employees214 LIMIT %(param_1)s, %(param_2)s215 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=M217 Employee no=10005 name='Kyoichi Maliniak' !!!gender=MyEnum.M gender=M218 Employee no=10006 name='Anneke Preusig' !!!gender=MyEnum.F gender=F219 Employee no=10007 name='Tzvetan Zielinski' !!!gender=MyEnum.F gender=F220 ==========================================================================================

221

222

223 Process finished with exit code 0

结果

1 # 消费者方法：

2 #消费者方法调用后，Query 对象(可迭代) 就转换成了一个容器

4 #总行数

5 emps = session.query(Employee).filter(Employee.emp_no >10017)6

7 print(len(list(emps))) #返回大量的结果，用list查看，求len

8 print(emps.count())#聚合函数count(*) 的查询

10 #去所有数据：

11 print(emps.all()) #返回列表，查不到返回空列表

13 #取首行

14 print(emps.first())#查不到，返回None

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=F5 Employee no=10018 name='Kazuhide Peha' !!!gender=MyEnum.F gender=F6 2

结果

1 # 聚合分组

2 #聚合函数

3 from sqlalchemy importfunc4 query =session.query(func.count(Employee.emp_no))5 print(query)6 print(query.one()) #只能有一行结果

7 print(query.scalar()) #取 one() 返回元组的第一个元素

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.py2 SELECT count(employees.emp_no) AS count_13 FROM employees4 2018-11-12 16:18:22,426 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

5 2018-11-12 16:18:22,426INFO 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,428INFO sqlalchemy.engine.base.Engine {}8 2018-11-12 16:18:22,430INFO sqlalchemy.engine.base.Engine SELECT DATABASE()9 2018-11-12 16:18:22,430INFO 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,431INFO 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_113 2018-11-12 16:18:22,433INFO 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_115 2018-11-12 16:18:22,434INFO 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_117 2018-11-12 16:18:22,435INFO sqlalchemy.engine.base.Engine {}18 2018-11-12 16:18:22,436INFO sqlalchemy.engine.base.Engine BEGIN (implicit)19 2018-11-12 16:18:22,437INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_120 FROM employees21 2018-11-12 16:18:22,437INFO sqlalchemy.engine.base.Engine {}22 (20,)23 2018-11-12 16:18:22,439INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_124 FROM employees25 2018-11-12 16:18:22,439INFO sqlalchemy.engine.base.Engine {}26 20

27 2018-11-12 16:18:22,440INFO sqlalchemy.engine.base.Engine SELECT max(employees.emp_no) AS max_128 FROM employees29 2018-11-12 16:18:22,440INFO sqlalchemy.engine.base.Engine {}30 10020

31 2018-11-12 16:18:22,441INFO sqlalchemy.engine.base.Engine SELECT min(employees.emp_no) AS min_132 FROM employees33 2018-11-12 16:18:22,441INFO sqlalchemy.engine.base.Engine {}34 10001

35 2018-11-12 16:18:22,443INFO sqlalchemy.engine.base.Engine SELECT avg(employees.emp_no) AS avg_136 FROM employees37 2018-11-12 16:18:22,443INFO sqlalchemy.engine.base.Engine {}38 10010.5000

39 ==========================================================================================

41 2018-11-12 16:18:22,445INFO sqlalchemy.engine.base.Engine SELECT employees.gender AS employees_gender, count(employees.emp_no) AS count_142 FROM employees GROUP BY employees.gender43 2018-11-12 16:18:22,445INFO sqlalchemy.engine.base.Engine {}44 [(, 12), (, 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

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

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 CASCADE31 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

dept_emp的设计表：

外键：

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

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

7 import enum #这里使用的是python 提供的枚举，sqlalchemy 提供的enum 不能使用

9 classMyEnum(enum.Enum):10 M = 'M'

11 F = 'F'

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 classEmployee(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.value34 )35

36 classDepartment(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_name44 )45

46 classDept_emp(Base):47 __tablename__ = 'dept_emp'

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 defshow(emps):59 for x inemps:60 print(x)61 print('======'*15, end='\n\n')62

63 '''

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

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_no81 WHERE employees.emp_no = %(emp_no_1)s82 '''

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.py2 2018-11-12 17:25:26,515 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

3 2018-11-12 17:25:26,515INFO 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,517INFO sqlalchemy.engine.base.Engine {}6 2018-11-12 17:25:26,519INFO sqlalchemy.engine.base.Engine SELECT DATABASE()7 2018-11-12 17:25:26,519INFO 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,522INFO 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_111 2018-11-12 17:25:26,524INFO 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_113 2018-11-12 17:25:26,525INFO 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_115 2018-11-12 17:25:26,526INFO sqlalchemy.engine.base.Engine {}16 2018-11-12 17:25:26,528INFO sqlalchemy.engine.base.Engine BEGIN (implicit)17 2018-11-12 17:25:26,529INFO 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_date18 FROM employees, dept_emp19 WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s20 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 ==========================================================================================

26 2018-11-12 17:25:26,532INFO 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_date27 FROM employees INNER JOIN dept_emp ON employees.emp_no =dept_emp.emp_no28 WHERE employees.emp_no = %(emp_no_1)s29 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=F32 ==========================================================================================

34 2018-11-12 17:25:26,534INFO 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_date35 FROM employees INNER JOIN dept_emp ON employees.emp_no =dept_emp.emp_no36 WHERE employees.emp_no = %(emp_no_1)s37 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=F40 ==========================================================================================

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 importcreate_engine,Column, Integer, String, Date2 from sqlalchemy importEnum, ForeignKey, create_engine3 from sqlalchemy.orm importsessionmaker, relationship4 from sqlalchemy.ext.declarative importdeclarative_base5 importsqlalchemy6

7 import enum #这里使用的是python 提供的枚举，sqlalchemy 提供的enum 不能使用

9 classMyEnum(enum.Enum):10 M = 'M'

11 F = 'F'

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 classEmployee(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')#关系，只是从一张表找另一张表用，并不是字段

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.departements37 )38

39 classDepartment(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_name47 )48

49 classDept_emp(Base):50 __tablename__ = 'dept_emp'

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 defshow(emps):62 for x inemps:63 print(x)64 print('======'*15, end='\n\n')65

66 '''

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

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.py2 2018-11-12 17:49:18,365 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'

3 2018-11-12 17:49:18,365INFO 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,367INFO sqlalchemy.engine.base.Engine {}6 2018-11-12 17:49:18,368INFO sqlalchemy.engine.base.Engine SELECT DATABASE()7 2018-11-12 17:49:18,369INFO 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,370INFO 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_111 2018-11-12 17:49:18,372INFO 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_113 2018-11-12 17:49:18,373INFO 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_115 2018-11-12 17:49:18,374INFO sqlalchemy.engine.base.Engine {}16 2018-11-12 17:49:18,376INFO sqlalchemy.engine.base.Engine BEGIN (implicit)17 2018-11-12 17:49:18,377INFO 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_date18 FROM employees INNER JOIN dept_emp ON employees.emp_no =dept_emp.emp_no19 WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s20 2018-11-12 17:49:18,377 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}21 2018-11-12 17:49:18,380INFO 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_date22 FROM dept_emp23 WHERE %(param_1)s =dept_emp.emp_no24 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,382INFO 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_date27 FROM employees INNER JOIN dept_emp ON employees.emp_no =dept_emp.emp_no28 WHERE employees.emp_no = %(emp_no_1)s29 2018-11-12 17:49:18,382 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}30 2018-11-12 17:49:18,384INFO 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_date31 FROM dept_emp32 WHERE %(param_1)s =dept_emp.emp_no33 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,386INFO 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_date36 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s37 2018-11-12 17:49:18,386 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010}38 2018-11-12 17:49:18,387INFO 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_date39 FROM dept_emp40 WHERE %(param_1)s =dept_emp.emp_no41 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 inresults: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.py2 [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这张表

程序幻境画师

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
pythonorm_Python-ORM

1、ORMORM：对象关系映射，对象和关系之间的映射，使用面向对象的方式来操作数据库关系模型和 Python对象之间的映射table ===> class , 表映射为类row ===> object , 行映射为实例column ===> property, 字段映射为属性举例：有表student，字段为 id int, name varchar, age int...
复制链接

扫一扫