DuckDB variables lost between execute calls due to cursor-per-statement pattern

[cofin]

Description

DuckDB variables set with SET VARIABLE are lost between execute() calls because SQLSpec's DuckDB adapter creates a new cursor for each statement execution.

Reproduction

from sqlspec.adapters.duckdb import DuckDBConfig
from sqlspec import SQLSpec

config = DuckDBConfig(connection_config={"database": ":memory:"})
db = SQLSpec()
db.add_config(config)

with db.provide_session(config) as driver:
    # Set variable
    driver.execute("SET VARIABLE my_var = 'hello'")
    
    # Try to retrieve - returns None!
    result = driver.select_one("SELECT getvariable('my_var') as val")
    print(result)  # {'val': None}

Root Cause

DuckDBCursor class (driver.py lines 43-58) creates a new cursor for each operation:

class DuckDBCursor:
    def __enter__(self) -> Any:
        self.cursor = self.connection.cursor()  # New cursor each time
        return self.cursor

    def __exit__(self, *_: Any) -> None:
        if self.cursor is not None:
            self.cursor.close()  # Cursor closed, variable lost

In DuckDB, SET VARIABLE state is cursor-scoped, not connection-scoped when using explicit cursors. When cursor A (where SET ran) closes, cursor B cannot see the variable.

Evidence

Raw DuckDB test confirms cursor-scoping:

import duckdb
conn = duckdb.connect(":memory:")

# Test 1: Same cursor - works
cursor1 = conn.cursor()
cursor1.execute("SET VARIABLE x = 'test'")
print(cursor1.execute("SELECT getvariable('x')").fetchone())  # ('test',)

# Test 2: Different cursor - fails  
cursor2 = conn.cursor()
cursor2.execute("SET VARIABLE x = 'test'")
cursor2.close()
cursor3 = conn.cursor()
print(cursor3.execute("SELECT getvariable('x')").fetchone())  # (None,)

# Test 3: Connection-level execute - works
conn.execute("SET VARIABLE y = 'test'")
print(conn.execute("SELECT getvariable('y')").fetchone())  # ('test',)

Impact

This breaks any ETL workflow that relies on DuckDB variables to pass context between SQL statements, such as:

• Workspace isolation (workspace_collection_id for multi-tenant data)
• Session state for complex transforms
• Any use of getvariable() in SQL

Suggested Fix

Options:

1. Reuse cursor across calls in a session - Keep cursor alive for the session duration
2. Use connection-level execute - connection.execute() instead of cursor.execute() preserves variables
3. Detect SET VARIABLE and use connection.execute - Special handling for state-setting commands

Version

sqlspec: Latest (installed via git)
duckdb: 1.x