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

问题:SQLAlchemy:引擎,连接和会话的区别

我使用SQLAlchemy并至少有三个实体:enginesession并且connection,其中有execute方法,所以如果我如想选择所有记录table我能做到这一点

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

还有这个

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

甚至这个

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

-结果将是相同的。

据我了解,如果有人使用engine.executeconnection,它会创建,打开session(Alchemy会为您处理)并执行查询。但是,执行此任务的这三种方式之间是否存在全局差异?

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?


回答 0

单行概述:

的行为execute()是在所有情况下相同,但它们是3种不同的方法,在EngineConnectionSession类。

到底是什么execute()

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

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


为了进一步澄清(仅用于概念澄清,不建议使用方法)

除了Engine.execute()(无连接执行),Connection.execute()和之外Session.execute(),还可以execute()直接在任何Executable构造上使用。该Executable班有它自己的执行execute()-每个正式文件作为,对什么人一行说明execute()确实是“ 编译并执行这个Executable ”。在这种情况下,我们需要将Executable(SQL表达式构造)与Connection对象或Engine对象(隐式获取Connection对象)进行显式绑定,以便execute()将知道在何处执行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表达式传递给以下execute()方法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表达式直接传递给execute()Engine方法:

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

隐式执行(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()

注意:出于说明的目的,陈述了隐式执行示例-强烈建议不按照这种方式执行这种执行方式-按照docs

“隐式执行”是一个非常古老的使用模式,在大多数情况下,它比有用的方法更令人困惑,并且不鼓励使用它。两种模式似乎都鼓励在应用程序设计中过度使用权宜之计的“捷径”,这会在以后导致问题。


你的问题:

据我了解,如果有人使用engine.execute,它将创建连接,打开会话(Alchemy会为您关心)并执行查询。

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

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

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

在数据库层,这完全是同一回事,所有这些都在执行SQL(文本表达式或各种SQL表达式构造)。从应用程序的角度来看,有两个选项:

  • 直接执行-使用Engine.execute()Connection.execute()
  • 使用sessions-通过有效地处理交易单单元的工作,轻松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更高级别的管理服务参与的应用程序,“引擎”和“连接”为王(也是王后?),请继续阅读。

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.


回答 1

Nabeel的答案涵盖了很多细节并且很有帮助,但是我发现难以理解。由于这是该问题的第一个Google结果,因此,我对以后发现此问题的人们加深了理解:

运行.execute()

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

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

何时使用引擎,连接,会话

  • 引擎是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功能,请使用会话。如果只执行不绑定对象的直接SQL查询,则最好直接使用连接。

Nabeel’s answer covers 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:

Running .execute()

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

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

When to use Engine, Connection, Session generally

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

  • 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 session; if you’re only doing straight SQL queries not bound to objects, you’re probably better off using connections directly.


回答 2

这是运行诸如GRANT之类的DCL(数据控制语言)的示例

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

Here is an example of running DCL (Data Control Language) such as 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