On Jul 18, 2014, at 4:33 AM, Johannes Klug <[email protected]> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
