Inserts, Updates, and Upserts
Create or modify TableBase objects in Python, then pass them to
DBConnection to save the changes:
DBConnection.insert(): Add new rows (INSERT).DBConnection.update(): Save changed fields on existing rows (UPDATE).DBConnection.upsert(): Insert rows or update them when a unique constraint conflicts (INSERT ... ON CONFLICT DO UPDATE).
Inserting data
An employee table needs an ID, a name, and an age:
from iceaxe import DBConnection, select, TableBase, Field
class Employee(TableBase):
id: int | None = Field(primary_key=True, default=None)
name: str
age: int
With primary_key=True and default=None, Iceaxe treats id as an
auto-incrementing primary key. Postgres assigns its value when the row is inserted.
Create employees with the usual Python constructor. TableBase uses Pydantic
to validate their fields; an age that cannot be converted to an integer raises
a validation error.
employees = [
Employee(name="Marcus Holloway", age=28),
Employee(name="Astrid Keller", age=42),
Employee(name="Jasper Okafor", age=36),
]
employees[0].id
> None
await conn.insert(employees)
employees[0].id
> 1
Before insert, the objects exist only in Python and their IDs are None.
After saving the rows, Iceaxe copies the generated IDs back onto those objects.
Updating data
You can edit an object after fetching or inserting it. Iceaxe tracks which
fields change but leaves the database untouched until you call update:
from iceaxe import select
query = select(Employee).where(Employee.name == "Marcus Holloway")
results = await conn.exec(query)
marcus = results[0]
marcus.age = 29
await conn.update([marcus])
Fetch the row again to check the saved age:
results = await conn.exec(query)
results[0].age
> 29
Upserting data
An upsert inserts a row if it is new and updates it if a unique constraint
conflicts. Checking with SELECT before inserting leaves a race: another
request can insert the same value between the two queries. upsert lets
Postgres resolve that conflict in one statement.
For this example, we'll identify employees by name. Add a unique constraint so Postgres can detect an existing name:
from iceaxe import UniqueConstraint
class Employee(TableBase):
id: int | None = Field(primary_key=True, default=None)
name: str
age: int
table_args = [
UniqueConstraint(columns=["name"])
]
employees = [
Employee(name="Marcus Holloway", age=28),
Employee(name="Astrid Keller", age=42),
]
# Upsert based on name, updating age if the name already exists
await conn.upsert(
employees,
conflict_fields=(Employee.name,),
update_fields=(Employee.age,)
)
conflict_fields identifies the unique constraint to check. If "Marcus Holloway"
already exists, update_fields limits the update to his age; otherwise Postgres
inserts a new employee.
Returning Values
Use returning_fields to retrieve values from the affected rows, including
generated IDs:
results = await conn.upsert(
employees,
conflict_fields=(Employee.name,),
update_fields=(Employee.age,),
returning_fields=(Employee.id, Employee.name)
)
for employee_id, name in results:
print(f"Upserted employee {name} with ID {employee_id}")
Query updates
To change rows without fetching them first, use the update query builder.
Specify the new value with set and select the affected rows with where:
from iceaxe import update
query = update(Employee).set(Employee.name, "Marcus Keller").where(Employee.name == "Marcus Holloway")
query.build()
> ('UPDATE "employee" SET "employee"."name" = $1
WHERE "employee"."name" = $2', ['Marcus Keller', 'Marcus Holloway'])
Batch updates
Pass objects to insert, update, or upsert in one list instead of calling
the method once per object. This lets Iceaxe reduce database round trips by:
- Splitting up the list of table instances into batches of parameters.
- Identifying manipulations that change the same keys and can be batched together.
- Sending separate operations for each underlying database table.
The batches account for two limits:
- The driver limits the number of parameters in a query to roughly 32,000.
- Prepared statements can't batch multiple statements in the same query execution.
Dirty objects
When you modify a local object in Python, you'll need to explicitly call conn.update() to sync the changes back to the database.
If you don't, the changes will be lost when the object is garbage collected. During development and testing, it can be helpful
to track these "dirty" objects - instances that have been modified but not synced to the database.
You can enable this tracking by setting the uncommitted_verbosity parameter when creating your DBConnection:
# Enable tracking with different warning levels
conn = DBConnection(
pg_connection,
uncommitted_verbosity="ERROR"
)
# Disable tracking (recommended for production)
conn = DBConnection(
pg_connection,
uncommitted_verbosity=None
)
Choose the log level for unsaved changes:
ERROR: Logs errors when objects are modified but not committedWARNING: Logs warnings when objects are modified but not committedINFO: Provides informational messages about modified but uncommitted objectsNone: Disables tracking entirely (default)
For example, with WARNING level enabled:
user = await conn.exec(select(User).where(User.id == 1))
user.name = "New Name"
# Session closes without calling conn.update([user])
# WARNING: Object <User id=1> has uncommitted changes: {'name'}
# File "...", line 10, in <module>
# user.name = "New Name"
The log includes where the object was first modified so you can find the code that left the change unsaved.
Performance Considerations
Dirty object tracking adds overhead because Iceaxe must:
- Track all modifications to objects
- Maintain a registry of modified objects and the location of the modification
- Check the state of objects when the session closes
Enable tracking during development or when investigating lost updates. Leave
uncommitted_verbosity=None in production to avoid that overhead.