Hopefully I'll be able to add this in 1.0 using the patch illustrated at https://bitbucket.org/zzzeek/sqlalchemy/issue/3133/support-dbs-that-magically-get-sql.
On Jul 18, 2014, at 11:54 AM, Michael Bayer <mike...@zzzcomputing.com> wrote: > > On Jul 18, 2014, at 4:33 AM, Johannes Klug <klug.johan...@gmail.com> wrote: > >> >> results in the stacktrace: >> >> InvalidRequestError: Instance <Foo at 0x224f710> cannot be refreshed - it's >> not persistent and does not contain a full primary key. > > so I hate to be a stickler here but that's not a stack trace, that's an error > message. > > if we reproduce your full script we can see what is going on. > > First, always use echo=True to see what's happening. We can see that the > statement does in fact work as planned: > > INSERT INTO foo (pk, bar) VALUES ((SELECT coalesce(max(foo.pk) + ?, ?) AS > coalesce_1 > > however, the error then happens later on, with this partial trace: > > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/unitofwork.py", line > 389, in finalize_flush_changes > self.session._register_newly_persistent(other) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/session.py", line > 1408, in _register_newly_persistent > instance_key = mapper._identity_key_from_state(state) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line > 2285, in _identity_key_from_state > for col in self.primary_key > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/attributes.py", line > 580, in get > value = callable_(state, passive) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/state.py", line 423, > in __call__ > self.manager.deferred_scalar_loader(self, toload) > File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/loading.py", line > 597, in load_scalar_attributes > "contain a full primary key." % state_str(state)) > sqlalchemy.exc.InvalidRequestError: Instance <Foo at 0x10165d510> cannot be > refreshed - it's not persistent and does not contain a full primary key. > > we can see the SQLAlchemy ORM, like all ORMs, needs to know the primary key > of this newly inserted object - "identity key from state". SQLAlchemy is > failing pretty ungracefully here. This could be improved. > > To understand the full picture here is pretty complicated because there are a > lot of nooks and crannies to the reality here so I will try to bullet them > out: > > 1. Typically, it's not possible to avoid a failure here. SQLite does not > support RETURNING, so the only way we can get at the value when it is done > inline like this is through cursor.lastrowid. Cursor.lastrowid typically > only gives us the value that was generated using the database's sequencing > mechanism. On MySQL, we would have to use an AUTOINCREMENT column; we get a > zero back from lastrowid instead. > > 2. Apparently, based on a short test, recent pysqlite is giving us the actual > value here in cursor.lastrowid. I've never seen a DBAPI do this before and > SQLAlchemy would need additional enhancements to distinguish between > databases where lastrowid only works for auto increment values, and lastrowid > that actually gives you the value no matter where it came from. That's not > available right now. > > 3. If we run this script as is on a database that supports RETURNING, the > whole thing works just fine. Here's Postgresql: > > INSERT INTO foo (pk, bar) VALUES ((SELECT coalesce(max(foo.pk) + %(max_1)s, > %(param_1)s) AS coalesce_1 > FROM foo), %(bar)s) RETURNING foo.pk > > > 4. So for now, for ORM use (which requires the PK, as opposed to Core where > this isn't needed since there is no object to track), to use this pattern on > a lastrowid backend the statement needs to be invoked outside of the INSERT. > We can achieve this using ColumnDefault: > > class Foo(Base): > __tablename__ = 'foo' > pk = Column(Integer, primary_key=True) > bar = Column(Integer) > > Foo.__table__.c.pk.default = > ColumnDefault(sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, > 1)]).as_scalar()) > > Where we'll see: > > SELECT (SELECT coalesce(max(foo.pk) + ?, ?) AS coalesce_1 > FROM foo) AS anon_1 > (1, 1) > INSERT INTO foo (pk, bar) VALUES (?, ?) > (1, None) > > > This will work on any backend. > > Or if the "per instance" system is needed, just run the statement: > > foo = Foo() > foo.pk = session.scalar(sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + > 1, 1)])) > session.add(foo) > session.commit() > > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
