如何在SQLite3中利用__rowid__实现多表关联更新

Got it, let's break this down properly. The key here is leveraging SQLite's built-in __rowid__—a unique, immutable identifier for every row—to reliably link your customers, services, and charges tables. This is way more reliable than using a customer name, since duplicates can easily happen! Here's a step-by-step solution tailored to your setup:

Step 1: Fetch the Target Customer's rowid #

First, grab the unique __rowid__ of the customer you want to modify. This ensures you're targeting the exact right row, even if multiple customers share the same name.

# Get the customer's __rowid__ using their current name
cursor.execute("SELECT __rowid__ FROM customers WHERE name = ?", (name_variable.get(),))
customer_row = cursor.fetchone()

if customer_row:
    customer_rowid = customer_row[0]
else:
    # Handle the case where no customer matches the provided name
    print("Error: Customer not found!")
    # Add your error handling logic here (e.g., show a message to the user)

Step 2: Update the Customers Table with rowid #

Replace your original UPDATE query to use __rowid__ instead of name—this is more efficient and eliminates ambiguity. Also, note a critical fix from your original code: you don't need single quotes around column names (that syntax would treat column names as string values, which causes errors!).

# Update core customer details using the fetched __rowid__
cursor.execute("""
    UPDATE customers 
    SET contact = ?, mail = ?, address = ? 
    WHERE __rowid__ = ?
""", (
    contact_variable.get(), 
    mail_variable.get(), 
    address_variable.get(), 
    customer_rowid
))

Step 3: Update Related Services and Charges Tables #

Assuming your services and charges tables have a column that stores the customer's __rowid__ (let's call it customer_rowid for consistency), use that to link and update the related records. If you haven't added this column yet, you'll want to alter those tables first (e.g., ALTER TABLE services ADD COLUMN customer_rowid INTEGER;) and backfill existing records with the correct __rowid__ values from customers.

Update the Services Table: #

# Adjust the SET clause to match the fields you need to update in your services table
cursor.execute("""
    UPDATE services 
    SET service_type = ?, service_date = ? 
    WHERE customer_rowid = ?
""", (
    service_type_var.get(),  # Replace with your actual service-related variables
    service_date_var.get(),
    customer_rowid
))

Update the Charges Table: #

# Adjust the SET clause to match the fields in your charges table
cursor.execute("""
    UPDATE charges 
    SET amount = ?, charge_date = ? 
    WHERE customer_rowid = ?
""", (
    amount_var.get(),  # Replace with your actual charge-related variables
    charge_date_var.get(),
    customer_rowid
))

Step 4: Commit All Changes #

Don't forget to commit your updates to make them permanent in the database:

conn.commit()

Key Tips: #

• Ditch Name-Based Updates: Using __rowid__ removes the risk of accidentally updating the wrong customer if multiple people share the same name.
• Fix Column Syntax: In your original code, wrapping column names like 'contact' in single quotes is incorrect—SQL interprets those as string literals, not column names. Just use the column name directly.
• Transaction Safety: If you want all three updates to succeed or fail together (to avoid partial updates), wrap them in an explicit transaction (SQLite's default auto-commit works, but explicit transactions are safer for multi-table changes).

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