Python -- ORM(SQLAlchemy库)

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来创建、删除表
  1. 删除继承自Base的所有表
    Base.metadata.drop_all(engine)
  2. 创建继承自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)函数查看状态
    常见的状态值有transientpendingpersistentdeleteddetached
状态说明
transient实体类尚未加入到session中,同时并没有保存到数据库中
pendingtransient的实体被add()到session中,状态切换到pending,但它还没有flush到数据库中
persistentsession中的实体对象对应着数据库中的真实记录。pending状态在提交成功后可以变成persistent状态,或者查询成功返回的实体也是persistent状态
deleted实体被删除且已经flush但未commit完成。事务提交成功了,实体变成detached,事务失败,返回persistent状态
detached删除成功的实体进入这个状态

新建一个实体,状态是transient临时的
一旦add()后从transient变成pending状态
成功commit()后从pending变成persistent状态
成功查询返回的实体对象,也是persistent状态

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
  1. 使用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来定义关系

是否使用外键约束?

  1. 力挺派
    能使数据保证完整性一致性
  2. 弃用派
    开发难度增加,大量数据的时候影响插入、修改、删除的效率。
    在业务层保证数据的一致性。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值