由于SQLAlchemy 中文资料比较少，所以根据官网给的tutorial外加其他大佬写的中文资料整合以后准备写一个SQLAlchemy 系列的基础入门教程。本系列可能会夹杂一些个人对于python 、SQLAlchemy 以及ORM的理解，如有出错部分，请指正我。

版本信息:

• SQLAlchemy 1.2.15
• Python 3.6+
• Mac OS 10.14
• DB基于SQLite

目录

Python SQLAlchemy ORM教程(2)-使用SQLAlchemy在数据库查询数据
Python SQLAlchemy ORM教程(3)-使用SQLAlchemy在数据库进行复杂查询

Python SQLAlchemy ORM教程(4)-使用SQLAlchemy建立表关系

ORM

所谓ORM（Object Relational Mapping），就是建立其由Python类到数据库表的映射关系：一个Python实例(instance)对应数据库中的一行(row)。这种映射包含两层含义，一是实现对象和与之关联的的行的状态同步，二是将涉及数据库的查询操作，表达为Python类的相互关系。

注意ORM和SQLAlchemy的Expression Language不同。后者可以视为对原始SQL的封装。ORM是基于Expression Language而构建的，其抽象层次要高于Expression Language。很多时候我们都是使用ORM，有时需要一些高度定制化的功能时，就需要使用到Expression Language。

Version Check

本教程是基于以下版本，如果你的版本号差距不大，本教程仍然具有参考意义

>>> sqlalchemy.__version__
'1.2.15'

Connecting

SQLite 支持在内存中创建数据库，这对于我们学习来说非常方便。在SQLAlchemy中，所有数据库都是使用引擎进行链接(engine)，引擎将链接不同数据库的差异磨平，让你可以非常快速的进行数据的链接。我们来看一段代码怎么创建SQLAlchemy的引擎:

>>> from sqlalchemy import create_engine
>>> engine = create_engine('sqlite:///:memory:',echo=True)
>>> engine
Engine(sqlite:///:memory:)

echo 的的作用类似debug,True的时候可以在控制台看到所有关于SQL语言的操作,不是必须选项。create_engine会返回engine实例,这是一个链接DBAPI的核心接口。
SQLAlchemy 使用了Lazy Connecting ,create_engine返回的engine 实际上并没有连接到数据库。只有当你实际执行一次任务的时候才会连接到数据库。比如执行exectue() 或者 connect()。
尽管SQLALchemy，尽可能的磨平了不同数据库之间的操作差异，但是SQLALchemy 本身提供了多样化的操作，来实现高度的ORM定制，但是本教程仅仅做基础方面的，关于Lazy Connecting仅仅知道就行。
关于更多的连接数据库的方式请参考Engine Configuration

Declaring a Mapping

当我们使用ORM的时候，其配置过程主要分为两个部分：

1. 理清楚我们的数据库表和表关系等等内容
2. 将这些数据库的内容表达成Python的映射类

在 SQLAlchemy 中Declarative System会帮助你完成一些事情，所有的映射类都需要继承基类(Base class)，而Declarative system的实例可以使用declarative_base() 函数来创建，这个函数在 from sqlalchemy.ext.declarative 包内。创建实例

>>> from sqlalchemy.ext.declarative import declarative_base
>>> Base = declarative_base()
>>> Base
<class 'sqlalchemy.ext.declarative.api.Base'>

现在我们已经有了一个Declarative system的实例了(实现Declarative system的类也叫基类），我们可以基于这个基类来创建我们的映射类了。我们以建立一个User类为例子。从Base派生一个名为User的类，在这个类里面我们可以定义将要映射到数据库的表上的属性（主要是表的名字，列的类型和名称等）

>>> from sqlalchemy import Column, Integer, String
>>> class User(Base):
...     __tablename__ = 'users'
...
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     fullname = Column(String)
...     password = Column(String)
...
...     def __repr__(self):
...        return "<User(name='%s', fullname='%s', password='%s')>" % (
...                             self.name, self.fullname, self.password)

通过Declarative system生成的类至少应该包含一个名为tablename的属性来给出目标表的名称，以及至少一个Column来给出表的主键(Primary Key)。SQLAlchemy不会对于类名和表名之间的关联做任何假设，也不会自动涉及数据类型以及约束的转换。一般的你可以自己创建一个模板来建立这些自动转换，这样可以减少你的很多重复劳动。
当我们的类声明完成后，Declarative system将会将所有的Column成员替换成为特殊的Python访问器(accessors)，我们称之为descriptors。这个过程我们称为instrumentation，经过instrumentation的映射类可以让我们能够读写数据库的表和列。
注意除了这些涉及ORM的映射意外，这些mapping类的其他部分仍然是不变的。

Create a Schema

通过继承基类创建 User 类以后，我们就有了与表相关的映射类的信息(此时表并没有在数据库内创建)。我们可以使用__table__属性来查看

>>> User.__table__ 
Table('users', MetaData(bind=None),
            Column('id', Integer(), table=<users>, primary_key=True, nullable=False),
            Column('name', String(), table=<users>),
            Column('fullname', String(), table=<users>),
            Column('password', String(), table=<users>), schema=None)

额外话题:Classical Mappings

Declarative system 在 SQLAlchemy ORM中不是必须的，但是SQLAlchemy还是非常推荐你使用的，如果你不使用Declarative system，任何独立的 Python 类都可以通过mapper()函数来手动映射到任何Table类，更多骚操作可以看官方的文档。作为新手，还是老老实实使用Declarative system 。

继续

如果我们使用Declarative system 的话，Declarative system 会帮我们使用Python的metaclass 类来对我们创建的映射类进行深加工。Declarative system 会创建一个与User类相关的Table对象，然后构造一个Mapper对象与之关联，这一步在Classical Mappings中是由程序员手动操作，但是使用Declarative system 后这些将自动完成。

Table Object

Table 对象是 MetaData中的一员。当我们使用Declarative system时，这个对象也可以在Declarative base class的.metadata属性中看到。可以看下Declarative base class中的Table对象

>>> Base.metadata.sorted_tables
[Table('users', MetaData(bind=None), Column('id', Integer(), table=<users>, primary_key=True, nullable=False), Column('name', String(), table=<users>), Column('fullname', String(), table=<users>), Column('password', String(), table=<users>), schema=None)]

MetaData是我们与数据库打交道的一个接口。在MetaData类内，储存了你创建的数据库相关的所有元数据(metaData)。对于我们的SQLite数据库而言，此时还没有一个名为users的表的存在，我们需要使用MetaData类的方法来发出CREATE TABLE的命令。下面我们使用MetaData.create_all()指令，将我们上面得到的Engine作为参数传入。如果你上面设置了echo为True的话，应该可以看到这一过程中的SQL指令。首先检查了users表的存在性，如果不存在的话会执行表的创建工作。

>>> Base.metadata.create_all(engine)
2018-12-14 14:21:45,329 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2018-12-14 14:21:45,335 INFO sqlalchemy.engine.base.Engine ()
2018-12-14 14:21:45,336 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2018-12-14 14:21:45,336 INFO sqlalchemy.engine.base.Engine ()
2018-12-14 14:21:45,337 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("users")
2018-12-14 14:21:45,337 INFO sqlalchemy.engine.base.Engine ()
2018-12-14 14:21:45,338 INFO sqlalchemy.engine.base.Engine
CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR,
    fullname VARCHAR,
    password VARCHAR,
    PRIMARY KEY (id)
)


2018-12-14 14:21:45,339 INFO sqlalchemy.engine.base.Engine ()
2018-12-14 14:21:45,339 INFO sqlalchemy.engine.base.Engine COMMIT

Create an Instance of the Mapped Class

ok！大部分前期准备工作已经完成，我们现在开始往数据库内插入数据看看

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> ed_user.name
'ed'
>>> ed_user.password
'edspassword'
>>> str(ed_user.id)
'None'

你可以看到，我们并没有指定id是多少，但是当我们想看看id是多少的时候，返回了None，但是我们并没有在构造器内填入id的值，SQLAlchemy 官方文档说，为了防止出现AttributeError的出现，我们帮你添加了。

Creating a Session

Session 在SQLAlchemy中是一个非常重要的概念，session可以理解为缓冲区，因为session的存在，所以很多功能得以方便的实现，比如Rollback。

我们现在可以和数据库对话了。在SQLAlchemy中对数据库的数据CDRU都是通过Session来实现的，Session 和 Engine 是同一级别下的类，相同的重要。我们定义一个Session类来作为生成新的Session的Factory类

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

sessionmaker 仅仅是session的前置配置，绑定engine不是必须的

>>> Session = sessionmaker()

你仍然可以通过configure来绑定engine

>>> Session.configure(bind=engine)  # once engine is available

然后你可以通过factory 类来创建一个与数据库绑定的session

>>> session = Session()

题外话：对于不同的session你可能有自己的新想法，所以factory类允许你改动配置以后重新创建一个新的session类。

到这一步，engine 仍然没有连接到数据库，engine在等待你的第一次使用，当你连接到数据库以后，Pool会维持你的连接直到你手动关闭session。

Adding and Updating Objects

我们要开始往数据库写入东西了。非常简单，session类内置了 add 方法

>>> ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')
>>> session.add(ed_user)

当你session.add以后，这个User实例的状态为Pending，你的数据仍然保存在Pool中，而不是数据库中，这是一个你与数据库中的缓冲地带，为你的错误提供rollback的机会。当SQL命令被执行后，你的数据才会出现在数据库中，这是过程SQLAlchemy称之为flush。

现在你就可以创建查询(Query)对象了。我们来查询下刚刚添加进去的一个User。（更多的查询，会在后面讲）

>>> our_user = session.query(User).filter_by(name='ed').first()
2018-12-14 14:57:27,320 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-12-14 14:57:27,322 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2018-12-14 14:57:27,322 INFO sqlalchemy.engine.base.Engine ('ed', 'Ed Jones', 'edspassword')
2018-12-14 14:57:27,323 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.name = ?
 LIMIT ? OFFSET ?
2018-12-14 14:57:27,323 INFO sqlalchemy.engine.base.Engine ('ed', 1, 0)
>>> our_user
<User(name='ed', fullname='Ed Jones', password='edspassword')>

我们已经拿回了ed_user,我们可以看到our_user和ed_user没什么区别

>>> id(ed_user)
4419178904
>>> id(our_user)
4419178904
>>> ed_user is our_user
True

我们尝试下大批量的加入数据:

>>> session.add_all([
...     User(name='wendy', fullname='Wendy Williams', password='foobar'),
...     User(name='mary', fullname='Mary Contrary', password='xxg527'),
...     User(name='fred', fullname='Fred Flinstone', password='blah')])

要记住，这个时候，我们还没有执行任何SQL语句，当我们发现的密码太短了，不太安全，我们想进行修改的时候,可以方便的修改

>>> ed_user.password='f8s7ccs'

当你修改了大量的数据是，你想看看哪些数据被修改了，SQLAlchemy已经替你想好了

>>> session.dirty
IdentitySet([<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>])

你可以查询下，新加入的数据

>>> session.new  
IdentitySet([<User(name='wendy', fullname='Wendy Williams', password='foobar')>,
<User(name='mary', fullname='Mary Contrary', password='xxg527')>,
<User(name='fred', fullname='Fred Flinstone', password='blah')>])

好了，都没问题，我们准备将数据放入到数据库了

>>> session.commit()
2018-12-14 15:04:29,162 INFO sqlalchemy.engine.base.Engine UPDATE users SET password=? WHERE users.id = ?
2018-12-14 15:04:29,162 INFO sqlalchemy.engine.base.Engine ('f8s87ccs', 1)
2018-12-14 15:04:29,162 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2018-12-14 15:04:29,162 INFO sqlalchemy.engine.base.Engine ('wendy', 'Wendy Williams', 'foobar')
2018-12-14 15:04:29,163 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2018-12-14 15:04:29,163 INFO sqlalchemy.engine.base.Engine ('mary', 'Mary Contrary', 'xxg527')
2018-12-14 15:04:29,163 INFO sqlalchemy.engine.base.Engine INSERT INTO users (name, fullname, password) VALUES (?, ?, ?)
2018-12-14 15:04:29,163 INFO sqlalchemy.engine.base.Engine ('fred', 'Fred Flinstone', 'blah')
2018-12-14 15:04:29,163 INFO sqlalchemy.engine.base.Engine COMMIT

我们来看下 ed_user 的id 还是不是 None

>> ed_user.id
2018-12-14 15:05:07,938 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2018-12-14 15:05:07,938 INFO sqlalchemy.engine.base.Engine SELECT users.id AS users_id, users.name AS users_name, users.fullname AS users_fullname, users.password AS users_password
FROM users
WHERE users.id = ?
2018-12-14 15:05:07,938 INFO sqlalchemy.engine.base.Engine (1,)
1

Reference:
SQLAlchemy ORM教程之一：Create
Object Relational Tutorial

由于偷懒复制了一些内容，如果有侵犯，我马上删