Understanding returning in SQLAlchemy with Python

https://www.bing.com/images

Understanding RETURNING in SQLAlchemy

The RETURNING clause, available in many SQL dialects like PostgreSQL, MySQL, and SQL Server, allows you to retrieve values from the rows affected by an UPDATE or DELETE statement within the same execution. This can be particularly useful when you need to:

• Access updated values after an update and use them further in your code.
• Obtain information about deleted rows for logging or auditing purposes.

Let’s get started:

Prerequisites

• SQLAlchemy installed: Ensure you have SQLAlchemy installed using pip install sqlalchemy.
• Database connection established: Set up a connection to your database using SQLAlchemy’s connection pool facilities.

Importing Necessary Modules

from sqlalchemy import create_engine, Column, Integer, String, update, delete, select

Defining the Data Model

Create a simple data model class to represent your database table (replace your_table_name and column names with your actual settings):

class YourModel(Base):
    __tablename__ = 'your_table_name'
    id = Column(Integer, primary_key=True)
    name = Column(String)

Using RETURNING with UPDATE

# Example: Update a name, retrieve the updated name
engine = create_engine('your_database_url')
Session = sessionmaker(bind=engine)
session = Session()

try:
    # Update a record with name='John' and retrieve the updated name
    updated_name = session.execute(
        update(YourModel)
        .where(YourModel.id == 1)
        .values({'name': 'Jane'})
        .returning(YourModel.name)
    ).fetchone()[0]  # Access the first element of the single-row result
    print("Updated name:", updated_name)
except Exception as e:
    session.rollback()
    print("Error:", e)
finally:
    session.close()

Code Walkthrough

• update(YourModel).where(YourModel.id == 1): Constructs an update statement targeting the YourModel table, filtering for the row with id=1.
• .values({'name': 'Jane'}): Sets the name column value to 'Jane' for the matching row(s).
• .returning(YourModel.name): Appends the RETURNING clause to the statement, specifying the name column to be returned.
• session.execute(): Executes the statement, returning an engine.Result object.
• .fetchone()[0]: Fetches the first row (as an expected single row in this case) and retrieves the first column value (the updated name).

Using RETURNING with DELETE

# Example: Delete a record, retrieve the deleted name
try:
    deleted_name = session.execute(
        delete(YourModel)
        .where(YourModel.id == 2)
        .returning(YourModel.name)
    ).fetchone()[0]

    print("Deleted name:", deleted_name)
except Exception as e:
    session.rollback()
    print("Error:", e)

Code Walkthrough

• delete(YourModel).where(YourModel.id == 2): Constructs a delete statement targeting the YourModel table, filtering for the row with id=2.
• .returning(YourModel.name): Appends the RETURNING clause to retrieve the name column of the deleted row.
• The rest is similar to the UPDATE example, fetching and printing the deleted name.

Can I use returning with select?

No, you cannot directly use RETURNING with a standard SELECT statement in SQLAlchemy or most SQL dialects. The RETURNING clause specifically functions within UPDATE and DELETE statements to retrieve the modified or deleted rows from the affected tables.

What if I have multiple columns to return and also multiple rows?

When working with multiple columns and rows in RETURNING with SQLAlchemy, the approach remains the same, but you need to adapt the selection part to retrieve the desired data.

Returning Multiple Columns:

Simply list the columns you want to return within the RETURNING clause, separated by commas:

# Example: Update a user and retrieve ID, name, and email

updated_data = session.execute(
    update(YourModel)
    .where(YourModel.id == 1)
    .values({'name': 'Jane', 'email': 'jane@example.com'})
    .returning(YourModel.id, YourModel.name, YourModel.email)
).fetchall()

This will fetch all affected rows (potentially multiple rows if there are duplicate keys matching the WHERE criteria) and create a list of tuples where each tuple contains the values of the specified columns for the corresponding row.

Returning Multiple Rows:

By default, RETURNING retrieves all affected rows in a database-specific format (usually a list of dictionaries or tuples). There's no direct way to control fetching specific rows within the RETURNING clause itself.

However, you can filter the returned data after the statement execution using Python list comprehension or filtering methods. For example:

# Example: Update and retrieve all names of updated users

updated_names = [
    row[1]  # Access the second element (name) in each row tuple
    for row in session.execute(
        update(YourModel)
        .where(YourModel.age > 20)
        .values({'name': 'Updated'})
        .returning(YourModel.id, YourModel.name)
    ).fetchall()
]
print("Updated names:", updated_names)

This approach iterates through the returned list and extracts the desired data (names in this case) from each row.

Does returning preserve order? If there are ids, name and email cols, the values will be corresponding like they are in the database rows?

In most cases, yes, RETURNING in SQLAlchemy will typically preserve the order of the affected rows as they appear in the database. This means that the order in which the rows are returned from the database will generally correspond to the order in which they were updated or deleted.

However, there are a few essential considerations to keep in mind:

1. Database-specific behaviour: The specific behaviour of RETURNING in terms of order preservation can vary slightly depending on the underlying database and its configuration. For some databases, specific settings might influence the order, or certain operations might inherently not guarantee order (e.g., concurrent updates on the same row).
2. Concurrent operations: If multiple concurrent operations are modifying the same data concurrently (e.g., multiple threads or processes accessing the database), the order of returned rows might not be deterministic due to the nature of concurrent transactions.
3. Custom logic or stored procedures: If you’re using custom Python logic within your application or stored procedures in the database to interact with RETURNING, the order might get influenced by how you handle the retrieved data and the operations you perform on it.

Conclusion

That’s it for this article! Feel free to leave feedback or questions in the comments.

Enjoyed the article and found it helpful? If you’d like to show your appreciation and support my work, you can buy me a coffee at https://www.paypal.com/paypalme/pythoncodenemesis! Your support goes a long way in helping me create more content like this. Thank you in advance! ☕️