Python SQLAlchemy：引擎、连接和会话差异

SQLAlchemy: engine, connection and session difference

提问by ololobus

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

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

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.executeit 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?

据我了解，如果有人使用engine.execute它connection，则创建、打开session（Alchemy 为您处理）并执行查询。但是这三种执行此类任务的方式之间是否存在全局差异？

采纳答案by Nabeel Ahmed

A one-line overview:

单行概述：

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

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

What exactly is execute():

究竟是什么execute()：

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

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

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-APIcursor object to provide easier access to row columns.)

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

---

---

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 Executableconstruct. The Executableclass 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 Connectionobject or, Engineobject (which implicitly get a Connectionobject), so the execute()will know where to execute the SQL.

除了Engine.execute()（无连接执行）、Connection.execute()、 和 之外Session.execute()，还可以execute()直接在任何Executable构造上使用 。这个Executable类有它自己的实现execute()- 根据官方文档，关于它的作用的一行描述execute()是“编译并执行这个Executable”。在这种情况下，我们需要将Executable（SQL 表达式构造）与Connection对象或Engine对象（隐式获取Connection对象）显式绑定，以便execute()知道在哪里执行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 executioni.e. Connection.execute()- passing the SQL text or constructed SQL expression to the execute()method of Connection:

显式执行即Connection.execute()- 将 SQL 文本或构造的 SQL 表达式传递给以下execute()方法Connection：

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

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

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

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

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

隐式执行ie Executable.execute()- 也是无连接的，并且调用 的execute()方法Executable，即execute()直接在SQL表达式构造（ 的实例Executable）本身上调用方法。

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.

据我了解，如果有人使用 engine.execute 它会创建连接，打开会话（Alchemy 会为您处理它）并执行查询。

You're right for the part "if someone use engine.executeit 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, Connectionobject gets created implicitly, and in later case we explicitly instantiate it. What really happens in this case is:

你是正确的部分“如果有人使用engine.execute它创建connection”而不是“打开session（Alchemy为你关心它）并执行查询” - 使用Engine.execute()和Connection.execute()（几乎）是同一件事，在正式的情况下，Connection对象被隐式创建，在后面的例子中，我们明确地实例化它。在这种情况下真正发生的是：

`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:

在 DB 层，它完全相同，它们都在执行 SQL（文本表达式或各种 SQL 表达式构造）。从应用程序的角度来看，有两种选择：

• 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_mapfor instantly getting already accessed or newly created/added objects during a single request.

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

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

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

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 ORM 时，一般不会访问这些对象；相反，Session 对象用作数据库的接口。但是，对于直接使用文本 SQL 语句和/或 SQL 表达式构造而无需 ORM 的更高级别管理服务参与的应用程序，引擎和连接是王者（和王后？） - 请继续阅读。

回答by Neal

Nabeel's answercovers a lot of details and is helpful, but I found it confusing to follow. Since this is currently the first Google result for this issue, adding my understanding of it for future people that find this question:

Nabeel 的回答涵盖了很多细节并且很有帮助，但我发现遵循起来很混乱。由于这是目前这个问题的第一个谷歌结果，为未来发现这个问题的人补充我对它的理解：

Running .execute()

运行 .execute()

As OP and Nabell Ahmed both note, when executing a plain SELECT * FROM tablename, there's no difference in the result provided.

正如 OP 和 Nabell Ahmed 都指出的那样，在执行 plain 时SELECT * FROM tablename，提供的结果没有区别。

The differences between these three objects do become important depending on the context that the SELECTstatement is used in or, more commonly, when you want to do other things like INSERT, DELETE, etc.

这三个对象之间的区别取决于上下文就成为非常重要的SELECT声明中，或者更常见的是，当你想要做其他事情一样使用INSERT，DELETE等等。

When to use Engine, Connection, Session generally

一般什么时候使用 Engine、Connection、Session

• Engineis the lowest level object used by SQLAlchemy. It maintains a pool of connectionsavailable for use whenever the application needs to talk to the database. .execute()is a convenience method that first calls conn = engine.connect(close_with_result=True)and the then conn.execute(). The close_with_result parameter means the connection is closed automatically. (I'm slightly paraphrasing the source code, but essentially true). edit: Here's the source code for engine.execute

  You can use engine to execute raw SQL.

  result = engine.execute('SELECT * FROM tablename;')
  #what engine.execute() is doing under the hood
  conn = engine.connect(close_with_result=True)
  result = conn.execute('SELECT * FROM tablename;')
  
  #after you iterate over the results, the result and connection get closed
  for row in result:
      print(result['columnname']
  
  #or you can explicitly close the result, which also closes the connection
  result.close()
  

  This is covered in the docs under basic usage.

• Connectionis (as we saw above) the thing that actually does the work of executing a SQL query. You should do this whenever you want greater control over attributes of the connection, when it gets closed, etc. For example, a very import example of this is a Transaction, which lets you decide when to commit your changes to the database. In normal use, changes are autocommitted. With the use of transactions, you could (for example) run several different SQL statements and if something goes wrong with one of them you could undo all the changes at once.

  connection = engine.connect()
  trans = connection.begin()
  try:
      connection.execute("INSERT INTO films VALUES ('Comedy', '82 minutes');")
      connection.execute("INSERT INTO datalog VALUES ('added a comedy');")
      trans.commit()
  except:
      trans.rollback()
      raise
  

  This would let you undo both changes if one failed, like if you forgot to create the datalog table.

  So if you're executing raw SQL code and need control, use connections

• Sessionsare used for the Object Relationship Management (ORM) aspect of SQLAlchemy (in fact you can see this from how they're imported: from sqlalchemy.orm import sessionmaker). They use connections and transactions under the hood to run their automatically-generated SQL statements. .execute()is a convenience function that passes through to whatever the session is bound to (usually an engine, but can be a connection).

  If you're using the ORM functionality, use session; if you're only doing straight SQL queries not bound to objects, you're probably better off using connections directly.

• Engine是 SQLAlchemy 使用的最低级别的对象。当应用程序需要与数据库通信时，它维护一个可用的连接池。.execute()是一种方便的方法，首先调用conn = engine.connect(close_with_result=True)然后调用conn.execute()。close_with_result 参数表示连接自动关闭。（我稍微解释了源代码，但基本上是正确的）。编辑：这是 engine.execute 的源代码

  您可以使用引擎来执行原始 SQL。

  result = engine.execute('SELECT * FROM tablename;')
  #what engine.execute() is doing under the hood
  conn = engine.connect(close_with_result=True)
  result = conn.execute('SELECT * FROM tablename;')
  
  #after you iterate over the results, the result and connection get closed
  for row in result:
      print(result['columnname']
  
  #or you can explicitly close the result, which also closes the connection
  result.close()
  

  这在基本用法下的文档中有所介绍。

• 连接是（正如我们在上面看到的）实际执行 SQL 查询工作的东西。每当您想要更好地控制连接的属性、何时关闭等时，您都应该这样做。例如，一个非常重要的示例是Transaction，它让您决定何时将更改提交到数据库。在正常使用中，更改是自动提交的。通过使用事务，您可以（例如）运行多个不同的 SQL 语句，如果其中一个出现问题，您可以立即撤消所有更改。

  connection = engine.connect()
  trans = connection.begin()
  try:
      connection.execute("INSERT INTO films VALUES ('Comedy', '82 minutes');")
      connection.execute("INSERT INTO datalog VALUES ('added a comedy');")
      trans.commit()
  except:
      trans.rollback()
      raise
  

  如果一个更改失败，这将允许您撤消两项更改，就像您忘记创建数据记录表一样。

  因此，如果您正在执行原始 SQL 代码并需要控制，请使用连接

• 会话用于 SQLAlchemy 的对象关系管理 (ORM) 方面（实际上您可以从它们的导入方式中看出这一点：）from sqlalchemy.orm import sessionmaker。他们在幕后使用连接和事务来运行自动生成的 SQL 语句。.execute()是一个方便的函数，它传递到会话绑定到的任何内容（通常是引擎，但可以是连接）。

  如果您使用 ORM 功能，请使用 session；如果您只执行未绑定到对象的直接 SQL 查询，则最好直接使用连接。

回答by Jie

Here is an example of running DCL (Data Control Language) such as GRANT

这是运行 DCL（数据控制语言）的示例，例如 GRANT

def grantAccess(db, tb, user):
  import sqlalchemy as SA
  import psycopg2

  url = "{d}+{driver}://{u}:{p}@{h}:{port}/{db}".\
            format(d="redshift",
            driver='psycopg2',
            u=username,
            p=password,
            h=host,
            port=port,
            db=db)
  engine = SA.create_engine(url)
  cnn = engine.connect()
  trans = cnn.begin()
  strSQL = "GRANT SELECT on table " + tb + " to " + user + " ;"
  try:
      cnn.execute(strSQL)
      trans.commit()
  except:
      trans.rollback()
      raise