[note: this is a repost from Stack Overflow:
http://stackoverflow.com/questions/24807961/assigning-inlined-query-result-to-column-in-sqlalchemy-using-sqlite.
If this violates group policy please let me know]
I want to generate a primary key column from a query result in SQLAlchemy.
And yes, I have a reason I'm not using autoincrement.
I have a table:
-- Describe THERAPY
CREATE TABLE foo (
pk INTEGER NOT NULL PRIMARY KEY,
bar INTEGER
)
and the SQLAlchemy ORM model:
class Foo(Base):
__tablename__ = 'foo'
pk = Column(Integer, primary_key=True)
bar = Column(Integer)
In SQLite this is no problem, I can do simple insert with a subquery:
insert into foo (pk) values ((select coalesce(max(foo.pk) + 1, 1) from foo
));
I cannot do this in SQLAlchemy:
foo = Foo()
foo.pk = sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)]).
as_scalar()
session.add(foo)
session.commit()
results in the stacktrace:
InvalidRequestError: Instance <Foo at 0x224f710> cannot be refreshed - it's
not persistent and does not contain a full primary key.
Assigning a query to foo.bar works perfectly fine, with intended results
foo = Foo()
foo.pk = 1
foo.bar = sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)]).
as_scalar()
session.add(foo)
session.commit()
# outputs (1, 1)
print (foo.bar, foo.pk)
--
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.
