<p><strong>单行概述:</strong></p>
<p>在所有情况下,<code>execute()</code>的行为都是相同的,但它们是3种不同的方法,在<code>Engine</code>、<code>Connection</code>和<code>Session</code>类中。</p>
<p><strong>究竟是什么<code>execute()</code>:</strong></p>
<p>为了理解<code>execute()</code>的行为,我们需要研究<code>Executable</code>类。<code>Executable</code>是所有“statement”类型对象的超类,包括select()、delete()、update()、insert()、text()——用最简单的话来说,<code>Executable</code>是SQLAlchemy支持的SQL表达式构造。</p>
<p>在所有情况下,<code>execute()</code>方法都接受SQL文本或构造的SQL表达式,即SQLAlchemy中支持的各种SQL表达式构造,并返回查询结果(一个<code>ResultProxy</code>-包装一个<code>DB-API</code>光标对象,以便更容易地访问行列)</p>
<hr/>
<p><strong>进一步澄清(仅用于概念澄清,而不是建议的方法)</strong>:</p>
<p>除了<code>Engine.execute()</code>(无连接执行)、<code>Connection.execute()</code>和<code>Session.execute()</code>之外,还可以直接在任何<code>Executable</code>构造上使用<code>execute()</code>。<code>Executable</code>类有自己的<code>execute()</code>实现——根据官方文档,关于<code>execute()</code>所做的一行描述是“<strong>编译并执行这个<code>Executable</code></strong>”。在这种情况下,我们需要显式地将<code>Executable</code>(SQL表达式构造)与<code>Connection</code>对象或<code>Engine</code>对象(隐式地获取<code>Connection</code>对象)绑定,这样<code>execute()</code>就知道在哪里执行<code>SQL</code>。</p>
<p>下面的示例很好地演示了它—给出了一个如下表:</p>
<pre><code>from sqlalchemy import MetaData, Table, Column, Integer
meta = MetaData()
users_table = Table('users', meta,
Column('id', Integer, primary_key=True),
Column('name', String(50)))
</code></pre>
<p><strong>显式执行</strong>即<code>Connection.execute()</code>-将SQL文本或构造的SQL表达式传递给<code>Connection</code>的<code>execute()</code>方法:</p>
<pre><code>engine = create_engine('sqlite:///file.db')
connection = engine.connect()
result = connection.execute(users_table.select())
for row in result:
# ....
connection.close()
</code></pre>
<p><strong>显式无连接执行</strong>即<code>Engine.execute()</code>-将SQL文本或构造的SQL表达式直接传递给引擎的<code>execute()</code>方法:</p>
<pre><code>engine = create_engine('sqlite:///file.db')
result = engine.execute(users_table.select())
for row in result:
# ....
result.close()
</code></pre>
<p><strong>隐式执行</strong>即<code>Executable.execute()</code>-也是无连接的,并调用<code>Executable</code>的<code>execute()</code>方法,即它直接在<code>SQL</code>表达式构造(一个<code>Executable</code>的实例)本身上调用<code>execute()</code>方法。</p>
<pre><code>engine = create_engine('sqlite:///file.db')
meta.bind = engine
result = users_table.select().execute()
for row in result:
# ....
result.close()
</code></pre>
<p>注意:为了澄清起见,声明了隐式执行示例-强烈建议不要使用这种执行方式-根据<a href="http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#connectionless-execution-implicit-execution" rel="noreferrer">docs</a>:</p>
<blockquote>
<p>“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.</p>
</blockquote>
<hr/>
<h2>你的问题:</h2>
<blockquote>
<p>As I understand if someone use engine.execute it creates connection,
opens session (Alchemy cares about it for you) and executes query.</p>
</blockquote>
<p>对于“如果有人使用<code>engine.execute</code>,它会创建<code>connection</code>”,而不是“打开<code>session</code>(炼金术会关心你)并执行查询”这一部分,你是对的——使用<code>Engine.execute()</code>和<code>Connection.execute()</code>是(几乎)同一件事,在形式上,<code>Connection</code>对象是隐式创建的,在以后的情况下,我们显式实例化它。在这种情况下真正发生的是:</p>
<pre><code>`Engine` object (instantiated via `create_engine()`) -> `Connection` object (instantiated via `engine_instance.connect()`) -> `connection.execute({*SQL expression*})`
</code></pre>
<blockquote>
<p>But is there a global difference between these three ways of
performing such task?</p>
</blockquote>
<p>在DB层,这是完全相同的事情,它们都在执行SQL(文本表达式或各种SQL表达式构造)。从应用程序的角度来看,有两个选项:</p>
<ul>
<li>直接执行-使用<code>Engine.execute()</code>或<code>Connection.execute()</code></li>
<li>使用<code>sessions</code>-有效地将事务处理为单个
工作单位,轻松通过<code>session.add()</code>、<code>session.rollback()</code>、<code>session.commit()</code>、<code>session.close()</code>。这是在ORM(即映射表)的情况下与数据库交互的方式。提供<a href="http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#" rel="noreferrer">identity_map</a>以便在单个请求期间立即获取已访问或新创建/添加的对象。</li>
</ul>
<p><code>Session.execute()</code>最终使用<code>Connection.execute()</code>语句执行方法来执行SQL语句。使用<code>Session</code>对象是SQLAlchemy ORM推荐的应用程序与数据库交互的方式。</p>
<p>摘自<a href="http://docs.sqlalchemy.org/en/rel_1_0/core/connections.html#" rel="noreferrer">docs</a>:</p>
<blockquote>
<p>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.</p>
</blockquote>