Akron ORM - Universal Python ORM

Overview

The update() method modifies existing records in database tables. It uses filtering conditions to identify which records to update and applies new values to specified columns. The method returns the number of affected records.

Signature

update(table_name: str, filters: Dict[str, Any], new_values: Dict[str, Any]) -> int

Parameters

table_name

Type: str

Name of the table containing records to update. The table must exist.

filters

Type: Dict[str, Any]

Dictionary mapping column names to values for identifying which records to update. Must be non-empty to prevent accidental mass updates.

⚠️ Important: All conditions are combined with AND logic. Empty filters are not allowed for safety.

new_values

Type: Dict[str, Any]

Dictionary mapping column names to their new values. Only specified columns will be updated; other columns remain unchanged.

Returns

Type: int

Number of records that were actually updated. Returns 0 if no records matched the filter conditions.

Examples

Basic Updates

Single Record Update

1from akron import Akron
2
3db = Akron("sqlite:///example.db")
4
5# Setup: Create table and insert sample data
6db.create_table("users", {
7    "id": "int",
8    "username": "str",
9    "email": "str",
10    "age": "int", 
11    "active": "bool",
12    "last_login": "str"
13})
14
15# Insert test data
16db.insert("users", {"username": "alice", "email": "alice@example.com", "age": 28, "active": True, "last_login": "2024-01-01"})
17db.insert("users", {"username": "bob", "email": "bob@example.com", "age": 32, "active": False, "last_login": "2023-12-15"})
18
19# Update a single user by ID
20updated_count = db.update("users", 
21    {"id": 1},  # Find user with ID 1
22    {"age": 29, "last_login": "2024-01-15"}  # Update age and last_login
23)
24
25print(f"Updated {updated_count} user(s)")
26
27# Verify the update
28user = db.find("users", {"id": 1})
29print(f"Updated user: {user[0]}")

Expected Output

Updated 1 user(s)
Updated user: {'id': 1, 'username': 'alice', 'email': 'alice@example.com', 'age': 29, 'active': True, 'last_login': '2024-01-15'}

Multiple Record Updates

Update multiple records that match the same criteria:

Bulk Status Update

1# Insert more test data
2db.insert("users", {"username": "charlie", "email": "charlie@example.com", "age": 25, "active": False, "last_login": "2023-11-20"})
3db.insert("users", {"username": "diana", "email": "diana@example.com", "age": 30, "active": False, "last_login": "2023-10-05"})
4
5# Activate all inactive users
6activated_count = db.update("users",
7    {"active": False},  # Find all inactive users
8    {"active": True, "last_login": "2024-01-16"}  # Activate them and update login
9)
10
11print(f"Activated {activated_count} user(s)")
12
13# Check all users now
14all_users = db.find("users")
15for user in all_users:
16    status = "Active" if user["active"] else "Inactive"
17    print(f"  {user['username']}: {status} (last login: {user['last_login']})")

Expected Output

Activated 3 user(s)
  alice: Active (last login: 2024-01-15)
  bob: Active (last login: 2024-01-16)
  charlie: Active (last login: 2024-01-16)
  diana: Active (last login: 2024-01-16)

Conditional Updates

Use multiple filter conditions for precise updates:

Age-Based Discount Update

1# Create products table
2db.create_table("products", {
3    "id": "int",
4    "name": "str",
5    "price": "float",
6    "category": "str",
7    "discount": "float"
8})
9
10# Insert sample products
11db.insert("products", {"name": "Laptop", "price": 999.99, "category": "Electronics", "discount": 0.0})
12db.insert("products", {"name": "Book", "price": 29.99, "category": "Books", "discount": 0.0})
13db.insert("products", {"name": "Phone", "price": 699.99, "category": "Electronics", "discount": 0.0})
14db.insert("products", {"name": "Desk", "price": 199.99, "category": "Furniture", "discount": 0.0})
15
16# Apply 10% discount to electronics over $500
17electronics_discount = db.update("products",
18    {"category": "Electronics", "price": 699.99},  # Electronics at exactly $699.99
19    {"discount": 0.10}  # 10% discount
20)
21
22print(f"Applied discount to {electronics_discount} electronics product(s)")
23
24# Apply different discount to books
25books_discount = db.update("products",
26    {"category": "Books"},
27    {"discount": 0.15}  # 15% discount on books
28)
29
30print(f"Applied discount to {books_discount} book(s)")
31
32# Check updated products
33discounted_products = db.find("products", {"discount": 0.10})
34print(f"Products with 10% discount: {len(discounted_products)}")
35for product in discounted_products:
36    final_price = product["price"] * (1 - product["discount"])
37    print(f"  {product['name']}: $" + str(product['price']) + " -> $" + f"{final_price:.2f}")

Expected Output

Applied discount to 1 electronics product(s)
Applied discount to 1 book(s)

Products with 10% discount: 1
  Phone: $699.99 -> $629.99

Updates with Foreign Keys

Update records that reference other tables:

Order Status Updates

1# Create order management tables
2db.create_table("customers", {
3    "id": "int",
4    "name": "str",
5    "email": "str"
6})
7
8db.create_table("orders", {
9    "id": "int",
10    "customer_id": "int->customers.id",
11    "total": "float",
12    "status": "str",
13    "created_date": "str"
14})
15
16# Insert customers and orders
17customer_id = db.insert("customers", {"name": "John Smith", "email": "john@example.com"})
18
19order1_id = db.insert("orders", {"customer_id": customer_id, "total": 149.99, "status": "pending", "created_date": "2024-01-10"})
20order2_id = db.insert("orders", {"customer_id": customer_id, "total": 299.99, "status": "pending", "created_date": "2024-01-12"})
21
22# Ship one specific order
23shipped_count = db.update("orders",
24    {"id": order1_id},
25    {"status": "shipped"}
26)
27
28print(f"Shipped {shipped_count} order(s)")
29
30# Process all pending orders for this customer
31processed_count = db.update("orders",
32    {"customer_id": customer_id, "status": "pending"},
33    {"status": "processing"}
34)
35
36print(f"Moved {processed_count} order(s) to processing")
37
38# Check final order statuses
39customer_orders = db.find("orders", {"customer_id": customer_id})
40for order in customer_orders:
41    print(f"  Order " + str(order['id']) + ": $" + str(order['total']) + " - " + order['status'])

Expected Output

Shipped 1 order(s)
Moved 1 order(s) to processing

  Order 1: $149.99 - shipped
  Order 2: $299.99 - processing

Database-Specific Behavior

🗄️ SQLite

Uses standard SQL UPDATE statements with WHERE clauses:

1# Generated SQL for SQLite
2UPDATE users SET age = ?, last_login = ? WHERE id = ?
3# Parameters: [29, '2024-01-15', 1]
4# Returns: cursor.rowcount

🐬 MySQL

Uses MySQL UPDATE syntax with parameter binding:

1# Generated SQL for MySQL
2UPDATE users SET age = %s, last_login = %s WHERE id = %s
3# Parameters: [29, '2024-01-15', 1]
4# Returns: cursor.rowcount

🐘 PostgreSQL

Uses PostgreSQL UPDATE syntax:

1# Generated SQL for PostgreSQL  
2UPDATE users SET age = %s, last_login = %s WHERE id = %s
3# Parameters: [29, '2024-01-15', 1]
4# Returns: cursor.rowcount

🍃 MongoDB

Translates to MongoDB updateMany operation:

1# MongoDB equivalent
2db.users.updateMany(
3    {"id": 1},  # filter
4    {"$set": {"age": 29, "last_login": "2024-01-15"}}  # update
5)
6# Returns: result.modified_count

Error Handling

Handling Update Errors

1from akron import Akron
2from akron.exceptions import AkronError, TableNotFoundError
3
4db = Akron("sqlite:///example.db")
5
6try:
7    # This will fail if table doesn't exist
8    result = db.update("nonexistent_table", {"id": 1}, {"name": "test"})
9    
10except TableNotFoundError as e:
11    print(f"Table not found: {e}")
12    
13except AkronError as e:
14    print(f"Update error: {e}")
15
16# Safe updating with validation
17def safe_update(table_name, filters, new_values):
18    try:
19        # Validate that we have filters (prevent mass updates)
20        if not filters:
21            print("Error: Filters required for update")
22            return 0
23            
24        # Validate that we have new values
25        if not new_values:
26            print("Error: No values to update")
27            return 0
28            
29        # Perform the update
30        count = db.update(table_name, filters, new_values)
31        print(f"Successfully updated {count} record(s)")
32        return count
33        
34    except TableNotFoundError:
35        print(f"Table '{table_name}' does not exist")
36        return 0
37    except AkronError as e:
38        print(f"Update failed: {e}")
39        return 0
40
41# Safe usage examples
42safe_update("users", {"id": 1}, {"age": 30})  # Works
43safe_update("users", {}, {"age": 30})  # Prevents mass update
44safe_update("missing_table", {"id": 1}, {"name": "test"})  # Handles missing table

Expected Output

Table not found: no such table: nonexistent_table
Successfully updated 1 record(s)
Error: Filters required for update
Table 'missing_table' does not exist

Best Practices

🎯 Targeted Updates

Always use specific filters to avoid accidental mass updates
Update by primary key (id) for single record modifications
Use meaningful filter combinations for bulk operations
Check the returned count to verify expected number of updates

🔒 Data Integrity

Validate new values before updating to maintain data quality
Be careful when updating foreign key references
Consider constraints and validation rules
Use transactions for multi-table updates when possible

⚡ Performance

Update only the columns that actually need changing
Use efficient filter conditions (indexed columns when possible)
Batch similar updates together when practical
Consider the impact of updating large numbers of records

🧪 Testing

Always test update queries with small datasets first
Verify updates by querying the data afterward
Have a backup strategy for important data modifications
Use development databases for testing complex updates

Common Patterns

Update Patterns and Examples

1# Pattern 1: Update single record by ID
2updated = db.update("users", {"id": user_id}, {"last_login": "2024-01-16"})
3
4# Pattern 2: Bulk status updates
5count = db.update("orders", {"status": "pending"}, {"status": "processing"})
6
7# Pattern 3: Conditional field updates
8db.update("products", 
9    {"category": "Electronics", "price": 999.99},
10    {"discount": 0.10, "promotion": "Winter Sale"}
11)
12
13# Pattern 4: Calculate and update derived fields
14users = db.find("users")
15for user in users:
16    # Calculate new value based on existing data
17    new_score = user["points"] * 1.1
18    db.update("users", {"id": user["id"]}, {"score": new_score})
19
20# Pattern 5: Update with timestamp
21from datetime import datetime
22now = datetime.now().isoformat()
23db.update("posts", {"id": post_id}, {
24    "content": new_content,
25    "updated_at": now
26})
27
28# Pattern 6: Safe increment (read-modify-write)
29product = db.find("products", {"id": product_id})[0]
30new_stock = product["stock"] + quantity_sold
31db.update("products", {"id": product_id}, {"stock": new_stock})

Next Steps

After updating records, you might want to:

Query Updated Data

Verify your updates by retrieving the modified records

→ learn about find()

Delete Records

Remove unwanted data from your tables

→ learn about delete()