ORM

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

关系模型和Python对象之间的映射 table => class ,表映射为类 row => object ,行映射为实例 column => property ,字段映射为属性

SQLALchemy

SQLALchemy是一个ORM框架

安装

pip install sqlalchemy

文档

官方文档：http://docs.sqlalchemy.org/en/latest/

开发

SQLALchemy内部使用了连接池

创建连接

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

dialect + driver://username:password@host:port/database # mysqldb的连接 mysql + mysqldb://<user>:<password>@<host>[:<port>]/<dbname> engine = sqlalchemy.create_engine("mysql+mysqldb://lee:123456@192.168.174.143:3306/test") # pymysql的连接 mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>] engine = sqlalchemy.create_engine("mysql+pymysql://lee:123456@192.168.174.143:3306/test") engine = sqlalchemy.create_engine("mysql+pymysql://lee:123456@192.168.174.143:3306/test", echo=True) echo = True 引擎是否打印执行的语句，调试的时候打开很方便。 lazy connecting:懒连接。创建引擎并不会马上连接数据库，直到让数据库执行任务时才连接。

Declare a Mapping创建映射

创建基类

from sqlalchemy.ext.declarative import declarative_base # 创建基类，便于实体类继承。SQLALchemy大量使用了元编程 Base = declarative_base()

创建实体类

# student表 CREATE TABLE student( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, age INTEGER, PRIMARY KEY (id) ) # 创建实体类 class Student(Base): # 指定表名 __tablename__ = 'student' # 定义类属性对应字段 id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), nullable=False) age = Column(Integer) def __repr__(self): return "{} id={} name={} age={}".format(self.__class__.__name__, self.id, self.name, self.age)

实例化

s = Student(name='tom') print(s.name) s.age = 20 print(s.age)

创建表

可以使用SQLALchemy来创建、删除表

# 删除继承自Base的所有表 Base.metadata.drop_all(engine) # 创建继承自Base的所有表 Base.metadata.create_all(engine)

生产环境很少这样创建表，都是系统上线的时候由脚本生成。 生产环境很少删除表，宁可废弃都不能删除。

创建会话session

在一个会话中操作数据库，会话建立在连接上，连接被引擎管理。 当第一次使用数据库时，从引擎维护的连接池中获取一个连接使用。

# 创建session Session = sessionmaker(bind=engine) # 工厂方法返回类 session = Session() # 实例化 # 依然在第一次使用时连接数据库

session对象线程不安全。所以不同线程应该使用不用的session对象。 Session类和engine有一个就行了。

CRUD操作

增

add(): 增加一个对象 add_all(): 可迭代对象，元素是对象

from sqlalchemy import Column, Integer, String, create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.ext.declarative import declarative_base USER = 'lee' PASSWORD = '123456' HOST = '192.168.174.143' PORT = 3306 DATABASE = 'test' constr = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(USER, PASSWORD, HOST, PORT, DATABASE) # constr = 'mysql + pymysql://{}:{}@{}:{}/{}'.format(USER, PASSWORD, HOST, PORT, DATABASE) # mysql + pymysql中间不能有空格 engine = create_engine(constr, echo=True) # 创建基类 Base = declarative_base() # 创建实体类 class Student(Base): # 指定表名 __tablename__ = 'student' # 定义属性对应字段 id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(64), nullable=False) age = Column(Integer) def __repr__(self): return "<{} {} {} {}>".format(self.__class__.__name__, self.id, self.name, self.age) # 删除表 Base.metadata.drop_all(engine) #创建表 Base.metadata.create_all(engine) #创建session Session = sessionmaker(bind=engine) session = Session() s = Student(name='tom') # 构造时传入 s.age = 20 print(s) session.add(s) print(s, '++++++++++') session.commit() print(s) print('====================') try: session.add_all([s]) print(s) session.commit() print(s) except: session.rollback() print('roll back') raise 2019-09-24 22:22:39,573 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-09-24 22:22:39,575 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,578 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-09-24 22:22:39,580 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,584 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-09-24 22:22:39,585 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,588 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-09-24 22:22:39,590 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,593 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-09-24 22:22:39,595 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,597 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-09-24 22:22:39,599 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,603 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1 2019-09-24 22:22:39,605 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,608 INFO sqlalchemy.engine.base.Engine DESCRIBE `student` 2019-09-24 22:22:39,609 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,616 INFO sqlalchemy.engine.base.Engine DROP TABLE student 2019-09-24 22:22:39,618 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,623 INFO sqlalchemy.engine.base.Engine COMMIT 2019-09-24 22:22:39,627 INFO sqlalchemy.engine.base.Engine DESCRIBE `student` 2019-09-24 22:22:39,629 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,633 INFO sqlalchemy.engine.base.Engine ROLLBACK 2019-09-24 22:22:39,636 INFO sqlalchemy.engine.base.Engine CREATE TABLE student ( id INTEGER NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, age INTEGER, PRIMARY KEY (id) ) 2019-09-24 22:22:39,639 INFO sqlalchemy.engine.base.Engine {} 2019-09-24 22:22:39,643 INFO sqlalchemy.engine.base.Engine COMMIT <Student None tom 20> <Student None tom 20> ++++++++++ 2019-09-24 22:22:39,649 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-09-24 22:22:39,652 INFO sqlalchemy.engine.base.Engine INSERT INTO student (name, age) VALUES (%(name)s, %(age)s) 2019-09-24 22:22:39,654 INFO sqlalchemy.engine.base.Engine {'name': 'tom', 'age': 20} 2019-09-24 22:22:39,659 INFO sqlalchemy.engine.base.Engine COMMIT 2019-09-24 22:22:39,666 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-09-24 22:22:39,668 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age FROM student WHERE student.id = %(param_1)s 2019-09-24 22:22:39,669 INFO sqlalchemy.engine.base.Engine {'param_1': 1} <Student 1 tom 20> ==================== <Student 1 tom 20> 2019-09-24 22:22:39,673 INFO sqlalchemy.engine.base.Engine COMMIT 2019-09-24 22:22:39,680 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-09-24 22:22:39,683 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age FROM student WHERE student.id = %(param_1)s 2019-09-24 22:22:39,684 INFO sqlalchemy.engine.base.Engine {'param_1': 1} <Student 1 tom 20>

add_all()方法不会提交成功的，因为s成功提交后，s的主键就有了值，所以，只要s没有修改过，就认为没有改动。s变化了，就可以提交修改了。

简单查询

使用query()方法，返回一个Query对象

students = session.query(Student) # 无条件 print(students) # 无内容，惰性的 for student in students: print(student) print('========================') student = session.query(Student).get(2) # 通过主键查询 print(student) SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age FROM student 2019-09-24 16:58:13,951 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age FROM student 2019-09-24 16:58:13,953 INFO sqlalchemy.engine.base.Engine {} <Student 1 tom 20> ======================== 2019-09-24 16:58:13,960 INFO sqlalchemy.engine.base.Engine SELECT student.id AS student_id, student.name AS student_name, student.age AS student_age FROM student WHERE student.id = %(param_1)s 2019-09-24 16:58:13,962 INFO sqlalchemy.engine.base.Engine {'param_1': 2} None

query方法将实体类传入，返回类的对象可迭代对象，这时候并不查询。迭代它就执行SQL来查询数据库，封装数据到指定类的实例。 get方法使用主键查询，返回一条传入类的一个实例。

改

student = session.query(Student).get(1) print(student) student.name = 'sam' student.age = 30 print(student) session.add(student) session.commit() <Student 1 tom 20> <Student 1 sam 30> 2019-09-24 17:03:28,533 INFO sqlalchemy.engine.base.Engine UPDATE student SET name=%(name)s, age=%(age)s WHERE student.id = %(student_id)s 2019-09-24 17:03:28,534 INFO sqlalchemy.engine.base.Engine {'name': 'sam', 'age': 30, 'student_id': 1} 2019-09-24 17:03:28,538 INFO sqlalchemy.engine.base.Engine COMMIT

先查，修改，再提交更改（commit）

删除

try: student = Student(id=2, name='sam', age=40) session.delete(student) session.commit() except Exception as e: session.rollback() print('=========================') print(e) 2019-09-24 22:13:05,757 INFO sqlalchemy.engine.base.Engine ROLLBACK ========================= Instance '<Student at 0x1b4e9caeba8>' is not persisted try: student = Student(id=2, name='sam', age=40) # session.delete(student) session.add(student) session.commit() except Exception as e: session.rollback() print('=========================') print(e) 2019-09-24 22:23:07,493 INFO sqlalchemy.engine.base.Engine INSERT INTO student (id, name, age) VALUES (%(id)s, %(name)s, %(age)s) 2019-09-24 22:23:07,494 INFO sqlalchemy.engine.base.Engine {'id': 2, 'name': 'sam', 'age': 40} 2019-09-24 22:23:07,500 INFO sqlalchemy.engine.base.Engine COMMIT try: student = Student(id=2, name='sam', age=40) session.delete(student) # session.add(student) session.commit() except Exception as e: session.rollback() print('=========================') print(e) ========================= Instance '<Student at 0x1b4ec475ef0>' is not persisted from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,Date,Enum,ForeignKey,create_engine from sqlalchemy.orm import sessionmaker import enum Base = declarative_base() connstr = "{}://{}:{}@{}:{}/{}".format('mysql+pymysql','lee','123456','192.168.174.144', 3306, 'test') engine = create_engine(connstr, echo=True) Session = sessionmaker(bind=engine) session = Session() class MyEnum(enum.Enum): M = 'M' F = 'F' class Employee(Base): __tablename__ = 'employees' emp_no = Column(Integer,primary_key=True) birth_date = Column(Date, nullable=False) first_name = Column(String(14), nullable=False) last_name = Column(String(16), nullable=False) gender = Column(Enum(MyEnum), nullable=False) hire_date = Column(Date, nullable=False) def __repr__(self): return "{} no={} name={} {} gender={}".format(self.__class__.__name__, self.emp_no, self.first_name, self.last_name, self.gender.value) def show(emps): for x in emps: print(x) print('=============================\n') # 简单条件查询 emps = session.query(Employee).filter(Employee.emp_no > 10015) show(emps) # 与或非 from sqlalchemy import or_, and_, not_ # AND 条件 emps = session.query(Employee).filter(Employee.emp_no > 10015).filter(Employee.gender == MyEnum.F) show(emps) emps = session.query(Employee).filter(Employee.emp_no > 10015, Employee.emp_no < 10020) show(emps) emps = session.query(Employee).filter(and_(Employee.emp_no > 10016, Employee.emp_no < 10020)) show(emps) emps = session.query(Employee).filter((Employee.emp_no > 10017) & (Employee.emp_no < 10020)) show(emps) # or 条件 emps = session.query(Employee).filter((Employee.emp_no > 10018) | (Employee.emp_no < 10016)) show(emps) emps = session.query(Employee).filter(or_(Employee.emp_no < 10005, Employee.emp_no > 10018)) show(emps) # not 条件 emps = session.query(Employee).filter(not_(Employee.emp_no > 10010)) show(emps) emps = session.query(Employee).filter(~(Employee.emp_no > 10010)) show(emps) # in、not in emplist = [10010, 10015, 10016] emps = session.query(Employee).filter(Employee.emp_no.in_(emplist)) show(emps) # like # ilike 可以忽略大小写匹配 emps = session.query(Employee).filter(Employee.last_name.like('P%')) show(emps) # 排序 # 升序 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no) show(emps) # 降序 emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.desc()) show(emps) # 多列排序 print('++++++++++++++++++++++++++++') emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.last_name).order_by(Employee.emp_no.desc()) show(emps) # 分页 emps = session.query(Employee).limit(4) show(emps) emps = session.query(Employee).limit(5).offset(18) show(emps) 2019-09-25 21:10:28,666 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode' 2019-09-25 21:10:28,667 INFO sqlalchemy.engine.base.Engine {} 2019-09-25 21:10:28,672 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names' 2019-09-25 21:10:28,674 INFO sqlalchemy.engine.base.Engine {} 2019-09-25 21:10:28,677 INFO sqlalchemy.engine.base.Engine SELECT DATABASE() 2019-09-25 21:10:28,679 INFO sqlalchemy.engine.base.Engine {} 2019-09-25 21:10:28,682 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin' 2019-09-25 21:10:28,683 INFO sqlalchemy.engine.base.Engine {} 2019-09-25 21:10:28,687 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1 2019-09-25 21:10:28,690 INFO sqlalchemy.engine.base.Engine {} 2019-09-25 21:10:28,696 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1 2019-09-25 21:10:28,698 INFO sqlalchemy.engine.base.Engine {} 2019-09-25 21:10:28,701 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1 2019-09-25 21:10:28,703 INFO sqlalchemy.engine.base.Engine {} 2019-09-25 21:10:28,706 INFO sqlalchemy.engine.base.Engine BEGIN (implicit) 2019-09-25 21:10:28,709 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s 2019-09-25 21:10:28,711 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015} Employee no=10016 name=Kazuhito Cappelletti gender=M Employee no=10017 name=Cristinel Bouloucos gender=F Employee no=10018 name=Kazuhide Peha gender=F Employee no=10019 name=Lillian Haddadi gender=M Employee no=10020 name=Mayuko Warwick gender=M ============================= 2019-09-25 21:10:28,722 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s AND employees.gender = %(gender_1)s 2019-09-25 21:10:28,724 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'gender_1': 'F'} Employee no=10017 name=Cristinel Bouloucos gender=F Employee no=10018 name=Kazuhide Peha gender=F ============================= 2019-09-25 21:10:28,730 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s AND employees.emp_no < %(emp_no_2)s 2019-09-25 21:10:28,732 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10015, 'emp_no_2': 10020} Employee no=10016 name=Kazuhito Cappelletti gender=M Employee no=10017 name=Cristinel Bouloucos gender=F Employee no=10018 name=Kazuhide Peha gender=F Employee no=10019 name=Lillian Haddadi gender=M ============================= 2019-09-25 21:10:28,739 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s AND employees.emp_no < %(emp_no_2)s 2019-09-25 21:10:28,741 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10016, 'emp_no_2': 10020} Employee no=10017 name=Cristinel Bouloucos gender=F Employee no=10018 name=Kazuhide Peha gender=F Employee no=10019 name=Lillian Haddadi gender=M ============================= 2019-09-25 21:10:28,747 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s AND employees.emp_no < %(emp_no_2)s 2019-09-25 21:10:28,748 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10017, 'emp_no_2': 10020} Employee no=10018 name=Kazuhide Peha gender=F Employee no=10019 name=Lillian Haddadi gender=M ============================= 2019-09-25 21:10:28,755 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s OR employees.emp_no < %(emp_no_2)s 2019-09-25 21:10:28,757 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10018, 'emp_no_2': 10016} Employee no=10001 name=Georgi Facello gender=M Employee no=10002 name=Bezalel Simmel gender=F Employee no=10003 name=Parto Bamford gender=M Employee no=10004 name=Chirstian Koblick gender=M Employee no=10005 name=Kyoichi Maliniak gender=M Employee no=10006 name=Anneke Preusig gender=F Employee no=10007 name=Tzvetan Zielinski gender=F Employee no=10008 name=Saniya Kalloufi gender=M Employee no=10009 name=Sumant Peac gender=F Employee no=10010 name=Duangkaew Piveteau gender=F Employee no=10011 name=Mary Sluis gender=F Employee no=10012 name=Patricio Bridgland gender=M Employee no=10013 name=Eberhardt Terkki gender=M Employee no=10014 name=Berni Genin gender=M Employee no=10015 name=Guoxiang Nooteboom gender=M Employee no=10019 name=Lillian Haddadi gender=M Employee no=10020 name=Mayuko Warwick gender=M ============================= 2019-09-25 21:10:28,769 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 FROM employees WHERE employees.emp_no < %(emp_no_1)s OR employees.emp_no > %(emp_no_2)s 2019-09-25 21:10:28,770 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10005, 'emp_no_2': 10018} Employee no=10001 name=Georgi Facello gender=M Employee no=10002 name=Bezalel Simmel gender=F Employee no=10003 name=Parto Bamford gender=M Employee no=10004 name=Chirstian Koblick gender=M Employee no=10019 name=Lillian Haddadi gender=M Employee no=10020 name=Mayuko Warwick gender=M ============================= 2019-09-25 21:10:28,777 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 FROM employees WHERE employees.emp_no <= %(emp_no_1)s 2019-09-25 21:10:28,778 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} Employee no=10001 name=Georgi Facello gender=M Employee no=10002 name=Bezalel Simmel gender=F Employee no=10003 name=Parto Bamford gender=M Employee no=10004 name=Chirstian Koblick gender=M Employee no=10005 name=Kyoichi Maliniak gender=M Employee no=10006 name=Anneke Preusig gender=F Employee no=10007 name=Tzvetan Zielinski gender=F Employee no=10008 name=Saniya Kalloufi gender=M Employee no=10009 name=Sumant Peac gender=F Employee no=10010 name=Duangkaew Piveteau gender=F ============================= 2019-09-25 21:10:28,787 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 FROM employees WHERE employees.emp_no <= %(emp_no_1)s 2019-09-25 21:10:28,788 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} Employee no=10001 name=Georgi Facello gender=M Employee no=10002 name=Bezalel Simmel gender=F Employee no=10003 name=Parto Bamford gender=M Employee no=10004 name=Chirstian Koblick gender=M Employee no=10005 name=Kyoichi Maliniak gender=M Employee no=10006 name=Anneke Preusig gender=F Employee no=10007 name=Tzvetan Zielinski gender=F Employee no=10008 name=Saniya Kalloufi gender=M Employee no=10009 name=Sumant Peac gender=F Employee no=10010 name=Duangkaew Piveteau gender=F ============================= 2019-09-25 21:10:28,798 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 FROM employees WHERE employees.emp_no IN (%(emp_no_1)s, %(emp_no_2)s, %(emp_no_3)s) 2019-09-25 21:10:28,800 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010, 'emp_no_2': 10015, 'emp_no_3': 10016} Employee no=10010 name=Duangkaew Piveteau gender=F Employee no=10015 name=Guoxiang Nooteboom gender=M Employee no=10016 name=Kazuhito Cappelletti gender=M ============================= 2019-09-25 21:10:28,808 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 FROM employees WHERE employees.last_name LIKE %(last_name_1)s 2019-09-25 21:10:28,810 INFO sqlalchemy.engine.base.Engine {'last_name_1': 'P%'} Employee no=10006 name=Anneke Preusig gender=F Employee no=10009 name=Sumant Peac gender=F Employee no=10010 name=Duangkaew Piveteau gender=F Employee no=10018 name=Kazuhide Peha gender=F ============================= 2019-09-25 21:10:28,816 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.emp_no 2019-09-25 21:10:28,818 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} Employee no=10011 name=Mary Sluis gender=F Employee no=10012 name=Patricio Bridgland gender=M Employee no=10013 name=Eberhardt Terkki gender=M Employee no=10014 name=Berni Genin gender=M Employee no=10015 name=Guoxiang Nooteboom gender=M Employee no=10016 name=Kazuhito Cappelletti gender=M Employee no=10017 name=Cristinel Bouloucos gender=F Employee no=10018 name=Kazuhide Peha gender=F Employee no=10019 name=Lillian Haddadi gender=M Employee no=10020 name=Mayuko Warwick gender=M ============================= 2019-09-25 21:10:28,827 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.emp_no DESC 2019-09-25 21:10:28,828 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} Employee no=10020 name=Mayuko Warwick gender=M Employee no=10019 name=Lillian Haddadi gender=M Employee no=10018 name=Kazuhide Peha gender=F Employee no=10017 name=Cristinel Bouloucos gender=F Employee no=10016 name=Kazuhito Cappelletti gender=M Employee no=10015 name=Guoxiang Nooteboom gender=M Employee no=10014 name=Berni Genin gender=M Employee no=10013 name=Eberhardt Terkki gender=M Employee no=10012 name=Patricio Bridgland gender=M Employee no=10011 name=Mary Sluis gender=F ============================= ++++++++++++++++++++++++++++ 2019-09-25 21:10:28,837 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 FROM employees WHERE employees.emp_no > %(emp_no_1)s ORDER BY employees.last_name, employees.emp_no DESC 2019-09-25 21:10:28,838 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} Employee no=10017 name=Cristinel Bouloucos gender=F Employee no=10012 name=Patricio Bridgland gender=M Employee no=10016 name=Kazuhito Cappelletti gender=M Employee no=10014 name=Berni Genin gender=M Employee no=10019 name=Lillian Haddadi gender=M Employee no=10015 name=Guoxiang Nooteboom gender=M Employee no=10018 name=Kazuhide Peha gender=F Employee no=10011 name=Mary Sluis gender=F Employee no=10013 name=Eberhardt Terkki gender=M Employee no=10020 name=Mayuko Warwick gender=M ============================= 2019-09-25 21:10:28,847 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 FROM employees LIMIT %(param_1)s 2019-09-25 21:10:28,849 INFO sqlalchemy.engine.base.Engine {'param_1': 4} Employee no=10001 name=Georgi Facello gender=M Employee no=10002 name=Bezalel Simmel gender=F Employee no=10003 name=Parto Bamford gender=M Employee no=10004 name=Chirstian Koblick gender=M ============================= 2019-09-25 21:10:28,857 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 FROM employees LIMIT %(param_1)s, %(param_2)s 2019-09-25 21:10:28,858 INFO sqlalchemy.engine.base.Engine {'param_1': 18, 'param_2': 5} Employee no=10019 name=Lillian Haddadi gender=M Employee no=10020 name=Mayuko Warwick gender=M =============================

消费者方法

消费者方法调用后，Query对象（可迭代）就转换成了一个容器。

emps = session.query(Employee) print(len(list(emps))) print(emps.count()) # 取所有数据 print(emps.all()) # 取首行 print(emps.first()) # 返回首行，查不到返回None，等价limit # 有且只有一行 # print(emps.one()) # 如果查询结果是多行抛异常 print('+++++++++++++++++++++++++++++') print(emps.limit(1).one()) # 删除 delete by query session.query(Employee).filter(Employee.emp_no > 10018).delete() # session.commit() # 提交则删除 2019-09-25 21:48:48,856 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 FROM employees 2019-09-25 21:48:48,858 INFO sqlalchemy.engine.base.Engine {} 20 2019-09-25 21:48:48,868 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1 FROM (SELECT employees.emp_no AS employees_emp_no, employees.birth_date AS employees_birth_date, employees.first_name AS employees_first_name, employees.last_name AS employees_last_name, employees.gender AS employees_gender, employees.hire_date AS employees_hire_date FROM employees) AS anon_1 2019-09-25 21:48:48,870 INFO sqlalchemy.engine.base.Engine {} 20 2019-09-25 21:48:48,875 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 FROM employees 2019-09-25 21:48:48,877 INFO sqlalchemy.engine.base.Engine {} [Employee no=10001 name=Georgi Facello gender=M, Employee no=10002 name=Bezalel Simmel gender=F, Employee no=10003 name=Parto Bamford gender=M, Employee no=10004 name=Chirstian Koblick gender=M, Employee no=10005 name=Kyoichi Maliniak gender=M, Employee no=10006 name=Anneke Preusig gender=F, Employee no=10007 name=Tzvetan Zielinski gender=F, Employee no=10008 name=Saniya Kalloufi gender=M, Employee no=10009 name=Sumant Peac gender=F, Employee no=10010 name=Duangkaew Piveteau gender=F, Employee no=10011 name=Mary Sluis gender=F, Employee no=10012 name=Patricio Bridgland gender=M, Employee no=10013 name=Eberhardt Terkki gender=M, Employee no=10014 name=Berni Genin gender=M, Employee no=10015 name=Guoxiang Nooteboom gender=M, Employee no=10016 name=Kazuhito Cappelletti gender=M, Employee no=10017 name=Cristinel Bouloucos gender=F, Employee no=10018 name=Kazuhide Peha gender=F, Employee no=10019 name=Lillian Haddadi gender=M, Employee no=10020 name=Mayuko Warwick gender=M] 2019-09-25 21:48:48,886 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 FROM employees LIMIT %(param_1)s 2019-09-25 21:48:48,888 INFO sqlalchemy.engine.base.Engine {'param_1': 1} Employee no=10001 name=Georgi Facello gender=M +++++++++++++++++++++++++++++ 2019-09-25 21:48:48,894 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 FROM employees LIMIT %(param_1)s 2019-09-25 21:48:48,896 INFO sqlalchemy.engine.base.Engine {'param_1': 1} Employee no=10001 name=Georgi Facello gender=M first方法本质上就是limit语句

聚合、分组

# 聚合函数 # count from sqlalchemy import func query = session.query(func.count(Employee.emp_no)) print(query.all()) # 列表中一个元素 print(query.first()) # 一个只有一个元素的元组 print(query.one()) # 只能有一行返回，一个元组 print(query.scalar()) # 取one()的第一个元素 # max/min/avg print(session.query(func.max(Employee.emp_no)).scalar()) print(session.query(func.min(Employee.emp_no)).scalar()) print(session.query(func.avg(Employee.emp_no)).scalar()) # 分组 query = session.query(Employee.gender, func.count(Employee.emp_no)).group_by(Employee.gender).all() # print(query) for g,y in query: print(g.value, y) 2019-09-25 22:04:41,623 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 FROM employees 2019-09-25 22:04:41,624 INFO sqlalchemy.engine.base.Engine {} [(20,)] 2019-09-25 22:04:41,629 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 FROM employees LIMIT %(param_1)s 2019-09-25 22:04:41,631 INFO sqlalchemy.engine.base.Engine {'param_1': 1} (20,) 2019-09-25 22:04:41,635 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 FROM employees 2019-09-25 22:04:41,637 INFO sqlalchemy.engine.base.Engine {} (20,) 2019-09-25 22:04:41,640 INFO sqlalchemy.engine.base.Engine SELECT count(employees.emp_no) AS count_1 FROM employees 2019-09-25 22:04:41,642 INFO sqlalchemy.engine.base.Engine {} 20 2019-09-25 22:04:41,646 INFO sqlalchemy.engine.base.Engine SELECT max(employees.emp_no) AS max_1 FROM employees 2019-09-25 22:04:41,648 INFO sqlalchemy.engine.base.Engine {} 10020 2019-09-25 22:04:41,653 INFO sqlalchemy.engine.base.Engine SELECT min(employees.emp_no) AS min_1 FROM employees 2019-09-25 22:04:41,654 INFO sqlalchemy.engine.base.Engine {} 10001 2019-09-25 22:04:41,658 INFO sqlalchemy.engine.base.Engine SELECT avg(employees.emp_no) AS avg_1 FROM employees 2019-09-25 22:04:41,659 INFO sqlalchemy.engine.base.Engine {} 10010.5000 2019-09-25 22:04:41,664 INFO sqlalchemy.engine.base.Engine SELECT employees.gender AS employees_gender, count(employees.emp_no) AS count_1 FROM employees GROUP BY employees.gender 2019-09-25 22:04:41,666 INFO sqlalchemy.engine.base.Engine {} [(<MyEnum.M: 'M'>, 12), (<MyEnum.F: 'F'>, 8)] M 12 F 8

关联查询

# class Employee(Base): # __tablename__ = 'employees' # emp_no = Column(Integer, primary_key=True) # birth_date = Column(Date, nullable=False) # first_name = Column(String(14), nullable=False) # last_name = Column(String(16), nullable=False) # gender = Column(Enum(MyEnum), nullable=False) # hire_date = Column(Date, nullable=False) # def __repr__(self): # return "{} no={} name={} {} gender={}".format(self.__class__.__name__, self.emp_no, self.first_name, self.last_name,self.gender.value) class Department(Base): __tablename__ = 'departments' dept_no = Column(String(4), primary_key=True) dept_name = Column(String(40), nullable=False, unique=True) def __rept__(self): return "{} no={} name={}".format(type(self).__name__, self.dept_no, self.dept_name) class Dept_emp(Base): __tablename__ = 'dept_emp' emp_no = Column(Integer, ForeignKey('employees.emp_no', ondelete='CASCADE'),primary_key=True) dept_no = Column(String(4), ForeignKey('departments.dept_no', ondelete='CASCADE'),primary_key=True) from_date = Column(Date, nullable=False) to_date = Column(Date, nullable=False) def __repr__(self): return "{} empno={} deptno={}".format(type(self).__name__, self.emp_no, self.dept_no)

隐式内连接

results = session.query(Employee, Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() show(results) 2019-09-25 23:07:05,946 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 FROM employees, dept_emp WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = %(emp_no_1)s 2019-09-25 23:07:05,948 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} (Employee no=10010 name=Duangkaew Piveteau gender=F, Dept_emp empno=10010 deptno=d004) (Employee no=10010 name=Duangkaew Piveteau gender=F, Dept_emp empno=10010 deptno=d006) =============================

使用join

results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == 10010).all() results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all() print(results) 2019-09-25 23:14:22,154 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 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no WHERE employees.emp_no = %(emp_no_1)s 2019-09-25 23:14:22,156 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} 2019-09-25 23:14:22,162 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 FROM employees INNER JOIN dept_emp ON employees.emp_no = dept_emp.emp_no WHERE employees.emp_no = %(emp_no_1)s 2019-09-25 23:14:22,164 INFO sqlalchemy.engine.base.Engine {'emp_no_1': 10010} [Employee no=10010 name=Duangkaew Piveteau gender=F]

这两种写法，返回都只有一行数据。原因在于query(Employee)这个只能返回一个实体对象中去，为了解决这个问题，需要修改实体类Employee，增加属性用来存放部门信息。

sqlalchemy.orm.relationship(实体类名字符串)

class Employee(Base): __tablename__ = 'employees' emp_no = Column(Integer, primary_key=True) birth_date = Column(Date, nullable=False) first_name = Column(String(14), nullable=False) last_name = Column(String(16), nullable=False) gender = Column(Enum(MyEnum), nullable=False) hire_date = Column(Date, nullable=False) departments = relationship('Dept_emp') def __repr__(self): return "{} no={} name={} {} gender={}".format(self.__class__.__name__, self.emp_no, self.first_name, self.last_name,self.gender.value)

查询信息

# 第一种 results = session.query(Employee).join(Dept_emp).filter(Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010) # 第二种 results = session.query(Employee).join(Dept_emp, Employee.emp_no == Dept_emp.emp_no),filter(Employee.emp_no == 10010) # 第三种 results = session.query(Employee).join(Dept_emp, (Employee.emp_no == Dept_emp.emp_no) & (Employee.emp_no == 10010)) show(results.all())

第一种方法join(Dept_emp)中没有等值条件，会自动生成一个等值条件，如果后面有filter，filter中的条件会在where中出现。这种自动增加等值条件的方法不好。

第二种方法在join中增加等值条件，阻止了自动的等值条件的生成。推荐这种方式

第三种方法就是第二种，这种方法也可以。

只要不访问departments属性，就不会查dept_emp这张表。

总结

在开发中，一般都会采用ORM框架，这样就可以使用对象操作表了。 定义表映射的类，使用Column的描述器定义类的属性，使用ForeignKey来定义外键约束。 如果在一个对象中，想查看其它表对应的对象内容，就要使用relationship来定义关系。

是否使用外键约束？

力挺派 能使数据保证完整性、一致性弃用派 开发难度增加，大量数据的时候影响插入、修改、删除的效率。

在业务层保证数据的一致性。