SQLAlchemy:引擎，连接和会话的区别

本文介绍了SQLAlchemy:引擎，连接和会话的区别的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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()

还有这个

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

甚至是这个

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

-结果将相同.

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

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?

推荐答案

单行概述:

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

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

execute()到底是什么:

What exactly is execute():

要了解execute()的行为，我们需要研究Executable类. Executable是所有语句"类型的对象的超类，包括select()，delete()，update()，insert()，text()-用尽可能简单的话来说，Executable是SQL表达式构造在SQLAlchemy中受支持.

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.

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

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.)

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

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

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)))

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

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()

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

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()

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

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:

您的问题:

您认为如果有人使用engine.execute会创建connection"这一部分是对的，但对于打开session(炼金术会在乎您的意思)并执行查询--使用Engine.execute()和Connection.execute()(几乎)是同一件事，在形式上，Connection对象是隐式创建的，在以后的情况下，我们显式实例化它.在这种情况下真正发生的是:

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*})`

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

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:

直接执行-使用Engine.execute()或Connection.execute()
使用sessions-有效地单笔处理交易工作单元，可轻松通过session.add()，session.rollback()，session.commit()，session.close()进行操作.在ORM(即映射表)的情况下，这是与DB进行交互的方式.提供 identity_map ，以便在访问期间立即获取已被访问或新创建/添加的对象一个请求.

Direct execution - Using Engine.execute() or Connection.execute()
Using sessions - efficiently handles transaction as singleunit-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()最终使用Connection.execute()语句执行方法来执行SQL语句.对于应用程序与数据库交互，SQLAlchemy ORM建议使用Session对象.

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.

摘录自文档:

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