如何通过ResultProxy对象使用SQLAlchemy更新大文本数据表的值？

Hey there! Let's break down how to handle updates using ResultProxy with SQLAlchemy Core, since your code snippet uses the Core approach (not ORM models).

First, a quick recap: ResultProxy is the object you get when you execute any SQL statement via SQLAlchemy's engine, connection, or session. For updates, you’ll typically construct an UPDATE statement, execute it, and then use the returned ResultProxy to verify the outcome or retrieve updated data.

Basic Update with ResultProxy #

The most straightforward way is to construct an UPDATE statement, execute it, and use the ResultProxy to check how many rows were affected:

from sqlalchemy import update

# Use a connection (matches your Core setup)
with engine.connect() as conn:
    # Build the update statement: target rows + new values
    update_stmt = update(data_table)\
        .where(data_table.c.id == 123)\  # Filter rows to update
        .values(content="Updated text content for your corpus")  # Set new value
    
    # Execute the statement to get a ResultProxy
    result = conn.execute(update_stmt)
    
    # Use ResultProxy's *rowcount* attribute to confirm updates
    print(f"Number of rows updated: {result.rowcount}")
    
    # Since your session uses autocommit=False, commit the change
    conn.commit()

Update Rows from a Query ResultProxy #

If you first fetch rows via a SELECT query (which returns a ResultProxy), you can iterate over those rows to update them individually:

with engine.connect() as conn:
    # First, fetch rows you want to process (returns ResultProxy)
    select_stmt = data_table.select().where(data_table.c.category == "technical")
    query_result = conn.execute(select_stmt)
    
    # Iterate over the ResultProxy rows to update each
    for row in query_result:
        # Build an update for the current row
        update_stmt = update(data_table)\
            .where(data_table.c.id == row.id)\
            .values(content=row.content + " [Processed]")  # Modify existing content
        
        # Execute and get a ResultProxy for this update
        update_result = conn.execute(update_stmt)
        print(f"Updated row {row.id}: {update_result.rowcount} row affected")
    
    conn.commit()

Using Your Preconfigured Session #

Since you’ve already set up a Session object, you can use it to execute updates too—you’ll still get a ResultProxy in return:

session = Session()
try:
    # Build and execute the update statement via the session
    update_stmt = update(data_table)\
        .where(data_table.c.id == 456)\
        .values(status="reviewed")
    
    result = session.execute(update_stmt)
    print(f"Rows updated: {result.rowcount}")
    
    # Commit the change (required with autocommit=False)
    session.commit()
except Exception as e:
    # Rollback on error to avoid dirty state
    session.rollback()
    raise e
finally:
    # Always close the session when done
    session.close()

Useful Advanced Tips #

Bulk Updates (For Large Corpora) #

For big text tables, bulk updates are way more efficient than updating rows one by one. Use in_() to target multiple rows at once:

update_stmt = update(data_table)\
    .where(data_table.c.id.in_([1, 2, 3, 4, 5]))\
    .values(status="processed")

result = conn.execute(update_stmt)
print(f"Bulk updated {result.rowcount} rows")

Retrieve Updated Rows #

If you need to get the actual updated data right after the update, use returning()—the ResultProxy will hold the returned rows:

from sqlalchemy import func

update_stmt = update(data_table)\
    .where(data_table.c.id == 123)\
    .values(content="Updated corpus text")\
    .returning(data_table.c.id, data_table.c.content)  # Specify columns to return

result = conn.execute(update_stmt)
# Fetch the updated row from ResultProxy
updated_row = result.fetchone()
print(f"Updated row: ID {updated_row.id} | Content: {updated_row.content}")

内容的提问来源于stack exchange，提问作者habet