Am Freitag, 2. Juli 2010 00:06:10 UTC+2 schrieb Chris Withers:
>
> Mike Lewis wrote:
> > I'm trying to do some DDL creation with declarative base. THe problem
> > I am running into is that I'm using a mixin, and it seems that the
> > order the columns are being created in is different than the order
> > they're declared with. Is there any way to control this?
>
> Please provide a simple, small example of your problem :-)
>
> Also, is there a reason the order of column creation matters?
>
Yes. When you want to query a composite primary key using Query.get()
method you need the exact ordering of key parts.
For example
PRIMARY KEY (key3, key2, key1),
vs.
PRIMARY KEY (key1, key2, key3),
So the position of parts of the composite key in the resulting DDL is very
important but (more or less) random.
Fore me (and a lot of other people) it seems very difficult to deal with
this kind of random when generating DDLs using Mixins.
Simple example:
Base = declarative_base()
class NameByClass(object):
""" just to set the name. no table. """
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()
class SomeKey(NameByClass, Base):
""" some simple table to refer to. table in db. """
keypart1 = Column(Integer, primary_key=True)
value = Column(Unicode)
class AnotherKey(NameByClass, Base):
""" another simple table to refer to. table in db. """
keypart2 = Column(Integer, primary_key=True)
value = Column(Unicode)
class AbstractPK(NameByClass):
""" this table defines a frequently used composite primary key """
@declared_attr
def key1(cls):
return Column(ForeignKey("somekey.keypart1"), primary_key=True)
@declared_attr
def key2(cls):
return Column(ForeignKey("anotherkey.keypart2"), primary_key=True)
key3 = Column( Integer, primary_key=True )
class RealTable(AbstractPK, Base):
""" a real table with composite PK from above and reference to SomeKey
and AnotherKey """
someothercolumn = Column(Unicode)
if __name__ == "__main__":
print "start"
from sqlalchemy import create_engine
engine = create_engine('postgresql://localhost:5432/dbname',
echo=True,
encoding="utf-8")
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
print "done"
This leads to
CREATE TABLE realtable (
key3 SERIAL NOT NULL,
someothercolumn VARCHAR,
key2 INTEGER NOT NULL,
key1 INTEGER NOT NULL,
PRIMARY KEY (key3, key2, key1),
FOREIGN KEY(key2) REFERENCES anotherkey (keypart2),
FOREIGN KEY(key1) REFERENCES somekey (keypart1)
)
And this leads to
session.query(realtable).get( (keypart3, keypart2, keypart1) )
which means: I have to change my code every time the ordering of elements
in Python's dictionary changes.
Best wishes for 2015
Sven
>
--
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.
