Python操作MySQL之SQLAlchemy

一概述

SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，使用关系对象映射进行数据库操作，简单来说即是：将对象转换成SQL，然后使用数据API执行SQL并获取执行结果。

类似于如下关系：

Python中的类 == 数据库中的表
Python中类的属性 == 表中的字段
python中类的实例 == 表中的行

ORM英文全称Object Relational Mapping，翻译过来即为对象映射关系。简单来说，对于Python这种面向对象的编程语言来说一切皆对象，但我们使用的数据库却是关系型的，为了保证使用的一致性，通过ORM将编程语言的对象模型和数据库的关系模型建立映射关系，这样我们在使用编程语言对数据库进行操作的时候，就可以直接使用编程语言的对象模型进行操作，而不用直接使用SQL语言。
除了本文学习到的 SQLAlchemy外，Python中常见的ORM库还有SQLObject、Storm以及Django中的ORM等。

扫盲：何为ORM？

1.1 安装

pip3 install sqlalchemy

1.2 架构与流程

SQLAlchemy主要有两个组件：SQLAlchemy ORM 和 SQLAlchemy Core 。

流程说明：

1、使用者通过ORM对象提交命令

2、将命令交给SQLAlchemy Core（Schema/Types SQL Expression Language）转换成SQL

3、使用 Engine/ConnectionPooling/Dialect 进行数据库操作

3.1 匹配使用者事先配置好的egine
3.2 egine从连接池中取出一个链接
3.3 基于该链接通过Dialect调用DB API，将SQL转交给它去执行

上述流程分析，可以大致分为两个阶段：

第一个阶段（流程1-2）：将SQLAlchemy的对象换成可执行的sql语句

第二个阶段（流程3）：将sql语句交给数据库执行

如果我们不依赖于SQLAlchemy的转换，而是自己写好sql语句，那是不是意味着可以直接从第二个阶段开始执行了，事实上正是如此，我们完全可以只用SQLAlchemy执行纯sql语句，实例参照2.2。

1.3 DB API

SQLAlchemy本身无法操作数据库，其必须用pymysql/mysqldb等第三方库，Dialect用于和DBAPI进行交流，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作，如：

# 1、MySQL-Python
mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
   
# 2、pymysql
mysql+pymysql://<username>:<password>@<host>/<dbname>[?<options>]
   
# 3、MySQL-Connector
mysql+mysqlconnector://<user>:<password>@<host>[:<port>]/<dbname>
   
# 4、cx_Oracle
oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]

二执行原生SQL

在1.2中我们说到，我们完全可以只用SQLAlchemy执行纯sql语句SQLAlchemy，它内部使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库，然后再通过Dialect执行SQL语句。

2.1 创建连接

所有的数据库操作，我们都需，先创建一个连接，告诉我们的代码，我们需要连接的数据库的路径，具体代码如下：

from sqlalchemy import create_engine

db_conn_str = "mysql+pymysql://username:password@hostname/tb"
engine = create_engine(db_conn_str,max_overflow=5,echo=True)
print(engine)   # Engine(mysql+pymysql://username:***@hostname/tb)

create_engine方法返回一个Engine实例，只有触发数据库事件发生时Engine实例才真正去连接数据库。

create_engine的可选参数很多，下面举例说明几个常用的：

max_overflow : 允许连接到数据库的最大连接数；
echo： 将其设置为True的时候，会打印所有的状态变化，包括转换的SQL语句。 一般情况下，将其设置为Flase的；
encoding：设置字符编码

2.2 一个实例

#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine

# 1. 准备
# 需要事先安装好pymysql
# 需要事先创建好数据库:create database sqlatest charset utf8;

# 2. 创建引擎
engine = create_engine('mysql+mysqlconnector://root@127.0.0.1/sqlatest?charset=utf8')

# 3. 执行sql
# engine.execute(
#     'create table if not EXISTS userinfo('
#         'id int(11) not null auto_increment primary key,'
#         'username varchar(32) NOT NULL,'
#         'password varchar(32) NOT NULL'
#     ')engine = innodb default charset utf8;')

# cur = engine.execute(
#     "INSERT INTO userinfo (username, password) VALUES ('joe1','123')"
# )

# 按关键字传值
# cur = engine.execute(
#     'INSERT INTO userinfo (username, password) VALUES(%(username)s,%(password)s);',username='joe2',password='123'
# )

# 按位置传值
# cur = engine.execute(
#     "INSERT INTO userinfo (username, password) VALUES(%s, %s);",[('joe3','123'),('joe4','123'),]
# )

# 4. 新插入行的自增id
# print(cur.lastrowid)

# 5. 查询
# cur = engine.execute('select * from userinfo')
#
# res1 = cur.fetchone()  # 获取一行
# res2 = cur.fetchmany(2)  # 获取多行
# res3 = cur.fetchall()  # 获取所有行
# print(res1)
# print(res2)
# print(res3)

View Code

注意：执行时会警告1366，如下：

..\pymysql\cursors.py:166: Warning: (1366, "Incorrect string value: '\\xB9\\xA4\\xD7\\xF7\\xC8\\xED...' for column 'VARIABLE_VALUE' at row 7")
  result = self._query(query)

造成该警告的应该是MySQL驱动的问题，解决方法如下：

第一步：

安装模块：pip3 install mysql-connector-python，本人亲测不建议加上参数--allow-external 直接pip3安装。以下为原文：

由于MySQL服务器以独立的进程运行，并通过网络对外服务，所以，需要支持Python的MySQL驱动来连接到MySQL服务器。MySQL官方提供了mysql-connector-python驱动，但是安装的时候需要给pip命令加上参数--allow-external：

pip install mysql-connector-python--allow-external mysql-connector-python

如果上面的命令安装失败，可以试试另一个驱动：

pip install mysql-connector

第二步：

将连接引擎的：engine = create_engine("mysql+pymysql//user:password@host/{data_base}")

修改为：engine = create_engine("mysql+mysqlconnector//user:password@host/{data_base}",encoding='utf-8')

就是将pymysql连接数据库换成了官方的连接引擎！

三 ORM功能使用

3.1 创建Base

declarative_base类维持了一个从类到表的关系，通常一个应用使用一个base实例，所有实体类都应该继承此类对象。

简单来说，就是调用这个方法，可以产生一个基类。这个基类和它的子类，可以通过接受到的数据，映射成一张表。

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

3.2 创建表

我们以工厂的一个情况出发，具备以下几种东东：车间，设备，维修组，工程师，利用ORM创建它们，并建立好它们直接的关系，具体如下：

#!/usr/bin/env python
# -*- coding:utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index

# 创建连接，等同于pymysql中的pymysql.connection()
db_conn_str = "mysql+mysqlconnector://root:@localhost/sqlatest"
engine = create_engine(db_conn_str, max_overflow=5)

# 生成一个SQLORM基类
Base = declarative_base()

class Workshop(Base):
    __tablename__ = "workshop"
    id = Column(Integer, primary_key=True, autoincrement=True)
    wname = Column(String(32), nullable=False, unique=True, index=True)

# 一对一:一个组管理一个车间，一个车间只被一个组管理
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    gname = Column(String(64), nullable=False, unique=True, index=True)
    workshop_id = Column(Integer, ForeignKey('workshop.id'), unique=True)

# 一对多:一个车间包含多台设备，一台设备只属于某一车间
class Equipment(Base):
    __tablename__ = 'equipment'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(64), nullable=False, index=True)
    workshop_id = Column(Integer,ForeignKey('workshop.id'))  # ForeignKey建在多的一方

# 多对多:多个工程师可以是同一个组，多个组可以包含同一个工程师
class Engineer(Base):
    __tablename__ = "engineer"
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(32), nullable=False, unique=True, index=True)
    age = Column(Integer, nullable=True)

class Engineer2Group(Base):
    __tablename__ = "workers2group"
    id = Column(Integer, primary_key=True, autoincrement=True)
    eid = Column(Integer, ForeignKey('engineer.id'))  # ForeignKey建在多的一方
    gid = Column(Integer, ForeignKey('group.id'))  # ForeignKey建在多的一方

    __table_args__ = (
        UniqueConstraint(eid, gid, name='uix_eid_gid'),
    )


def init_db():
    """
    根据类创建数据库表
    :return:
    """
    Base.metadata.create_all(engine)

def drop_db():
    """
    根据类删除数据库表
    :return:
    """
    Base.metadata.drop_all(engine)

if __name__ == '__main__':
    init_db()

View Code

注：设置外键的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])

元数据（Metadata），又称中介数据、中继数据，为描述数据的数据（data about data），主要是描述数据属性（property）的信息，用来支持如指示存储位置、历史数据、资源查找、文件记录等功能。
比如，我们在上面创建表时，需要告诉计算机，我们要创建的表的名字，在哪个数据库，具体有哪些列，怎样进行连接等，这些都为元数据。

扫盲：何为元数据？

注意点：

每个类一定要有__tablename__，也就是一定要有表名；
至少有一列是主键；
类中定义的__repr()方法可选。

对于主键，Oracle没有自增主键，解决方法如下：

from sqlalchemy import Sequence
Column(Integer,Sequence('user_idseq'),prmary_key=True)

Column中常有的参数如下：

primary_key：True     设置此字段为主键；
autoincrement：True      表示这个字段是自增的；
index：True      设置这个字段为索引；
nullable：True       允许次字段为空；
unique: True     设置此字段唯一。此字段相同的数据，第一条数据插入后，之后的就不允许再插入了，直接pass掉。

3.3 创建session

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()

从字面意思来看，sessionmaker为会话制造工厂，是一个可以不断产生新会话的类，Session是真正与数据库通信的handler，Session类将创建新的Session对象绑定到我们的数据库。

当你需要与数据库连接时，实例化一个Session对象即可。

类似于pymysql，我们先要与数据库建立连接（create_engine），然后通过会话来（与pymysql中的游标（cursor）作用类似）建立自己代码与数据库之间的通信。

通信过程剖析

# 建立会话
Session = sessionmaker(bind=engine)
session = Session()

# 添加数据
session.add(data)

# 触发语句
session.commit()

# 关闭会话
session.close()

3.4 操作表

#!/usr/bin/env python
# -*- coding:utf-8 -*-

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker

# 创建连接，等同于pymysql中的pymysql.connection()
db_conn_str = "mysql+mysqlconnector://root:@localhost/sqlatest"
engine = create_engine(db_conn_str, max_overflow=5)

# 生成一个SQLORM基类
Base = declarative_base()

class Workshop(Base):
    __tablename__ = "workshop"
    id = Column(Integer, primary_key=True, autoincrement=True)
    wname = Column(String(32), nullable=False, unique=True, index=True)

# 一对一:一个组管理一个车间，一个车间只被一个组管理
class Group(Base):
    __tablename__ = 'group'
    id = Column(Integer, primary_key=True, autoincrement=True)
    gname = Column(String(64), nullable=False, unique=True, index=True)
    workshop_id = Column(Integer, ForeignKey('workshop.id'), unique=True)

# 一对多:一个车间包含多台设备，一台设备只属于某一车间
class Equipment(Base):
    __tablename__ = 'equipment'
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(64), nullable=False, index=True)
    workshop_id = Column(Integer,ForeignKey('workshop.id'))  # ForeignKey建在多的一方

# 多对多:多个工程师可以是同一个组，多个组可以包含同一个工程师
class Engineer(Base):
    __tablename__ = "engineer"
    id = Column(Integer, primary_key=True, autoincrement=True)
    ename = Column(String(32), nullable=False, unique=True, index=True)
    age = Column(Integer, nullable=True)

class Engineer2Group(Base):
    __tablename__ = "workers2group"
    id = Column(Integer, primary_key=True, autoincrement=True)
    eid = Column(Integer, ForeignKey('engineer.id'))  # ForeignKey建在多的一方
    gid = Column(Integer, ForeignKey('group.id'))  # ForeignKey建在多的一方

    __table_args__ = (
        UniqueConstraint(eid, gid, name='uix_eid_gid'),
    )


def init_db():
    """
    根据类创建数据库表
    :return:
    """
    Base.metadata.create_all(engine)

def drop_db():
    """
    根据类删除数据库表
    :return:
    """
    Base.metadata.drop_all(engine)

# 创建会话，相当于pymysql中的游标
Session = sessionmaker(bind=engine)
session = Session()

表结构+数据库连接

增

# 添加单行数据
# add_data = Engineer(ename="技师1", age=20)  # add_data实则为Engineer的一个对象，即是数据表中的一行数据
# session.add(add_data)

# 添加多行数据
add_datas = [
    Engineer(ename="技师2", age=24),
    Engineer(ename="技师3", age=24),
    Engineer(ename="技师4", age=24),
     ]
session.add_all(add_datas)
session.commit()
session.close()

View Code

删

session.query(Engineer).filter(Engineer.age == 24).delete()
session.commit()
session.close()

View Code

改

session.query(Engineer).filter(Engineer.id == 2).update({"age" : 30})
session.query(Engineer).filter(Engineer.id > 2).update({Engineer.ename: Engineer.ename + "号"}, synchronize_session=False)
session.query(Engineer).filter(Engineer.age == 20).update({"age": Engineer.age + 3}, synchronize_session="evaluate")
session.commit()
session.close()

View Code

synchronize_session用于query在进行delete or update操作时，对session的同步策略：
•False -- 不对session进行同步，直接进行delete or update操作;
•'fetch' -- 在delete or update操作之前，先发一条sql到数据库获取符合条件的记录；在delete or update操作之后，将session的identity_map与前一步获取到的记录进行match，符合条件的就从session中删掉或更新。
•'evaluate' -- 在delete or update操作之前，用query中的条件直接对session的identity_map中的objects进行eval操作，将符合条件的记录下来; 在delete or update操作之后，将符合条件的记录删除或更新。

synchronize_session

synchronize_session更多可参考：https://www.cnblogs.com/rednada/p/5520896.html

查

前面已经用到了，在Session上使用query()方法，创建了一个Query对象。此函数接受数目可变的参数，可以是任意组合的类和类表的描述符。

query.all()：返回列表
query.first()：返回第一个元素
query.one()：有且只有一个元素时才正确返回。

# 查询Engineer的数据
ress = session.query(Engineer).all()
for res in ress:
    print(res.id,res.ename,res.age)

View Code

filter_by()与filter()

filter_by()接收的参数形式是关键字参数，而filter接收的参数是更加灵活的SQL表达式结构。

# ress = session.query(Engineer).filter_by(age=23).all()
ress = session.query(Engineer).filter(Engineer.age==23).all()
for res in ress:
    print(res.id,res.ename,res.age)

View Code

3.5 其他查询相关

3.5.1 准备表和数据

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import sessionmaker

egine = create_engine('mysql+mysqlconnector://root@127.0.0.1:3306/sqlatest?charset=utf8', max_overflow=5)

Base = declarative_base()


# 一对多：一个部门可以有多个员工，员工不能属于多个部门
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)

class Staff(Base):
    __tablename__ = 'staff'
    id = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('department.id'))

def init_db():
    Base.metadata.create_all(egine)

def drop_db():
    Base.metadata.drop_all(egine)

drop_db()
init_db()
Session = sessionmaker(bind=egine)
session = Session()

# 准备数据
session.add_all([
    Department(dname='设备'),
    Department(dname='销售'),
    Department(dname='技术'),
    Department(dname='人事'),
])

session.add_all([
    Staff(sname='Joe1', dep_id=1),
    Staff(sname='Joe2', dep_id=2),
    Staff(sname='Joe3', dep_id=3),
    Staff(sname='Joe4', dep_id=4),
    Staff(sname='Jack', dep_id=1),
    Staff(sname='Tracy', dep_id=1),
    Staff(sname='Kobe', dep_id=3),
    Staff(sname='James', dep_id=4),
])

session.commit()
session.close()

View Code

3.5.2 条件、通配符、limit、排序、分组、连表、组合

# 1. 条件
# sql = session.query(Staff).filter_by(sname='Joe1')  # filter_by只能传参数：什么等于什么
# res = sql.all()  # sql语句的执行结果

# and/or
# res = session.query(Staff).filter(Staff.id>0, Staff.sname == 'Joe1').all()  # filter内传的是表达式，逗号分隔，默认为and
# res = session.query(Staff).filter(Staff.id.between(1,3), Staff.sname == 'Joe1').all()

# in/not in
# res = session.query(Staff).filter(Staff.id.in_([1,3,5,7]), Staff.sname == 'Joe1').all()
# res = session.query(Staff).filter(~Staff.id.in_([1,3,5,7]), Staff.sname == 'Joe1').all()  # ~代表取反，转换成sql就是关键字not

# and_/or_
# from sqlalchemy import and_,or_
# res = session.query(Staff).filter(and_(Staff.id > 0, Staff.sname=='Joe1')).all()
# res = session.query(Staff).filter(or_(Staff.id < 2, Staff.sname=='Joe1')).all()
# res = session.query(Staff).filter(
#     or_(
#         Staff.dep_id == 3,
#         and_(Staff.id < 1, Staff.sname != 'Joe1'),
#         Staff.sname == 'James'
#     )
# ).all()

# null/not null
# res = session.query(Staff).filter(Staff.sname == None).all()
# res = session.query(Staff).filter(Staff.sname != None).all()

# 2. 通配符like
# res = session.query(Staff).filter(Staff.sname.like('%Joe%')).all()
# res = session.query(Staff).filter(~Staff.sname.like('%Joe%')).all()

# 3. 限制limit
# res = session.query(Staff)[0:5:2]

# 4. 排序order_by
# res = session.query(Staff).order_by(Staff.dep_id.desc()).all()
# res = session.query(Staff).order_by(Staff.dep_id.desc(), Staff.id.asc()).all()

# 5. 分组group_by
from sqlalchemy.sql import func
# res = session.query(Staff.dep_id).group_by(Staff.dep_id).all()
# res = session.query(
#     func.max(Staff.dep_id),
#     func.min(Staff.dep_id),
#     func.sum(Staff.dep_id),
#     func.avg(Staff.dep_id),
#     func.count(Staff.dep_id),
# ).group_by(Staff.dep_id).all()
# res = session.query(
#     Staff.dep_id,
#     func.count(),
# ).group_by(Staff.dep_id).having(func.count() > 1).all()

# 6. 连表
# 笛卡尔积模式，调取结果是需要加下标
# res = session.query(Staff, Department).all()    # select * from staff,department;

# where条件
# res = session.query(Staff, Department).filter(Staff.dep_id == Department.id).all()
# for row in res:
#     Staff_tb = row[0]
#     Department_tb = row[1]
#     print(Staff_tb.id, Staff_tb.sname, Department_tb.id, Department_tb.dname)

# 内连接
# res = session.query(Staff).join(Department)
# join默认为内连接，SQLAlchemy会自动帮我们通过foreign key字段去找关联关系
# 但是上述查询的结果均为Staff表的字段，这样链表还有毛线意义，于是我们修改为
# res = session.query(Staff.id, Staff.sname, Staff.dep_id, Department.dname).join(Department).all()

# 左连接:isouter=True；右连接同左连接，只是把两个表的位置换一下
# res = session.query(Staff.id, Staff.sname, Staff.dep_id, Department.dname).join(Department,isouter=True).all()

# 7.组合
q1 = session.query(Staff.id, Staff.sname).filter(Staff.id > 0,Staff.id < 3)
q2 = session.query(Staff.id, Staff.sname).filter(
    or_(
        Staff.sname.like('%Joe%'),
        Staff.sname.like('%T%'),
    )
)
res1 = q1.union(q2)  # 组合+去重
res2 = q1.union_all(q2)  # 组合，不去重

print([i.sname for i in q1.all()])  # ['Joe1', 'Joe2', 'Joe3', 'Joe4']
print([i.sname for i in q2.all()])  # ['Joe1', 'Joe2', 'Joe3', 'Joe4', 'Tracy']
print([i.sname for i in res1.all()])  # ['Joe1', 'Joe2', 'Joe3', 'Joe4', 'Tracy']
print([i.sname for i in res2.all()])  # ['Joe1', 'Joe2', 'Joe3', 'Joe4', 'Joe1', 'Joe2', 'Joe3', 'Joe4', 'Tracy']

View Code

3.5.3 子查询

有三种形式的子查询，注意：子查询的sql必须用括号包起来，尤其在形式三中需要注意这一点

# 示例：查出id大于2的员工，当做子查询的表使用
# 原生SQL:
# select * from (select * from staff where id > 2) as A;

# ORM:
res = session.query(
    session.query(Staff).filter(Staff.id > 2).subquery()
).all()

形式一:子查询当做一张表来用,调用subquery()

# 示例：查出销设备部门的员工姓名
# 原生SQL:
# select sname from staff where dep_id in (select id from department where dname='设备');

# ORM:
res = session.query(Staff.sname).filter(Staff.dep_id.in_(
    # session.query(Department.id).filter_by(dname = '设备')  # 传的是参数
    session.query(Department.id).filter(Department.dname == '设备')  # 传的是表达式
)).all()

形式二:子查询当做in的范围用,调用in_

# 示例:查询所有的员工姓名与部门名
# 原生SQL:
# select sname as 员工姓名,(select dname from department where id = staff.dep_id) as 部门名 from staff;

# ORM:
sub_sql = session.query(Department.dname).filter(Department.id == Staff.dep_id)  # SELECT department.dname FROM department, staff WHERE department.id = staff.dep_id;
sub_sql.as_scalar()  # as_scalar的功能就是把上面的sub_sql加上了括号
res = session.query(Staff.sname, sub_sql.as_scalar()).all()

形式三:子查询当做select后的字段,调用as_scalar()

四 relationship

如新建表格一样，SQLAlchemy中的映射关系有四种，分别是多对一，一对多，一对一，多对多。使用relationship与生成表结构无关，仅用于查询方便

4.1 多对一与一对多

因为外键(ForeignKey)始终定义在多的一方。如果relationship定义在多的一方，那就是多对一，一对多与多对一的区别在于其关联(relationship)的属性在多的一方还是一的一方，如果relationship定义在一的一方那就是一对多。

class Department(Base):
    __tablename__ = "department"
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)

class Staff(Base):
    __tablename__ = 'staff'
    # ForeignKey建在多的一方
    id = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('department.id'))
    # relationship建在多的一方
    dep = relationship("Department")

多对一

class Department(Base):
    __tablename__ = "department"
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)
    # relationship建在一的一方
    staf = relationship("Staff")

class Staff(Base):
    __tablename__ = 'staff'
    # ForeignKey建在多的一方
    id = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('department.id'))

一对多

为了建立双向关系，可以在relationship()中设置backref（使用方法：backref=“指定名称”），比如在多对一的代码中，插入backref，department对象就有staff属性。

department = relationship("Department", backref = "staf")

同时，如果设置 cascade= ‘all’，可以级联删除。如果不设置cascade，删除department时，其关联的staff不会删除，只会把staff关联的staff.dep_id设置为空，设置cascade后就可以级联删除staff。

dep = relationship("Department", cascade="all", backref = "staf")

4.2 一对一

一对一就是多对一和一对多的一个特例，只需在relationship加上一个参数uselist=False替换多的一端就是一对一。

class Department(Base):
    __tablename__ = "department"
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)

class Staff(Base):
    # ForeignKey建在多的一方
    __tablename__ = 'staff'
    id = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('department.id'))
    # relationship建在多的一方
    dep = relationship("Department", backref ="staff", uselist=False)

多对一转一对一

class Department(Base):
    __tablename__ = "department"
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)
    # relationship建在一的一方
    staf = relationship("Staff", uselist=False, backref = "department")

class Staff(Base):
    __tablename__ = 'staff'
    # ForeignKey建在多的一方
    id = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('department.id'))

一对多转一对一

4.3 对多对

多对多需要一个中间关联表，通过参数secondary指定中间关联表来建立。

class Staff(Base):
    __tablename__ = 'staff'
    id = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('department.id'))
    ser_id = Column(Integer, ForeignKey("province.id"))

class Department(Base):
    __tablename__ = "department"
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)
    pro = relationship("Server", secondary=Staff, backref="department")

class Province(Base):
    __tablename__ = 'province'
    id = Column(Integer, primary_key=True, autoincrement=True)
    pname = Column(String(64), unique=True, nullable=False)

多对多

五正查、反查

5.1 表修改

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column,Integer,String,ForeignKey
from sqlalchemy.orm import sessionmaker,relationship

egine = create_engine('mysql+mysqlconnector://root@127.0.0.1:3306/test1?charset=utf8', max_overflow=5)
Base = declarative_base()

# 一对多：一个部门可以有多个员工，员工不能属于多个部门
class Department(Base):
    __tablename__ = 'department'
    id = Column(Integer, primary_key=True, autoincrement=True)
    dname = Column(String(64), nullable=False, index=True)

class Staff(Base):
    __tablename__ = 'staff'
    id = Column(Integer, primary_key=True, autoincrement=True)
    sname = Column(String(32), nullable=False, index=True)
    dep_id = Column(Integer, ForeignKey('department.id'))
    # 在ForeignKey所在的类内添加relationship的字段,注意:
    # 1:Department是类名
    # 2:depart字段不会再数据库表中生成字段
    # 3:depart用于Staff表查询Department表(正向查询)，而xxoo用于Department表查询Staff表(反向查询)
    depart = relationship('Department', backref='xxoo')

def init_db():
    Base.metadata.create_all(egine)

def drop_db():
    Base.metadata.drop_all(egine)

drop_db()
init_db()
Session = sessionmaker(bind=egine)
session = Session()

# 准备数据
session.add_all([
    Department(dname='设备'),
    Department(dname='销售'),
    Department(dname='技术'),
    Department(dname='人事'),
])

session.add_all([
    Staff(sname='Joe1', dep_id=1),
    Staff(sname='Joe2', dep_id=2),
    Staff(sname='Joe3', dep_id=3),
    Staff(sname='Joe4', dep_id=4),
    Staff(sname='Jack', dep_id=1),
    Staff(sname='Tracy', dep_id=1),
    Staff(sname='Kobe', dep_id=3),
    Staff(sname='James', dep_id=4),
])

session.commit()
session.close()

View Code

5.2 标准连表查询

# 示例：查询员工名与其部门名
res = session.query(Staff.sname, Department.dname).join(Department)  # 迭代器
for row in res:
    print(row[0],row[1])

View Code

5.3 基于relationship的正查、反查

# SQLAlchemy的relationship在内部帮我们做好表的链接

# 查询员工名与其部门名(正向查)
res = session.query(Staff)
for row in res:
    print(row.id, row.sname, row.depart.dname)

# 查询部门名以及该部门下的员工(反向查)
res = session.query(Department)
for row in res:
    # print(row.dname, row.xxoo)
    print(row.dname,[r.sname for r in row.xxoo])

View Code

六其他补充

由于Session工作在一个事务内，我们可以回滚所做过的更改。

session.rollback()

posted @ 2018-08-29 14:54 Joe1991 阅读(377) 评论(0) 收藏举报

刷新页面返回顶部

Joe1991

Python操作MySQL之SQLAlchemy

一 概述

1.1 安装

1.2 架构与流程

1.3 DB API

二 执行原生SQL

2.1 创建连接

2.2 一个实例

三 ORM功能使用

3.1 创建Base

3.2 创建表

3.3 创建session

3.4 操作表

3.5 其他查询相关

四 relationship

4.1 多对一与一对多

4.2 一对一

4.3 对多对

五 正查、反查

5.1 表修改

5.2 标准连表查询

5.3 基于relationship的正查、反查

六 其他补充

公告

一概述

二执行原生SQL

五正查、反查

六其他补充