SQLAlchemy: engine, connection and session difference

Question

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?

Answer 1

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.