文章目录
ORM
- ORM,对象关系映射,对象和关系之间的映射,使用面向对象的方式来操作数据库
关系模型和Python对象之间的映射
table => class ,表映射为类
row => object ,行映射为实例
column => property ,字段映射为属性
举例
有表student,字段为id int,name varchar,age int
- 映射到Python为
class Student:
id = ?某类型字段
name = ?某类型字段
age = ?某类型字段
最终得到实例
class Student:
def __init__(self):
self.id = ?
self.name = ?
self.age = ?
SQLALchemy
- SQLAlchemy是一个ORM框架
安装
$ pip install sqlalchemy
文档
官方文档 http://docs.sqlalchemy.org/en/latest/
查看版本
import sqlalchemy
print(sqlalchemy.__version__)# 1.3.5
开发
- SQLAlchemy内部使用了连接池
创建连接
- 数据库连接的事情,交给引擎
from sqlalchemy import create_engine
dialect+driver://username:password@host:port/database
#mysqldb的连接
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
engine = sqlalchemy.create_engine("mysql+mysqldb://lqx:lqx@127.0.0.1:3306/hello")
#pymysql的连接
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
engine = sqlalchemy.create_engine("mysql+pymysql://lqx:lqx@127.0.0.1:3306/hello")
engine = sqlalchemy.create_engine("mysql+pymysql://lqx:lqx@127.0.0.1:3306/hello", echo=True)
-
echo=True
所有的操作都输入到日志。引擎是否打印执行的语句,调试的时候打开很方便 -
lazy connecting
懒连接。创建引擎并不会马上连接数据库,直到让数据库执行任务时才连接
import sqlalchemy
from sqlalchemy import create_engine
# mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True)
print(engine)
# 执行结果
Engine(mysql+pymysql://lqx:***@192.168.1.6:3306/test)
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)
)
- 做关系映射
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
# ORM Mapping
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)
# 第一个参数是字段名,如果和属性名不一致,一定要指定
# age = Column('age', Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
# 查看表结构
print(Student)
print(repr(Student.__table__))
# 显示结果
<class '__main__.Student'>
Table('student', MetaData(bind=None),
Column('id', Integer(), table=<student>, primary_key=True, nullable=False),
Column('name', String(length=64), table=<student>, nullable=False),
Column('age', Integer(), table=<student>),
schema=None)
__tablename__
指定表名
Column类指定对应的字段,必须指定
实例化
s = Student(name='tom')
print(s.name) # tom
s.age= 20
print(s.age) # 20
创建表
- 可以使用SQLAlchemy来创建、删除表
- 删除继承自Base的所有表
Base.metadata.drop_all(engine)
- 创建继承自Base的所有表
Base.metadata.create_all(engine)
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.ext.declarative import declarative_base
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒
print(engine)
# ORM Mapping
Base = declarative_base() # 基类
class Student(Base):
# 指定表名
__tablename__= 'student'
# 定义类属性对应字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
age = Column(Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
student = Student(id=1, name='jerry')
student.name = 'tom'
student.age= 20
print(student)
生产环境很少这样创建表,都是系统上线的时候由脚本生成
生成环境很少删除表,宁可废弃都不能删除
创建回话session
在一个会话中操作数据库,会话建立在连接上,连接被引擎管理。
当第一次使用数据库时,从引擎维护的连接池中获取一个连接使用.
from sqlalchemy.orm import sessionmaker
# 创建session
Session = sessionmaker(bind=engine) # 工厂方法返回类
session = Session() # 实例化
# 依然在第一次使用时连接数据库
session对象线程不安全。所以不同线程应该使用不用的session对象。
Session类和engine有一个就行了
CRUD操作
增
add()
:增加一个对象add_all()
:可迭代对象,元素是对象
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒
# ORM Mapping
Base = declarative_base() # 基类
# 创建实体类
class Student(Base):
# 指定表名
__tablename__= 'student'
# 定义类属性对应字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
age = Column(Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
# 删除表
Base.metadata.drop_all(engine)
# 创建表
Base.metadata.create_all(engine)
# 创建seesion
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.rollbake()
print('roll back')
raise
add_all()方法不会提交成功的,不是因为它不对,而是s,s成功提交后,s的主键就有了值,所以,只要s没有修改过,就认为没有改动。如下,s变化了,就可以提交修改了
s.name = 'jerry' # 修改
session.add_all([s])
s主键没有值,就是新增;主键有值,就是找到主键对应的记录修改
简单查询 query()
使用query()
方法,返回一个Query对象
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒
# ORM Mapping
Base = declarative_base() # 基类
# 创建实体类
class Student(Base):
# 指定表名
__tablename__= 'student'
# 定义类属性对应字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
age = Column(Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
# # 删除表
# Base.metadata.drop_all(engine)
# 创建表
# Base.metadata.create_all(engine)
# 创建seesion
Session = sessionmaker(bind=engine)
session = Session()
students = session.query(Student)
print(students.count())
for student in students:
print(student)
print('~~~~~~~~~~~')
student = session.query(Student).get(1) # 通过主键查询
print(student)
query方法将实体类传入,返回类的对象可迭代对象,这时候并不查询。迭代它就执行SQL来查询数据库,封装数据到指定类的实例
get方法使用主键查询,返回一条传入类的一个实例
修改
- 修改需先查询,在修改,不然会调用插入方法
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒
# ORM Mapping
Base = declarative_base() # 基类
# 创建实体类
class Student(Base):
# 指定表名
__tablename__= 'student'
# 定义类属性对应字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
age = Column(Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
# # 删除表
# Base.metadata.drop_all(engine)
# 创建表
# Base.metadata.create_all(engine)
# 创建seesion
Session = sessionmaker(bind=engine)
session = Session()
student = session.query(Student).get(1) # 通过主键查询
print(student)
student.name = 'ben'
student.age = 30
print(student)
session.add(student)
session.commit()
- 修改前
- 修改后
删除
先看下数据库,表中有
1 ben 30
编写如下程序来删除,会发生什么?
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒
# ORM Mapping
Base = declarative_base() # 基类
# 创建实体类
class Student(Base):
# 指定表名
__tablename__= 'student'
# 定义类属性对应字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
age = Column(Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
Session = sessionmaker(bind=engine)
session = Session()
try:
student = Student(id=1, name='ben', age=30)
session.delete(student)
session.commit()
except Exception as e:
session.rollback()
print('roll back')
print(e)
# 执行结果
roll back
Instance '<Student at 0x1779fea9b70>' is not persisted
会产生一个异常
Instance '<Student at 0x3e654e0>' is not persisted
未持久的异常!
状态**
需导入from sqlalchemy.orm.state import InstanceState
库
- 每一个实体,都有一个状态属性
_sa_instance_state
,其类型是sqlalchemy.orm.state.InstanceState
- 使用
sqlalchemy.inspect(entity)
函数查看状态
常见的状态值有transient
、pending
、persistent
、deleted
、detached
状态 | 说明 |
---|---|
transient | 实体类尚未加入到session中,同时并没有保存到数据库中 |
pending | transient的实体被add()到session中,状态切换到pending,但它还没有flush到数据库中 |
persistent | session中的实体对象对应着数据库中的真实记录。pending状态在提交成功后可以变成persistent状态,或者查询成功返回的实体也是persistent状态 |
deleted | 实体被删除且已经flush但未commit完成。事务提交成功了,实体变成detached,事务失败,返回persistent状态 |
detached | 删除成功的实体进入这个状态 |
新建一个实体,状态是transient
临时的
一旦add()
后从transient
变成pending
状态
成功commit()
后从pending
变成persistent
状态
成功查询返回的实体对象,也是persisten
t状态
persistent
状态的实体,修改依然是persistent
状态
persistent
状态的实体,删除后,flush
但没有commit
,就变成deteled
状态
成功提交,变为detached
状态提交失败,还原到persistent
状态。flush
方法,主动把改变应用到数据库中去
删除、修改操作,需要对应一个真实的记录,所以要求实体对象是persistent状态
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒
# ORM Mapping
Base = declarative_base() # 基类
# 创建实体类
class Student(Base):
# 指定表名
__tablename__= 'student'
# 定义类属性对应字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
age = Column(Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
Session = sessionmaker(bind=engine)
session = Session()
def getstate(instance, i):
inp:InstanceState = sqlalchemy.inspect(student)
states = "{}: key={}\nid={}, attached={}, transient={}," \
"pending={}, \npersistant={}, deleted={}, detached={}".format(
i ,inp.key,
inp.session_id, inp._attached, inp.transient,
inp.pending, inp.persistent, inp.deleted, inp.detached
)
print(states, end='\n~~~~~~~~~\n')
student = session.query(Student).get(1)
getstate(student, 1) # persistent
try:
student = Student(id=1, name='ben', age=30)
getstate(student, 2) # transit
student = Student(name='tom', age=20)
getstate(student, 3) # transit
session.add(student) # add后变成pending
getstate(student,4) # pending
# session.delete(student) # 异常,删除的前提必须是persistent,也就是说先查后删
session.commit() # 提交后,变成persistent
getstate(student, 6) # persistent
except Exception as e:
session.rollback()
print('roll back')
运行结果
1: key=(<class '__main__.Student'>, (1,), None)
id=1, attached=True, transient=False,pending=False,
persistant=True, deleted=False, detached=False
persistent就是key不为None,附加的,且不是删除的,有sessionid
~~~~~~~~~
2: key=None
id=None, attached=False, transient=True,pending=False,
persistant=False, deleted=False, detached=False
transient的key为None,且无附加
~~~~~~~~~
3: key=None
id=None, attached=False, transient=True,pending=False,
persistant=False, deleted=False, detached=False
同上
~~~~~~~~~
4: key=None
id=1, attached=True, transient=False,pending=True,
persistant=False, deleted=False, detached=False
add后变成pending,已附加,但是没有key,有了sessionid
~~~~~~~~~
6: key=(<class '__main__.Student'>, (2,), None)
id=1, attached=True, transient=False,pending=False,
persistant=True, deleted=False, detached=False
提交成功后,变成persistent,有了key
~~~~~~~~~
Process finished with exit code 0
- 删除状态的变化
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True) # lazy 懒
# ORM Mapping
Base = declarative_base() # 基类
# 创建实体类
class Student(Base):
# 指定表名
__tablename__= 'student'
# 定义类属性对应字段
id = Column(Integer, primary_key=True, autoincrement=True)
name = Column(String(64), nullable=False)
age = Column(Integer)
def __repr__(self):
return "<{} id={}, name={}, age={}>".format(
__class__.__name__, self.id, self.name, self.age
)
Session = sessionmaker(bind=engine)
session = Session()
def getstate(instance, i):
inp:InstanceState = sqlalchemy.inspect(student)
states = "{}: key={}\nid={}, attached={}, transient={}," \
"pending={}, \npersistant={}, deleted={}, detached={}".format(
i ,inp.key,
inp.session_id, inp._attached, inp.transient,
inp.pending, inp.persistent, inp.deleted, inp.detached
)
print(states, end='\n~~~~~~~~~\n')
student = session.query(Student).get(1)
getstate(student, 7) # persistent
try:
session.delete(student) # 删除的前提是persistent
getstate(student, 8) # persistent
session.flush()
getstate(student, 9) # deleted
session.commit()
getstate(student, 13) # detached
except Exception as e:
session.rollback()
print('roll back')
执行结果
7: key=(<class '__main__.Student'>, (1,), None)
id=1, attached=True, transient=False,pending=False,
persistant=True, deleted=False, detached=False
~~~~~~~~~
8: key=(<class '__main__.Student'>, (1,), None)
id=1, attached=True, transient=False,pending=False,
persistant=True, deleted=False, detached=False
~~~~~~~~~
2019-06-27 16:49:04,554 INFO sqlalchemy.engine.base.Engine DELETE FROM student WHERE student.id = %(id)s
2019-06-27 16:49:04,554 INFO sqlalchemy.engine.base.Engine {'id': 1}
9: key=(<class '__main__.Student'>, (1,), None)
id=1, attached=True, transient=False,pending=False,
persistant=False, deleted=True, detached=False
delete后flush,状态变成deleted,不过是附加的
~~~~~~~~~
2019-06-27 16:49:04,556 INFO sqlalchemy.engine.base.Engine COMMIT
一旦提交后
13: key=(<class '__main__.Student'>, (1,), None)
id=None, attached=False, transient=False,pending=False,
persistant=False, deleted=False, detached=True
状态转为detached
~~~~~~~~~
复杂查询 filter
实体类
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer, Date, Enum, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState
import enum
Base = declarative_base() # 基类
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), 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(
__class__.__name__, self.emp_no, "{} {}".format(
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, Employee.emp_no < 10018)
show(emps)
emps = session.query(Employee).filter(emps.emp_no > 10015).filter(Employee.emp_no < 10018)
show(emps)
emps = session.query(Employee).filter(and_(Employee.emp_no > 10015, Employee.emp_no <10018))
show(emps)
emps = session.query(Employee).filter((Employee.emp_no > 10015) & (Employee.emp_no < 10018))
show(emps)
- 查询 或 or 两种方法
emps = session.query(Employee).filter(or_(Employee.emp_no > 10015, Employee.emp_no < 10018))
show(emps)
emps = session.query(Employee).filter((Employee.emp_no > 10015) | (Employee.emp_no < 10018))
show(emps)
- 查询 非 两种方法
emps = session.query(Employee).filter(not_(Employee.emp_no < 10018))
show(emps)
emps = session.query(Employee).filter(~(Employee.emp_no > 10018))
show(emps)
- in 操作
emps = session.query(Employee).filter(Employee.emp_no.in_([10015, 10018, 10020]))
show(emps)
- not in 操作
emps = session.query(Employee).filter(~Employee.emp_no.in_([10015, 10018, 10020]))
show(emps)
emps = session.query(Employee).filter(~Employee.emp_no.notin_([10015, 10018, 10020]))
show(emps)
- like 字符串匹配操作
emps = session.query(Employee).filter(Employee.last_name.like('p%'))
show(emps)
- not like
emps = session.query(Employee).filter(Employee.last_name.notlike('p%'))
show(emps)
ilike可以忽略带小写匹配
排序 order_by
- 升序
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no)
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.asc())
show(emps)
- 降序
emps = session.query(Employee).filter(Employee.emp_no > 10010).order_by(Employee.emp_no.desc())
show(emps)
- 多列排序
emps = session.query(Employee).filter(Employee.emp_no >
10010).order_by(Employee.last_name).order_by(Employee.emp_no.desc())
show(emps)
分页 limit
emps = session.query(Employee).limit(4)
show(emps)
emps = session.query(Employee).limit(4).offset(18)
show(emps)
消费者方法
消费者方法调用后,Query对象(可迭代)就转换成了一个容器
# 总行数
emps = session.query(Employee)
print(emps.count()) # 聚合函数count(*)的查询
# 取所有数据
print(emps.all()) # 返回列表,查不到返回空列表
# 取首行
print(emps.first()) # 返回首行,查不到返回None,等价limit
# 有且只能有一行
print(emps.one()) #如果查询结果是多行抛异常
print(emps.limit(1).one())
# 删除 delete by query
session.query(Employee).filter(Employee.emp_no > 10018).delete()
session.commmit # 提交则删除
聚合、分组
需导入 from sqlalchemy import func
- 聚合函数
# 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()
for g,y in query:
print(g.value, y)
关联查询
从语句看出员工、部门之间的关系是多对多关系。
先把这些表的Model类和字段属性建立起来。
import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer, Date, Enum, ForeignKey
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.state import InstanceState
from sqlalchemy import or_, and_, not_
import enum
from sqlalchemy import func
Base = declarative_base() # 基类
IP = '192.168.1.6'
USERNAME = 'lqx'
PASSWORD = 'lqx'
DBNAME ='test'
PORT = 3306
engine = create_engine("mysql+pymysql://{}:{}@{}:{}/{}".format(
USERNAME, PASSWORD, IP, PORT, DBNAME
), echo=True)
Session = sessionmaker(bind=engine)
session = Session()
class MyEnum(enum.Enum):
M = 'M'
F = 'F'
# 打印函数
def show(emps):
for x in emps:
print(x)
print('~~~~~~~~~~~~\n')
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(
__class__.__name__, self.emp_no, "{} {}".format(
self.first_name, self.last_name), self.gender.value
)
class Department(Base):
__tablename__ = 'departments'
dept_no = Column(String(4), primary_key=True)
dept_name = Column(String(40), nullable=False, unique=True)
def __repr__(self):
return "{} no={} name={}".format(
type(self).__name__, self.dept_no, self.dept_name)
class Dept_emp(Base):
__tablename__ = 'dept_emp'
emp_no = Column(Integer, ForeignKey('employees.emp_no',
ondelete='CASCADE'), primary_key=True)
dept_no = Column(String(4), ForeignKey('depatments.dept_no', ondelete='CASCADE'),
primary_key=True)
from_date = Column(Date, nullable=False)
to_date = Column(Date, nullable=False)
def __repr__(self):
return "{} empno={} deptno={}".format(
type(self).__name__, self.emp_no, self.dept_no)
ForeignKey('employees.emp_no', ondelete='CASCADE')
定义外键约束
需求:查询10010员工的所在的部门编号及员工信息
- 1、使用隐式内连接
# 查询10010员工的所在的部门编号及员工信息
results = session.query(Employee, Dept_emp).filter(
Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010).all()
show(results)
# 查询结果2行
(<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)
这种方式会产生隐式连接的语句
SELECT *
FROM employees, dept_emp
WHERE employees.emp_no = dept_emp.emp_no AND employees.emp_no = 10010
- 使用join
# 查询10010员工的所在的部门编号及员工信息
# 第一种写法
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()
show(results)
这两种写法,返回都只有一行数据,为什么?
- 它们生成的SQL语句是一样的,执行该SQL语句返回确实是2行记录,可是Python中的返回值列表中只有一个元素?
- 原因在于
query(Employee)
这个只能返回一个实体对象中去,为了解决这个问题,需要修改实体类Employee,增加属性用来存放部门信息
sqlalchemy.orm.relationship(实体类名字符串)
需导入from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy.orm import sessionmaker,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)
departmens = relationship('Dept_emp') #
def __repr__(self): # 注意增加self.dept_emps
return "<{} no={}, name={}, gender={} depts={}>".format(
__class__.__name__, self.emp_no, "{} {}".format(
self.first_name, self.last_name), self.gender.value,
self.departmens
)
- 查询信息
# 查询10010员工的所在的部门编号及员工信息
# 第一种
results = session.query(Employee).join(Dept_emp).filter(
Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010)
# 第二种
results = session.query(Employee).join(Dept_emp,
Employee.emp_no == Dept_emp.emp_no).fiter(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(Employee.emp_no == Dept_emp.emp_no)
,这个条件会在where中出现。第一种这种自动增加join的等值条件的方式不好,不要这么写 - 第二种方法在join中增加等值条件,阻止了自动的等值条件的生成。这种方式推荐
- 第三种方法就是第二种,这种方式也可以
再看一个现象
results = session.query(Employee).join(Dept_emp).filter(
Employee.emp_no == Dept_emp.emp_no).filter(Employee.emp_no == 10010)
for x in results:
print(x.emp_no)
可以看出只要不访问departments属性,就不会查dept_emp这张表
总结
在开发中,一般都会采用ORM框架,这样就可以使用对象操作表了
定义表映射的类,使用Column的描述器定义类属性,使用ForeignKey来定义外键约束
如果在一个对象中,想查看其它表对应的对象的内容,就要使用relationship来定义关系
是否使用外键约束?
- 力挺派
能使数据保证完整性一致性 - 弃用派
开发难度增加,大量数据的时候影响插入、修改、删除的效率。
在业务层保证数据的一致性。