Re: [sqlalchemy] Eager Load issues unneeded SELECTS
On Nov 15, 2010, at 3:53 PM, ahamilton wrote: > I have a standard 1 to Many relationship between two model classes > (declarative base). In this project I have an Object class that will > have many instances of the Property class. I added the lazy=False > option to the relationship declaration to force eager loading of > Property instances when I query for an Object instance. Now, when I > run session.query(model.Object).get(id), SA executes 1 SELECT...JOIN > statement for all columns in both tables. I would expect SA to do > this. However, it also executes N SELECT statements for the Property > class, where N equals the number of Property instances linked to the > Object instance. Am I crazy, or should SA only be executing 1 > SELECT...JOIN statement and filling in the relationship with the > result? this is not nearly enough detail to know the answer, what SELECT statements are being emitted, what table is the target, how are the mappings configured, at what point is the SELECT emitted, etc. It sort of seems like you might be saying that object.properties loads, but then it loads property.object for each one (though you didn't use the word 'backref', no mappings here, etc.). That can be caused by many things, including a mis-configured many-to-one, or the parent Object falling out of scope before Property.object is accessed, etc. > > -- > 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.
Re: [sqlalchemy] Re: relationships for no-table-related Class
On Nov 15, 2010, at 10:46 AM, neurino wrote:
> Thanks for your answer first.
>
> Root is a singleton, its class is not mapped to a table.
>
> What I mean is I could add a table "roots" to the database with a
> sigle row and add areas a foreign key "root_id" and create a
> relationship as from subareas with parent area and get what I'm
> talking about.
sure, then you're mapping Root to a table, and having just one row. That
would make Root.area act exactly like a relationship() though its a little
strange to have a row in the database just to make your application work a
certain way (where "certain way" here is not clear).
>
> This relationship, between root and area, as long as areas and
> subareas would come in handy for example to traverse the tree for
> extracting an xml simply, or to make recursive calculations.
>
> Before sqlalchemy I was used to add all areas, subareas, items, parent
> attributes to classes by myself but now I'm in the situation that 80%
> of the work is done by sqlalchemy automatically and I'm not sure how
> to fill the remaining, possibly having both areas and subareas behave
> at the same way to avoid confusion (just as an example, lazy loading).
>
> Thanks for your support
> neurino
>
> On Nov 15, 3:49 pm, Michael Bayer wrote:
>> On Nov 15, 2010, at 8:06 AM, neurino wrote:
>>
>>> So no advice?
>>
>>> Are relationships and backref something more than attributes I can
>>> setup with a query?
>>
>>> Thank you for your support.
>>
>> what's not stated clearly here is what "Root" is. If that's not a class
>> mapped to a table, then you'd just need to use regular Python attributes and
>> descriptors to establish the in-python behavior you're looking for. Seems
>> like its essentially some kind of query object, so your query.all()/.parent
>> = some_root approach is what you'd go with, though it would appear that Root
>> is a singleton anyway, meaning this could be established on Area at the
>> class level instead of assigning to each instance.
>>
>> Its not clear what other behavior of "relationship()" would apply here,
>> since Root has no database identity.
>>
>>
>>
>>
>>
>>> On Nov 11, 9:45 am, neurino wrote:
I have a tree structure
>>
Root
|
+--Area
||
|+--SubArea
|||
||+--Item
|||
||+--Item
||
|+--SubArea
| |
| +--Item
| |
| +--Item
|
+--Area
|
+--SubArea
||
|+--Item
||
|+--Item
|
+--SubArea
|
+--Item
|
+--Item
>>
The tree structure corresponds to slqalchemy db tables `areas`,
`subareas` and `items`.
>>
Something like this:
>>
mapper(Area, areas_table, properties={
'subareas': relationship(SubArea, backref='parent'),
})
mapper(SubArea, subareas__table, properties={
'items': relationship(Item, backref='parent'),
})
mapper(Item, items_table)
>>
so each Area instance will have a `subareas` list and each SubArea
will have a `items` list,
>>
also I easyly get a backref `parent` from Item to parent SubArea and
from
SubArea to parent Area.
>>
But this won't be for Root: it will not have a `areas` list in Root
nor its areas will have a parent reference to Root.
>>
The quick-and-dirty solution is to do this in Root:
>>
self.areas = query(Area).all()
for area in self.areas:
area.parent = self
>>
But it won't be the same thing as sqlalchemy `relationship` attributes
so:
are there alternative solutions more sqlalchemy-like?
>>
Any tip appreciated!
>>
Thank you for your support
>>
Greetings
neurino
>>
>>> --
>>> 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
Re: [sqlalchemy] order_by: ArgumentError
its looking for a Column object.menus_table.c.weight instead of 'weight'.
On Nov 15, 2010, at 10:03 AM, Enrico Morelli wrote:
> Dear all,
>
> I've a lot of applications using SA 0.5.6. Now I upgraded my personal
> computer and now I can use SA 0.6.5 but my applications stops to work.
>
> I receive the error:
> ArgumentError: Column-based expression object expected for argument
> 'order_by'; got: 'weight', type
>
> I try to search in google but I don't understand why I receive this
> error. Someone can explain to me?
>
> Thanks in advance
>
> This is the table declaration:
>
> menus_table = Table('menus', metadata,
>Column('id', types.Integer, primary_key=True),
>Column('parent_id', types.Integer, ForeignKey('menus.id')),
>Column('name', types.Unicode(80), nullable=False),
>Column('title', types.Unicode(80)),
>Column('url', types.Unicode(80)),
>Column('weight', types.Integer, index=True),
>Column('lang', types.Unicode(2))
> )
>
> This is the mapper declaration:
> mapper(Menu, menus_table,
> properties={
> 'children': relation(Menu, order_by='weight'),
> 'permissions': relation(Permissions, backref='menus',
> secondary=menus_permissions_table)
> })
>
> At the end the query:
> main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
> Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()
>
> --
> ---
> (o_
> (o_//\ Coltivate Linux che tanto Windows si pianta da solo.
> (/)_ V_/_
> +--+
> | ENRICO MORELLI | email: more...@cerm.unifi.it |
> | * * * *| phone: +39 055 4574269 |
> | University of Florence| fax : +39 055 4574253 |
> | CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
> +--+
>
> --
> 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.
[sqlalchemy] Eager Load issues unneeded SELECTS
I have a standard 1 to Many relationship between two model classes (declarative base). In this project I have an Object class that will have many instances of the Property class. I added the lazy=False option to the relationship declaration to force eager loading of Property instances when I query for an Object instance. Now, when I run session.query(model.Object).get(id), SA executes 1 SELECT...JOIN statement for all columns in both tables. I would expect SA to do this. However, it also executes N SELECT statements for the Property class, where N equals the number of Property instances linked to the Object instance. Am I crazy, or should SA only be executing 1 SELECT...JOIN statement and filling in the relationship with the result? -- 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.
Re: [sqlalchemy] version_id_col and custom DeclarativeMeta with 0.6
On Nov 15, 2010, at 12:41 PM, Joril wrote:
> Hi everyone!
> I'm trying to port my application from SA 0.5.8 to 0.6.5, and I'm
> having a problem with __mapper_args__.. :/
>
> The application uses the declarative plugin, and every class derives
> from a customized declarative base that aims to add to each one the
> "version_id_col".
>
> Right now the code is like this:
>
>
> class MyMeta(DeclarativeMeta):
>
>def __init__(cls, classname, bases, dict_):
>cls.version = Column(Integer)
>cls.__mapper_args__ = { "version_id_col" : cls.version }
>return DeclarativeMeta.__init__(cls, classname, bases, dict_)
>
> Base = declarative_base(metaclass=MyMeta)
>
> class Entity(Base):
>
> __tablename__ = "entities"
> whatever...
>
>
> but I think I'm missing something, since even a simple
>
> print Entity().__mapper__.version_id_col
>
> produces a "(no name)" instead of something like "entities.version",
> so I'm guessing that some part of SA doesn't "see" the
> version_id_col...?
>
> I understand that SA 0.6 includes a "Declarative Mixin" feature, but
> that would require to touch-up every class declaration to include the
> mixin, am I right? It'd be no big deal, but I'd prefer to keep that
> "complexity" away, if it's possible... :)
First off, I can't reproduce your issue:
from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base
from sqlalchemy import *
class MyMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
cls.version = Column(Integer)
cls.__mapper_args__ = { "version_id_col" : cls.version }
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=MyMeta)
class Entity(Base):
__tablename__ = "entities"
id = Column(Integer, primary_key=True)
assert Entity.__mapper__.version_id_col is Entity.version.property.columns[0]
assert isinstance(Entity.__mapper__.version_id_col, Column)
assert Entity.__mapper__.version_id_col.name == 'version'
assert str(Entity.__mapper__.version_id_col) == 'entities.version'
Second, "(no name)" is produced if the column object hasn't been named. Make
sure you aren't doing something that's causing cls.version to get copied or
overwritten or conflicted, thereby making the one you're sticking in
__mapper_args__ incorrect. A good candidate here is if you're using joined
table inheritance.
Second, if you want to switch to newer declarative features, just stick your
__mapper_args__ on Base:
class Base(object):
version = Column(Integer)
@declared_attr
def __mapper_args__(cls):
return {'version_id_col':cls.version}
Base = declarative_base(cls=Base)
--
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.
Re: [sqlalchemy] Booleans not converted to integers on pymssql
confirmed for pymssql committed in r1a1ef69edef7 .
On Nov 15, 2010, at 11:57 AM, Bertrand Janin wrote:
> Hello all,
>
> Here is what I have:
>
>table = Table("contract", metadata,
>...
>Column("is_awesome", Boolean),
>...
>)
>
>mapper(Contract, table, properties={
>...
>"is_awesome": table.c.is_awesome,
>...
>})
>
> When using "mssql+pymssql", if I set the ``is_awesome`` value on a
> ``Contract`` instance, I get the following:
>
>OperationalError: (OperationalError) SQL Server message 207, severity 16,
> state 1, line 1:
>Invalid column name 'False'.
>DB-Lib error message 207, severity 16: General SQL Server error:
>Check messages from the SQL Server
>'UPDATE contract SET is_awesome=%(is_awesome)s'
>{'is_awesome': False}
>
> You can find a patch below that converts the ``bool`` to an ``int`` for
> dialects not supporting "native_boolean", this fixes the problem for me,
> but maybe I'm approaching the issue the wrong way.
>
> Thanks,
> Bertrand
>
>
> diff -r d5ab043dc08f lib/sqlalchemy/types.py
> --- a/lib/sqlalchemy/types.py Mon Nov 15 09:55:43 2010 -0500
> +++ b/lib/sqlalchemy/types.py Mon Nov 15 11:42:28 2010 -0500
> @@ -1681,6 +1681,12 @@
> self._should_create_constraint)
> )
> table.append_constraint(e)
> +
> +def bind_processor(self, dialect):
> +if dialect.supports_native_boolean:
> +return None
> +else:
> +return lambda b: int(b)
>
> def result_processor(self, dialect, coltype):
> if dialect.supports_native_boolean:
>
> --
> 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.
[sqlalchemy] version_id_col and custom DeclarativeMeta with 0.6
Hi everyone!
I'm trying to port my application from SA 0.5.8 to 0.6.5, and I'm
having a problem with __mapper_args__.. :/
The application uses the declarative plugin, and every class derives
from a customized declarative base that aims to add to each one the
"version_id_col".
Right now the code is like this:
class MyMeta(DeclarativeMeta):
def __init__(cls, classname, bases, dict_):
cls.version = Column(Integer)
cls.__mapper_args__ = { "version_id_col" : cls.version }
return DeclarativeMeta.__init__(cls, classname, bases, dict_)
Base = declarative_base(metaclass=MyMeta)
class Entity(Base):
__tablename__ = "entities"
whatever...
but I think I'm missing something, since even a simple
print Entity().__mapper__.version_id_col
produces a "(no name)" instead of something like "entities.version",
so I'm guessing that some part of SA doesn't "see" the
version_id_col...?
I understand that SA 0.6 includes a "Declarative Mixin" feature, but
that would require to touch-up every class declaration to include the
mixin, am I right? It'd be no big deal, but I'd prefer to keep that
"complexity" away, if it's possible... :)
So my question is: since it was possible with SA 0.5, is there a way
to specify a version_id_col in a custom DeclarativeMeta using 0.6?
Many thanks for your attention!
--
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.
Re: [sqlalchemy] Booleans not converted to integers on pymssql
any chance on contacting the pymssql author on that one ? "False" is actually
the integer 0. If pymssql is doing a naive "sql % params" kind of expression,
that's a seriously poor practice on their part. will try to commit a
compatible patch on this end.
On Nov 15, 2010, at 11:57 AM, Bertrand Janin wrote:
> Hello all,
>
> Here is what I have:
>
>table = Table("contract", metadata,
>...
>Column("is_awesome", Boolean),
>...
>)
>
>mapper(Contract, table, properties={
>...
>"is_awesome": table.c.is_awesome,
>...
>})
>
> When using "mssql+pymssql", if I set the ``is_awesome`` value on a
> ``Contract`` instance, I get the following:
>
>OperationalError: (OperationalError) SQL Server message 207, severity 16,
> state 1, line 1:
>Invalid column name 'False'.
>DB-Lib error message 207, severity 16: General SQL Server error:
>Check messages from the SQL Server
>'UPDATE contract SET is_awesome=%(is_awesome)s'
>{'is_awesome': False}
>
> You can find a patch below that converts the ``bool`` to an ``int`` for
> dialects not supporting "native_boolean", this fixes the problem for me,
> but maybe I'm approaching the issue the wrong way.
>
> Thanks,
> Bertrand
>
>
> diff -r d5ab043dc08f lib/sqlalchemy/types.py
> --- a/lib/sqlalchemy/types.py Mon Nov 15 09:55:43 2010 -0500
> +++ b/lib/sqlalchemy/types.py Mon Nov 15 11:42:28 2010 -0500
> @@ -1681,6 +1681,12 @@
> self._should_create_constraint)
> )
> table.append_constraint(e)
> +
> +def bind_processor(self, dialect):
> +if dialect.supports_native_boolean:
> +return None
> +else:
> +return lambda b: int(b)
>
> def result_processor(self, dialect, coltype):
> if dialect.supports_native_boolean:
>
> --
> 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.
[sqlalchemy] Booleans not converted to integers on pymssql
Hello all,
Here is what I have:
table = Table("contract", metadata,
...
Column("is_awesome", Boolean),
...
)
mapper(Contract, table, properties={
...
"is_awesome": table.c.is_awesome,
...
})
When using "mssql+pymssql", if I set the ``is_awesome`` value on a
``Contract`` instance, I get the following:
OperationalError: (OperationalError) SQL Server message 207, severity 16,
state 1, line 1:
Invalid column name 'False'.
DB-Lib error message 207, severity 16: General SQL Server error:
Check messages from the SQL Server
'UPDATE contract SET is_awesome=%(is_awesome)s'
{'is_awesome': False}
You can find a patch below that converts the ``bool`` to an ``int`` for
dialects not supporting "native_boolean", this fixes the problem for me,
but maybe I'm approaching the issue the wrong way.
Thanks,
Bertrand
diff -r d5ab043dc08f lib/sqlalchemy/types.py
--- a/lib/sqlalchemy/types.py Mon Nov 15 09:55:43 2010 -0500
+++ b/lib/sqlalchemy/types.py Mon Nov 15 11:42:28 2010 -0500
@@ -1681,6 +1681,12 @@
self._should_create_constraint)
)
table.append_constraint(e)
+
+def bind_processor(self, dialect):
+if dialect.supports_native_boolean:
+return None
+else:
+return lambda b: int(b)
def result_processor(self, dialect, coltype):
if dialect.supports_native_boolean:
--
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.
[sqlalchemy] Re: relationships for no-table-related Class
Thanks for your answer first.
Root is a singleton, its class is not mapped to a table.
What I mean is I could add a table "roots" to the database with a
sigle row and add areas a foreign key "root_id" and create a
relationship as from subareas with parent area and get what I'm
talking about.
This relationship, between root and area, as long as areas and
subareas would come in handy for example to traverse the tree for
extracting an xml simply, or to make recursive calculations.
Before sqlalchemy I was used to add all areas, subareas, items, parent
attributes to classes by myself but now I'm in the situation that 80%
of the work is done by sqlalchemy automatically and I'm not sure how
to fill the remaining, possibly having both areas and subareas behave
at the same way to avoid confusion (just as an example, lazy loading).
Thanks for your support
neurino
On Nov 15, 3:49 pm, Michael Bayer wrote:
> On Nov 15, 2010, at 8:06 AM, neurino wrote:
>
> > So no advice?
>
> > Are relationships and backref something more than attributes I can
> > setup with a query?
>
> > Thank you for your support.
>
> what's not stated clearly here is what "Root" is. If that's not a class
> mapped to a table, then you'd just need to use regular Python attributes and
> descriptors to establish the in-python behavior you're looking for. Seems
> like its essentially some kind of query object, so your query.all()/.parent =
> some_root approach is what you'd go with, though it would appear that Root is
> a singleton anyway, meaning this could be established on Area at the class
> level instead of assigning to each instance.
>
> Its not clear what other behavior of "relationship()" would apply here, since
> Root has no database identity.
>
>
>
>
>
> > On Nov 11, 9:45 am, neurino wrote:
> >> I have a tree structure
>
> >> Root
> >> |
> >> +--Area
> >> | |
> >> | +--SubArea
> >> | | |
> >> | | +--Item
> >> | | |
> >> | | +--Item
> >> | |
> >> | +--SubArea
> >> | |
> >> | +--Item
> >> | |
> >> | +--Item
> >> |
> >> +--Area
> >> |
> >> +--SubArea
> >> | |
> >> | +--Item
> >> | |
> >> | +--Item
> >> |
> >> +--SubArea
> >> |
> >> +--Item
> >> |
> >> +--Item
>
> >> The tree structure corresponds to slqalchemy db tables `areas`,
> >> `subareas` and `items`.
>
> >> Something like this:
>
> >> mapper(Area, areas_table, properties={
> >> 'subareas': relationship(SubArea, backref='parent'),
> >> })
> >> mapper(SubArea, subareas__table, properties={
> >> 'items': relationship(Item, backref='parent'),
> >> })
> >> mapper(Item, items_table)
>
> >> so each Area instance will have a `subareas` list and each SubArea
> >> will have a `items` list,
>
> >> also I easyly get a backref `parent` from Item to parent SubArea and
> >> from
> >> SubArea to parent Area.
>
> >> But this won't be for Root: it will not have a `areas` list in Root
> >> nor its areas will have a parent reference to Root.
>
> >> The quick-and-dirty solution is to do this in Root:
>
> >> self.areas = query(Area).all()
> >> for area in self.areas:
> >> area.parent = self
>
> >> But it won't be the same thing as sqlalchemy `relationship` attributes
> >> so:
> >> are there alternative solutions more sqlalchemy-like?
>
> >> Any tip appreciated!
>
> >> Thank you for your support
>
> >> Greetings
> >> neurino
>
> > --
> > 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.
[sqlalchemy] order_by: ArgumentError
Dear all,
I've a lot of applications using SA 0.5.6. Now I upgraded my personal
computer and now I can use SA 0.6.5 but my applications stops to work.
I receive the error:
ArgumentError: Column-based expression object expected for argument
'order_by'; got: 'weight', type
I try to search in google but I don't understand why I receive this
error. Someone can explain to me?
Thanks in advance
This is the table declaration:
menus_table = Table('menus', metadata,
Column('id', types.Integer, primary_key=True),
Column('parent_id', types.Integer, ForeignKey('menus.id')),
Column('name', types.Unicode(80), nullable=False),
Column('title', types.Unicode(80)),
Column('url', types.Unicode(80)),
Column('weight', types.Integer, index=True),
Column('lang', types.Unicode(2))
)
This is the mapper declaration:
mapper(Menu, menus_table,
properties={
'children': relation(Menu, order_by='weight'),
'permissions': relation(Permissions, backref='menus',
secondary=menus_permissions_table)
})
At the end the query:
main_menu = Session.query(Menu).filter(and_(Menu.parent_id==None,
Menu.lang==session['lang'])).order_by(Menu.weight.asc()).all()
--
---
(o_
(o_//\ Coltivate Linux che tanto Windows si pianta da solo.
(/)_ V_/_
+--+
| ENRICO MORELLI | email: more...@cerm.unifi.it |
| * * * *| phone: +39 055 4574269 |
| University of Florence| fax : +39 055 4574253 |
| CERM - via Sacconi, 6 - 50019 Sesto Fiorentino (FI) - ITALY|
+--+
--
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.
Re: [sqlalchemy] Re: relationships for no-table-related Class
On Nov 15, 2010, at 8:06 AM, neurino wrote:
> So no advice?
>
> Are relationships and backref something more than attributes I can
> setup with a query?
>
> Thank you for your support.
what's not stated clearly here is what "Root" is. If that's not a class mapped
to a table, then you'd just need to use regular Python attributes and
descriptors to establish the in-python behavior you're looking for. Seems like
its essentially some kind of query object, so your query.all()/.parent =
some_root approach is what you'd go with, though it would appear that Root is a
singleton anyway, meaning this could be established on Area at the class level
instead of assigning to each instance.
Its not clear what other behavior of "relationship()" would apply here, since
Root has no database identity.
>
> On Nov 11, 9:45 am, neurino wrote:
>> I have a tree structure
>>
>> Root
>> |
>> +--Area
>> ||
>> |+--SubArea
>> |||
>> ||+--Item
>> |||
>> ||+--Item
>> ||
>> |+--SubArea
>> | |
>> | +--Item
>> | |
>> | +--Item
>> |
>> +--Area
>>|
>>+--SubArea
>>||
>>|+--Item
>>||
>>|+--Item
>>|
>>+--SubArea
>> |
>> +--Item
>> |
>> +--Item
>>
>> The tree structure corresponds to slqalchemy db tables `areas`,
>> `subareas` and `items`.
>>
>> Something like this:
>>
>> mapper(Area, areas_table, properties={
>> 'subareas': relationship(SubArea, backref='parent'),
>> })
>> mapper(SubArea, subareas__table, properties={
>> 'items': relationship(Item, backref='parent'),
>> })
>> mapper(Item, items_table)
>>
>> so each Area instance will have a `subareas` list and each SubArea
>> will have a `items` list,
>>
>> also I easyly get a backref `parent` from Item to parent SubArea and
>> from
>> SubArea to parent Area.
>>
>> But this won't be for Root: it will not have a `areas` list in Root
>> nor its areas will have a parent reference to Root.
>>
>> The quick-and-dirty solution is to do this in Root:
>>
>> self.areas = query(Area).all()
>> for area in self.areas:
>> area.parent = self
>>
>> But it won't be the same thing as sqlalchemy `relationship` attributes
>> so:
>> are there alternative solutions more sqlalchemy-like?
>>
>> Any tip appreciated!
>>
>> Thank you for your support
>>
>> Greetings
>> neurino
>
> --
> 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.
[sqlalchemy] Re: relationships for no-table-related Class
So no advice?
Are relationships and backref something more than attributes I can
setup with a query?
Thank you for your support.
On Nov 11, 9:45 am, neurino wrote:
> I have a tree structure
>
> Root
> |
> +--Area
> | |
> | +--SubArea
> | | |
> | | +--Item
> | | |
> | | +--Item
> | |
> | +--SubArea
> | |
> | +--Item
> | |
> | +--Item
> |
> +--Area
> |
> +--SubArea
> | |
> | +--Item
> | |
> | +--Item
> |
> +--SubArea
> |
> +--Item
> |
> +--Item
>
> The tree structure corresponds to slqalchemy db tables `areas`,
> `subareas` and `items`.
>
> Something like this:
>
> mapper(Area, areas_table, properties={
> 'subareas': relationship(SubArea, backref='parent'),
> })
> mapper(SubArea, subareas__table, properties={
> 'items': relationship(Item, backref='parent'),
> })
> mapper(Item, items_table)
>
> so each Area instance will have a `subareas` list and each SubArea
> will have a `items` list,
>
> also I easyly get a backref `parent` from Item to parent SubArea and
> from
> SubArea to parent Area.
>
> But this won't be for Root: it will not have a `areas` list in Root
> nor its areas will have a parent reference to Root.
>
> The quick-and-dirty solution is to do this in Root:
>
> self.areas = query(Area).all()
> for area in self.areas:
> area.parent = self
>
> But it won't be the same thing as sqlalchemy `relationship` attributes
> so:
> are there alternative solutions more sqlalchemy-like?
>
> Any tip appreciated!
>
> Thank you for your support
>
> Greetings
> neurino
--
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.
Re: [sqlalchemy] Inheriting custom collection to create another custom collection. Issues with the appenders/parents
Thanks for your interest! And yeah... sorry for the typos... I was
writing the code right in the email...
I'm afraid I won't be able to test it until December 1 but make sure
as soon as I get my hands on the computer where that problem was
happening, I'll test it.
I really appreciate your effort! :) I'll keep you posted!
2010/11/13 jason kirtland :
> Hi Hector,
>
> On Fri, Nov 12, 2010 at 7:46 AM, Hector Blanco wrote:
>> Hello everyone.
>>
>> I was wondering if it's possible to inherit a custom collection to
>> create another custom collection.
>>
>> A few days ago I was trying to use my own class as a custom_collection
>> (http://groups.google.com/group/sqlalchemy/msg/ba1c64c3d227f586).
>> Thanks to Michael Bayer I was able to do it, but now I would like to
>> go one step further, and inherit my custom collection to create
>> another custom collection.
>>
>> To simplify a little what I asked in the other message, let's say I have a:
>>
>> def ClassA(declarativeBase):
>> __tablename__ = "aes"
>> id = Column("id", Integer, primary_key=True)
>> _whatever = Column("type", String(64))
>> def __init__(self):
>> self._whatever = "whatever"
>>
>> Then I have my custom collection for instances of "ClassA":
>>
>> def ContainerOfA(dict):
>> __emulates__ = set
>> def __init__(self):
>> self._field = "I'm a great... awesom! container"
>>
>> #I also defined the appender, remover and iterator
>> �...@collection.iterator
>> def __iter__(self):
>> return self.itervalues()
>>
>> �...@collection.appender
>> def append(self, item):
>> self[item.getUniqueHash()] = item
>>
>> �...@collection.remover
>> def remove(self, item):
>> if item.getUniqueHash() in self.keys():
>> del self[item.getUniqueHash()]
>>
>> And then I was happily able to use it in any relationships:
>>
>> def YetAnotherClass(declarativeBase):
>> id = Column("id", Integer, primary_key=True)
>> classesA = relationship("ClassA",
>> uselist=True,
>> secondary="intermediate_table",
>> collection_class=lambda: ContainerOfA(),
>> cascade="all, delete, delete-orphan",
>> single_parent=True
>> )
>>
>> Now I needed to extend "ClassA" in a "Class B" and "ContainerOfA" in
>> "ContainerOfB". I added the polymorphic stuff to "ClassA" and "ClassB"
>> to create a joined table inheritance, as detailed in
>> http://www.sqlalchemy.org/docs/orm/inheritance.html#joined-table-inheritance
>> . (it seems to be working fine, that's why I am not completely
>> detailing it here)
>>
>> def ClassB(ClassA):
>> __tablename__ = "bs" #Sorry for that
>> __mapper_args__ = {'polymorphic_identity': 'ClassB'}
>> id = Column("id", Integer, ForeignKey('aes.id'), primary_key=True)
>> def __init__(self):
>> self._anotherWhatever = "another whatever"
>>
>> def ContainerOfB(ContainerOfA):
>> def __init__(self):
>> super(ContainerOfB, self).__init__()
>> def anotherMethodOnlyForBInstances(self):
>> # do interesting stuff for B classes
>>
>> Then I tried to use it in a relationship:
>>
>> def YetYetAnotherClass(declarativeBase):
>> id = Column("id", Integer, primary_key=True)
>> classesB = relationship("ClassB",
>> uselist=True,
>> secondary="another_intermediate_table",
>> collection_class=lambda: ContainerOfB(),
>> cascade="all, delete, delete-orphan",
>> single_parent=True
>> )
>>
>> But when I tried to append a "ClassB" instance through the
>> relationship detailed above, I got this exception:
>>
Type ContainerOfB must elect an appender method to be a collection class
>
> I haven't been able to replicate this behavior. When testing your
> code I did notice that you are using 'def' to declare your classes,
> which won't actually create the type. I make that same typo myself
> periodically and it can be quite tricky to track down the one "def'd"
> class that's causing seemingly unrelated errors.
>
> Anyhow, I've attached the working test case I put together. If you
> can modify this to replicate your behavior, we can track down any bugs
> that might be present in the collection API's appender metadata
> bookkeeping. You definitely should not have to re-declare an
> @appender on a subclass- the collection mechanics should be sweeping
> over your inherited class and transparently picking up the methods.
> This is definitely working for the cases in the SQLA unit tests, but
> it's definitely possible you've found some corner case with that dict
> that's declared to be emulating a set.
>
> Cheers,
> Jason
>
>> I thought... "ok, ok... let's just explicitly add the 'appender' to
>> the ContainerOfB class... The only thing I need to do is c
