[sqlalchemy] could not assemble any primary key columns for mapped table '...'

[Yang Zhang]

How do I create an ORM type with no primary key columns? For some
reason I'm getting:

sqlalchemy.exc.ArgumentError: Mapper
Mapper|ActorActivity|actor_activities could not assemble any primary
key columns for mapped table 'actor_activities'

for:

class ActorActivity(Base):
__tablename__ = 'actor_activities'
actor_id = Column(UUID, ForeignKey(Actor.id), nullable = False)
t = Column(SmallInteger, nullable = False)
ts = Column(TIMESTAMP, nullable = False)
a = Column(UUID, ForeignKey(A.id))
b = Column(UUID, ForeignKey(B.id))
n = Column(SmallInteger)
x = Column(SmallInteger)

Thanks for any hints.
--
Yang Zhang
http://yz.mit.edu/

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

On May 20, 2010, at 5:51 PM, Yang Zhang wrote:

> How do I create an ORM type with no primary key columns? For some
> reason I'm getting:
>
> sqlalchemy.exc.ArgumentError: Mapper
> Mapper|ActorActivity|actor_activities could not assemble any primary
> key columns for mapped table 'actor_activities'
>
> for:
>
> class ActorActivity(Base):
> __tablename__ = 'actor_activities'
> actor_id = Column(UUID, ForeignKey(Actor.id), nullable = False)
> t = Column(SmallInteger, nullable = False)
> ts = Column(TIMESTAMP, nullable = False)
> a = Column(UUID, ForeignKey(A.id))
> b = Column(UUID, ForeignKey(B.id))
> n = Column(SmallInteger)
> x = Column(SmallInteger)
>
> Thanks for any hints.

the orm has to be given the list of columns that serve as the primary key even if the table doesn't actually have any. FAQ entry here:
http://www.sqlalchemy.org/trac/wiki/FAQ#IhaveaschemawheremytabledoesnthaveaprimarykeycanSAsORMhandleit

although here you can just put "primary_key=True" on those columns you'd like to consider as PK cols, since you are using declarative and everything is in one place anyway.

if the issue is, you want no PK at all, even a python-only one, that's not really possible. The ORM needs a way to locate the row for your instance in the DB in order to issue updates/deletes etc.

[Yang Zhang]

On Thu, May 20, 2010 at 8:09 PM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>
> On May 20, 2010, at 5:51 PM, Yang Zhang wrote:
>
>> How do I create an ORM type with no primary key columns? For some
>> reason I'm getting:
>>
>>  sqlalchemy.exc.ArgumentError: Mapper
>> Mapper|ActorActivity|actor_activities could not assemble any primary
>> key columns for mapped table 'actor_activities'
>>
>> for:
>>
>> class ActorActivity(Base):
>>  __tablename__ = 'actor_activities'
>>  actor_id     =  Column(UUID,    ForeignKey(Actor.id),      nullable  =  False)
>>  t        =  Column(SmallInteger,  nullable = False)
>>  ts    =  Column(TIMESTAMP,     nullable = False)
>>  a  =  Column(UUID,    ForeignKey(A.id))
>>  b   =  Column(UUID,    ForeignKey(B.id))
>>  n   =  Column(SmallInteger)
>>  x  =  Column(SmallInteger)
>>
>> Thanks for any hints.
>
>
> the orm has to be given the list of columns that serve as the primary key even if the table doesn't actually have any.  FAQ entry here:
> http://www.sqlalchemy.org/trac/wiki/FAQ#IhaveaschemawheremytabledoesnthaveaprimarykeycanSAsORMhandleit

I did see that, and the only thing I'll say here is: I know what I'm doing :)

>
> although here you can just put "primary_key=True" on those columns you'd like to consider as PK cols, since you are using declarative and everything is in one place anyway.
>
> if the issue is, you want no PK at all, even a python-only one, that's not really possible.   The ORM needs a way to locate the row for your instance in the DB in order to issue updates/deletes etc.

Thanks for the anwser.

It would be nice if this restriction could be lifted if explicitly
requested somehow (__use_pk__ = False, and have those operations raise
run-time exceptions if attempted). (As for why I'm using the ORM, it's
basically because it affords many relatively minor benefits such as
packaging up the tuple in its own object, a cleaner declaration syntax
than Table(...), no need to use table.c.blah, custom constructors and
methods, etc.)

[Michael Bayer]

On May 21, 2010, at 2:11 AM, Yang Zhang wrote:

>
> It would be nice if this restriction could be lifted if explicitly
> requested somehow (__use_pk__ = False, and have those operations raise
> run-time exceptions if attempted). (As for why I'm using the ORM, it's
> basically because it affords many relatively minor benefits such as
> packaging up the tuple in its own object, a cleaner declaration syntax
> than Table(...), no need to use table.c.blah, custom constructors and
> methods, etc.)

if you'd like to tell me what UPDATE statement should run during this operation:


my_object_with_no_pk = Session.query(MyClassWithNoPk).filter(...).one()

my_object_with_no_pk.some_attribute = 'new value'

Session.commit()

I'm all ears.

[Michael Bayer]

On May 21, 2010, at 9:12 AM, Michael Bayer wrote:

>
> On May 21, 2010, at 2:11 AM, Yang Zhang wrote:
>
>>
>> It would be nice if this restriction could be lifted if explicitly
>> requested somehow (__use_pk__ = False, and have those operations raise
>> run-time exceptions if attempted). (As for why I'm using the ORM, it's
>> basically because it affords many relatively minor benefits such as
>> packaging up the tuple in its own object, a cleaner declaration syntax
>> than Table(...), no need to use table.c.blah, custom constructors and
>> methods, etc.)
>
> if you'd like to tell me what UPDATE statement should run during this operation:
>
>
> my_object_with_no_pk = Session.query(MyClassWithNoPk).filter(...).one()
>
> my_object_with_no_pk.some_attribute = 'new value'
>
> Session.commit()
>
> I'm all ears.

additionally, if you'd argue that such an operation would simply be not allowed, it still brings us no closer to determining how the identity map would function here. The ORM is archtected around an identity map model. The identity map is not just a "cache", its real purpose is so that objects associated with a session are unique relative to the row they represent in the database.

I think here you really mean to be using the SQL expression language directly, since if your row has no primary key you really won't get any benefit from the ORM in any case.

[Michael Bayer]

On May 21, 2010, at 9:21 AM, Michael Bayer wrote:

>
> On May 21, 2010, at 9:12 AM, Michael Bayer wrote:
>
>>
>> On May 21, 2010, at 2:11 AM, Yang Zhang wrote:
>>
>>>
>>> It would be nice if this restriction could be lifted if explicitly
>>> requested somehow (__use_pk__ = False, and have those operations raise
>>> run-time exceptions if attempted). (As for why I'm using the ORM, it's
>>> basically because it affords many relatively minor benefits such as
>>> packaging up the tuple in its own object, a cleaner declaration syntax
>>> than Table(...), no need to use table.c.blah, custom constructors and
>>> methods, etc.)
>>
>> if you'd like to tell me what UPDATE statement should run during this operation:
>>
>>
>> my_object_with_no_pk = Session.query(MyClassWithNoPk).filter(...).one()
>>
>> my_object_with_no_pk.some_attribute = 'new value'
>>
>> Session.commit()
>>
>> I'm all ears.
>
> additionally, if you'd argue that such an operation would simply be not allowed, it still brings us no closer to determining how the identity map would function here. The ORM is archtected around an identity map model. The identity map is not just a "cache", its real purpose is so that objects associated with a session are unique relative to the row they represent in the database.
>
> I think here you really mean to be using the SQL expression language directly, since if your row has no primary key you really won't get any benefit from the ORM in any case.

Also the other features you mention like using objects to represent rows are pretty trivial to re-implement. The declarative system you like is all part of ext.declarative and follows a common metaclass model that I use for all kinds of non-ORM tasks. Table.c.blah can be avoided by just passing "c" around, but even better just implement them as descriptors on your classes - just follow the example of sqlalchemy.orm.attributes.QueryableAttribute, subclass expression.ColumnOperators and implement __clause_element__(), operate(), and reverse_operate(). The rows that come back from a result do allow attribute acccess, like row.bar, but you'd wrap around connection.execute() to return your instrumented objects in any case.

It was always intended that users would create their own "micro-ORMs" for those cases where the decidedly opinionated approach of the SQLA ORM is not appropriate. This is one reason the SQL expression system is so prominent.