[Sqlalchemy-tickets] Issue #3133: support DB's that magically get SQL expressions to come out in cursor.lastrowid (zzzeek/sqlalchemy)

[Mike B. <iss...@bi...>]

New issue 3133: support DB's that magically get SQL expressions to come out in cursor.lastrowid
https://bitbucket.org/zzzeek/sqlalchemy/issue/3133/support-dbs-that-magically-get-sql

Mike Bayer:

key to this is that we should never put a "primary key" column in "postfetch".  If we define "postfetch" as, "columns we will fetch by selecting by PK", then that's how we should go.

patch:


```
#!diff

diff --git a/lib/sqlalchemy/orm/persistence.py b/lib/sqlalchemy/orm/persistence.py
index 6669efc..410bf33 100644
--- a/lib/sqlalchemy/orm/persistence.py
+++ b/lib/sqlalchemy/orm/persistence.py
@@ -595,13 +595,19 @@ def _emit_insert_statements(base_mapper, uowtransaction,
                                         execute(statement, params)
 
                 primary_key = result.context.inserted_primary_key
-
                 if primary_key is not None:
                     # set primary key attributes
                     for pk, col in zip(primary_key,
                                     mapper._pks_by_table[table]):
                         prop = mapper_rec._columntoproperty[col]
-                        if state_dict.get(prop.key) is None:
+                        existing = state_dict.get(prop.key)
+                        if existing is None or \
+                            (
+                                # inline executed statement somehow made
+                                # it into last inserted rec.  OK !
+                                pk is not None and
+                                isinstance(existing, sql.ClauseElement)
+                            ):
                             # TODO: would rather say:
                             #state_dict[prop.key] = pk
                             mapper_rec._set_state_attr_by_column(
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 384cf27..6cabe50 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -2080,7 +2080,8 @@ class SQLCompiler(Compiled):
                         self.returning.append(c)
                         value = self.process(value.self_group(), **kw)
                     else:
-                        self.postfetch.append(c)
+                        if not c.primary_key:
+                            self.postfetch.append(c)
                         value = self.process(value.self_group(), **kw)
                 values.append((c, value))
 

```

SQLite then lets us get at SQL expressions using lastrowid, who knew:


```
#!python

from sqlalchemy import *
from sqlalchemy import sql
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Foo(Base):
    __tablename__ = 'foo'
    pk = Column(Integer, primary_key=True)
    bar = Column(Integer)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

session = Session(e)

foo = Foo()
foo.pk = sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)])
session.add(foo)
session.commit()

```



Responsible: zzzeek