Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 37 ยท Core and Expressions

INSERT, UPDATE and DELETE in Core

Change data with SQLAlchemy Core: insert one or many rows, read primary keys, update with expressions, delete, use RETURNING, and write an upsert for SQLite.

Reading data is only half of the job. This lesson covers the three statements that change it: insert(), update() and delete(). They are built as Python objects, run with conn.execute(), and, like everything else in 2.0, take bound parameters so values are always safe.

All three return a CursorResult with information about what happened: how many rows changed, which keys were generated, and (on databases that support it) the rows themselves through RETURNING.

INSERT

You can pass values with .values(), or provide a dictionary (or list of dictionaries) as the execution parameters.

Python Inserting one row and many rows

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

inserted_primary_key is available after a single-row insert. For many rows use RETURNING (below). The two multi-row forms differ: passing a list as execution parameters is executemany, which most drivers optimize; .values([...]) builds one statement with a multi-row VALUES clause.

UPDATE

update() takes a WHERE clause and the new values. Values can be Python values or SQL expressions that refer to the current row, which makes atomic changes like "add 1" possible without reading first.

Python UPDATE with expressions

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

An update() or delete() without .where() affects every row in the table. Check statements before running them, and consider printing them first (print(stmt)) when you build them dynamically.

DELETE

delete() works the same way: a table and an optional WHERE.

Python DELETE and rowcount

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

RETURNING

Several databases (PostgreSQL, SQLite 3.35+, MariaDB, Oracle, SQL Server) can send changed rows back in the same round trip. In SQLAlchemy you add .returning(...) to any DML statement.

Python insert, update and delete with returning()

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

RETURNING saves a second query for generated ids and server defaults. Check your target database before relying on it: MySQL (not MariaDB) has no RETURNING.

Upsert: insert or update

"Insert this row, or update it if the key already exists" is called an upsert. The syntax differs per database, so SQLAlchemy provides it in each dialect module. For SQLite and PostgreSQL it is on_conflict_do_update().

Python SQLite upsert with on_conflict_do_update

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

There is also on_conflict_do_nothing() to skip duplicates. For PostgreSQL import insert from sqlalchemy.dialects.postgresql instead (the read-only equivalent: same method names). MySQL uses on_duplicate_key_update().

insert from select

You can fill a table straight from a query, entirely inside the database: insert(t).from_select(["col_a", "col_b"], select(...)). This is the fastest way to copy or summarize data, as no row travels through Python.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does result.inserted_primary_key give you after a single-row insert()?

    It is a tuple of the primary key values of the new row.
  2. 2. What happens with update(item).values(qty=0) and no where()?

    Without a WHERE clause the statement applies to all rows.
  3. 3. Which is the idiomatic way to atomically add 5 to a column?

    The expression is evaluated by the database, so it is atomic and needs no extra read.
  4. 4. Which method makes an insert update the row if the key exists (SQLite/PostgreSQL)?

    on_conflict_do_update() is available from the dialect-specific insert constructs.

Summary

  • insert(), update() and delete() are built as objects and run with conn.execute(); values are always bound.
  • Lists of dictionaries run as executemany; inserted_primary_key, rowcount and returning() report what happened.
  • Use SQL expressions (t.c.qty + 1) for atomic changes, and always check the WHERE clause.
  • Upserts come from the dialect-specific insert (on_conflict_do_update).

Next: Building SELECT Queries with where()