insert, update, delete

A table object has three commands for single-record writes. They are insert, update and delete. Each command works on one record. The record is a plain Python dictionary. Its keys are the column names of the model, written without the dollar sign. The dollar sign belongs to query expressions, not to records. Insert and update change the record you pass, in place. Then they return that same object. You do not get a new object or a result set. Delete returns nothing. None of the three commands commits. The write stays pending until you call commit on the database, as you saw in lesson 19 for reads with a lock.

Here we insert a customer of the course model. The record has a key called full address. In the model, full address is a formula column. A formula column has no physical column, so insert ignores that key. The same holds for alias columns, for keys that start with an at sign, and for keys that are not columns at all. They are not an error. They are simply not written. The email key holds None. Insert leaves None values out of the INSERT statement. The column then gets its database default, or NULL when it has none. After the call, the record holds new keys. The id was generated. The timestamps and the user columns come from the system fields of the table. Lesson 25 explains them. The point here is that the record you passed is the record that was written.

Before the INSERT, the write checks the primary key. The method is called check pkey. It acts when the key is None or an empty string. When the key column declares an SQL default, the database assigns the key and nothing is done in Python. When the key is an integer, the next key is the current maximum plus one. This value is read without a lock. Two connections that insert at the same time can compute the same key, so this strategy is not safe under concurrency. When the key is a text column of size twenty-two, or a text column with no size, the key is a new twenty-two-character id. These ids sort in creation order. To choose your own keys, define new pkey value on a table subclass. You attach the subclass to the table with the x table class attribute. In the example, the region code becomes the first two letters of the name.

Some values are decided by the database, not by Python. A column default, an SQL value expression and a generated column are examples. For these columns the INSERT ends with a RETURNING clause. The returned values are written into your record. In the example, the opened column has an SQL default. We do not pass it, so the database fills it, and the record shows it after the call. The num key was generated as the maximum plus one. Update does the same for SQL value expressions and generated columns. Only those columns are read back. The other columns of the record are what you passed and what the hooks changed. RETURNING works on PostgreSQL and on SQLite. SQLite must be version 3.35 or later.

Update takes the record, an optional old record and an optional key. The key of the WHERE clause comes from the old record first. If there is no old record, it comes from the pkey argument. If there is neither, it comes from the record itself. The SET clause contains only the columns present in the record, None included. So a partial record changes only its own columns. The second call shows this. It changes the subject of ticket two and nothing else. The third call changes the key itself. The old record names the row, and the new record carries the new key value.

Update never reads the row from the database. If you pass no old record, the hooks receive None as old record. If you need the old values, read the row yourself. Read it with for update equal to True, so that no other connection changes it before your write. Lesson 19 showed this lock. Then pass a copy of the row as old record, and change another copy. Another rule follows from the absence of a read. When no row matches the key, update raises no error. The UPDATE statement changes zero rows. Lesson 26 shows two helpers that do the read and the update for you.

Delete accepts a key string or a record. With a key string, delete reads the row again with a lock. The read ignores the draft, logical deletion and partition filters. When the row is missing, delete returns without an error. With a dictionary, delete does not read the row. It uses the dictionary as it is, for the hooks and for the WHERE clause. The DELETE statement must remove exactly one row. Otherwise it raises record not found error. A table declared with deletable equal to False refuses every delete with a delete exception. After an error raised inside a write, the connection accepts only a rollback. Lesson 21 explains why.

A composite key is a JSON string. It lists the members in the order of the composite column. Dates and decimals carry a type suffix, such as colon colon D for a date. Insert writes this string into the record, under the name of the composite column. Update and delete accept the same string as key. A last rule concerns Bag values. When a record value is a Bag, the write stores its XML text. An empty Bag is stored as NULL.

Insert and update change and return the record you pass. Update takes its key from the old record, the pkey argument, or the record, and it never reads the row. Delete by key reads the row with a lock, and delete by record must remove exactly one row. The next lesson lists the steps that run inside each of these commands, in their order.