Add Appender API - #661

Add an appender to the Python client

Python has had no appender. This uses the same pieces as the stable C++ API's Appender: a buffer of rows and one statement that reads it.

There are two ways to create one. The table form writes the statement for you:

with con.appender("orders") as app:                     # INSERT into every writable column
    app.append_row(1, "widget", 3)
    app.append_rows(rows)

con.appender("orders", columns=["sku", "qty"])          # other columns take their DEFAULT, per row
con.appender("orders", action="upsert")                 # INSERT OR REPLACE on the table's unique key
con.appender("orders", action="update", key=["id"])     # sets every other appended column
con.appender("orders", action="delete", key=["id"])     # appends only the key

The query form runs your own INSERT, UPDATE, DELETE or MERGE INTO. An optional source query can reshape the rows first:

con.query_appender(
    "INSERT INTO orders (product_id, qty) FROM appender_data",
    {"sku": "VARCHAR", "qty": "INTEGER"},
    source="SELECT p.id AS product_id, b.qty FROM appender_buffer b JOIN products p USING (sku)",
)

Both forms also exist as module-level functions that take connection=.

How it behaves:

• Rows sit in a buffer the engine manages. A large buffer counts against memory_limit and spills to the temp directory. Appending runs no statement and never takes the connection, so it works while a result is open.
• Each flush runs exactly one statement, WITH appender_buffer AS (...), appender_data AS (<source>) <dml>, over the rows appended since the last flush. It commits on its own or joins the transaction you opened. An empty flush runs nothing.
• Nothing flushes on its own. There is no automatic flush, and an appender garbage collected with rows still buffered drops them with a ResourceWarning. A with block flushes on a normal exit and clears on an exception. There is no close().
• The statement is parsed once and bound at creation, without running it. A typo, a wrong column count, a statement that isn't one of those four, a RETURNING clause or a parameter all fail at creation and leave your transaction usable.
• Values convert exactly as executemany parameters do, so JSON is validated and timestamps follow the session time zone. test_appender_values.py checks this against executemany for about 100 Python values into 48 column types, errors included.
• A flush that was refused, because a result was open or another call was running, keeps its rows for a retry. So does an interrupted flush. Any other failure drops the rows and raises.
• One caller at a time. A second thread, or a callback running inside a flush, is refused instead of waiting.
• DataFrames named in source or dml are looked up once, at creation, and reused at every flush. Tables resolve at each flush, as in any statement.
• A plain insert keeps append order under preserve_insertion_order and still runs in parallel. Upserts don't keep append order, because the engine runs them as a MERGE.
• Within one flush, a repeated key is up to the statement: MERGE raises, and OR REPLACE keeps one row.

A few engine quirks are handled here so users don't see them:

• The engine's own appender adds the buffer's WITH clause after the statement's, so a source query couldn't read it. The appender builds that list itself.
• INSERT OR REPLACE into a table called excluded fails to bind, so the target table is aliased.
• Under 2.0's transaction policy, a refused table lookup aborts an open transaction, so the appender catches that refusal inside the engine's transaction function.

What it doesn't do:

• There's no DataFrame or Arrow input. con.append(df) and INSERT ... SELECT * FROM df already cover that without a copy.
• Upsert uses the table's single unique constraint. There is no upsert on a named key yet.
• Values are converted one at a time in Python, so for data that's already in a frame, con.append(df) stays the faster path.

tests/fast/api/test_appender.py has one class per area: the table form, the actions, the query form, statement kinds, checks at creation, rows, flushes, flush failures, the buffer, garbage collection, the with block, threads, and closed connections. ASan, UBSan and LSan runs are clean.