python - SQLAlchemy: engine, connection and session difference -

i use sqlalchemy , there @ least 3 entities: engine, session , connection, have execute method, if e.g. want select records table can this

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

and this

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

and this

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

result same.

as understand if use engine.execute creates connection, opens session (alchemy cares you) , executes query. there global difference between these 3 ways of performing such task?

a one-line overview:

the behavior of execute() same in cases, 3 different methods, in engine, connection, , session classes.

what execute():

to understand behavior of execute() need executable class. executable superclass “statement” types of objects, including select(), delete(),update(), insert(), text() - in simplest words possible, executable sql expression construct supported in sqlalchemy.

in cases execute() method takes sql text or constructed sql expression i.e. of variety of sql expression constructs supported in sqlalchemy , returns query results (a resultproxy - wraps db-api cursor object provide easier access row columns.)

---

to clarify further (only conceptual clarification, not recommended approach):

in addition engine.execute() (connectionless execution), connection.execute(), , session.execute(), possible use execute() directly on executable construct. executable class has it's own implementation of execute() - per official documentation, 1 line description execute() "compile , execute executable". in case need explicitly bind executable (sql expression construct) connection object or, engine object (which implicitly connection object), execute() know execute sql.

the following example demonstrates - given table 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 sql text or constructed sql expression execute() method of connection:

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

explicit connectionless execution i.e. engine.execute() - passing sql text or constructed sql expression directly execute() method of engine:

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

implicit execution i.e. executable.execute() - connectionless, , calls execute() method of executable, is, calls execute() method directly on sql expression construct (an instance of executable) itself.

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

note: stated implicit execution example purpose of clarification - way of execution highly not recommended - per docs:

“implicit execution” old usage pattern in cases more confusing helpful, , usage discouraged. both patterns seem encourage overuse of expedient “short cuts” in application design lead problems later on.

---

your questions:

as understand if use engine.execute creates connection, opens session (alchemy cares you) , executes query.

you're right part "if use engine.execute creates connection " not "opens session (alchemy cares you) , executes query " - using engine.execute() , connection.execute() (almost) 1 same thing, in formal, connection object gets created implicitly, , in later case explicitly instantiate it. happens in case is:

`engine` object (instantiated via `create_engine()`) -> `connection` object (instantiated via `engine_instance.connect()`) -> `connection.execute({*sql expression*})` 

but there global difference between these 3 ways of performing such task?

at db layer it's same thing, of them executing sql (text expression or various sql expression constructs). application's point of view there 2 options:

• direct execution - using engine.execute() or connection.execute()
• using sessions - efficiently handles transaction single unit-of-work, ease via session.add(), session.rollback(), session.commit(), session.close(). way interact db in case of orm i.e. mapped tables. provides identity_map instantly getting accessed or newly created/added objects during single request.

session.execute() uses connection.execute() statement execution method in order execute sql statement. using session object sqlalchemy orm's recommended way application interact database.

an excerpt docs:

its important note when using sqlalchemy orm, these objects not accessed; instead, session object used interface database. however, applications built around direct usage of textual sql statements and/or sql expression constructs without involvement orm’s higher level management services, engine , connection king (and queen?) - read on.