Inserts & Updates

To add data into your database, you'll usually want to create the objects in Python and then write them out to your database table. Let's consider this table schema.

from iceaxe import DBConnection, select, TableBase, Field

class Employee(TableBase):
    id: int | None = Field(primary_key=True, default=None)
    name: str
    age: int

We configure the id of the table to be explicitly the primary key and set it to None by default. This signals to Iceaxe and Postgres that your table should be auto-incremented. Upon insert it will automatically assign a unique integer to each row.

Define a few employees with a typical constructor.

employees = [
    Employee(name="Marcus Holloway", age=28),
    Employee(name="Astrid Keller", age=42),
    Employee(name="Jasper Okafor", age=36),
]

await conn.insert(employees)

employees[0].id
> 1

After this insert, Iceaxe will have assigned any auto-generated primary keys back into the instance element.

Individual updates

Once you have Iceaxe instances in Python, either fetched from a select or after you've made an insert, you can manipulate the attributes like you do with any regular Python object. In the background we track all of your field modifications so we can write them out when requested. When you're ready to save your changes back to the database, you can call the update method on the instance.

from iceaxe import select

query = select(Employee).where(Employee.name == "Marcus Holloway")
results = await conn.exec(query)

marcus = results[0]
marcus.age = 29

# Before the conn.update(), the database will have the old value.
await conn.update([marcus])
# Now the database will have the new value.

Bulk updates

If you have a large number of rows to update in a consistent way, you can also make use of a database level UPDATE statement. This avoids the SQL->Python->SQL round trip of doing a fetch before your updates. We export an update query builder for this purpose. It's close in nature to a select query but lets you choose the type values to update.

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'])