10.17 INSERT, UPDATE and DELETE: the insert, update and delete compilers

The insert: InsertQuery and its compiler

InsertQuery is made with the conflict mode, an OnConflict, which is OnConflict.IGNORE, OnConflict.UPDATE or None, and the fields to update and the unique fields that mode may need; InsertQuery.insert_values then gives it the fields to write, the instances, and whether the values are to be taken as they stand on the instances, which a raw save asks for. It has the joins and the where tree of any query, and its compiler writes neither. QuerySet._insert makes one and at once calls SQLInsertCompiler.execute_sql on its compiler with the fields whose values are to come back.

The values, field by field

SQLInsertCompiler.as_sql begins with the statement’s first words, which the backend’s operations write: "INSERT INTO" from BaseDatabaseOperations.insert_statement, or on SQLite "INSERT OR IGNORE INTO" and on MySQL "INSERT IGNORE INTO" for OnConflict.IGNORE. Then it goes field by field, and for each field instance by instance. SQLInsertCompiler.pre_save_val takes the value: the field’s Field.pre_save with add=True, which is the hook by which auto_now_add sets the moment of signing on, or the attribute as it stands where the query is raw. Then SQLInsertCompiler.prepare_value, unless the value is a DatabaseDefault, the mark of a default the database is to apply.

prepare_value resolves a value that has resolve_expression against the insert query, with allow_joins=False and for_save=True, and refuses three kinds of what comes back: an expression with contains_column_references, with ValueError and a message that says F() expressions can only be used to update, not to insert, since, as the comment puts it, a column refers to an existing row and the row of an insert does not exist yet; one with contains_aggregate, with FieldError; and one with contains_over_clause, with FieldError too. Then the field’s Field.get_db_prep_save, with the connection, which hands an expression back as it is and prepares a plain value through the field’s get_db_prep_value (A field: the base class).

Port.objects.create(name=Lower(Value('TOBERMORY')), country=Concat(Value('Scot'), Value('land'))).name  ->  'tobermory'
  SQL INSERT INTO "fleet_port" ("name", "country") VALUES (LOWER(%s), (COALESCE(%s, %s) || COALESCE(%s, %s))) RETURNING "fleet_port"."id", "fleet_port"."name", "fleet_port"."country" with params ('TOBERMORY', 'Scot', '', 'land', '')
...
Port.objects.create(name=F('country'), country='x')  ->  raises ValueError: Failed to insert expression "Col(fleet_port, fleet.Port.country)" on fleet.Port.name. F() expressions can only be used to update, not to insert.
Port.objects.create(name=Count('ships'), country='x')  ->  raises FieldError: Joined field references are not permitted in this query
Port.objects.create(name=Window(RowNumber()), country='x')  ->  raises FieldError: Window expressions are not allowed in this query (name=<Window: RowNumber() OVER ()>).

The first port was given two expressions, and each was compiled into its place in the "VALUES" list; the statement asked both columns back with the key, because Model._save_table adds to the fields to return every field whose value is an expression, and the instance’s name came back in lower case (Saving an instance). Of the three refusals, the first is prepare_value’s own. The second came earlier: resolving Count("ships") with allow_joins=False refused the join its name needs, in Query.resolve_ref, before the aggregate was tested for. The window reached the third.

A default the database applies, and a column it computes

A Manifest of the office, in the models of From QuerySet to SQL, has a weight the database computes, "kilos", a GeneratedField, and a stamp it defaults, "stamped", with db_default=False. For a field with db_default and no default, Field.get_default returns a DatabaseDefault around the default’s expression, so an instance made without a value for "stamped" holds one; pre_save returns it and as_sql leaves it unprepared. A field with a database default whose values are all such is dropped from the statement with its values, unless it is the only field left, since an insert needs one column: the comment calls the values redundant, and says they could prevent optimizations. Where the field stays, because some instance was given a value or it is the last, and the backend has supports_default_keyword_in_bulk_insert, each DatabaseDefault goes into the statement as an expression, and DatabaseDefault.as_sql writes "DEFAULT", or compiles the default’s own expression where the backend lacks supports_default_keyword_in_insert, as SQLite does; where the backend lacks the keyword in a bulk insert, Oracle, each is replaced by the prepared default before the statement is assembled. Neither Model._save_table nor bulk_create sends a GeneratedField to the compiler: each leaves out every field with Field.generated.

What the database decides is asked back instead. Options.db_returning_fields lists the fields of a model that have db_returning: an auto field, a field with a database default, a generated field. Here spring is the Petrel’s voyage:

Manifest.objects.create(voyage=spring, crates=12, kilos_each=80)
  InsertQuery.insert_values(fields=[Manifest.serial, Manifest.voyage, Manifest.crates, Manifest.kilos_each, Manifest.stamped, Manifest.scan], objs=[<Manifest: Manifest object (M-0002)>])
  SQLInsertCompiler.execute_sql(returning_fields=[Manifest.kilos, Manifest.stamped])
...
      SQLInsertCompiler.pre_save_val(Manifest.stamped, <Manifest: Manifest object (M-0002)>)  ->  DatabaseDefault(Value(False))
      SQLInsertCompiler.pre_save_val(Manifest.scan, <Manifest: Manifest object (M-0002)>)  ->  a FieldFile
      SQLInsertCompiler.prepare_value(Manifest.scan, a FieldFile)  ->  ''
      SQLInsertCompiler.assemble_as_sql(fields=[Manifest.serial, Manifest.voyage, Manifest.crates, Manifest.kilos_each, Manifest.scan], value_rows=[('M-0002', 1, 12, 80, '')])
...
      BaseDatabaseOperations.returning_columns([Manifest.kilos, Manifest.stamped])  ->  ('RETURNING "office_manifest"."kilos", "office_manifest"."stamped"', ())
      -> [('INSERT INTO "office_manifest" ("serial", "voyage_id", "crates", "kilos_each", "scan") VALUES (%s, %s, %s, %s, %s) RETURNING "office_manifest"."kilos", "office_manifest"."stamped"', ('M-0002', 1, 12, 80, ''))]
    SQL INSERT INTO "office_manifest" ("serial", "voyage_id", "crates", "kilos_each", "scan") VALUES (%s, %s, %s, %s, %s) RETURNING "office_manifest"."kilos", "office_manifest"."stamped" with params ('M-0002', 1, 12, 80, '')
    BaseDatabaseOperations.fetch_returned_rows  ->  [(960, False)]
    -> [[960, False]]

Six fields came in, the generated one already left out. The value of "stamped" was a DatabaseDefault, which was not prepared, and the field was dropped before the values were assembled: five columns. The two columns asked back were the computed weight, twelve crates of eighty kilos, and the default the database applied. Where a second manifest gives the field a value, the column cannot be left out, and on SQLite the default is written out as its expression:

Manifest.objects.bulk_create([Manifest(voyage=spring, crates=1, kilos_each=1), Manifest(voyage=spring, crates=2, kilos_each=2, stamped=True)])
  InsertQuery.insert_values(fields=[Manifest.serial, Manifest.voyage, Manifest.crates, Manifest.kilos_each, Manifest.stamped, Manifest.scan], objs=[<Manifest: Manifest object (M-0003)>, <Manifest: Manifest object (M-0004)>])
  SQLInsertCompiler.execute_sql(returning_fields=[Manifest.kilos, Manifest.stamped])
...
      SQLInsertCompiler.pre_save_val(Manifest.stamped, <Manifest: Manifest object (M-0003)>)  ->  DatabaseDefault(Value(False))
      SQLInsertCompiler.pre_save_val(Manifest.stamped, <Manifest: Manifest object (M-0004)>)  ->  True
      SQLInsertCompiler.prepare_value(Manifest.stamped, True)  ->  True
...
        SQLInsertCompiler.field_as_sql(Manifest.stamped, DatabaseDefault(Value(False)))  ->  ('%s', (False,))
...
    SQL INSERT INTO "office_manifest" ("serial", "voyage_id", "crates", "kilos_each", "stamped", "scan") VALUES (%s, %s, %s, %s, %s, %s), (%s, %s, %s, %s, %s, %s) RETURNING "office_manifest"."kilos", "office_manifest"."stamped" with params ('M-0003', 1, 1, 1, False, '', 'M-0004', 1, 2, 2, True, '')
    BaseDatabaseOperations.fetch_returned_rows  ->  [(1, False), (4, True)]
    -> [[1, False], [4, True]]

The first manifest’s value was the DatabaseDefault, left unprepared, and the second’s was True. The field stayed, and field_as_sql compiled the DatabaseDefault, whose as_sql on SQLite is the default’s own expression, a placeholder with False as its parameter, where a backend with the keyword would have written "DEFAULT". Both rows came back with their weight and their stamp.

Placeholders, and the shape of the statement

SQLInsertCompiler.assemble_as_sql turns the fields and the rows of values into rows of placeholders and rows of parameters, one value at a time through SQLInsertCompiler.field_as_sql. With no field at all, which the comment says can only be a model whose primary key is a single auto field, as_sql writes one row an instance with BaseDatabaseOperations.pk_default_value, "DEFAULT" in the base class and "NULL" on SQLite, MySQL and Oracle, under a field of None. A value whose field is None is that placeholder, taken as it is; a field with a get_placeholder_sql method writes its own, as BinaryField.get_placeholder_sql does, and a field class that declares the older "get_placeholder" instead is given a get_placeholder_sql by Field.__init_subclass__, which warns with RemovedInDjango2028Warning; an expression is compiled; anything else is "%s" with the value as its parameter. BaseDatabaseOperations.modify_insert_params is given each pair last, a hook the comment says is used by Oracle Spatial alone.

The rest of the statement is the backend’s. BaseDatabaseOperations.on_conflict_suffix_sql is given the fields, the mode and the columns to update and to match on, and writes nothing in the base class; SQLite’s and PostgreSQL’s write ON CONFLICT(...) DO UPDATE SET with each column set from "EXCLUDED" for OnConflict.UPDATE, PostgreSQL’s ON CONFLICT DO NOTHING for OnConflict.IGNORE, and MySQL’s ON DUPLICATE KEY UPDATE. Then the statement takes one of three shapes, by whether execute_sql set SQLInsertCompiler.returning_fields and by what the backend’s features allow; as_sql returns a list of statements with their parameters, one or many.

SQLInsertCompiler for one row: each field's value through pre_save and prepare_value, one placeholder each, and RETURNING for the key
    oban = Port.objects.create(name='Oban', country='Scotland')
      InsertQuery.insert_values(fields=[Port.name, Port.country], objs=[<Port: Oban>])
      SQLInsertCompiler.execute_sql(returning_fields=[Port.id])
        SQLInsertCompiler.as_sql
          DatabaseOperations.insert_statement(on_conflict=None)  (sqlite3)
            BaseDatabaseOperations.insert_statement
            -> 'INSERT INTO'
          SQLInsertCompiler.pre_save_val(Port.name, <Port: Oban>)  ->  'Oban'
          SQLInsertCompiler.prepare_value(Port.name, 'Oban')  ->  'Oban'
          SQLInsertCompiler.pre_save_val(Port.country, <Port: Oban>)  ->  'Scotland'
          SQLInsertCompiler.prepare_value(Port.country, 'Scotland')  ->  'Scotland'
          SQLInsertCompiler.assemble_as_sql(fields=[Port.name, Port.country], value_rows=[('Oban', 'Scotland')])
            SQLInsertCompiler.field_as_sql(Port.name, 'Oban')  ->  ('%s', ['Oban'])
            SQLInsertCompiler.field_as_sql(Port.country, 'Scotland')  ->  ('%s', ['Scotland'])
            -> ((('%s', '%s'),), [['Oban', 'Scotland']])
          DatabaseOperations.on_conflict_suffix_sql(on_conflict=None)  (sqlite3)
            BaseDatabaseOperations.on_conflict_suffix_sql(fields=[Port.name, Port.country], on_conflict=None, update_fields=a generator, unique_fields=a generator)
            -> ''
          BaseDatabaseOperations.bulk_insert_sql(fields=[Port.name, Port.country], placeholder_rows=[['%s', '%s']])  ->  'VALUES (%s, %s)'
          BaseDatabaseOperations.returning_columns([Port.id])  ->  ('RETURNING "fleet_port"."id"', ())
          -> [('INSERT INTO "fleet_port" ("name", "country") VALUES (%s, %s) RETURNING "fleet_port"."id"', ('Oban', 'Scotland'))]
        SQL INSERT INTO "fleet_port" ("name", "country") VALUES (%s, %s) RETURNING "fleet_port"."id" with params ('Oban', 'Scotland')
        BaseDatabaseOperations.fetch_returned_rows  ->  [(4,)]
        -> [(4,)]
    oban.pk  ->  4

The key was asked for, and SQLite can return it, so the first shape was taken: one statement with a "RETURNING", run on a cursor, and the returned row fetched. Two ports at once, through bulk_create:

Port.objects.bulk_create([Port(name='Mull', country='Scotland'), Port(name='Skagen', country='Denmark')])
  InsertQuery.insert_values(fields=[Port.name, Port.country], objs=[<Port: Mull>, <Port: Skagen>])
  SQLInsertCompiler.execute_sql(returning_fields=[Port.id])
...
      BaseDatabaseOperations.bulk_insert_sql(fields=[Port.name, Port.country], placeholder_rows=[['%s', '%s'], ['%s', '%s']])  ->  'VALUES (%s, %s), (%s, %s)'
      BaseDatabaseOperations.returning_columns([Port.id])  ->  ('RETURNING "fleet_port"."id"', ())
      -> [('INSERT INTO "fleet_port" ("name", "country") VALUES (%s, %s), (%s, %s) RETURNING "fleet_port"."id"', ('Mull', 'Scotland', 'Skagen', 'Denmark'))]
    SQL INSERT INTO "fleet_port" ("name", "country") VALUES (%s, %s), (%s, %s) RETURNING "fleet_port"."id" with params ('Mull', 'Scotland', 'Skagen', 'Denmark')
    BaseDatabaseOperations.fetch_returned_rows  ->  [(5,), (6,)]
    -> [(5,), (6,)]

The values were gathered field by field and so instance by instance within a field, and assemble_as_sql turned them back into rows. Since SQLite returns the rows of a bulk insert, bulk_insert_sql wrote one list for both and both keys came back. On a backend that returns columns but not the rows of a bulk insert, as_sql writes the first row alone, which execute_sql’s assertion allows only for a single instance; QuerySet._batched_insert asks for nothing back on such a backend (bulk_create and bulk_update). The two conflict modes, over a logbook whose key is already taken:

Logbook.objects.bulk_create([Logbook(pk=1, title='Log 1, again')], ignore_conflicts=True)
  InsertQuery.insert_values(fields=[Logbook.id, Logbook.title], objs=[<Logbook: Logbook object (1)>])
  SQLInsertCompiler.execute_sql(returning_fields=None)
    SQLInsertCompiler.as_sql
      DatabaseOperations.insert_statement(on_conflict=OnConflict.IGNORE)  (sqlite3)  ->  'INSERT OR IGNORE INTO'
      DatabaseOperations.on_conflict_suffix_sql(on_conflict=OnConflict.IGNORE)  (sqlite3)
        BaseDatabaseOperations.on_conflict_suffix_sql(fields=[Logbook.id, Logbook.title], on_conflict=OnConflict.IGNORE, update_fields=a generator, unique_fields=a generator)
        -> ''
      BaseDatabaseOperations.bulk_insert_sql(fields=[Logbook.id, Logbook.title], placeholder_rows=[['%s', '%s']])  ->  'VALUES (%s, %s)'
      -> [('INSERT OR IGNORE INTO "office_logbook" ("id", "title") VALUES (%s, %s)', (1, 'Log 1, again'))]
    SQL INSERT OR IGNORE INTO "office_logbook" ("id", "title") VALUES (%s, %s) with params (1, 'Log 1, again')
    -> []
...
Logbook.objects.bulk_create([Logbook(pk=1, title='Log 1, fair copy')], update_conflicts=True, update_fields=['title'], unique_fields=['pk'])
  InsertQuery.insert_values(fields=[Logbook.id, Logbook.title], objs=[<Logbook: Logbook object (1)>])
  SQLInsertCompiler.execute_sql(returning_fields=[Logbook.id])
    SQLInsertCompiler.as_sql
      DatabaseOperations.insert_statement(on_conflict=OnConflict.UPDATE)  (sqlite3)
        BaseDatabaseOperations.insert_statement(on_conflict=OnConflict.UPDATE)
        -> 'INSERT INTO'
      DatabaseOperations.on_conflict_suffix_sql(on_conflict=OnConflict.UPDATE)  (sqlite3)  ->  'ON CONFLICT("id") DO UPDATE SET "title" = EXCLUDED."title"'
      BaseDatabaseOperations.bulk_insert_sql(fields=[Logbook.id, Logbook.title], placeholder_rows=[['%s', '%s']])  ->  'VALUES (%s, %s)'
      BaseDatabaseOperations.returning_columns([Logbook.id])  ->  ('RETURNING "office_logbook"."id"', ())
      -> [('INSERT INTO "office_logbook" ("id", "title") VALUES (%s, %s) ON CONFLICT("id") DO UPDATE SET "title" = EXCLUDED."title" RETURNING "office_logbook"."id"', (1, 'Log 1, fair copy'))]
    SQL INSERT INTO "office_logbook" ("id", "title") VALUES (%s, %s) ON CONFLICT("id") DO UPDATE SET "title" = EXCLUDED."title" RETURNING "office_logbook"."id" with params (1, 'Log 1, fair copy')
    BaseDatabaseOperations.fetch_returned_rows  ->  [(1,)]

For the first mode SQLite’s insert_statement wrote the conflict into the statement’s first words and the suffix was empty; nothing was asked back, because QuerySet._batched_insert asks for the returning fields only where the mode is None or OnConflict.UPDATE. For the second the suffix carried the update and the key came back. The "RETURNING" is the base class’s, used because SQLite’s features class can return columns from an insert; the suffix is SQLite’s own, which its operations class writes only where its features class has supports_update_conflicts_with_target.

execute_sql: the statements run, and the rows asked back

SQLInsertCompiler.execute_sql takes the fields whose columns are to come back and asserts that, where there are any and more than one instance, the backend has can_return_rows_from_bulk_insert. It opens a cursor, runs each statement as_sql returns, and with no returning fields returns an empty list. Otherwise the rows come one of two ways. Where the backend can return them, the rows of a bulk insert for several instances or the columns of an insert for one, BaseDatabaseOperations.fetch_returned_rows reads them from the cursor, with the kept parameters; or else, where the first returning field is an AutoField, one row is made of BaseDatabaseOperations.last_insert_id, which is the last row id the driver’s cursor reports in the base class and the current value of the key’s sequence on Oracle; and where neither holds, the list is empty. The rows go through the converters of the returned columns, SQLCompiler.get_converters and SQLCompiler.apply_converters, and come back as a list.

Who asks for what is the callers’ choice. Model._save_table starts from db_returning_fields, adds every field whose value is an expression, and takes off a field with db_returning where the backend cannot return columns, unless the key is set and the field is the auto field; QuerySet._batched_insert asks for db_returning_fields where the backend can return bulk rows and the conflict mode allows.

The update: UpdateQuery and its compiler

UpdateQuery keeps three things beyond a query’s: UpdateQuery.values, the triples of a field, its model and the value to set; UpdateQuery.related_updates, the values that belong to a parent model’s table, by model; and UpdateQuery.related_ids, the keys for those, once fetched. UpdateQuery._setup_query sets values and related_updates empty and related_ids to None, and runs both at construction and at the end of Query.chain, which calls it on any query class that has it, so that a queryset’s query chained into an UpdateQuery arrives with its joins and where tree and with these three fresh; UpdateQuery.clone copies the related updates.

UpdateQuery.add_update_values is the entry from QuerySet.update, with the keyword arguments as a dictionary. It finds each name’s field through the options and takes the concrete model of the field’s model, and refuses two things with FieldError: "pk" where the key is composite, which must be updated field by field, and a field that is not concrete, a reverse relation or a many-to-many. A field whose concrete model is not the query’s is a parent’s, and goes to UpdateQuery.add_related_update, which gathers the values under the model so that, its docstring says, one update is run for each ancestor. The rest go to UpdateQuery.add_update_fields, which skips a generated field, resolves a value that has resolve_expression at once, with allow_joins=False and for_save=True, so that, the comment says, the annotations are no longer needed, and appends the triple. UpdateQuery.get_related_updates makes an UpdateQuery for each ancestor with its values, filtered to the keys in related_ids where those were fetched. UpdateQuery.update_batch is the Collector’s way in for the instances whose foreign key it has to set, to null, to a default or to a value, before the row they point at goes: the values through add_update_values, then the keys in chunks of GET_ITERATOR_CHUNK_SIZE, each chunk a fresh where tree of "pk__in" and a statement run for NO_RESULTS.

as_sql: the SET list

SQLUpdateCompiler.as_sql calls SQLUpdateCompiler.pre_sql_setup first, and returns an empty statement where the query has no values; the base execute_sql takes an empty statement for EmptyResultSet, sends nothing and answers None. Each value reaches the "SET" list in one of three forms: a compiled expression, a placeholder the field writes, or "%s" and a parameter. Before that it is made ready. An expression is resolved here as add_update_fields resolved it, with for_save=True, the values of a related update coming to as_sql from add_related_update unresolved; and it is refused with FieldError where it contains_aggregate, where it contains_over_clause, and where it is a ColPairs, the F("pk") of a composite key. A model instance, anything with prepare_database_save, is accepted for a relation field alone and gives the value of the column the relation points at, the key unless the field names another, Model.prepare_database_save refusing an unsaved instance; for any other field it is TypeError. Then the field’s get_db_prep_save, and the column’s entry: the field’s own get_placeholder_sql where it has one, the compiled expression, or the placeholder and the value. The statement is "UPDATE", the base table, "SET" and the list; the where tree compiled, with FullResultSet meaning no "WHERE" at all; and returning_columns where SQLUpdateCompiler.returning_fields was set. One update of the Petrel, given an expression and a port, with leith the port Leith:

Ship.objects.filter(pk=1).update(tonnage=F('tonnage') + 10, home=leith)
  Query.chain  ->  a Query of Ship
  Query.chain(klass=UpdateQuery)  ->  an UpdateQuery of Ship
  UpdateQuery.add_update_values({'tonnage': <CombinedExpression: F(tonnage) + Value(10)>, 'home': <Port: Leith>})
    UpdateQuery.add_update_fields([(Ship.tonnage, <CombinedExpression: F(tonnage) + Value(10)>), (Ship.home, <Port: Leith>)])
  SQLUpdateCompiler.execute_sql('row count')
    SQLCompiler.execute_sql(result_type='row count')
      SQLUpdateCompiler.as_sql
        SQLUpdateCompiler.pre_sql_setup
          Query.count_active_tables  ->  1
        -> ('UPDATE "fleet_ship" SET "tonnage" = ("fleet_ship"."tonnage" + %s), "home_id" = %s WHERE "fleet_ship"."id" = %s', (10, 2, 1))
      SQL UPDATE "fleet_ship" SET "tonnage" = ("fleet_ship"."tonnage" + %s), "home_id" = %s WHERE "fleet_ship"."id" = %s with params (10, 2, 1)
      -> 1
    UpdateQuery.get_related_updates  ->  []

The first Query.chain is the filter’s clone; the second makes the UpdateQuery. add_update_values handed both pairs to add_update_fields, the expression resolved there. The update compiler’s execute_sql ran the base’s, as_sql had pre_sql_setup count one active table and come straight back, and the statement set the tonnage from its own column, the home from the port’s key, under the filter’s condition. What update accepts and refuses as a value:

Sailor.objects.filter(pk=1).update(name=petrel)  ->  raises TypeError: Tried to update field crew.Sailor.name with a model instance, <Ship: Petrel>. Use a value compatible with CharField.
Ship.objects.filter(pk=1).update(tonnage=Count('crew'))  ->  raises FieldError: Joined field references are not permitted in this query
Ship.objects.filter(pk=1).update(tonnage=Window(RowNumber()))  ->  raises FieldError: Window expressions are not allowed in this query (tonnage=<Window: RowNumber() OVER ()>).
Ship.objects.filter(pk=1).update(crew=1)  ->  raises FieldError: Cannot update model field <ManyToOneRel: crew.sailor> (only concrete fields are permitted).
...
Manifest.objects.filter(serial='M-0001').update(kilos=1)  ->  0
Manifest.objects.filter(serial='M-0001').update(crates=41), Manifest.objects.get(serial='M-0001').kilos  ->  (1, 1025)
...
Berth.objects.filter(number=1).update(pk=(1, 9))  ->  raises FieldError: Composite primary key fields must be updated individually.
Ship.objects.filter(pk=1).update(tonnage=F('home__id'))  ->  1
  SQL UPDATE "fleet_ship" SET "tonnage" = "fleet_ship"."home_id" WHERE "fleet_ship"."id" = %s with params (1,)

An instance for a field that is no relation, here a CharField, is the TypeError of as_sql; the count is refused for its join, as it was in the insert; the window by as_sql; the reverse relation "crew" by add_update_values, as a field that is not concrete. An update of the generated column alone leaves no values, so nothing was sent and the answer was zero, and the column follows the crates it is computed from. The composite key as a whole is refused, and F("home__id") is allowed with no join: the name reaches the key column the ship’s own table holds (From a name to a column: names_to_path, setup_joins and build_lookup).

pre_sql_setup: the joins of the filter moved aside

SQLUpdateCompiler.pre_sql_setup is where a filter that joined other tables is turned into a condition on the table’s own key, the docstring’s “format required for (portable) SQL updates”, and where, for an update that will be several statements, the keys are fetched first so that, as the docstring says, they do not change as a result of the progressive updates. It saves the reference counts, makes sure the base table is in the query, and asks Query.count_active_tables how many aliases are referred to. With one, and no related updates, it returns, and as_sql writes the query as it stands.

Otherwise one decision remains: whether the keys must be fetched by a statement of their own before the update, which is so where there are related updates, the docstring’s several statements, or where more than one table was active and the backend lacks update_can_self_select. Either way the update query is chained into a plain Query, Query.chain(klass=Query), which keeps the joins and the where tree and is stripped of what a select for keys does not want: select_related off, the ordering cleared by force, the extra selects and the selection emptied. It is given the primary key’s name to select, which serves an ancestor too where every link on the path to it is a primary key, as the "sailor_ptr" of an Officer is; for an ancestor reached otherwise, which the comment calls one outside the primary key chain of a multi-table tree, the ancestor’s own key is selected as well. The base pre_sql_setup then runs, and the update query’s own where tree is cleared, Query.clear_where. Where the keys must be fetched first, the plain query is run for MULTI and its rows gathered: the keys, and under each ancestor its keys, which become UpdateQuery.related_ids; the update query gets a "pk__in" filter of the list. Otherwise, the comment’s fast path, the plain query itself becomes the value of "pk__in": the filter resolves it as a subquery, Query.resolve_expression moving its aliases to a prefix of its own, and the "in" lookup then selects its key (Subqueries: Subquery, Exists, OuterRef and a query inside another). The reference counts are put back last. BaseDatabaseFeatures.update_can_self_select is true in the base class, and MySQL’s features class answers true for MariaDB alone, the comment giving MySQL as the backend that cannot select from the table it updates.

Sailor.objects.filter(ship__name='Petrel').update(name=Upper('name'))
  UpdateQuery.add_update_values({'name': Upper(F(name))})
    UpdateQuery.add_update_fields([(Sailor.name, Upper(F(name)))])
  SQLUpdateCompiler.execute_sql('row count')
    SQLCompiler.execute_sql(result_type='row count')
      SQLUpdateCompiler.as_sql
        SQLUpdateCompiler.pre_sql_setup
          Query.count_active_tables  ->  2
          Query.chain(klass=Query)  ->  a Query of Sailor
          Query.add_fields(field_names=['id'])
          Query.clear_where
          Query.add_filter('pk__in', a Query of Sailor)
            Query.add_fields(field_names=['pk'])
          Query.reset_refcounts({'crew_sailor': 2, 'fleet_ship': 1})
        Query.reset_refcounts({'U0': 5, 'U1': 1})
        -> ('UPDATE "crew_sailor" SET "name" = UPPER("crew_sailor"."name") WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" INNER JOIN "fleet_ship" "U1" ON ("U0"."ship_id" = "U1"."id") WHERE "U1"."name" = %s)', ('Petrel',))
      SQL UPDATE "crew_sailor" SET "name" = UPPER("crew_sailor"."name") WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" INNER JOIN "fleet_ship" "U1" ON ("U0"."ship_id" = "U1"."id") WHERE "U1"."name" = %s) with params ('Petrel',)

count_active_tables found two, the sailors and the ships. The plain query was chained, given the key to select, and became the value of the "pk__in" filter added to the cleared update query, whose add_fields of "pk" is the "in" lookup’s doing; the first reset_refcounts is pre_sql_setup’s, and the second, on the "U" aliases, is the subquery’s own as_sql putting its counts back. The statement updates one table, and the join stands inside the subquery. With update_can_self_select switched off, the other road:

Sailor.objects.filter(ship__name='Petrel').update(signed_on=F('signed_on'))
...
        SQLUpdateCompiler.pre_sql_setup
          Query.count_active_tables  ->  2
          Query.chain(klass=Query)  ->  a Query of Sailor
          Query.add_fields(field_names=['id'])
          Query.clear_where
          SQLCompiler.execute_sql
            Query.reset_refcounts({'crew_sailor': 4, 'fleet_ship': 1})
            SQL SELECT "crew_sailor"."id" FROM "crew_sailor" INNER JOIN "fleet_ship" ON ("crew_sailor"."ship_id" = "fleet_ship"."id") WHERE "fleet_ship"."name" = %s with params ('Petrel',)
            -> [[(1,), (2,)]]
          Query.add_filter('pk__in', [1, 2])
          Query.reset_refcounts({'crew_sailor': 2, 'fleet_ship': 1})
        -> ('UPDATE "crew_sailor" SET "signed_on" = "crew_sailor"."signed_on" WHERE "crew_sailor"."id" IN (%s, %s)', (1, 2))
      SQL UPDATE "crew_sailor" SET "signed_on" = "crew_sailor"."signed_on" WHERE "crew_sailor"."id" IN (%s, %s) with params (1, 2)

The plain query was run first and its rows gathered, and the update names the two keys. A child model takes the same road for the sake of its parent’s table. An Officer is a Sailor with a rank, kept in a table of its own; its name is the parent’s:

Officer.objects.filter(rank='master').update(name='Cora C.', rank='master')
  Query.chain  ->  a Query of Officer
  Query.chain(klass=UpdateQuery)  ->  an UpdateQuery of Officer
  UpdateQuery.add_update_values({'name': 'Cora C.', 'rank': 'master'})
    UpdateQuery.add_related_update(model=Sailor, field=Sailor.name, value='Cora C.')
    UpdateQuery.add_update_fields([(Officer.rank, 'master')])
...
        SQLUpdateCompiler.pre_sql_setup
          Query.count_active_tables  ->  1
          Query.chain(klass=Query)  ->  a Query of Officer
          Query.add_fields(field_names=['sailor_ptr'])
          Query.clear_where
          SQLCompiler.execute_sql
            Query.reset_refcounts({'crew_officer': 3, 'crew_sailor': 0})
            SQL SELECT "crew_officer"."sailor_ptr_id" FROM "crew_officer" WHERE "crew_officer"."rank" = %s with params ('master',)
            -> [[(3,)]]
          Query.add_filter('pk__in', [3])
          Query.reset_refcounts({'crew_officer': 1})
        -> ('UPDATE "crew_officer" SET "rank" = %s WHERE "crew_officer"."sailor_ptr_id" IN (%s)', ('master', 3))
...
    UpdateQuery.get_related_updates
      Query.add_filter('pk__in', [3])
      -> [an UpdateQuery of Sailor]
    SQLUpdateCompiler.execute_sql('row count')
      SQLCompiler.execute_sql(result_type='row count')
        SQLUpdateCompiler.as_sql
          SQLUpdateCompiler.pre_sql_setup
            Query.count_active_tables  ->  1
          -> ('UPDATE "crew_sailor" SET "name" = %s WHERE "crew_sailor"."id" IN (%s)', ('Cora C.', 3))
        SQL UPDATE "crew_sailor" SET "name" = %s WHERE "crew_sailor"."id" IN (%s) with params ('Cora C.', 3)
...
Officer.objects.filter(rank='master').update(name='Cora')
  SQL SELECT "crew_officer"."sailor_ptr_id" FROM "crew_officer" WHERE "crew_officer"."rank" = %s with params ('master',)
  SQL UPDATE "crew_sailor" SET "name" = %s WHERE "crew_sailor"."id" IN (%s) with params ('Cora', 3)

add_update_values sent the name to a related update for Sailor and the rank to the officer’s own values. With a related update to run, pre_sql_setup fetched the keys first, one statement for the officers’ table, and here the link to the parent is the primary key itself, so the one column served both. The officers’ statement ran, then get_related_updates made the update of the sailors’ table, filtered to the key, and the update compiler ran it through a compiler of its own. In the last lines only the parent’s field was given: the officer’s own query had no values and sent no statement, but its pre_sql_setup still fetched the keys, and the parent’s statement ran.

execute_sql and execute_returning_sql

SQLUpdateCompiler.execute_sql runs the main statement through the base execute_sql and takes its row count, None where nothing was sent, then runs each related update through a compiler of that query’s. The docstring says what it returns: the rows affected by the primary update, which is the first non-empty statement that was executed, so that where the main statement was empty and a related one touched rows, that count is the answer. SQLUpdateCompiler.execute_returning_sql is for Model.save, which wants back a generated column that depends on the fields it set, and a field it set to an expression; it raises NotImplementedError where there are related updates, falls back to execute_sql and a list of empty tuples, one a row, where no fields are to come back or the backend lacks can_return_rows_from_update, and otherwise sets returning_fields, runs the one statement on a cursor, fetches the rows with fetch_returned_rows and applies the converters. The base class has the feature false; SQLite and PostgreSQL set it true, and Oracle’s connection sets it from its "use_returning_into" option, true unless the setting says otherwise. Here compiler is the compiler of an UpdateQuery made by hand over the manifest, filtered to its serial and given forty crates:

compiler.execute_returning_sql([Manifest._meta.get_field('kilos')])
  SQLUpdateCompiler.execute_returning_sql([Manifest.kilos])
    UpdateQuery.get_related_updates  ->  []
    SQLUpdateCompiler.as_sql
      SQLUpdateCompiler.pre_sql_setup
        Query.count_active_tables  ->  1
      -> ('UPDATE "office_manifest" SET "crates" = %s WHERE "office_manifest"."serial" = %s RETURNING "office_manifest"."kilos"', (40, 'M-0001'))
    SQL UPDATE "office_manifest" SET "crates" = %s WHERE "office_manifest"."serial" = %s RETURNING "office_manifest"."kilos" with params (40, 'M-0001')
    -> [(1000,)]
connection.features.can_return_rows_from_update  ->  True

The delete: DeleteQuery and its compiler

DeleteQuery adds two methods to Query and nothing to hold. DeleteQuery.delete_batch takes a list of keys and runs a statement for each chunk of GET_ITERATOR_CHUNK_SIZE of them: for each chunk it clears the where tree, adds an "in" filter on the key’s attname, and calls DeleteQuery.do_query, which narrows the alias map to the one table, sets the where tree, and runs the compiler for ROW_COUNT; the counts are summed. That is the Collector’s way of deleting the instances it holds, model by model, and its short way for a single instance with no dependents. For the rows it never fetched it calls QuerySet._raw_delete, which clones the queryset’s query, sets the clone’s class to DeleteQuery, and runs the compiler for ROW_COUNT.

SQLDeleteCompiler.as_sql has one question to settle: whether the delete can be written against the table alone. SQLDeleteCompiler.single_alias is whether one alias of the query is still referred to, the base table at least: Query.alias_refcount counts, for each alias, the conditions and columns that use it, and a join a condition was built on and then dropped is in the map with a count of zero (Tables and joins: the alias map and join promotion). SQLDeleteCompiler.contains_self_reference_subquery is whether any annotation or any child of the where tree holds a query of the model being deleted from, which SQLDeleteCompiler._expr_refs_base_model looks for through the source expressions. With a single alias, and either the backend’s delete_can_self_reference_subquery or no such subquery, SQLDeleteCompiler._as_sql writes the query as it is: "DELETE FROM" the base table, and the where tree compiled, with no "WHERE" where that raises FullResultSet. Otherwise the joins go into a subquery on the key, as in an update: the query is cloned and made a plain Query, its selection cleared and set to the key column of the base table; a new Query of the model gets it as the value of "pk__in", and _as_sql writes that. Where the backend lacks update_can_self_select, the inner query is compiled first and wrapped in a RawSQL as SELECT * FROM (...) subquery, to force, the comment says, its materialization on MySQL. BaseDatabaseFeatures.delete_can_self_reference_subquery is true in the base class and false for MySQL. Where the where tree raises EmptyResultSet, the base execute_sql sends nothing and answers None.

Port.objects.filter(name__in=['Oban', 'Mull', 'Skagen'])._raw_delete('default')
  SQLCompiler.execute_sql(result_type='row count')
    SQLDeleteCompiler.as_sql
      SQLDeleteCompiler._as_sql(a DeleteQuery of Port)  ->  ('DELETE FROM "fleet_port" WHERE "fleet_port"."name" IN (%s, %s, %s)', ('Oban', 'Mull', 'Skagen'))
      -> ('DELETE FROM "fleet_port" WHERE "fleet_port"."name" IN (%s, %s, %s)', ('Oban', 'Mull', 'Skagen'))
    SQL DELETE FROM "fleet_port" WHERE "fleet_port"."name" IN (%s, %s, %s) with params ('Oban', 'Mull', 'Skagen')
    -> 3
...
Sailor.objects.filter(ship__name='Nobody')._raw_delete('default')
  SQLCompiler.execute_sql(result_type='row count')
    SQLDeleteCompiler.as_sql
      Query.clear_select_clause
      Query.add_filter('pk__in', a Query of Sailor)
        Query.clear_select_clause
      SQLDeleteCompiler._as_sql(a Query of Sailor)  ->  ('DELETE FROM "crew_sailor" WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" INNER JOIN "fleet_ship" "U1" ON ("U0"."ship_id" = "U1"."id") WHERE "U1"."name" = %s)', ('Nobody',))
      -> ('DELETE FROM "crew_sailor" WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" INNER JOIN "fleet_ship" "U1" ON ("U0"."ship_id" = "U1"."id") WHERE "U1"."name" = %s)', ('Nobody',))
    SQL DELETE FROM "crew_sailor" WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" INNER JOIN "fleet_ship" "U1" ON ("U0"."ship_id" = "U1"."id") WHERE "U1"."name" = %s) with params ('Nobody',)
    -> 0
...
Sailor.objects.filter(pk__in=Sailor.objects.filter(name='Nobody'))._raw_delete('default')
  Query.clear_select_clause
  SQLCompiler.execute_sql(result_type='row count')
    SQLDeleteCompiler.as_sql
      SQLDeleteCompiler._as_sql(a DeleteQuery of Sailor)  ->  ('DELETE FROM "crew_sailor" WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" WHERE "U0"."name" = %s)', ('Nobody',))
      -> ('DELETE FROM "crew_sailor" WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" WHERE "U0"."name" = %s)', ('Nobody',))
    SQL DELETE FROM "crew_sailor" WHERE "crew_sailor"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" WHERE "U0"."name" = %s) with params ('Nobody',)
    -> 0

The first delete, on the table itself, was written as it was. The second joined the ships, so the query was cloned, its selection cleared, and put under "pk__in" of a new query, which _as_sql wrote: the first clear_select_clause is as_sql’s own, on the clone, and the one under add_filter is the "in" lookup’s, which then selects the key; the join is inside the subquery. The third holds a query of the sailors already, as the value of a filter: SQLite can refer to the table being deleted from in a subquery, so the query was written as it was. With delete_can_self_reference_subquery switched off:

Sailor.objects.filter(pk__in=Sailor.objects.filter(name='Nobody'))._raw_delete('default')
  Query.clear_select_clause
  SQLCompiler.execute_sql(result_type='row count')
    SQLDeleteCompiler.as_sql
      Query.clear_select_clause
      Query.add_filter('pk__in', a Query of Sailor)
        Query.clear_select_clause
      SQLDeleteCompiler._as_sql(a Query of Sailor)  ->  ('DELETE FROM "crew_sailor" WHERE "crew_sailor"."id" IN (SELECT "V0"."id" FROM "crew_sailor" "V0" WHERE "V0"."id" IN (SELECT "U0"."id" FROM "crew_sailor" "U0" WHERE "U0"."name" = %s))', ('Nobody',))

With the feature off, the query’s reference to its own table sent it down the second road: the sailors’ table appears under a second select, aliased "V0", with the original subquery inside it. The batches of delete_batch, with the chunk lowered to five for the block, over keys that no logbook has:

DeleteQuery(Logbook).delete_batch(list(range(100, 112)), 'default')
  DeleteQuery.delete_batch(pk_list=[100, 101, and 10 more], using='default')
    Query.clear_where
    Query.add_filter('id__in', [100, 101, 102, 103, 104])
    DeleteQuery.do_query(table='office_logbook', where=a WhereNode of 5 keys, using='default')
      SQLCompiler.execute_sql(result_type='row count')
        SQLDeleteCompiler.as_sql
          SQLDeleteCompiler._as_sql(a DeleteQuery of Logbook)  ->  ('DELETE FROM "office_logbook" WHERE "office_logbook"."id" IN (%s, %s, %s, %s, %s)', (100, 101, 102, 103, 104))
          -> ('DELETE FROM "office_logbook" WHERE "office_logbook"."id" IN (%s, %s, %s, %s, %s)', (100, 101, 102, 103, 104))
        SQL DELETE FROM "office_logbook" WHERE "office_logbook"."id" IN (%s, %s, %s, %s, %s) with params (100, 101, 102, 103, 104)
...
    Query.add_filter('id__in', [110, 111])
    DeleteQuery.do_query(table='office_logbook', where=a WhereNode of 2 keys, using='default')
      SQLCompiler.execute_sql(result_type='row count')
        SQLDeleteCompiler.as_sql
          SQLDeleteCompiler._as_sql(a DeleteQuery of Logbook)  ->  ('DELETE FROM "office_logbook" WHERE "office_logbook"."id" IN (%s, %s)', (110, 111))
          -> ('DELETE FROM "office_logbook" WHERE "office_logbook"."id" IN (%s, %s)', (110, 111))
        SQL DELETE FROM "office_logbook" WHERE "office_logbook"."id" IN (%s, %s) with params (110, 111)

The two ends of the where tree:

A delete that can match nothing sends nothing, and one that matches everything has no WHERE
    Sailor.objects.filter(pk__in=[])._raw_delete('default')  ->  None
    LogEntry.objects.exclude(pk__in=[])._raw_delete('default')  ->  0
      SQL DELETE FROM "office_logentry"

The first delete can match nothing, by its comparison with an empty list, and no statement was sent; the second can match everything, and the statement has no "WHERE".

The constants the query and the compilers share

django/db/models/sql/constants.py holds the few constants the query and the compilers share: the five result types of SQLCompiler.execute_sql, which The compiler: as_sql, execute_sql and results_iter has, and INNER and LOUTER, the two join types of Tables and joins: the alias map and join promotion. The other two are GET_ITERATOR_CHUNK_SIZE, 100, the chunk of keys in delete_batch and update_batch and the number of rows SQLCompiler.execute_sql asks a cursor for at a time, and ORDER_DIR, which maps "ASC" and "DESC" each to the pair of itself and its opposite, for get_order_dir to read a leading "-" against.