CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
David Wolever
David Wolever

Reputation: 154664

SQLAlchemy raises TypeError when an INSERT returns no rows?

I've got an insert statement like this:

insert = MyTable.insert()
row = {"foo": 42}
result = cxn.execute(insert, row)

But a Postgres trigger is redirecting inserts from mytable to a new table, so the insert statement doesn't actually return anything:

# INSERT INTO mytable VALUES (DEFAULT, 'stuff') RETURNING LASTVAL();
 lastval 
---------
(0 rows)

This causes SQLAlchemy (0.8b2) to raise a TypeError: 'NoneType' is not subscriptable. It looks like this is happening when the DefaultExecutionContext tries to fetch the primary key of the inserted row.

Is this a bug that should be reported? And is there any way I can work around this, telling SQLAlchemy not to expect a return value?

The traceback

… snip… 
  File "myfile.py", line 85, in store
    result = cxn.execute(insert, row)
  File ".../sqlalchemy/engine/base.py", line 664, in execute
    params)
  File ".../sqlalchemy/engine/base.py", line 764, in _execute_clauseelement
    compiled_sql, distilled_params
  File ".../sqlalchemy/engine/base.py", line 899, in _execute_context
    context._fetch_implicit_returning(result)
  File ".../sqlalchemy/engine/default.py", line 697, in _fetch_implicit_returning
    ipk.append(row[c])
TypeError: 'NoneType' object is not subscriptable

The Postgres trigger

CREATE OR REPLACE FUNCTION mytable_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO mytable_cur VALUES (NEW.*);
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER mytable_insert_trigger
    BEFORE INSERT ON mytable
    FOR EACH ROW EXECUTE PROCEDURE mytable_insert_trigger();

Upvotes: 2

Views: 796

Answers (2)

zzzeek
zzzeek

Reputation: 75317

yeah, you can fall back to the old system of obtaining the last inserted id by turning off implicit_returning, which you can do for the Table:

Table('sometable', metadata, ... columns ..., implicit_returning=False)

what will happen there is, assuming this is a straight up SERIAL primary key, it will execute "tablename_id_seq" explicitly beforehand and just use that value in the INSERT statement.

Upvotes: 2

Eevee
Eevee

Reputation: 48594

You broke the contract here. Against a Postgres backend, SQLAlchemy will tend to use INSERT ... RETURNING so that it can retrieve e.g. values from generated sequences. You're inserting a row but lying about it.

Based on the documentation, I think you want to use an INSTEAD OF trigger rather than BEFORE, and have it return the NEW row unchanged. Postgres will assume you did whatever inserting is necessary, and merely shuttle the returned row back to the client.

Upvotes: 2

Related Questions