On Aug 4, 2010, at 9:05 PM, jgs9000 wrote:
>
> Michael
>
> thanks very much for your helpful advice - the problem seems to
> actually involve the autoloading of the table in question.
so there's a small bug that is easy to fix, that is ticket #1865 and it is
fixed in r742bd985b4e0, latest tip, so at the very least the code you have
(minus your __init__ method) will work.
But, if you are on Postgresql, and you're using reflection, there is absolutely
no reason to specify image_id and amp explicitly, nor is there a need to
specify the composite foreign key constraint - all of that will be reflected.
Postgresql reflection is very complete and will pull all those details in for
you. Perhaps though you're using reflection just to pull in "extra" columns.
I hardly ever use reflection for non-trivial applications.
The __init__ method of a declarative class is not where you'd put things
related to the configuration of the mapped table. A Python class's __init__
method is called for each instantaition of the object, and in the case of an
ORM a mapped class instantiation corresponds to a row in the mapped table.
You can keep the ForeignKeyConstraint in the __table_args__ now (even though
like I said none of that should be needed), but if you were to use
append_constraint(), you'd do that outside of the class definition, right after
the class has been declared.
>
> The table structure is as follows:
>
> CREATE TABLE wcs (
> image_id INTEGER NOT NULL,
> amp INTEGER NOT NULL,
> ctype1 TEXT,
> (other column defs deleted)
> PRIMARY KEY (image_id, amp),
> FOREIGN KEY (image_id, amp) REFERENCES science_amp
> ON DELETE NO ACTION
> ON UPDATE CASCADE)
>
> and the class definition is as follows:
>
> class Wcs(skymapper_db.db.TableBase):
>
> from sqlalchemy import Table, Column, Integer, String, MetaData,
> ForeignKey, ForeignKeyConstraint
> from sqlalchemy.orm import relationship, backref
>
> __tablename__ = 'wcs'
> __table_args__ = {'autoload':True}
>
> image_id = Column(Integer, primary_key=True)
> amp = Column(Integer, primary_key=True)
>
> def __init__(self):
> from sqlalchemy import ForeignKeyConstraint
> self.__table__.append_constraint(ForeignKeyConstraint(['image_id',
> 'amp'], ['science_amp.image_id', 'science_amp.amp']))
>
> def __repr__(self):
> return "<Wcs(%s, %s)>" % (self.image_id, self.amp)
>
> If I attempt to instantiate the class as defined, I get:
>
> Traceback (most recent call last):
> File "C:\Users\jgs900\Work\skymapper-alchemy\wcs.py", line 13, in
> <module>
> class Wcs(skymapper_db.db.TableBase):
> File "C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py",
> line 1017, in __init__
> _as_declarative(cls, classname, cls.__dict__)
> File "C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py",
> line 926, in _as_declarative
> **table_kw)
> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 209,
> in __new__
> table._init(name, metadata, *args, **kw)
> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 269,
> in _init
> self._init_items(*args)
> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 60,
> in _init_items
> item._set_parent(self)
> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 809,
> in _set_parent
> table.constraints.remove(fk.constraint)
> KeyError: ForeignKeyConstraint()
>
> but if I disable the autoloading, there is no problem
>
> PS: sqlalchemy v0.6.3, Python 2.7, PostgreSQL 8.4.1
>
> Thanks again - Jon
>
> On Aug 4, 2:06 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
>> On Aug 3, 2010, at 10:24 PM, jgs9000 wrote:
>>
>>> Hi
>>
>>> I'm relatively new to SQLAlchemy, so thanks in advance for any help
>>> with this issue.
>>
>>> I'm trying to construct a class to model a legacy table which has a
>>> composite primary key which is also
>>> a composite foreign key referencing the composite primary key of a
>>> second table. I'm trying to define this
>>> class declaratively, and also have it autoload the remaining table
>>> structure from the underlying table.
>>
>> i dont know that we have any tests which do a pure autoload plus a foreign
>> key constraint otherwise not associated with anything. so its likely a
>> bug. you might want to try calling table.append_constraint(constraint)
>> after the autoload completes.
>>
>>
>>
>>> This is what I have:
>>
>>> class Wcs(skymapper_db.db.TableBase):
>>
>>> from sqlalchemy import Column, Integer, ForeignKeyConstraint
>>
>>> __tablename__ = 'wcs'
>>> __table_args__ = (
>>> ForeignKeyConstraint(['image_id', 'amp'],
>>> ['science_amp.image_id', 'science_amp.amp']),
>>> {'autoload':True}
>>> )
>>
>>> image_id = Column(Integer, primary_key=True)
>>> amp = Column(Integer, primary_key=True)
>>
>>> def __init__(self):
>>> pass
>>
>>> def __repr__(self):
>>> return "<Wcs(%s, %s)>" % (self.image_id, self.amp)
>>
>>> As it stands, I get an error when I try to instantiate this class:
>>
>>> C:\Users\jgs900\Work\skymapper-alchemy>wcs.py
>>> Traceback (most recent call last):
>>> File "C:\Users\jgs900\Work\skymapper-alchemy\wcs.py", line 13, in
>>> <module>
>>> class Wcs(skymapper_db.db.TableBase):
>>> File "C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py",
>>> line 1017, in __init__
>>> _as_declarative(cls, classname, cls.__dict__)
>>> File "C:\Python27\lib\site-packages\sqlalchemy\ext\declarative.py",
>>> line 926, in _as_declarative
>>> **table_kw)
>>> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 209,
>>> in __new__
>>> table._init(name, metadata, *args, **kw)
>>> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 269,
>>> in _init
>>> self._init_items(*args)
>>> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 60,
>>> in _init_items
>>> item._set_parent(self)
>>> File "C:\Python27\lib\site-packages\sqlalchemy\schema.py", line 809,
>>> in _set_parent
>>> table.constraints.remove(fk.constraint)
>>> KeyError: ForeignKeyConstraint()
>>
>>> but if I leave out the autoload instruction, there is no problem.
>>
>>> Am i doing something fundamentally wrong? Or am I just making a syntax
>>> error of some sort. Any help
>>> would be greatly appreciated.
>>
>>> --
>>> You received this message because you are subscribed to the Google Groups
>>> "sqlalchemy" group.
>>> To post to this group, send email to sqlalch...@googlegroups.com.
>>> To unsubscribe from this group, send email to
>>> sqlalchemy+unsubscr...@googlegroups.com.
>>> For more options, visit this group
>>> athttp://groups.google.com/group/sqlalchemy?hl=en.
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
