SQLAlchemy : 엔진, 연결 및 세션 차이

SQLAlchemy : 엔진, 연결 및 세션 차이

---

저는 SQLAlchemy를 사용하고 최소한 세 개의 엔티티가 있습니다 : engine, session및 connection, execute메소드가 있으므로 예를 들어 모든 레코드를 선택하려면 table이 작업을 수행 할 수 있습니다.

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

이

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

그리고 이것도

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

-결과는 동일합니다.

내가 알고있는 것처럼 사람이 사용하는 경우, engine.execute이 생성 connection열립니다 session(연금술 당신을 위해 처리합니다) 쿼리를 실행합니다. 그러나 이러한 작업을 수행하는이 세 가지 방법 사이에 글로벌 차이가 있습니까?

---

한 줄 개요 :

의 동작은 execute()모든 경우에 동일하지만, 그들은 3 개 가지 방법에 Engine, Connection그리고 Session클래스.

정확히 무엇입니까 execute():

의 행동을 이해하려면 수업 execute()을 살펴볼 필요가 Executable있습니다. Executableselect (), delete (), update (), insert (), text ()를 포함한 모든 "문"유형의 객체에 대한 수퍼 클래스입니다. 가능한 가장 단순한 단어로,는 ExecutableSQLAlchemy에서 지원되는 SQL 표현식 구조입니다.

모든 경우에이 execute()메서드는 SQL 텍스트 또는 구성된 SQL 식, 즉 SQLAlchemy에서 지원되는 다양한 SQL 식 구문을 가져와 쿼리 결과를 반환합니다 ( ResultProxya- DB-API행 열에 쉽게 액세스 할 수 있도록 커서 개체를 래핑합니다 .).

---

더 명확하게하기 위해 (개념적 설명을 위해서만 권장되는 접근 방식이 아님) :

이외에 Engine.execute()(연결형 실행) Connection.execute()와 Session.execute(), 그것을 사용하는 것도 가능 execute()하나에서 직접 Executable구조물. Executable클래스의 그것의 자신의 구현이 execute()- 공식 문서 당으로을의이 일에 대해 한 줄 설명 execute()하지는 " 컴파일이 실행Executable ". 이 경우 우리는 명시 적으로 Y 인드에 필요한 ExecutableA를 (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()SQLExecutable

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

참고 : 명시된 설명의 목적에 대한 암시 실행 예 - 실행이 방법은 추천하지 않습니다 -에 따라 문서 :

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

---

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.

---

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

참고URL : https://stackoverflow.com/questions/34322471/sqlalchemy-engine-connection-and-session-difference