Of course! The term "affected rows" in Python is almost exclusively used in the context of database operations. It refers to the number of rows in a database table that were changed (inserted, updated, or deleted) by the most recent SQL statement.

This is a crucial piece of information for understanding the outcome of your database write operations.

The Core Concept: Why "Affected Rows" Matter

When you execute an INSERT, UPDATE, or DELETE statement, you often want to know if the command actually did anything. For example:

• Did my UPDATE statement actually change any rows? Maybe the WHERE clause didn't match any records.
• How many records did I INSERT? This confirms the operation was successful.
• Did my DELETE statement remove anything? This can prevent accidental, silent failures.

The "affected rows" count provides this feedback.

How to Get Affected Rows in Python

The method for getting affected rows depends on the database driver you are using. The most common approach is to use a method on the database cursor object after executing your query.

Here are the primary methods, categorized by database driver.

The sqlite3 Module (Python's Built-in Library)

The sqlite3 cursor has a rowcount attribute that is updated after you execute a statement.

Key Points for sqlite3:

• rowcount is -1 for a SELECT statement.
• For INSERT, UPDATE, DELETE, it reflects the number of rows changed.
• Crucially, rowcount is only guaranteed to be accurate after you have fetched all the results from a SELECT query or called connection.commit().

Example:

import sqlite3
# Connect to an in-memory database
conn = sqlite3.connect(':memory:')
cursor = conn.cursor()
# Create a table
cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)")
conn.commit()
# --- INSERT Example ---
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Alice', 'alice@example.com'))
# Check the rowcount immediately after execute
print(f"Rows affected by INSERT: {cursor.rowcount}") # Output: 1
# --- UPDATE Example ---
# Update a user that exists
cursor.execute("UPDATE users SET email = ? WHERE name = ?", ('alice_new@example.com', 'Alice'))
print(f"Rows affected by UPDATE (existing record): {cursor.rowcount}") # Output: 1
# Update a user that does NOT exist
cursor.execute("UPDATE users SET email = ? WHERE name = ?", ('nonexistent@example.com', 'Bob'))
print(f"Rows affected by UPDATE (non-existent record): {cursor.rowcount}") # Output: 0
# --- DELETE Example ---
# Delete a user that exists
cursor.execute("DELETE FROM users WHERE name = 'Alice'")
print(f"Rows affected by DELETE (existing record): {cursor.rowcount}") # Output: 1
# Delete a user that does NOT exist
cursor.execute("DELETE FROM users WHERE name = 'Charlie'")
print(f"Rows affected by DELETE (non-existent record): {cursor.rowcount}") # Output: 0
conn.close()

The mysql-connector-python Library (for MySQL)

This library also uses the rowcount attribute on the cursor. The behavior is very similar to sqlite3.

Installation:

pip install mysql-connector-python

Example:

import mysql.connector
from mysql.connector import Error
try:
    # Replace with your actual database connection details
    conn = mysql.connector.connect(
        host='localhost',
        database='testdb',
        user='your_username',
        password='your_password'
    )
    if conn.is_connected():
        cursor = conn.cursor()
        # --- UPDATE Example ---
        query = "UPDATE employees SET salary = %s WHERE department = %s"
        data = (95000, 'Engineering')
        cursor.execute(query, data)
        affected_rows = cursor.rowcount
        print(f"{affected_rows} row(s) were updated.")
        conn.commit() # Don't forget to commit!
except Error as e:
    print(f"Error while connecting to MySQL: {e}")
finally:
    if conn.is_connected():
        cursor.close()
        conn.close()

The psycopg2 Library (for PostgreSQL)

psycopg2 follows the same pattern, using the rowcount attribute. It's a very consistent and widely adopted convention.

Installation:

pip install psycopg2-binary

Example:

import psycopg2
# Replace with your actual database connection details
conn = psycopg2.connect(
    dbname="testdb",
    user="your_username",
    password="your_password",
    host="localhost"
)
cursor = conn.cursor()
# --- INSERT Example (using executemany for multiple rows) -->
insert_query = "INSERT INTO products (product_name, price) VALUES (%s, %s)"
product_data = [('Laptop', 1200), ('Mouse', 25), ('Keyboard', 75)]
cursor.executemany(insert_query, product_data)
affected_rows = cursor.rowcount
print(f"{affected_rows} row(s) were inserted.")
conn.commit() # Commit the transaction
cursor.close()
conn.close()

The pymysql Library (Another popular MySQL driver)

pymysql also uses the rowcount attribute, maintaining consistency across the Python database ecosystem.

Installation:

pip install PyMySQL

Example:

import pymysql
# Replace with your actual database connection details
conn = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='testdb'
)
cursor = conn.cursor()
# --- DELETE Example ---
cursor.execute("DELETE FROM orders WHERE status = 'cancelled'")
affected_rows = cursor.rowcount
print(f"{affected_rows} cancelled order(s) deleted.")
conn.commit()
cursor.close()
conn.close()

Important Considerations and Best Practices

1. rowcount vs. fetchone()/fetchall() for SELECT

   • For SELECT statements, cursor.rowcount typically returns -1 or the number of rows fetched so far. It is not the reliable way to get the total number of rows a query would return.
   • To get the count of rows from a SELECT query, you have two options:
     • Fetch all and count: len(cursor.fetchall())
     • *Use a `COUNT()` query:** This is much more efficient for large tables.

   # Inefficient for large tables
   cursor.execute("SELECT * FROM huge_table")
   all_rows = cursor.fetchall()
   print(f"Found {len(all_rows)} rows (inefficient).")
   # Efficient way
   cursor.execute("SELECT COUNT(*) FROM huge_table")
   count = cursor.fetchone()[0]
   print(f"Found {count} rows (efficient).")

2. Transaction Committing

   • In most database drivers (like mysql-connector-python and psycopg2), the rowcount might not be accurate until you call connection.commit(). This is because the changes are held in a transaction until they are permanently saved to the database.
   • sqlite3 is a bit different and often updates rowcount immediately, but it's still good practice to commit your changes.

3. ORMs (like SQLAlchemy)

   • If you use an Object-Relational Mapper (ORM) like SQLAlchemy, you typically don't interact with rowcount directly.

   • Instead, the ORM provides higher-level methods that return the result. For example, in SQLAlchemy Core:

     from sqlalchemy import create_engine, text
     engine = create_engine('sqlite:///mydatabase.db')
     with engine.connect() as conn:
         # The result object has a rowcount attribute
         result = conn.execute(text("UPDATE users SET name = 'Bob' WHERE id = 1"))
         print(f"Rows affected: {result.rowcount}")

   • In SQLAlchemy ORM, you would get the count from the result of the session.execute() call.

Summary Table

In short, for direct database access in Python, cursor.rowcount is the universal