SQLAlchemy for Python Developers

# Executing Transactions and Queries


# Obtaining connection

You can connect to the database and execute a query as follows.

>>> from sqlalchemy import text

>>> with engine.connect() as conn:
...     result = conn.execute(text("select 'hello world'"))
...     print(result.all())

[('hello world',)]
  • Obtain a Connection (opens new window) object through engine.connect() and store it in conn.
    • This Connection object allows you to interact with the database.
    • The with statement becomes a single transaction unit.
  • Transactions are not committed automatically.
    • You have to invoke the Connection.commit() to commit changes.

# Committing Changes

Obtaining a connection, initiating a transaction, and interacting with the database do not automatically commit changes.

To commit the change, you need to call Connection.commit() as follows.

>>> with engine.connect() as conn:
...     # DDL - Creating the table
...     conn.execute(text("CREATE TABLE some_table (x int, y int)")) 
...     # DML - Inserting data into the table
...     conn.execute(
...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
...         [{"x": 1, "y": 1}, {"x": 2, "y": 4}]
...     )
...     # TCL - Commiting changes.
...     conn.commit()

When you run the code above, you'll see the following result below.

BEGIN (implicit)
CREATE TABLE some_table (x int, y int)
[...] ()
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] ((1, 1), (2, 4))
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

You can also automatically commit at the end of a transaction using Engine.begin() and with statement.

>>> with engine.begin() as conn:
...     conn.execute(
...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
...         [{"x": 6, "y": 8}, {"x": 9, "y": 10}]
...     )
... # Transaction commits automatically when the execution is done.

Executing the code above will yield the following results.

BEGIN (implicit)
INSERT INTO some_table (x, y) VALUES (?, ?)
[...] ((6, 8), (9, 10))
<sqlalchemy.engine.cursor.CursorResult object at 0x...>
COMMIT

# Command Line Execution Basics

You can execute queries and retrieve results as follows.

>>> with engine.connect() as conn:
...     # conn.execute() initializes the result in an object named `result`.
...     result = conn.execute(text("SELECT x, y FROM some_table"))
...     for row in result:
...         print(f"x: {row.x}  y: {row.y}")

x: 1  y: 1
x: 2  y: 4
x: 6  y: 8
x: 9  y: 10
  • The Result (opens new window) object is the object that holds the "query result" returned by conn.execute().
    • You can see what features it provides by clicking on the link.
    • For instance, you can receive a list of Row objects using Result.all().

cf. Both Result and Row are objects provided by SQLAlchemy.

You can access each row using the Result object as follows.

result = conn.execute(text("select x, y from some_table"))

# Accessing the tuple.
for x, y in result:
    # ...
    
# Accessing the value by using integer index.
for row in result:
    x = row[0]

# Accessing the value by using a name of the property.
for row in result:
    y = row.y
    
# Accessing the value by using a mapping access.
for dict_row in result.mappings():
    x = dict_row['x']
    y = dict_row['y']

# Passing parameters to your query

You can pass a parameter to a query as follows.

>>> with engine.connect() as conn:
...     result = conn.execute(
...         text("SELECT x, y FROM some_table WHERE y > :y"),  # Receive in colon format (`:`).
...         {"y": 2}  # Pass by `dict`.
...     )
...     for row in result:
...        print(f"x: {row.x}  y: {row.y}")

x: 2  y: 4
x: 6  y: 8
x: 9  y: 10

You can also send multiple parameters like this.

>>> with engine.connect() as conn:
...     conn.execute(
...         text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
...         [{"x": 11, "y": 12}, {"x": 13, "y": 14}]  # Pass by `List[dict]``
...     )
...     conn.commit()

The above code executes the following query.

INSERT INTO some_table (x, y) VALUES (?, ?) [...] ((11, 12), (13, 14))

# Executing ORM by using Session

From now on, let's execute the query using Session provided by the ORM, instead of the Connection object. You can do it as follows:

>>> from sqlalchemy.orm import Session

>>> stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y").bindparams(y=6)

>>> # Pass an instance of the Engine object to the Session object
>>> # to get an instance that can interact with the database.
>>> with Session(engine) as session:
...     # Executing the query using Session.execute().
...     result = session.execute(stmt)
...     for row in result:
...        print(f"x: {row.x}  y: {row.y}")

Like Connection, Session also does not automatically commit upon closing. To commit, you need to explicitly call Session.commit() as follows:

>>> with Session(engine) as session:
...     result = session.execute(
...         text("UPDATE some_table SET y=:y WHERE x=:x"),
...         [{"x": 9, "y":11}, {"x": 13, "y": 15}]
...     )
...     # You have to call `commit()` explicitly.
...     session.commit()

← Working with Database Metadata →