【翻译】SQLAlchemy Basics Tutorial

原文链接：https://leportella.com/sqlalchemy-tutorial/

SQLAlchemy 基础教程

从去年9月份我就开始Jupyter项目的工作，并且成为一段美好的经历。最近几天，我在与工作在JupyterHub内部的SQLAlchemy库进行斗争。由于我需要学习这个库并且不得不抓取一些StackOverflow上的问题来寻找答案，所以我创建了这篇文章来帮助消化我的一些疑问和发现。

由于这篇文章已经非常长了，所以我决定将我在SQLAlchemy中遇到的主要问题放在一篇单独的文章中。所以请持续关注。

所有的代码放在这里

创建并理解Engine

在学习SQLALchemy开始，这篇文章首先将会你创建Engine。Engine是用来进行SQLAlchemy与数据库之间通信的，所以，当创建Engine时，你应该提供数据库的URL，这是最基础的工作。

尽管我们可以通过Engine命令直接访问数据库，但我们通常不这么做，而且建议你也不要这么做。

from sqlalchemy import create_engine

engine = create_engine('sqlite:///:memory:', echo=True)

上面的代码仅仅告诉了你的数据库的当前位置。属性echo=True将会令SQLAlchemy在你使用它时记录所有它执行的所有SQL命令。不建议在生产环境开启。

一旦Engine配置了数据库地址，我们就可以通过执行engine.execute(…)方法非常容易的执行命令。如下图所示。

如此我们就有了一个知道你数据库地址的Engine以及访问数据库的方法engine.execute(…)，如下图所示。

Engine还是connection

在一些别的教程中，你会看到另外一种通过connection执行SQL命令的方式

conn = engine.connect()
conn.execute(...)

这允许我们创建事务（命令全部执行成功或失败后全部回滚）

trans = conn.begin()
conn.execute('INSERT INTO "EX1" (name) '
             'VALUES ("Hello")')
trans.commit()

事实上，结构就变成下图所示的样子

但是，深入探究一些关于engine.execute(…)和connection.execute(…)差异的回答后我发现他们根本就没有区别。

“使用Engine.execute()和Connection.execute()几乎是一回事，前者是隐式创建了Connection对象，而后者是显式实例化了它”

所以根据你的喜好随意使用吧

创建并理解Session

到这，我们已经能够连接我们的数据库并通过SQL语句操作数据库。但是，直到现在我们都还没有谈到使SQLAlchemy如此吸引人的地方——它的ORM。

使用ORM必须依靠Session来作为Python中我们操作的对象以及实际与数据库通信的engine之间的中间层。所以我们需要一个函数sessionmaker来传入我们的engine

from sqlalchemy.orm import sessionmaker

Session = sessionmaker(bind=engine)
session = Session()

所以，你将使用session来与表沟通以及进行查询，但实际上，是engine在执行这些操作。

尽管上图中的三个实体让我们感到困惑，但在初始化设置后的绝大多数时间我们都将使用session而不是engine和connection。

创建表

现在，我们希望使用之前所学在我们的数据库中创建表并且看一下何谓SQLAlchemy ORM。为了创建表，我们需要创建包含属性的类。每一个类即为数据库中的一张表，类的每一个属性即为表的一个字段。为了映射表到我们定义的类，我们需要使用名叫Declarative的SQLAlchemy系统。首先，我们必须实例化一个Base：

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

现在我们创建一个继承自Base declarative的User类。我们将创建三个类属性：

• id（主键且不能为null）
• name
• password

由于我们使用Declarative，所以我们必须添加两个属性：

1. __tablename__，指定了在数据库中的表名；
2. 至少一个字段，且为主键（参考：https://docs.sqlalchemy.org/en/latest/orm/tutorial.html#declare-a-mapping）

另外我们可以添加__repr__方法，用来输出该用户的名字

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    password = Column(String)

    def __repr__(self):
        return f'User {self.name}'

我们现在构建好了一个类来表示数据库中的表。但是，一切都还没发生变化。为了真正在数据库中创建表，我们需要Base以及engine：

Base.metadata.create_all(engine)

如此SQLAlchemy才在数据库中进行了操作。由于我们将属性echo设置为了True，所以我们可以准确地看到engine实际对数据库执行了哪些SQL语句。

添加新的记录

现在，我们可以使用这个类在我们的数据库中创建一条记录。我们可以使用User类创建一个新的用户并使用session.add(…)来添加这个实例到数据库中。

user = User(name='John Snow', password='johnspassword')
session.add(user)

print(user.id)  # None

虽然我说我们需要主键，但我在这里并没有提供。如果我尝试打印它，它将返回None。

这是由于session.add在执行过程中注册了事务，而事实上并没有任何变化（参考：https://stackoverflow.com/a/4202016/3538098）

正如上面链接中的解释一样，我们有两个方法产生变化：

• session.flush() 向数据库传达一系列操作（插入、更新、删除）。数据库将它们作为事务中的挂起操作进行维护。直到数据库收到当前事务的 COMMIT（这就是 session.commit() 所做的）之前，更改不会永久保存到磁盘，也不会对其他事务可见。
• session.commit() 提交（持久化）到数据库中。session.commit()会调用session.flush()作为其执行的一部分。

译者注：这里flush和commit讲的不太清楚，可以参考https://www.jianshu.com/p/b15c224b556c

在SQLAlchemy中一个Session（可以看作）是一个transaction，每个操作（基本上）对应一条或多条SQL语句，这些SQL语句需要发送到数据库服务器才能被真正执行，而整个transaction需要commit才能真正生效，如果没提交，一旦你的程序挂了，所有未提交的事务都会被回滚到事务开始之前的状态。
flush就是把客户端尚未发送到数据库服务器的SQL语句发送过去，commit就是告诉数据库服务器提交事务。
简单说，flush之后你才能在这个Session中看到效果，而commit之后你才能从其它Session中看到效果。

查询

一旦我们数据库中有了数据，我们需要对他们进行查询。

因此，对于session的query函数，我们将传递要查找实例的类，然后使用该方法的filter_by属性进行过滤。

query = session.query(User).filter_by(name='John')

最后，我们传递一个方法来指示我们将要对查询结果做什么：

• 统计查询到的数据数量（.count()）
• 返回所有数据（.all()）
• 返回第一条数据（.first()）
• 等等

query.count()

不同于filter_by的语法，我们可以使用filter方法。

session.query(User).filter(User.name=='John').first()

使用filter方法，你可以进行模糊查找：

session.query(User).filter(User.name.like('%John%')).first()

在JupyterHub上，他们向每个模型添加了一个类方法，以简化上面这种复杂的语法。我们可以添加一个方法，仅需要传入session、属性就可以返回查询结果。例如：

class User(Base):
  ...

  @classmethod
  def find_by_name(cls, session, name):
    return session.query(cls).filter_by(name=name).all()

所以，查抄所有仅命名为“John”的用户的方法就变为：

Product.find_by_name(session, 'John')

在初始化create_all后创建新表

在我为Jupyter项目工作时遇到的一个问题是我需要为已经初始创建的数据库和引擎(Base.metadata.create_all(engine))创建一个新表

假设现在我现在需要一个Products表

from sqlalchemy import Column, Integer, String

class Product(Base):
    __tablename__ = 'products'

    id = Column(Integer, primary_key=True)
    name = Column(String)

我发现的最简单的键表方法为：

Product.__table__.create(engine)

创建外键

假设在系统中你需要为每个产品连接一个用户。所以，在每个Product实例中需要存储一个User实例

如果此时你创建所有的表，你需要在Product类中添加一个字段来引用user的外键以及与User类的关系：

from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import relationship

class Product(Base):
    __tablename__ = 'product'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship('User')

并在 User 类上添加 User 和 Product 之间的关系：

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import relationship

class User(Base):
    __tablename__ = 'user'  # if you use base it is obligatory

    id = Column(Integer, primary_key=True)  # obligatory
    name = Column(String)
    password = Column(String)
    products = relationship(Product, backref="users")

现在你可以使用我们之前学到的Base.metada.create_all(engine)来创建表

user = User(name='John')
product = Product(name='wolf', user=user)

session.add_all([user, product])
session.commit()