Column order with declarative base

[Mike Lewis]

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?

Thanks,
Mike Lewis

[Chris Withers]

Please provide a simple, small example of your problem :-)

Also, is there a reason the order of column creation matters?

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Mike Lewis]

> Please provide a simple, small example of your problem :-)
>
> Also, is there a reason the order of column creation matters?
>
> Chris
>
> --
> Simplistix - Content Management, Batch Processing & Python Consulting
>             -http://www.simplistix.co.uk

class Foo(object):
id = Column(Integer, primary_key=True)
foo = Column(Integer, nullable=True)

class Bar(Base, object):
__tablename__ = 'bar'

then on creation of bar, foo might be first. I believe this is
because DeclarativeBase adds new documns with dir() and that returns
stuff in an arbitrary order.

I'm trying to use __metaclass__ to make a metaclass for Foo where it
retains order of the dictionary, but I am getting stumped.

Thanks,
Mike

[Michael Bayer]

The Column object contains a "sort key" when constructed, against a single global value, that is used as a sort key when the Table is generated. This is to get around the fact that the attribute dictionary of the declarative class is unordered.

The mixin columns should copy their "sort key" over, or it should somehow be tailored in the declarative base so that the order of the two columns stays relatively the same, and perhaps is also tailored to be at the same position relative to the other columns in the ultimate table.

I'd welcome any patches in this regard since I don't usually deal with the "mixin" feature.

[Michael Bayer]

On Jul 1, 2010, at 8:24 PM, Michael Bayer wrote:

>
> The Column object contains a "sort key" when constructed, against a single global value, that is used as a sort key when the Table is generated. This is to get around the fact that the attribute dictionary of the declarative class is unordered.
>
> The mixin columns should copy their "sort key" over, or it should somehow be tailored in the declarative base so that the order of the two columns stays relatively the same, and perhaps is also tailored to be at the same position relative to the other columns in the ultimate table.
>
> I'd welcome any patches in this regard since I don't usually deal with the "mixin" feature.

fine, how about Chris works up the unit test for it:

diff -r af4bdd33564e lib/sqlalchemy/ext/declarative.py
--- a/lib/sqlalchemy/ext/declarative.py Thu Jul 01 16:57:02 2010 -0400
+++ b/lib/sqlalchemy/ext/declarative.py Thu Jul 01 20:25:59 2010 -0400
@@ -673,7 +673,8 @@
if name not in dict_ and not (
'__table__' in dict_ and name in dict_['__table__'].c
):
- potential_columns[name]=column_copies[obj]=obj.copy()
+ potential_columns[name] = column_copies[obj] = obj.copy()
+ column_copies[obj]._creation_order = obj._creation_order
elif isinstance(obj, RelationshipProperty):
raise exceptions.InvalidRequestError(
"relationships are not allowed on "

[Chris Withers]

Michael Bayer wrote:
>> I'd welcome any patches in this regard since I don't usually deal with the "mixin" feature.
>
> fine, how about Chris works up the unit test for it:

Done and pushed back to tip with the fix.

cheers,

Chris

[Mike Lewis]

On Jul 1, 2010, at 8:24 PM, Michael Bayer wrote:

> The Column object contains a "sort key" when constructed, against a single global value, that is used as a sort key when the Table is generated.  This is to get around the fact that the attribute dictionary of the declarative class is unordered.  

Interesting. I guess I didn't come across this in the source. I
actually ended up implementing a similar thing in a subclass I have of
column (I think I'm doing some stuff that isn't very standard)

Since we're talking about columns, I also noticed that copy() doesn't
copy the .info object over.

[Sven Teresniak]

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

[Sven Teresniak]

Am Freitag, 2. Juli 2010 02:24:05 UTC+2 schrieb Michael Bayer:

The Column object contains a "sort key" when constructed, against a single global value, that is used as a sort key when the Table is generated.  This is to get around the fact that the attribute dictionary of the declarative class is unordered.  

The mixin columns should copy their "sort key" over, or it should somehow be tailored in the declarative base so that the order of the two columns stays relatively the same, and perhaps is also tailored to be at the same position relative to the other columns in the ultimate table.

I'd welcome any patches in this regard since I don't usually deal with the "mixin" feature.

Is there any simple way to modify/set this "sort key" or is there any way for me to workaround this random ordering in the class dict? Or to simple inspect the ordering to generate code that re-orders my primary composite key parts accordingly?

Thanks

Sven

[Michael Bayer]

why don’t you set up your PrimaryKeyConstraint directly?

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 )

    @declared_attr

    def __table_args__(self):

        return (

            PrimaryKeyConstraint('key1', 'key2', 'key3'),

        )

Sven Teresniak <realk...@gmail.com> wrote:

--
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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Sven Teresniak]

Aaah Michael,

thanks!

This is awesome!

I tried a lot and all the time I felt that I missed exact this kind of easy answer. ;)

Thanks again.

Will implement this now.

Sven