一行概述:

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

execute()到底是什么:

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

在所有情况下，execute()方法接受SQL文本或构造的SQL表达式，即SQLAlchemy支持的任何SQL表达式构造并返回查询结果(ResultProxy -包装一个DB-API游标对象，以便更容易访问行列)。

---

为了进一步澄清(仅作概念上的澄清，不是推荐的方法):

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.

下面的例子很好地说明了这一点——给出一个如下表:

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()方法:

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()方法:

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

隐式执行，即Executable.execute() -也是无连接的，并且调用可执行文件的execute()方法，也就是说，它直接对SQL表达式构造(可执行文件的实例)本身调用execute()方法。

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

注意:说明隐式执行示例的目的是为了澄清-这种执行方式是非常不推荐的-根据文档:

“隐式执行”是一种非常古老的使用模式，在大多数情况下也是如此 混淆多于帮助，它的使用是不鼓励的。这两个 模式似乎鼓励过度使用权宜之计的“捷径” 导致以后出现问题的应用程序设计。

---

你的问题:

据我所知，如果有人使用引擎。执行它创建连接， 打开会话(Alchemy为您关心它)并执行查询。

如果有人使用引擎，你说得很对。使用Engine.execute()和connection .execute()是(几乎)同一件事，在形式上，连接对象是隐式创建的，在稍后的情况下，我们显式实例化它。在这种情况下真正发生的是:

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

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

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

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

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

文件节选如下:

重要的是要注意，在使用SQLAlchemy ORM时，这些 对象通常不被访问;相反，Session对象是 用作数据库的接口。然而，对于应用程序 是建立在直接使用文本SQL语句和/或SQL 表达式的构造不需要ORM的更高级别的参与 管理服务，引擎和连接是王(和皇后?)- 继续读下去。

运行. execute ()

当执行一个普通的SELECT * FROM表名时，所提供的结果没有区别。

这三个对象之间的差异确实变得很重要，这取决于SELECT语句所使用的上下文，或者更常见的情况是，当您想执行INSERT、DELETE等其他操作时。

什么时候使用引擎，连接，会话一般

Engine is the lowest level object used by SQLAlchemy. It maintains a pool of connections available 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. Connection is (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. An important example of this is a transaction, which lets you decide when to commit your changes to the database (if at all). In normal use, changes are auto-committed. 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 Exception: 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 Sessions are 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 a session. If you're only doing straight SQL queries not bound to objects, you're probably better off using connections directly.

下面是一个运行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