Column order with declarative base and @declared_attr

[Hans-Martin v. Gaudecker]

Hi,

I am creating a bunch of columns by means of the declared_attr decorator since many of them contain foreign keys. Similar to the issue in this thread [1] from a year ago, it seems that the column order is not preserved. To shamelessly borrow the example from that thread, when doing:

class Foo(object):

@declared_attr
def id(self):
return Column(Integer, primary_key=True)

@declared_attr
def foo(self):
return Column(Integer, nullable=True)

class Bar(Base, object):

__tablename__ = 'bar'

then the order of 'foo' and 'id' appears to be random. Is there a way around this?

FWIW, I'm using SQLAlchemy 0.7.1 on Python 3.2, using SQLite as the backend.

Thanks,
Hans-Martin

[1] http://groups.google.com/group/sqlalchemy/browse_thread/thread/0c8aa608c8dd4d7a

[Michael Bayer]

On Jul 21, 2011, at 4:07 PM, Hans-Martin v. Gaudecker wrote:

> Hi,
>
> I am creating a bunch of columns by means of the declared_attr decorator since many of them contain foreign keys. Similar to the issue in this thread [1] from a year ago, it seems that the column order is not preserved. To shamelessly borrow the example from that thread, when doing:
>
>
> class Foo(object):
>
> @declared_attr
> def id(self):
> return Column(Integer, primary_key=True)
>
> @declared_attr
> def foo(self):
> return Column(Integer, nullable=True)
>
> class Bar(Base, object):
>
> __tablename__ = 'bar'
>
>
> then the order of 'foo' and 'id' appears to be random. Is there a way around this?
>
> FWIW, I'm using SQLAlchemy 0.7.1 on Python 3.2, using SQLite as the backend.

The order is determined by the order in which the actual Column constructor is called, and an ordering token is applied. When a mixin is used, the Column is copied from the mixin, but it's likely that the ordering token is preserved. You'd declare them on Foo directly without using a @declared_attr function. Columns that have no ForeignKey can be created in this way.

There's no other way to approach this as the dictionary created by a new class is unordered in Python (unless perhaps we added ordering tokens to the usage of @declared_attr also, that could work).

[Hans-Martin]

> The order is determined by the order in which the actual Column constructor is called, and an ordering token is applied.    When a mixin is used, the Column is copied from the mixin, but it's likely that the ordering token is preserved.   You'd declare them on Foo directly without using a @declared_attr function.  Columns that have no ForeignKey can be created in this way.

Yip, did that first -- but then these appeared always before any
columns declared via @declared_attr (no surprise, if I my very limited
understanding of SQLAlchemy's internals is correct). Unfortunately,
I'd like to have the columns with foreign keys up front :-(

> There's no other way to approach this as the dictionary created by a new class is unordered in Python (unless perhaps we added ordering tokens to the usage of @declared_attr also, that could work).

Would be wonderful... Although it's more of a nuisance than a real
problem, it only really affects browsing through the tables after
all.

Thanks,
Hans-Martin

[Seth P]

I was just bitten by this issue. Is it still the case that there is no way to specify the order of two columns declared in a mixin using @declared_attr?

[Mike Bayer]

the @declarative_attr object would need util.set_creation_order()
applied and the _MapperConfig._scan_attributes() would need to take this
into account. However, it would not behave well across multiple mixins.
The mixins must be scanned in __mro__ order first. So it would be of
limited use for there to be an ordering under @declared_attr.

[Seth P]

If I understand what you're saying, the problem is that set _creation_order() specifies an ordering across all columns from all mixins. But shouldn't it suffice to specify the "local" ordering of the columns within a given mixin, and then process the mixins in __mro__ order?

(FWIW I worked around my particular problem by making the Columns non-@declared_attr, eliminating ForeignKey parameters (which are cls-dependent), and creating cls-dependent ForeignKeyConstraints in a cls-dependent __table_args__.)

the @declarative_attr object would need util.set_creation_order() 
applied and the _MapperConfig._scan_attributes() would need to take this 
into account.  However, it would not behave well across multiple mixins. 
  The mixins must be scanned in __mro__ order first.   So it would be of 
limited use for there to be an ordering under @declared_attr.


On 08/25/2016 02:46 PM, Seth P wrote:
> I was just bitten by this issue. Is it still the case that there is no way to specify the order of two columns declared in a mixin using @declared_attr?
>

-- 
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OA-n_pY0tuM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Mike Bayer]

On 08/25/2016 04:37 PM, Seth P wrote:
> If I understand what you're saying, the problem is that set
> _creation_order() specifies an ordering across all columns from all
> mixins. But shouldn't it suffice to specify the "local" ordering of the
> columns within a given mixin, and then process the mixins in __mro__ order?

the way set_creation_order() works is by assigning a counter to an
object when it is actually created, not when it is encountered in a
class definition.

That's how we get around:

class Foo(object):
attr_one = some_attr("im first!")

attr_two = some_attr("im second!")

attr_three = some_attr("im third!")

When we get a Python metaclass for the above, attr_one/two/three are all
in an unordered dict(). So instead, there's a counter that was set up
when some_attr() was actually called.

That approach doesn't really work here:

class Foo(object):
@some_attr("im first!")
def return_a_thing(self):
return Thing()

@some_attr("im second!")
def return_another_thing(self):
return Thing()

@some_attr("im third!")
def return_a_third_thing(self):
return Thing()


that is, while we can still give a counter to some_attr(), the thing
that is calling those methods also has to be invoked in that order,
because we really want each Thing() to have the ordering, and they've
yet to be created.

You can of course try to make each Thing() with a hardcoded ordering:


class Foo(object):
@some_attr("im first!")
def return_a_thing(self):
return Thing(hardcode_ordering=100)

@some_attr("im second!")
def return_another_thing(self):
return Thing(hardcode_ordering=200)

@some_attr("im third!")
def return_a_third_thing(self):
return Thing(hardcode_ordering=300)

if you want to go that approach you can assign _creation_order to each
Column you're creating, but those numbers have to all be what you want
in relation to all the other columns being set up.

Overall in SQL it really shouldn't matter what order columns are set up
in. If you *really* need this then I'd suggest using inner __table__
style:
http://docs.sqlalchemy.org/en/rel_1_0/orm/extensions/declarative/table_config.html#using-a-hybrid-approach-with-table

>
> (FWIW I worked around my particular problem by making the Columns
> non-@declared_attr, eliminating ForeignKey parameters (which are
> cls-dependent), and creating cls-dependent ForeignKeyConstraints in a
> cls-dependent __table_args__.)
>
>
>
>
>
> On Thu, Aug 25, 2016 at 4:24 PM -0400, "Mike Bayer"
> <mik...@zzzcomputing.com <mailto:mik...@zzzcomputing.com>> wrote:
>
> the @declarative_attr object would need util.set_creation_order()
> applied and the _MapperConfig._scan_attributes() would need to take this
> into account. However, it would not behave well across multiple mixins.
> The mixins must be scanned in __mro__ order first. So it would be of
> limited use for there to be an ordering under @declared_attr.
>
>
> On 08/25/2016 02:46 PM, Seth P wrote:
> > I was just bitten by this issue. Is it still the case that there is no way to specify the order of two columns declared in a mixin using @declared_attr?
> >
>
> --
> You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/OA-n_pY0tuM/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
> --

> 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
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.