SQLAlchemy:引擎、连接和会话的区别 [英] SQLAlchemy: engine, connection and session difference

问题描述

我使用 SQLAlchemy 并且至少有三个实体:engine、session 和 connection，它们具有 execute> 方法，所以如果我例如想要从 table 中选择所有记录我可以这样做

engine.execute(select([table])).fetchall()

还有这个

connection.execute(select([table])).fetchall()

甚至这个

session.execute(select([table])).fetchall()

- 结果是一样的.

据我所知，如果有人使用 engine.execute 它会创建 connection，打开 session(Alchemy 会为您处理)并执行查询.但是，这三种执行此类操作的方式之间是否存在全局差异?任务?

解决方案

单行概述:

execute() 的行为在所有情况下都相同，但它们是 3 种不同的方法，分别在 Engine、Connection 和Session 类.

究竟什么是execute():

要了解 execute() 的行为，我们需要查看 Executable 类.Executable 是所有语句"类型对象的超类，包括 select()、delete()、update()、insert()、text() - 用最简单的话说，一个 Executable 是 SQLAlchemy 中支持的 SQL 表达式构造.

在所有情况下，execute() 方法采用 SQL 文本或构造的 SQL 表达式，即 SQLAlchemy 支持的各种 SQL 表达式构造中的任何一种，并返回查询结果(ResultProxy - 包装一个 DB-API 游标对象，以便更轻松地访问行列.)

<小时>

进一步澄清(仅用于概念澄清，不是推荐的方法):

除了Engine.execute()(无连接执行)、Connection.execute()和Session.execute()，也可以直接在任何 Executable 结构上使用 execute().Executable 类有它自己的 execute() 实现 - 根据官方文档，关于 execute() 做什么的一行描述是"编译并执行这个Executable".在这种情况下，我们需要将 Executable(SQL 表达式构造)与 Connection 对象或 Engine 对象(隐式地获得 Connection 对象)，所以 execute() 将知道在哪里执行 SQL.

下面的例子很好地展示了它 - 给定一个表格如下:

from sqlalchemy import MetaData, Table, Column, Integer元 = 元数据()用户表 = 表('用户'，元，列('id'，整数，primary_key=True)，列('名称'，字符串(50)))

显式执行 ie Connection.execute() - 将 SQL 文本或构造的 SQL 表达式传递给 execute() 方法>连接:

engine = create_engine('sqlite:///file.db')连接 = engine.connect()结果 = connection.execute(users_table.select())对于结果行:# ....连接.close()

显式无连接执行，即 Engine.execute() - 将 SQL 文本或构造的 SQL 表达式直接传递给引擎:

engine = create_engine('sqlite:///file.db')结果 = engine.execute(users_table.select())对于结果行:# ....结果.关闭()

隐式执行即Executable.execute()——也是无连接的，调用Executable的execute()方法，也就是说，它直接在SQL 表达式构造(Executable 的一个实例)本身上调用execute() 方法.

engine = create_engine('sqlite:///file.db')meta.bind = 引擎结果 = users_table.select().execute()对于结果行:# ....结果.关闭()

注意:声明隐式执行示例是为了澄清 - 强烈不推荐这种执行方式 - 根据 文档:

<块引用>

隐式执行"是一种非常古老的使用模式，在大多数情况下是更令人困惑而不是有用，不鼓励使用它.两个都模式似乎鼓励过度使用权宜之计的捷径"应用程序设计，导致以后出现问题.

<小时>

您的问题:

<块引用>

据我所知，如果有人使用 engine.execute 它会创建连接，打开会话(Alchemy 会为您处理)并执行查询.

对于如果有人使用 engine.execute 它会创建 connection "而不是opens session"(Alchemy为你关心它)并执行查询"- 使用 Engine.execute() 和 Connection.execute() 是(几乎)同一件事，在正式的，Connection 对象是隐式创建的，稍后我们会显式实例化它.在这种情况下真正发生的是:

`Engine` 对象(通过 `create_engine()` 实例化)->`Connection` 对象(通过 `engine_instance.connect()` 实例化)->`connection.execute({*SQL 表达式*})`

<块引用>

但这三种方式之间是否存在全局差异执行这样的任务?

在 DB 层完全一样，它们都在执行 SQL(文本表达式或各种 SQL 表达式构造).从应用程序的角度来看，有两种选择:

• 直接执行 - 使用 Engine.execute() 或 Connection.execute()
• 使用 sessions - 有效地将事务作为单个事务处理工作单元，通过 session.add()、session.rollback()、session.commit()、 轻松实现>session.close().这是在 ORM(即映射表)的情况下与数据库交互的方式.提供 identity_map 以便在访问期间立即获取已访问或新创建/添加的对象单个请求.

Session.execute() 最终使用Connection.execute() 语句执行方法来执行SQL 语句.使用 Session 对象是 SQLAlchemy ORM 推荐的应用程序与数据库交互的方式.

摘自文档:

<块引用>

需要注意的是，在使用 SQLAlchemy ORM 时，这些对象通常不被访问；相反，会话对象是用作数据库的接口.但是，对于应用程序，围绕直接使用文本 SQL 语句和/或 SQL 构建不涉及 ORM 更高级别的表达式结构管理服务，引擎和连接是王者(和王后?) -继续阅读.

I use SQLAlchemy and there are at least three entities: engine, session and connection, which have execute method, so if I e.g. want to select all records from table I can do this

engine.execute(select([table])).fetchall()

and this

connection.execute(select([table])).fetchall()

and even this

session.execute(select([table])).fetchall()

- the results will be the same.

As I understand it, if someone uses engine.execute it creates connection, opens session (Alchemy takes care of it for you) and executes the query. But is there a global difference between these three ways of performing such a task?

解决方案

A one-line overview:

The behavior of execute() is same in all the cases, but they are 3 different methods, in Engine, Connection, and Session classes.

What exactly is execute():

To understand behavior of execute() we need to look into the Executable class. Executable is a superclass for all "statement" types of objects, including select(), delete(),update(), insert(), text() - in simplest words possible, an Executable is a SQL expression construct supported in SQLAlchemy.

In all the cases the execute() method takes the SQL text or constructed SQL expression i.e. any of the variety of SQL expression constructs supported in SQLAlchemy and returns query results (a ResultProxy - Wraps a DB-API cursor object to provide easier access to row columns.)

---

To clarify it further (only for conceptual clarification, not a recommended approach):

In addition to Engine.execute() (connectionless execution), Connection.execute(), and Session.execute(), it is also possible to use the execute() directly on any Executable construct. The Executable class has it's own implementation of execute() - As per official documentation, one line description about what the execute() does is "Compile and execute this Executable". In this case we need to explicitly bind the Executable (SQL expression construct) with a Connection object or, Engine object (which implicitly get a Connection object), so the execute() will know where to execute the SQL.

The following example demonstrates it well - Given a table as below:

from sqlalchemy import MetaData, Table, Column, Integer

meta = MetaData()
users_table = Table('users', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(50)))

Explicit execution i.e. Connection.execute() - passing the SQL text or constructed SQL expression to the execute() method of Connection:

engine = create_engine('sqlite:///file.db')
connection = engine.connect()
result = connection.execute(users_table.select())
for row in result:
    # ....
connection.close()

Explicit connectionless execution i.e. Engine.execute() - passing the SQL text or constructed SQL expression directly to the execute() method of Engine:

engine = create_engine('sqlite:///file.db')
result = engine.execute(users_table.select())
for row in result:
    # ....
result.close()

Implicit execution i.e. Executable.execute() - is also connectionless, and calls the execute() method of the Executable, that is, it calls execute() method directly on the SQL expression construct (an instance of Executable) itself.

engine = create_engine('sqlite:///file.db')
meta.bind = engine
result = users_table.select().execute()
for row in result:
    # ....
result.close()

Note: Stated the implicit execution example for the purpose of clarification - this way of execution is highly not recommended - as per docs:

"implicit execution" is a very old usage pattern that in most cases is more confusing than it is helpful, and its usage is discouraged. Both patterns seem to encourage the overuse of expedient "short cuts" in application design which lead to problems later on.

---

Your questions:

As I understand if someone use engine.execute it creates connection, opens session (Alchemy cares about it for you) and executes query.

You're right for the part "if someone use engine.execute it creates connection " but not for "opens session (Alchemy cares about it for you) and executes query " - Using Engine.execute() and Connection.execute() is (almost) one the same thing, in formal, Connection object gets created implicitly, and in later case we explicitly instantiate it. What really happens in this case is:

`Engine` object (instantiated via `create_engine()`) -> `Connection` object (instantiated via `engine_instance.connect()`) -> `connection.execute({*SQL expression*})`

But is there a global difference between these three ways of performing such task?

At DB layer it's exactly the same thing, all of them are executing SQL (text expression or various SQL expression constructs). From application's point of view there are two options:

• Direct execution - Using Engine.execute() or Connection.execute()
• Using sessions - efficiently handles transaction as single unit-of-work, with ease via session.add(), session.rollback(), session.commit(), session.close(). It is the way to interact with the DB in case of ORM i.e. mapped tables. Provides identity_map for instantly getting already accessed or newly created/added objects during a single request.

Session.execute() ultimately uses Connection.execute() statement execution method in order to execute the SQL statement. Using Session object is SQLAlchemy ORM's recommended way for an application to interact with the database.

An excerpt from the docs:

Its important to note that when using the SQLAlchemy ORM, these objects are not generally accessed; instead, the Session object is used as the interface to the database. However, for applications that are built around direct usage of textual SQL statements and/or SQL expression constructs without involvement by the ORM’s higher level management services, the Engine and Connection are king (and queen?) - read on.

这篇关于SQLAlchemy:引擎、连接和会话的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！