Re: [sqlalchemy] single table inheritance and instrument_class events

[Chris Withers]

On 17/01/2017 15:08, mike bayer wrote:


On 01/17/2017 06:15 AM, Chris Withers wrote:

Great, thanks. I assume has_inherited_table returns False where the
table is defined on the class itself?



it looks like has_inherited_table is just looking for non-None __table__
attribute up the inheritance chain.


Ah well, tests pass, must be fine *head back in sand* ;-)

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] single table inheritance and instrument_class events

[mike bayer]

On 01/17/2017 06:15 AM, Chris Withers wrote:

Great, thanks. I assume has_inherited_table returns False where the
table is defined on the class itself?



it looks like has_inherited_table is just looking for non-None __table__ 
attribute up the inheritance chain.




(ie: normal models, and the "base" model in the case of single table
inheritance)

Chris

On 16/01/2017 17:49, mike bayer wrote:

at the declarative level we have this:

http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.has_inherited_table



this might be better since you're really looking for a "table" up
above

On 01/16/2017 12:05 PM, Chris Withers wrote:

Hi All,

If I'm using instrument_class events to add some constraints to a table,
what's the 'right' way to spot when it's a subclass is being
instrumented? (where I'm guessing I shouldn't add the constraints).

My current attempt is here:

https://github.com/Mortar/mortar_mixins/commit/b82bea1dbc1393e24116943804b192df056a70e7




...but that feels a bit hacky.

Thoughts?

Chris







--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] single table inheritance and instrument_class events

[Chris Withers]

Great, thanks. I assume has_inherited_table returns False where the 
table is defined on the class itself?


(ie: normal models, and the "base" model in the case of single table 
inheritance)


Chris

On 16/01/2017 17:49, mike bayer wrote:

at the declarative level we have this:

http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.has_inherited_table


this might be better since you're really looking for a "table" up above

On 01/16/2017 12:05 PM, Chris Withers wrote:

Hi All,

If I'm using instrument_class events to add some constraints to a table,
what's the 'right' way to spot when it's a subclass is being
instrumented? (where I'm guessing I shouldn't add the constraints).

My current attempt is here:

https://github.com/Mortar/mortar_mixins/commit/b82bea1dbc1393e24116943804b192df056a70e7



...but that feels a bit hacky.

Thoughts?

Chris





--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] single table inheritance and instrument_class events

[mike bayer]

at the declarative level we have this:

http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html#sqlalchemy.ext.declarative.has_inherited_table

this might be better since you're really looking for a "table" up above

On 01/16/2017 12:05 PM, Chris Withers wrote:

Hi All,

If I'm using instrument_class events to add some constraints to a table,
what's the 'right' way to spot when it's a subclass is being
instrumented? (where I'm guessing I shouldn't add the constraints).

My current attempt is here:

https://github.com/Mortar/mortar_mixins/commit/b82bea1dbc1393e24116943804b192df056a70e7


...but that feels a bit hacky.

Thoughts?

Chris



--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] single table inheritance and instrument_class events

[Chris Withers]

Hi All,

If I'm using instrument_class events to add some constraints to a table, 
what's the 'right' way to spot when it's a subclass is being 
instrumented? (where I'm guessing I shouldn't add the constraints).


My current attempt is here:

https://github.com/Mortar/mortar_mixins/commit/b82bea1dbc1393e24116943804b192df056a70e7

...but that feels a bit hacky.

Thoughts?

Chris

--
SQLAlchemy - 
The Python SQL Toolkit and Object Relational Mapper


http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable 
Example.  See  http://stackoverflow.com/help/mcve for a full description.
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Single table inheritance and __table_args__

[Michael Bayer]

this feature should be added and is http://www.sqlalchemy.org/trac/ticket/2700, 
 here's a workaround for now:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

class FixTableArgs(object):
@classmethod
def __declare_last__(cls):
if '__extra_table_args__' in cls.__dict__:
cls.__table__._init_items(*cls.__extra_table_args__)
del cls.__extra_table_args__

Base = declarative_base(cls=FixTableArgs)

class BaseClass(Base):
__tablename__ = "base"

id = Column(Integer, primary_key=True)
base_col = Column(Integer)

__table_args__ = (Index("ix1", base_col),)

class SubClass(BaseClass):
col_a = Column(Integer)
col_b = Column(Integer)

__extra_table_args__ = (UniqueConstraint("col_a", "col_b"), )

configure_mappers()

print BaseClass.__table__.constraints




On Apr 9, 2013, at 5:38 AM, "Gombas, Gabor (IT)" 
 wrote:

> Hi,
> 
> What is the recommended method of specifying constraints on columns
> added by a subclass using single-table inheritance? This does not work,
> I get a KeyError for "col_a":
> 
> class BaseClass(Base)
>__table__ = "base"
>__table_args__ = (Index("base_col"),
>  UniqueConstraint("col_a", "col_b"))
> 
>base_col = Column(Integer)
> 
> class SubClass(BaseClass):
>col_a = Column(Integer)
>col_b = Column(Integer)
> 
> SQLA does not allow __table_args__ placed on SubClass. I can do a
> workaround by using BaseClass.__table__.append_constraint() after
> defining SubClass, but mixing different ways of defining constraints
> does not look nice.
> 
> Gabor
> 
> -- 
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
> For more options, visit https://groups.google.com/groups/opt_out.
> 
> 

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

[sqlalchemy] Single table inheritance and __table_args__

[Gombas, Gabor (IT)]

Hi,

What is the recommended method of specifying constraints on columns
added by a subclass using single-table inheritance? This does not work,
I get a KeyError for "col_a":

class BaseClass(Base)
__table__ = "base"
__table_args__ = (Index("base_col"),
  UniqueConstraint("col_a", "col_b"))

base_col = Column(Integer)

class SubClass(BaseClass):
col_a = Column(Integer)
col_b = Column(Integer)

SQLA does not allow __table_args__ placed on SubClass. I can do a
workaround by using BaseClass.__table__.append_constraint() after
defining SubClass, but mixing different ways of defining constraints
does not look nice.

Gabor

-- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

Re: [sqlalchemy] single table inheritance does not work with multiple inheritance

[Michael Bayer]

I agree this could be better.   Not sure how deep we'd have to go to get 
multiple inheritance in single inheritance form to work, mapping to inheritance 
hierarchies is probably what adds most complexity to the ORM as it is.   It's a 
very rare use case though, and also gets way harder when we start talking about 
joined inheritance which is generally supported in the same hierarchy as 
single,  so it's hard to prioritize this feature.



On Jan 14, 2013, at 1:37 PM, Ethan Fremen wrote:

> I am all down with the power of mixins. The reason I mapped things this way 
> is because I do have concrete instances of string, amount, and quantity.
> 
> (I'm capturing data from receipts: "string" can be e.g. a receipt number, 
> amount can be e.g. a total, and quantity can be something unaffiliated with a 
> line item, like "number of seats reserved".
> 
> I finally got this to work by re-declaring all the properties I wanted using 
> declared_attr on the "final" class, like the below.  Which is fine in this 
> case, but slightly more annoying in the other case I have, where there are 
> quite a few inherited properties.
> 
> -ethan
> 
> class EvidenceLineItem(EvidenceAmount, EvidenceQuantity, EvidenceString):
> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.line_item }
> @declared_attr
> def string(cls):
> return Evidence.__table__.c.get('string',Column(String(length=255), 
> index=True))
> @declared_attr
> def quantity(cls):
> return Evidence.__table__.c.get('quantity',Column(Integer))
> @declared_attr
> def amount(cls):
> return Evidence.__table__.c.get('amount',Column(Integer, index=True))
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/VuHxC54meNsJ.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] single table inheritance does not work with multiple inheritance

[Ethan Fremen]

I am all down with the power of mixins. The reason I mapped things this way 
is because I do have concrete instances of string, amount, and quantity.

(I'm capturing data from receipts: "string" can be e.g. a receipt number, 
amount can be e.g. a total, and quantity can be something unaffiliated with 
a line item, like "number of seats reserved".

I finally got this to work by re-declaring all the properties I wanted 
using declared_attr on the "final" class, like the below.  Which is fine in 
this case, but slightly more annoying in the other case I have, where there 
are quite a few inherited properties.

-ethan

class EvidenceLineItem(EvidenceAmount, EvidenceQuantity, EvidenceString):
__mapper_args__ = { 'polymorphic_identity': 
ProofItemClassType.line_item }
@declared_attr
def string(cls):
return Evidence.__table__.c.get('string',Column(String(length=255), 
index=True))
@declared_attr
def quantity(cls):
return Evidence.__table__.c.get('quantity',Column(Integer))
@declared_attr
def amount(cls):
return Evidence.__table__.c.get('amount',Column(Integer, 
index=True))

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/VuHxC54meNsJ.
To post to this group, send email to sqlalchemy@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] single table inheritance does not work with multiple inheritance

[Michael Bayer]

and if you expand upon this approach you'll probably have column conflicts too, 
refer to 
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#resolving-column-conflicts
 for the strategy for dealing with that.


On Jan 14, 2013, at 1:17 PM, Michael Bayer wrote:

> The inheritance mechanics don't support multiple inheritance from the POV of 
> multiple mapped classes as the base for another mapped class.   Single table 
> inheritance would be the easiest case to work at some point, but there's lots 
> of mechanics in place right now that assume a linear path from subclass to 
> superclass.
> 
> If you don't actually need instances of EvidenceString, EvidenceAmount, and 
> EvidenceQuantity to exist in the database distinctly (that is, you'd never 
> actually have any rows with "string", "amount", or "quantity" present in the 
> DB as the discriminator), you'd define these as unmapped mixins, which is 
> very well supported (see 
> http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#mixin-and-custom-base-classes)
>  .   It seems here like that's what you're really looking for.
> 
> 
> 
> On Jan 14, 2013, at 1:09 PM, Ethan Fremen wrote:
> 
>> I have a pretty simple case. A base class with three sub-classes, and then a 
>> final class that inherits from the three derived classes. When persisting, 
>> sqlalchemy only sets the properties of the parent class and the first child 
>> class.
>> 
>> In all other respects the sub-class is behaving properly; it has the correct 
>> attributes, etc.
>> 
>> So, if EvidenceLineItem inherits from EvidenceString first, only the string 
>> property (and the properties in Evidence) will update; if I change the first 
>> inherited class to EvidenceAmount, amount will be persisted, but the rest 
>> not.
>> 
>> I'll try declared_attr, but I'm pretty sure that this behaviour isn't what 
>> is intended.
>> 
>> ~ethan
>> 
>> class Evidence(Base):
>> __tablename__ = "evidence"
>> discriminator = Column(DBEnum(ProofItemClassType), nullable=False) 
>> __mapper_args__ = { 'polymorphic_on': discriminator,
>> 'polymorphic_identity': ProofItemClassType.generic }
>> 
>> class EvidenceString(Evidence):
>> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.string }
>> string = Column(String(length=255), index=True)
>> 
>> class EvidenceAmount(Evidence):
>> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.amount }
>> amount = Column(Numeric(precision=7, scale=2), index=True)
>> 
>> class EvidenceQuantity(Evidence):
>> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.quantity }
>> quantity = Column(Integer)
>> 
>> class EvidenceLineItem(EvidenceString, EvidenceAmount, EvidenceQuantity):
>> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.line_item 
>> }
>> 
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msg/sqlalchemy/-/5hEAmVfC-EYJ.
>> To post to this group, send email to sqlalchemy@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 sqlalchemy@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 sqlalchemy@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] single table inheritance does not work with multiple inheritance

[Michael Bayer]

The inheritance mechanics don't support multiple inheritance from the POV of 
multiple mapped classes as the base for another mapped class.   Single table 
inheritance would be the easiest case to work at some point, but there's lots 
of mechanics in place right now that assume a linear path from subclass to 
superclass.

If you don't actually need instances of EvidenceString, EvidenceAmount, and 
EvidenceQuantity to exist in the database distinctly (that is, you'd never 
actually have any rows with "string", "amount", or "quantity" present in the DB 
as the discriminator), you'd define these as unmapped mixins, which is very 
well supported (see 
http://docs.sqlalchemy.org/en/rel_0_8/orm/extensions/declarative.html#mixin-and-custom-base-classes)
 .   It seems here like that's what you're really looking for.



On Jan 14, 2013, at 1:09 PM, Ethan Fremen wrote:

> I have a pretty simple case. A base class with three sub-classes, and then a 
> final class that inherits from the three derived classes. When persisting, 
> sqlalchemy only sets the properties of the parent class and the first child 
> class.
> 
> In all other respects the sub-class is behaving properly; it has the correct 
> attributes, etc.
> 
> So, if EvidenceLineItem inherits from EvidenceString first, only the string 
> property (and the properties in Evidence) will update; if I change the first 
> inherited class to EvidenceAmount, amount will be persisted, but the rest not.
> 
> I'll try declared_attr, but I'm pretty sure that this behaviour isn't what is 
> intended.
> 
> ~ethan
> 
> class Evidence(Base):
> __tablename__ = "evidence"
> discriminator = Column(DBEnum(ProofItemClassType), nullable=False) 
> __mapper_args__ = { 'polymorphic_on': discriminator,
> 'polymorphic_identity': ProofItemClassType.generic }
> 
> class EvidenceString(Evidence):
> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.string }
> string = Column(String(length=255), index=True)
> 
> class EvidenceAmount(Evidence):
> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.amount }
> amount = Column(Numeric(precision=7, scale=2), index=True)
> 
> class EvidenceQuantity(Evidence):
> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.quantity }
> quantity = Column(Integer)
> 
> class EvidenceLineItem(EvidenceString, EvidenceAmount, EvidenceQuantity):
> __mapper_args__ = { 'polymorphic_identity': ProofItemClassType.line_item }
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/5hEAmVfC-EYJ.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] single table inheritance does not work with multiple inheritance

[Ethan Fremen]

I have a pretty simple case. A base class with three sub-classes, and then 
a final class that inherits from the three derived classes. When 
persisting, sqlalchemy only sets the properties of the parent class and the 
first child class.

In all other respects the sub-class is behaving properly; it has the 
correct attributes, etc.

So, if EvidenceLineItem inherits from EvidenceString first, only the string 
property (and the properties in Evidence) will update; if I change the 
first inherited class to EvidenceAmount, amount will be persisted, but the 
rest not.

I'll try declared_attr, but I'm pretty sure that this behaviour isn't what 
is intended.

~ethan

class Evidence(Base):
__tablename__ = "evidence"
discriminator = Column(DBEnum(ProofItemClassType), nullable=False) 
__mapper_args__ = { 'polymorphic_on': discriminator,
'polymorphic_identity': ProofItemClassType.generic }

class EvidenceString(Evidence):
__mapper_args__ = { 'polymorphic_identity': ProofItemClassType.string }
string = Column(String(length=255), index=True)

class EvidenceAmount(Evidence):
__mapper_args__ = { 'polymorphic_identity': ProofItemClassType.amount }
amount = Column(Numeric(precision=7, scale=2), index=True)

class EvidenceQuantity(Evidence):
__mapper_args__ = { 'polymorphic_identity': ProofItemClassType.quantity 
}
quantity = Column(Integer)

class EvidenceLineItem(EvidenceString, EvidenceAmount, EvidenceQuantity):
__mapper_args__ = { 'polymorphic_identity': 
ProofItemClassType.line_item }

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/5hEAmVfC-EYJ.
To post to this group, send email to sqlalchemy@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] Single Table Inheritance with mult-column keys

[Rob]

Hi Michael,

That does exactly what I was after (and I've learned a little bit more 
about sqalchemy!)

Thank you very much for your help.




On Thursday, 23 August 2012 19:13:27 UTC+1, Michael Bayer wrote:
>
>
> On Aug 23, 2012, at 10:04 AM, Rob wrote:
>
> Hi Michael,
>
> I have a similar (but subtly different) problem to this, trying to mix 
> single- and joined-table inheritance.
> Essentially my model looks as follows:
>
> Product(Base)
> PhysicalProduct(Product)   
> NonPhysicalProduct(Product)
>
> The Physical/NonPhysicalProduct use single table inheritance whilst 
> objects inheriting from them use joined tables...  
>
> I have a fully working model --- and there's no question that it works!! 
>  But I can't help feeling that I've missed something relating to the 
> __mapper_args__ which is then requiring explicit calls to __init__ objects 
> higher up the tree.  (rather than bunging up this message, please see the 
> attached file) 
>
> I'd be really grateful if you could take a look and hopefully point me in 
> the right direction.
>
>
>
> this model wouldn't "work" fully, as if you did query(Product), the 
> discriminator would only be the "product_type" column.  p_discr and 
> np_discr would be ignored.   The polymorphic load only checks for one key 
> in one map, and that map is always shared among all classes in the 
> hierarchy starting at Product.
>
> I'm going to call this pattern "cascading polymorphic ons" for now, I 
> guess.   The attached file will get you there,  but requires two techniques 
> that are probably outside of the scope of what a the average SQLA user 
> could be expected to come up with on his or her own.  To get SQLA to do 
> these kinds of things automatically should be possible, most likely by just 
> having the "match polymorphic_on to class" phase during loading continue 
> it's process several times.   The concept is not too hard but there's edges 
> to it which may make it a more involved enhancement, this is 
> http://www.sqlalchemy.org/trac/ticket/2555.
>
> For now here's the workaround version:
>
> """
> mixed single and joined table inheritance.
> """
>
> from sqlalchemy import *
> from sqlalchemy import types
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base,  declared_attr
> from sqlalchemy import event
>
> Base = declarative_base()
>
> class Product(Base):
> __tablename__ = 'products'
> id = Column(types.Integer, primary_key=True)
> discriminator = Column('product_type', types.String(50), 
> nullable=False)
>
> _discriminator = "discriminator"
>
> def price_history(self):
> return []
>
> class PhysicalProduct(Product):
> p_discr = Column(types.String(50))
>
> _discriminator = "p_discr"
>
> @declared_attr
> def __mapper_args__(cls):
> return {'polymorphic_identity': 'physical_product'}
>
> def inventory(self):
> return "computed inventory"
>
> class NonPhysicalProduct(Product):
> np_discr = Column(types.String(50))
>
> _discriminator = "np_discr"
>
> @declared_attr
> def __mapper_args__(cls):
> return {'polymorphic_identity': 'nonphysical_product'}
>
> def somefunc(self):
> return "someval"
>
> # set polymorphic on as a coalesce of those three
> # columns.  It's after the fact beacuse p_discr and np_discr
> # are defined after Product, but if you move them up then
> # this can be inline inside of Product.__mapper_args__.
> # this would improve loads too as it appears the p_discr/np_discr columns
> # aren't loaded directly when you query for Product
> for mp in Product.__mapper__.self_and_descendants:
> mp._set_polymorphic_on(
> func.coalesce(
> Product.__table__.c.p_discr,
> Product.__table__.c.np_discr,
> Product.__table__.c.product_type
> ))
>
> # build our own system of assigning polymorphic identities
> # to instances; use the 'init' event.
> # Add a "print" for the "identity" dict to see what it's doing.
> @event.listens_for(Product, "init", propagate=True)
> def init(target, args, kwargs):
> identity = {}
> for cls, supercls in zip(type(target).__mro__, 
> type(target).__mro__[1:]):
> if not hasattr(supercls, '_discriminator'):
> break
> discriminator_attr = supercls._discriminator
> poly_identity = cls.__mapper__.polymorphic_identity
> identity.setdefault(discriminator_attr, poly_identity)
> for key in identity:
> setattr(target, key, identity[key])
>
>
> class Newspaper(PhysicalProduct):
> __tablename__ = 'newspapers'
> __mapper_args__ = {'polymorphic_identity': 'newspaper'}
>
> id = Column(types.Integer,
> ForeignKey('products.id'),
> primary_key=True
> )
> title = Column(types.String(50))
>
> def __init__(self, title):
> self.title = title
>
>
> class NewspaperDelivery(NonPhysicalProd

Re: [sqlalchemy] Single Table Inheritance with mult-column keys

[Michael Bayer]

On Aug 23, 2012, at 10:04 AM, Rob wrote:

> Hi Michael,
> 
> I have a similar (but subtly different) problem to this, trying to mix 
> single- and joined-table inheritance.
> Essentially my model looks as follows:
> 
> Product(Base)
> PhysicalProduct(Product)   
> NonPhysicalProduct(Product)
> 
> The Physical/NonPhysicalProduct use single table inheritance whilst objects 
> inheriting from them use joined tables...  
> 
> I have a fully working model --- and there's no question that it works!!  But 
> I can't help feeling that I've missed something relating to the 
> __mapper_args__ which is then requiring explicit calls to __init__ objects 
> higher up the tree.  (rather than bunging up this message, please see the 
> attached file) 
> 
> I'd be really grateful if you could take a look and hopefully point me in the 
> right direction.


this model wouldn't "work" fully, as if you did query(Product), the 
discriminator would only be the "product_type" column.  p_discr and np_discr 
would be ignored.   The polymorphic load only checks for one key in one map, 
and that map is always shared among all classes in the hierarchy starting at 
Product.

I'm going to call this pattern "cascading polymorphic ons" for now, I guess.   
The attached file will get you there,  but requires two techniques that are 
probably outside of the scope of what a the average SQLA user could be expected 
to come up with on his or her own.  To get SQLA to do these kinds of things 
automatically should be possible, most likely by just having the "match 
polymorphic_on to class" phase during loading continue it's process several 
times.   The concept is not too hard but there's edges to it which may make it 
a more involved enhancement, this is http://www.sqlalchemy.org/trac/ticket/2555.

For now here's the workaround version:

"""
mixed single and joined table inheritance.
"""

from sqlalchemy import *
from sqlalchemy import types
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base,  declared_attr
from sqlalchemy import event

Base = declarative_base()

class Product(Base):
__tablename__ = 'products'
id = Column(types.Integer, primary_key=True)
discriminator = Column('product_type', types.String(50), nullable=False)

_discriminator = "discriminator"

def price_history(self):
return []

class PhysicalProduct(Product):
p_discr = Column(types.String(50))

_discriminator = "p_discr"

@declared_attr
def __mapper_args__(cls):
return {'polymorphic_identity': 'physical_product'}

def inventory(self):
return "computed inventory"

class NonPhysicalProduct(Product):
np_discr = Column(types.String(50))

_discriminator = "np_discr"

@declared_attr
def __mapper_args__(cls):
return {'polymorphic_identity': 'nonphysical_product'}

def somefunc(self):
return "someval"

# set polymorphic on as a coalesce of those three
# columns.  It's after the fact beacuse p_discr and np_discr
# are defined after Product, but if you move them up then
# this can be inline inside of Product.__mapper_args__.
# this would improve loads too as it appears the p_discr/np_discr columns
# aren't loaded directly when you query for Product
for mp in Product.__mapper__.self_and_descendants:
mp._set_polymorphic_on(
func.coalesce(
Product.__table__.c.p_discr,
Product.__table__.c.np_discr,
Product.__table__.c.product_type
))

# build our own system of assigning polymorphic identities
# to instances; use the 'init' event.
# Add a "print" for the "identity" dict to see what it's doing.
@event.listens_for(Product, "init", propagate=True)
def init(target, args, kwargs):
identity = {}
for cls, supercls in zip(type(target).__mro__, type(target).__mro__[1:]):
if not hasattr(supercls, '_discriminator'):
break
discriminator_attr = supercls._discriminator
poly_identity = cls.__mapper__.polymorphic_identity
identity.setdefault(discriminator_attr, poly_identity)
for key in identity:
setattr(target, key, identity[key])


class Newspaper(PhysicalProduct):
__tablename__ = 'newspapers'
__mapper_args__ = {'polymorphic_identity': 'newspaper'}

id = Column(types.Integer,
ForeignKey('products.id'),
primary_key=True
)
title = Column(types.String(50))

def __init__(self, title):
self.title = title


class NewspaperDelivery(NonPhysicalProduct):
__tablename__ = 'deliveries'
__mapper_args__ = {'polymorphic_identity': 'delivery'}

id = Column(types.Integer,
ForeignKey('products.id'),
primary_key=True
)
destination = Column(types.String(50))

def __init__(self, destination):
self.destination = destination


# note here how the polymorphic map works out:
print Product.__mappe

Re: [sqlalchemy] Single Table Inheritance with mult-column keys

[Rob]

Hi Michael,

I have a similar (but subtly different) problem to this, trying to mix 
single- and joined-table inheritance.
Essentially my model looks as follows:

Product(Base)
PhysicalProduct(Product)   
NonPhysicalProduct(Product)

The Physical/NonPhysicalProduct use single table inheritance whilst objects 
inheriting from them use joined tables...  

I have a fully working model --- and there's no question that it works!! 
 But I can't help feeling that I've missed something relating to the 
__mapper_args__ which is then requiring explicit calls to __init__ objects 
higher up the tree.  (rather than bunging up this message, please see the 
attached file) 

I'd be really grateful if you could take a look and hopefully point me in 
the right direction.

Many thanks,
Rob 
   





On Wednesday, 17 August 2011 00:42:28 UTC+1, Michael Bayer wrote:
>
>
> On Aug 16, 2011, at 5:37 PM, Mike Gilligan wrote:
>
> I have a single table that looks similar to the following:
>
> class Equipment(Base):
> type = Column(CHAR(1), primary_key=True)
> sub_type = Column(CHAR(1), primary_key=True)
> code = Column(CHAR(5), primary_key=True)
>
>
> For historical purposes, I cannot modify this table. I would like to setup 
> multi-level inheritance similar to this, however it does not work:
>
> class Equipment(Base):
> type = Column(CHAR(1), primary_key=True)
> sub_type = Column(CHAR(1), primary_key=True)
> code = Column(CHAR(5), primary_key=True)
> __mapper_args__ = {'polymorphic_on': type}
>
>
> class Vehicle(Equipment):
>  __mapper_args__ = {'polymorphic_identity': 'V', 'polymorphic_on': 
> sub_type}
>
>
> class Bus(Vehicle)
>  __mapper_args__ = {'polymorphic_identity': 'B'}
>
>
> class Rail(Vehicle)
>  __mapper_args__ = {'polymorphic_identity': 'R'}
>
>
> I can concatenate the multiple column values into a single discriminator 
> column_property but then I do not have an easy way to retrieve all 
> vehicles. Any ideas?
>
>
> The inheritance querying does handle multi-level inheritance so if your 
> discriminator was on a concatenation of both things would work just fine, 
> i.e. if you queried for Vehicle, etc.   Each object's 
> "polymorphic_identity" would need to include the concatenated value, of 
> course.
>
> Unfortunately we're just beginning to support inheritance discriminators 
> on a column_property(), and you need to use a very specific approach to 
> make this work right now.  There's some tickets in trac to allow this 
> functionality out of the box.Attached is an example script which 
> exercises the above mapping - it uses declarative to minimize the impact of 
> the workaround.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/KzPgMan_6MIJ.
To post to this group, send email to sqlalchemy@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.

"""
mixed single and joined table inheritance.
"""

from sqlalchemy import *
from sqlalchemy import types
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base,  declared_attr

Base = declarative_base()

class Product(Base):
__tablename__ = 'products'
id   = Column(types.Integer, primary_key=True)
discriminator = Column('product_type', types.String(50), nullable=False)

__mapper_args__ = {'polymorphic_on': discriminator}

def price_history(self):
return []


class PhysicalProduct(Product):
p_discr = Column(types.String(50))

@declared_attr
def __mapper_args__(cls):
return {'polymorphic_on': cls.p_discr,
'polymorphic_identity' : 'physical_product'}

def __init__(self, **kw):
print "init PP kwargs:", kw
self.discriminator = 'physical_product'

def inventory(self):
return "computed inventory"


class NonPhysicalProduct(Product):
np_discr = Column(types.String(50))

@declared_attr
def __mapper_args__(cls):
return {'polymorphic_on': cls.np_discr,
'polymorphic_identity' : 'nonphysical_product'}

def __init__(self, **kw):
print "init NP kwargs:", kw
self.discriminator = 'nonphysical_product'

def somefunc(self):
return "someval"


class Newspaper(PhysicalProduct):
__tablename__ = 'newspapers'
__mapper_args__ = {'polymorphic_identity': 'newspaper'}

id = Column(types.Integer,
ForeignKey('products.id'),
primary_key=True
)
title = Column(types.String(50))

def __init__(self, title):
self.title = title
super(Newspaper, self).__init__()


class NewspaperDelivery(NonPhysicalProduct):
__tablename__ = 'deliveries'
__mapper_args__ = {'polymorphic_identity': 'delivery'}

id = Colu

Re: [sqlalchemy] Single table inheritance - column not mapped

[Kuba Dolecki]

Cool, I think the approach you outlined briefly works, and I look forward 
to hopefully seeing it in the next release. 

For now, I will just add the columns back to the AutocreatedGroup class. 

Again, thank you. I really appreciate your help. I'll make sure to make my 
emails in the future more explanatory according to your input.

On Wednesday, August 15, 2012 12:22:59 PM UTC-4, Michael Bayer wrote:
>
>
> On Aug 15, 2012, at 11:41 AM, Kuba Dolecki wrote:
>
> You responded! Woot. Thanks. Here's some additional info:
>
> 1. __tablename__ is generated automatically based on the class name. 
> Flask-SQLAlchemy does this for us. The __tablename__ for AutocreatedGroup 
> and TopicAutocreatedGroup model is both "autocreated_group".
>
>
> 
>
>
> 2. Sorry, I might have included too much code in my snippet. Group extends 
> Community through joined table inheritance (primary key with foreign key to 
> community). AutocreatedGroup extends Group in the same manner. The 
> relationship of interest is between AutocreatedGroup and 
> TopicAutocreatedGroup. TopicAutocreatedGroup does appear to inherit through 
> a single table. 
>
>
> OH.   no, this wasn't *enough* code.  I always need to know the full 
> expanse of classes and tables in play.  I'll recreate the full mapping now 
> based on your descriptions here in order to reproduce.
>
>
> 3. Yeah, I'm redefining polymorphic_on for Group since it depends on a 
> boolean switch. I'm doing the same for AutocreatedGroup since it depends on 
> the name of the class. 
>
> AutocreatedGroup and TopicAutocreatedGroup do appear to have all of the 
> same columns of Group and Community. The AutocreatedGroup table does have 
> the topic_created_from_id column, but that same column doesn't appear in 
> the mapper. 
>
>
> there is logic added to declarative that detects columns applied to a 
> single table inheritance subclass, and moves them up to the superclass 
> table, mapping it only for the subclass.  Due to the complex and deep chain 
> of mappings here, this logic is likely failing.  let's create a test case 
> and see.  (goes to do that).OK yeah this isn't going to work right now 
> and would require a major architectural change to the expression system in 
> order to accommodate.   The declarative system adds the new column to the 
> AutocreatedGroup.__table__ correctly.  However, AutocreatedGroup's "mapped" 
> table is the join() of community->group->autocreated_group.  The collection 
> of columns on this join() object is determined only once from the Table 
> objects given to it.   Adding a new column to one of those Table objects 
> after the fact is not affecting that collection so the column isn't mapped.
>
> There's ways this might work but they are definitely destabilizing, I'l 
> try to see if I can put something into 0.8 for that - basically I'd make 
> derived selectables add an event listener to the selectable they're derived 
> from, so that new column additions can propagate.
>
> However, to make this work for now you'd need to declare 
> topic_created_from_id on the AutocreatedGroup class directly.   A more 
> involved workaround would be to delay the production of mappings on the 
> hierarchy, though in 0.7 you need to do a slightly hacky approach for that 
> (it's what's described in 
> https://bitbucket.org/sqlalchemy/sqlalchemy/src/f47a971874b6/examples/declarative_reflection/declarative_reflection.py
> ).
>
>
>
> Thanks, again. I'm definitely not a SQL Alchemy expert, so your help is 
> appreciated greatly. 
>
> On Wednesday, August 15, 2012 11:21:41 AM UTC-4, Michael Bayer wrote:
>>
>>
>> On Aug 15, 2012, at 10:45 AM, Kuba Dolecki wrote:
>>
>> Hi,
>>
>> We've used joined table inheritance up to this point, but performance 
>> issues are making us explore single table inheritance. I'm running into a 
>> problem with mapper configuration. Using SQLAlchemy 0.7.8, Flask-SQLAlchemy 
>> 0.16, and Flask 0.8. Here is the inheritance: 
>>
>>
>> there are many things that are confusing about this model.
>>
>> 1.  I don't see any mention of __tablename__ anywhere.  Where is the 
>> table defined ?
>>
>> 2. You say this is single table inheritance, but I see each subclass has 
>> it's own primary key column, with a foreign key column pointing back to the 
>> superclass "table" (which isn't present).   This doesn't fit with single 
>> table inheritance at all.
>>
>> 3. There's a second re-definition of "polymorphic_on".   This isn't 
>> necessarily a mistake, as it is supported that loading a specific 
>> sub-hierarchy may have a different system of determining polymorphic 
>> identity - usually this applies to concrete schemes.  But with joined or 
>> single table inheritance it's extremely unusual, as the base table is what 
>> determines object identity in both of these cases.   If you load a 
>> collection of "community" rows, the ORM needs to decide from that base 
>> table alone what the subclass should be.   It won't see a 
>> sub-polymorphic-on unles

Re: [sqlalchemy] Single table inheritance - column not mapped

[Michael Bayer]

On Aug 15, 2012, at 11:41 AM, Kuba Dolecki wrote:

> You responded! Woot. Thanks. Here's some additional info:
> 
> 1. __tablename__ is generated automatically based on the class name. 
> Flask-SQLAlchemy does this for us. The __tablename__ for AutocreatedGroup and 
> TopicAutocreatedGroup model is both "autocreated_group".



> 
> 2. Sorry, I might have included too much code in my snippet. Group extends 
> Community through joined table inheritance (primary key with foreign key to 
> community). AutocreatedGroup extends Group in the same manner. The 
> relationship of interest is between AutocreatedGroup and 
> TopicAutocreatedGroup. TopicAutocreatedGroup does appear to inherit through a 
> single table. 

OH.   no, this wasn't *enough* code.  I always need to know the full expanse of 
classes and tables in play.  I'll recreate the full mapping now based on your 
descriptions here in order to reproduce.

> 
> 3. Yeah, I'm redefining polymorphic_on for Group since it depends on a 
> boolean switch. I'm doing the same for AutocreatedGroup since it depends on 
> the name of the class. 
> 
> AutocreatedGroup and TopicAutocreatedGroup do appear to have all of the same 
> columns of Group and Community. The AutocreatedGroup table does have the 
> topic_created_from_id column, but that same column doesn't appear in the 
> mapper. 

there is logic added to declarative that detects columns applied to a single 
table inheritance subclass, and moves them up to the superclass table, mapping 
it only for the subclass.  Due to the complex and deep chain of mappings here, 
this logic is likely failing.  let's create a test case and see.  (goes to do 
that).OK yeah this isn't going to work right now and would require a major 
architectural change to the expression system in order to accommodate.   The 
declarative system adds the new column to the AutocreatedGroup.__table__ 
correctly.  However, AutocreatedGroup's "mapped" table is the join() of 
community->group->autocreated_group.  The collection of columns on this join() 
object is determined only once from the Table objects given to it.   Adding a 
new column to one of those Table objects after the fact is not affecting that 
collection so the column isn't mapped.

There's ways this might work but they are definitely destabilizing, I'l try to 
see if I can put something into 0.8 for that - basically I'd make derived 
selectables add an event listener to the selectable they're derived from, so 
that new column additions can propagate.

However, to make this work for now you'd need to declare topic_created_from_id 
on the AutocreatedGroup class directly.   A more involved workaround would be 
to delay the production of mappings on the hierarchy, though in 0.7 you need to 
do a slightly hacky approach for that (it's what's described in 
https://bitbucket.org/sqlalchemy/sqlalchemy/src/f47a971874b6/examples/declarative_reflection/declarative_reflection.py).


> 
> Thanks, again. I'm definitely not a SQL Alchemy expert, so your help is 
> appreciated greatly. 
> 
> On Wednesday, August 15, 2012 11:21:41 AM UTC-4, Michael Bayer wrote:
> 
> On Aug 15, 2012, at 10:45 AM, Kuba Dolecki wrote:
> 
>> Hi,
>> 
>> We've used joined table inheritance up to this point, but performance issues 
>> are making us explore single table inheritance. I'm running into a problem 
>> with mapper configuration. Using SQLAlchemy 0.7.8, Flask-SQLAlchemy 0.16, 
>> and Flask 0.8. Here is the inheritance: 
> 
> there are many things that are confusing about this model.
> 
> 1.  I don't see any mention of __tablename__ anywhere.  Where is the table 
> defined ?
> 
> 2. You say this is single table inheritance, but I see each subclass has it's 
> own primary key column, with a foreign key column pointing back to the 
> superclass "table" (which isn't present).   This doesn't fit with single 
> table inheritance at all.
> 
> 3. There's a second re-definition of "polymorphic_on".   This isn't 
> necessarily a mistake, as it is supported that loading a specific 
> sub-hierarchy may have a different system of determining polymorphic identity 
> - usually this applies to concrete schemes.  But with joined or single table 
> inheritance it's extremely unusual, as the base table is what determines 
> object identity in both of these cases.   If you load a collection of 
> "community" rows, the ORM needs to decide from that base table alone what the 
> subclass should be.   It won't see a sub-polymorphic-on unless the original 
> query was against that subclass.
> 
>> 
>> class Community(BaseModel, db.Model):
>># Bar
>> 
>> class Group(Community):
>> ###
>> Group utilized joined table inheritance to extend Community
>> ###
>> id  = db.Column(db.Integer, db.ForeignKey('community.id'), 
>> primary_key=True, nullable=False)
>> autocreated = db.Column(db.Boolean, nullable=False, default=False)
>> 
>> @declared_attr
>> def __mapper_args__(cls):
>> name = cls.

Re: [sqlalchemy] Single table inheritance - column not mapped

[Kuba Dolecki]

You responded! Woot. Thanks. Here's some additional info:

1. __tablename__ is generated automatically based on the class name. 
Flask-SQLAlchemy does this for us. The __tablename__ for AutocreatedGroup 
and TopicAutocreatedGroup model is both "autocreated_group".

2. Sorry, I might have included too much code in my snippet. Group extends 
Community through joined table inheritance (primary key with foreign key to 
community). AutocreatedGroup extends Group in the same manner. The 
relationship of interest is between AutocreatedGroup and 
TopicAutocreatedGroup. TopicAutocreatedGroup does appear to inherit through 
a single table. 

3. Yeah, I'm redefining polymorphic_on for Group since it depends on a 
boolean switch. I'm doing the same for AutocreatedGroup since it depends on 
the name of the class. 

AutocreatedGroup and TopicAutocreatedGroup do appear to have all of the 
same columns of Group and Community. The AutocreatedGroup table does have 
the topic_created_from_id column, but that same column doesn't appear in 
the mapper. 

Thanks, again. I'm definitely not a SQL Alchemy expert, so your help is 
appreciated greatly. 

On Wednesday, August 15, 2012 11:21:41 AM UTC-4, Michael Bayer wrote:
>
>
> On Aug 15, 2012, at 10:45 AM, Kuba Dolecki wrote:
>
> Hi,
>
> We've used joined table inheritance up to this point, but performance 
> issues are making us explore single table inheritance. I'm running into a 
> problem with mapper configuration. Using SQLAlchemy 0.7.8, Flask-SQLAlchemy 
> 0.16, and Flask 0.8. Here is the inheritance: 
>
>
> there are many things that are confusing about this model.
>
> 1.  I don't see any mention of __tablename__ anywhere.  Where is the table 
> defined ?
>
> 2. You say this is single table inheritance, but I see each subclass has 
> it's own primary key column, with a foreign key column pointing back to the 
> superclass "table" (which isn't present).   This doesn't fit with single 
> table inheritance at all.
>
> 3. There's a second re-definition of "polymorphic_on".   This isn't 
> necessarily a mistake, as it is supported that loading a specific 
> sub-hierarchy may have a different system of determining polymorphic 
> identity - usually this applies to concrete schemes.  But with joined or 
> single table inheritance it's extremely unusual, as the base table is what 
> determines object identity in both of these cases.   If you load a 
> collection of "community" rows, the ORM needs to decide from that base 
> table alone what the subclass should be.   It won't see a 
> sub-polymorphic-on unless the original query was against that subclass.
>
>
> class Community(BaseModel, db.Model):
># Bar
>
> class Group(Community):
> ###
> Group utilized joined table inheritance to extend Community
> ###
> id  = db.Column(db.Integer, db.ForeignKey('community.id'), 
> primary_key=True, nullable=False)
> autocreated = db.Column(db.Boolean, nullable=False, default=False)
>
> @declared_attr
> def __mapper_args__(cls):
> name = cls._name()
> identity = {
> 'polymorphic_on': case([
> (cls.autocreated == True, "autocreated_group"),
> (cls.autocreated == False, "group")
> ]),
> 'polymorphic_identity': name
> }
> return identity
>
> class AutocreatedGroup(Group):
> ###
> AutocreatedGroup extends Group through joined table inheritance
> ###
> id = db.Column(db.Integer, db.ForeignKey('group.id'), 
> primary_key=True, \
> nullable=False)
> 
> @declared_attr
> def __mapper_args__(cls):
> name = cls._name()
> identity = {
> 'polymorphic_on': cls.autocreated_type,
> 'polymorphic_identity': name
> }
> return identity
>
> class TopicAutocreatedGroup(AutocreatedGroup):
> ###
> TopicAutocreatedGroup extends AutocreatedGroup through single 
> table inheritance
> ###
> @declared_attr
> def __mapper_args__(cls):
> return {'polymorphic_identity': cls._name()}
>
> topic_created_from_id = db.Column(
> db.Integer,
> db.ForeignKey('topic.id'),
> nullable = True
> )
> topic_created_from = db.relation(
> "Topic",
> primaryjoin="Topic.id == 
> TopicAutocreatedGroup.topic_created_from_id",
> backref=db.backref("topic_groups", uselist=True),
> uselist=False, lazy='subquery'
> )
>
> Here is the stacktrace: 
> Traceback (most recent call last):
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1518, in 
> __call__
> return self.wsgi_app(environ, start_response)
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1506, in 
> wsgi_app
> response = self.make_response(self.handle_exception(e))
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1504, in 
> wsgi_app
> response = self.full_di

Re: [sqlalchemy] Single table inheritance - column not mapped

[Michael Bayer]

On Aug 15, 2012, at 10:45 AM, Kuba Dolecki wrote:

> Hi,
> 
> We've used joined table inheritance up to this point, but performance issues 
> are making us explore single table inheritance. I'm running into a problem 
> with mapper configuration. Using SQLAlchemy 0.7.8, Flask-SQLAlchemy 0.16, and 
> Flask 0.8. Here is the inheritance: 

there are many things that are confusing about this model.

1.  I don't see any mention of __tablename__ anywhere.  Where is the table 
defined ?

2. You say this is single table inheritance, but I see each subclass has it's 
own primary key column, with a foreign key column pointing back to the 
superclass "table" (which isn't present).   This doesn't fit with single table 
inheritance at all.

3. There's a second re-definition of "polymorphic_on".   This isn't necessarily 
a mistake, as it is supported that loading a specific sub-hierarchy may have a 
different system of determining polymorphic identity - usually this applies to 
concrete schemes.  But with joined or single table inheritance it's extremely 
unusual, as the base table is what determines object identity in both of these 
cases.   If you load a collection of "community" rows, the ORM needs to decide 
from that base table alone what the subclass should be.   It won't see a 
sub-polymorphic-on unless the original query was against that subclass.

> 
> class Community(BaseModel, db.Model):
># Bar
> 
> class Group(Community):
> ###
> Group utilized joined table inheritance to extend Community
> ###
> id  = db.Column(db.Integer, db.ForeignKey('community.id'), 
> primary_key=True, nullable=False)
> autocreated = db.Column(db.Boolean, nullable=False, default=False)
> 
> @declared_attr
> def __mapper_args__(cls):
> name = cls._name()
> identity = {
> 'polymorphic_on': case([
> (cls.autocreated == True, "autocreated_group"),
> (cls.autocreated == False, "group")
> ]),
> 'polymorphic_identity': name
> }
> return identity
> 
> class AutocreatedGroup(Group):
> ###
> AutocreatedGroup extends Group through joined table inheritance
> ###
> id = db.Column(db.Integer, db.ForeignKey('group.id'), primary_key=True, \
> nullable=False)
> 
> @declared_attr
> def __mapper_args__(cls):
> name = cls._name()
> identity = {
> 'polymorphic_on': cls.autocreated_type,
> 'polymorphic_identity': name
> }
> return identity
> 
> class TopicAutocreatedGroup(AutocreatedGroup):
> ###
> TopicAutocreatedGroup extends AutocreatedGroup through single table 
> inheritance
> ###
> @declared_attr
> def __mapper_args__(cls):
> return {'polymorphic_identity': cls._name()}
> 
> topic_created_from_id = db.Column(
> db.Integer,
> db.ForeignKey('topic.id'),
> nullable = True
> )
> topic_created_from = db.relation(
> "Topic",
> primaryjoin="Topic.id == TopicAutocreatedGroup.topic_created_from_id",
> backref=db.backref("topic_groups", uselist=True),
> uselist=False, lazy='subquery'
> )
> 
> Here is the stacktrace: 
> Traceback (most recent call last):
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1518, in 
> __call__
> return self.wsgi_app(environ, start_response)
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1506, in 
> wsgi_app
> response = self.make_response(self.handle_exception(e))
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1504, in 
> wsgi_app
> response = self.full_dispatch_request()
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1264, in 
> full_dispatch_request
> rv = self.handle_user_exception(e)
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1260, in 
> full_dispatch_request
> rv = self.preprocess_request()
>   File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1387, in 
> preprocess_request
> rv = func()
>   File "/srv/franklin-api/api/helpers/login_manager.py", line 53, in 
> _load_user
> user = User.for_auth_token(token)
>   File "/srv/franklin-api/api/models/user.py", line 584, in for_auth_token
> user_for_token = cls.query.filter(cls.auth_token == auth_token).first()
>   File "/srv/.env/lib/python2.6/site-packages/flask_sqlalchemy.py", line 394, 
> in __get__
> mapper = orm.class_mapper(type)
>   File "/srv/.env/lib/python2.6/site-packages/sqlalchemy/orm/util.py", line 
> 660, in class_mapper
> mapperlib.configure_mappers()
>   File "/srv/.env/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py", line 
> 2255, in configure_mappers
> raise e
> InvalidRequestError: One or more mappers failed to initialize - can't proceed 
> with initialization of other mappers.  Original exception was: Class  'api.models.autoc

[sqlalchemy] Single table inheritance - column not mapped

[Kuba Dolecki]

Hi,

We've used joined table inheritance up to this point, but performance 
issues are making us explore single table inheritance. I'm running into a 
problem with mapper configuration. Using SQLAlchemy 0.7.8, Flask-SQLAlchemy 
0.16, and Flask 0.8. Here is the inheritance: 

class Community(BaseModel, db.Model):
   # Bar

class Group(Community):
###
Group utilized joined table inheritance to extend Community
###
id  = db.Column(db.Integer, db.ForeignKey('community.id'), 
primary_key=True, nullable=False)
autocreated = db.Column(db.Boolean, nullable=False, default=False)

@declared_attr
def __mapper_args__(cls):
name = cls._name()
identity = {
'polymorphic_on': case([
(cls.autocreated == True, "autocreated_group"),
(cls.autocreated == False, "group")
]),
'polymorphic_identity': name
}
return identity

class AutocreatedGroup(Group):
###
AutocreatedGroup extends Group through joined table inheritance
###
id = db.Column(db.Integer, db.ForeignKey('group.id'), primary_key=True, 
\
nullable=False)

@declared_attr
def __mapper_args__(cls):
name = cls._name()
identity = {
'polymorphic_on': cls.autocreated_type,
'polymorphic_identity': name
}
return identity

class TopicAutocreatedGroup(AutocreatedGroup):
###
TopicAutocreatedGroup extends AutocreatedGroup through single table 
inheritance
###
@declared_attr
def __mapper_args__(cls):
return {'polymorphic_identity': cls._name()}

topic_created_from_id = db.Column(
db.Integer,
db.ForeignKey('topic.id'),
nullable = True
)
topic_created_from = db.relation(
"Topic",
primaryjoin="Topic.id == 
TopicAutocreatedGroup.topic_created_from_id",
backref=db.backref("topic_groups", uselist=True),
uselist=False, lazy='subquery'
)

Here is the stacktrace: 
Traceback (most recent call last):
  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1518, in 
__call__
return self.wsgi_app(environ, start_response)
  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1506, in 
wsgi_app
response = self.make_response(self.handle_exception(e))
  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1504, in 
wsgi_app
response = self.full_dispatch_request()
  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1264, in 
full_dispatch_request
rv = self.handle_user_exception(e)
  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1260, in 
full_dispatch_request
rv = self.preprocess_request()
  File "/srv/.env/lib/python2.6/site-packages/flask/app.py", line 1387, in 
preprocess_request
rv = func()
  File "/srv/franklin-api/api/helpers/login_manager.py", line 53, in 
_load_user
user = User.for_auth_token(token)
  File "/srv/franklin-api/api/models/user.py", line 584, in for_auth_token
user_for_token = cls.query.filter(cls.auth_token == auth_token).first()
  File "/srv/.env/lib/python2.6/site-packages/flask_sqlalchemy.py", line 
394, in __get__
mapper = orm.class_mapper(type)
  File "/srv/.env/lib/python2.6/site-packages/sqlalchemy/orm/util.py", line 
660, in class_mapper
mapperlib.configure_mappers()
  File "/srv/.env/lib/python2.6/site-packages/sqlalchemy/orm/mapper.py", 
line 2255, in configure_mappers
raise e
InvalidRequestError: One or more mappers failed to initialize - can't 
proceed with initialization of other mappers.  Original exception was: 
Class  does not 
have a mapped column named 'topic_created_from_id'


It appears the Columns inside classes that inherit through single table 
inheritance are not included in the mapper. Any idea of what may be going 
wrong?

Thank you,
Kuba

P.S. What should __mapper_args__ look like for AutocreatedGroup? Should 
"topic_created_from_id" be automatically included in the 
"excluded_properties" value? Currently, it's empty if I print it out. 

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/l3a-3tBjaf4J.
To post to this group, send email to sqlalchemy@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] single table inheritance + composite primary key problem

[Vladimir Iliev]

On Saturday, June 16, 2012 4:52:14 PM UTC+3, Michael Bayer wrote:
>
>
> On Jun 16, 2012, at 9:37 AM, Vladimir Iliev wrote: 
>
> > hi, 
> > 
> > what's wrong with the following mapping ? 
> > 
> > TYPE_DEFAULT = 1 
> > 
> > class BaseFriend(DeclarativeBase): 
> > 
> > __tablename__ = 'friends' 
> > __table_args__ = {'mysql_engine': 'InnoDB', 
> >   'mysql_charset': 'utf8'} 
> > 
> > type = Column(SmallInteger, primary_key=True) 
> > 
> > user_id = Column(None, ForeignKey('users.id', ondelete='CASCADE'), 
> primary_key=True) 
> > 
> > friend_id = Column(None, ForeignKey('users.id', 
> ondelete='CASCADE')) 
> > 
> > friend_external_id = Column(String(100), primary_key=True) 
> > 
> > __mapper_args__ = dict( 
> > polymorphic_on=type, 
> > polymorphic_identity=TYPE_DEFAULT, 
> > ) 
> > 
> > TYPE_FACEBOOK = 2 
> > 
> > class FacebookFriend(BaseFriend): 
> > 
> > __mapper_args__ = dict( 
> > polymorphic_identity=TYPE_FACEBOOK, 
> > ) 
> > 
> > User.facebook_friends = relationship(FacebookFriend, 
> > lazy=False, 
> > primaryjoin=FacebookFriend.user_id==User.id, 
> > backref='user' 
> > ) 
> > 
> > when i try to session.query(User).first() i'm getting: 
> > 
> > New instance  with identity key ( '...Friend.BaseFriend'>, (2, 75L, u'11288076626')) conflicts with 
> persistent instance  
>
> Two friends with the same identity key, that is, associated with the same 
> type, user_id (and hence User object), and friend_external_id. 
>
> I'm not sure what friend_external_id is but if it just refers to some 
> outside source, I doubt you want want the "user_id" column to be part of 
> the primary key here, that establishes one-to-one, not one-to-many. 
>

what i need is friend model with pk (facebook, user id (multiple users 
might have same external id as friend so it must be part of the key), and 
friend external id (which in this case is facebook uid)) ... i can probably 
just use autoincrement integer for primary key but there would be millions 
friend rows and i prefer if it's tiny. i'm adding and deleting 
FacebookFriend objects without problems but it fails when i try to 
session.query(User) with facebook_friends eagerloaded or 
session.merge(user) if user is loaded from memcached

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/1m3eNDxYORsJ.
To post to this group, send email to sqlalchemy@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] single table inheritance + composite primary key problem

[Michael Bayer]

On Jun 16, 2012, at 9:37 AM, Vladimir Iliev wrote:

> hi,
> 
> what's wrong with the following mapping ?
> 
> TYPE_DEFAULT = 1
> 
> class BaseFriend(DeclarativeBase):
> 
> __tablename__ = 'friends'
> __table_args__ = {'mysql_engine': 'InnoDB',
>   'mysql_charset': 'utf8'}
> 
> type = Column(SmallInteger, primary_key=True)
> 
> user_id = Column(None, ForeignKey('users.id', ondelete='CASCADE'), 
> primary_key=True)
> 
> friend_id = Column(None, ForeignKey('users.id', ondelete='CASCADE'))
> 
> friend_external_id = Column(String(100), primary_key=True)
> 
> __mapper_args__ = dict(
> polymorphic_on=type,
> polymorphic_identity=TYPE_DEFAULT,
> )
> 
> TYPE_FACEBOOK = 2
> 
> class FacebookFriend(BaseFriend):
> 
> __mapper_args__ = dict(
> polymorphic_identity=TYPE_FACEBOOK,
> )
> 
> User.facebook_friends = relationship(FacebookFriend,
> lazy=False,
> primaryjoin=FacebookFriend.user_id==User.id,
> backref='user'
> )
> 
> when i try to session.query(User).first() i'm getting:
> 
> New instance  with identity key ( '...Friend.BaseFriend'>, (2, 75L, u'11288076626')) conflicts with 
> persistent instance 

Two friends with the same identity key, that is, associated with the same type, 
user_id (and hence User object), and friend_external_id.

I'm not sure what friend_external_id is but if it just refers to some outside 
source, I doubt you want want the "user_id" column to be part of the primary 
key here, that establishes one-to-one, not one-to-many.


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] single table inheritance + composite primary key problem

[Vladimir Iliev]

hi,

what's wrong with the following mapping ?

TYPE_DEFAULT = 1

class BaseFriend(DeclarativeBase):

__tablename__ = 'friends'
__table_args__ = {'mysql_engine': 'InnoDB',
  'mysql_charset': 'utf8'}

type = Column(SmallInteger, primary_key=True)

user_id = Column(None, ForeignKey('users.id', ondelete='CASCADE'), 
primary_key=True)

friend_id = Column(None, ForeignKey('users.id', ondelete='CASCADE'))

friend_external_id = Column(String(100), primary_key=True)

__mapper_args__ = dict(
polymorphic_on=type,
polymorphic_identity=TYPE_DEFAULT,
)

TYPE_FACEBOOK = 2

class FacebookFriend(BaseFriend):

__mapper_args__ = dict(
polymorphic_identity=TYPE_FACEBOOK,
)

User.facebook_friends = relationship(FacebookFriend,
lazy=False,
primaryjoin=FacebookFriend.user_id==User.id,
backref='user'
)

when i try to session.query(User).first() i'm getting:

New instance  with identity key (, (2, 75L, u'11288076626')) conflicts with 
persistent instance 

i've tried to define the relationship on both sides with the same results

thanks

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/k_QD0fDYRJ0J.
To post to this group, send email to sqlalchemy@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] Single table inheritance

[Kent Bower]

http://docs.sqlalchemy.org/en/latest/orm/mapper_config.html?highlight=polymorphic_on#sqlalchemy.orm.mapper.Mapper.polymorphic_on


On 3/21/2012 10:46 AM, Michael Bayer wrote:

can you point me right to where it says that


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Single table inheritance

[Michael Bayer]

On Mar 21, 2012, at 10:29 AM, Kent Bower wrote:

> That will work for me, thanks!
> 
> P.S. make a note that the doc statement that it will be a future release 
> should be updated.

can you point me right to where it says that



> 
> On 3/21/2012 10:04 AM, Michael Bayer wrote:
>> also polymorphic_on can be any SQL expression in 0.7, like a CASE statement 
>> if you wanted.
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] Single table inheritance

[Kent Bower]

That will work for me, thanks!

P.S. make a note that the doc statement that it will be a future release 
should be updated.


On 3/21/2012 10:04 AM, Michael Bayer wrote:

also polymorphic_on can be any SQL expression in 0.7, like a CASE statement if 
you wanted.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Single table inheritance

[Michael Bayer]

On Mar 21, 2012, at 9:49 AM, Kent wrote:

> Hoping for advice:  I'm using sqlalchemy against a legacy application's 
> database design, most of which isn't in my control.  I have a situation where 
> single table inheritance should work beautifully but there is one catch: of 
> the 7 polymorphic sub classes, there is one which is allowed to change into 
> another.  The rest are immutable.  As an example, suppose a Employee were 
> allowed to be promoted to Manager.  
> 
> The docs state that the polymorphic_identity is a read only attribute and 
> that "Behavior is undefined if directly modified."  
> 
> I could work around this by mapping 2 polymorphic_identities to one class, 
> but as far a I can see, this can only be a single scalar value.  The docs say 
> that polymorphic_on may also be of other types besides Column in a future 
> SQLAlchemy release... I wonder if I can help sqla map either of 2 values to a 
> class in this way?
> 
> Any advice?

did you try just saying employee.type = 'manager', flushing, then loading a new 
copy of it ?  

also polymorphic_on can be any SQL expression in 0.7, like a CASE statement if 
you wanted.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Single table inheritance

[Kent]

Hoping for advice:  I'm using sqlalchemy against a legacy application's 
database design, most of which isn't in my control.  I have a situation 
where single table inheritance should work beautifully but there is one 
catch: of the 7 polymorphic sub classes, there is one which is allowed to 
change into another.  The rest are immutable.  As an example, suppose a 
Employee were allowed to be promoted to Manager.  

The docs state that the polymorphic_identity is a read only attribute and 
that "Behavior is undefined if directly modified."  

I could work around this by mapping 2 polymorphic_identities to one class, 
but as far a I can see, this can only be a single scalar value.  The docs 
say that polymorphic_on may also be of other types besides Column in a 
future SQLAlchemy release... I wonder if I can help sqla map either of 2 
values to a class in this way?

Any advice?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/vU1nLi3v8sEJ.
To post to this group, send email to sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[Michael Bayer]

works in 0.7.3.  will also work in 0.7.4  Just not the current tip at the 
moment.

the regular mapper() setup is equivalent (see 
http://www.sqlalchemy.org/docs/orm/mapper_config.html#classical-mappings) :

discriminator_expr = case(...)

mapper(Person, ..., polymorphic_on=discriminator_expr, 
polymorphic_identity='person', properties={
'discriminator':column_property(discriminator_expr)
})

@event.listens_for(Person, ...)
...

note the "event" part of this is just to set the "type" column automatically, 
it's not required.


On Dec 8, 2011, at 12:49 PM, kris wrote:

> Does this mean I can use this in 7.3?  or Do I need to download tip?
> 
> Also could you show me how this would work with old style mapper(...) s 
> instead of declarative_base?
> 
> Thanks,
> Kris
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/i2RCGGlPyKsJ.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[kris]

Does this mean I can use this in 7.3?  or Do I need to download tip?

Also could you show me how this would work with old style mapper(...) s 
instead of declarative_base?

Thanks,
Kris

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/i2RCGGlPyKsJ.
To post to this group, send email to sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[Michael Bayer]

OK it works (not yet in tip though).


On Dec 8, 2011, at 11:43 AM, Michael Bayer wrote:

> the recipe is broken, please wait until i have time to fix it, thanks.
> 
> 
> On Dec 8, 2011, at 11:27 AM, Michael Bayer wrote:
> 
>> 
>> On Dec 7, 2011, at 11:08 PM, kris wrote:
>> 
>>> Hmm, I think I want the opposite behavior.  I was the defualt mapper to 
>>> Resource unless a more specific mapper has been defined.
>>> I never defined a 'C' or 'D' and I would like simple get a Resource when no 
>>> better mapper is found. 
>>> i.e. I get Resource class for C, D and get a Resource_A for A and a 
>>> Resource_B for B.  
>>> 
>>> Is this possible?
>> 
>> So you're looking for a row that has "C" or "D" in it to act as though it 
>> were "default".As an easy feature, that is ticket #2238 set on the 0.8 
>> milestone.
>> 
>> the workaround for now is to use column_property() in conjunction with a 
>> case() statement, I've posted the recipe for that at 
>> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/CaseBasedPolymorphicOn .
>> 
>> It will work in 0.7.3 but not in the current tip as there is a bug 
>> preventing it from working.
>> 
>> 
>> 
>>> 
>>> -- 
>>> You received this message because you are subscribed to the Google Groups 
>>> "sqlalchemy" group.
>>> To view this discussion on the web visit 
>>> https://groups.google.com/d/msg/sqlalchemy/-/FHmjireFL_EJ.
>>> To post to this group, send email to sqlalchemy@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 sqlalchemy@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 sqlalchemy@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 sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[Michael Bayer]

the recipe is broken, please wait until i have time to fix it, thanks.


On Dec 8, 2011, at 11:27 AM, Michael Bayer wrote:

> 
> On Dec 7, 2011, at 11:08 PM, kris wrote:
> 
>> Hmm, I think I want the opposite behavior.  I was the defualt mapper to 
>> Resource unless a more specific mapper has been defined.
>> I never defined a 'C' or 'D' and I would like simple get a Resource when no 
>> better mapper is found. 
>> i.e. I get Resource class for C, D and get a Resource_A for A and a 
>> Resource_B for B.  
>> 
>> Is this possible?
> 
> So you're looking for a row that has "C" or "D" in it to act as though it 
> were "default".As an easy feature, that is ticket #2238 set on the 0.8 
> milestone.
> 
> the workaround for now is to use column_property() in conjunction with a 
> case() statement, I've posted the recipe for that at 
> http://www.sqlalchemy.org/trac/wiki/UsageRecipes/CaseBasedPolymorphicOn .
> 
> It will work in 0.7.3 but not in the current tip as there is a bug preventing 
> it from working.
> 
> 
> 
>> 
>> -- 
>> You received this message because you are subscribed to the Google Groups 
>> "sqlalchemy" group.
>> To view this discussion on the web visit 
>> https://groups.google.com/d/msg/sqlalchemy/-/FHmjireFL_EJ.
>> To post to this group, send email to sqlalchemy@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 sqlalchemy@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 sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[Michael Bayer]

On Dec 7, 2011, at 11:08 PM, kris wrote:

> Hmm, I think I want the opposite behavior.  I was the defualt mapper to 
> Resource unless a more specific mapper has been defined.
> I never defined a 'C' or 'D' and I would like simple get a Resource when no 
> better mapper is found. 
> i.e. I get Resource class for C, D and get a Resource_A for A and a 
> Resource_B for B.  
> 
> Is this possible?

So you're looking for a row that has "C" or "D" in it to act as though it were 
"default".As an easy feature, that is ticket #2238 set on the 0.8 milestone.

the workaround for now is to use column_property() in conjunction with a case() 
statement, I've posted the recipe for that at 
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/CaseBasedPolymorphicOn .

It will work in 0.7.3 but not in the current tip as there is a bug preventing 
it from working.



> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/FHmjireFL_EJ.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[kris]

Hmm, I think I want the opposite behavior.  I was the defualt mapper to 
Resource unless a more specific mapper has been defined.
I never defined a 'C' or 'D' and I would like simple get a Resource when no 
better mapper is found. 
i.e. I get Resource class for C, D and get a Resource_A for A and a 
Resource_B for B.  

Is this possible?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/FHmjireFL_EJ.
To post to this group, send email to sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[Michael Bayer]

On Dec 7, 2011, at 2:53 AM, kris wrote:

> 
> I am using a single table scheme to store for a set of resource types.
> 
> I would like to load a specific class if a mapper is defined and use the base 
> class if no
> more specific version can be found.   Is there a way to do this?
> i.e.
> resource  = Table('resource', 
>Column('resource_type',  String),
>...
> 
> I also have a set of predefined helper classes
> class Resource, Resource_A, Resource_B, 
> 
> mapper(Resource, ...
>polymorphic_on = resource.c.resource_type, 
>   
>#polymorphic_identity = ''*"  # I think I need something like this
> mapper(Resource_A,...
>polymorphic_on = resource.c.resource_type, 
>   
>polymorphic_identity = ''A"
> mapper(Resource_B,...
>polymorphic_on = resource.c.resource_type, 
>   
>polymorphic_identity = ''B"
> 
> 
> Now if I have inserted types 
> A = Resource_A()
> B = Resource_B()
> C = Resource()
> C.resource_type = "C"
> D = Resource()
> D.resource_type = "D"
> session.add([A,B,C,D])
> 
> And then I do 
> session.query(Resource)
> 
> Is there a way to specify the polymorphic_identity of Resource is default ?

sure, just put a polymorphic_identity on your base Resource class and that's 
the class you'll get if that identity value is the one retrieved from the row.

However in your example above, the Resource you have where you stuck a "D" on 
it, you'd get a Resource_D back when querying.  It's best not to manually set 
discriminators like that as you'll have inconsistent typing between the "to be 
flushed" set of objects and the "returned from a query" set.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Single Table Inheritance, multiple mappers and default (base) mapper

[kris]

I am using a single table scheme to store for a set of resource types.

I would like to load a specific class if a mapper is defined and use the 
base class if no
more specific version can be found.   Is there a way to do this?
i.e.
resource  = Table('resource', 
   Column('resource_type',  String),
   ...

I also have a set of predefined helper classes
class Resource, Resource_A, Resource_B, 

mapper(Resource, ...
   polymorphic_on = resource.c.resource_type,   

   #polymorphic_identity = ''*"  # I think I need something like this
mapper(Resource_A,...
   polymorphic_on = resource.c.resource_type,   

   polymorphic_identity = ''A"
mapper(Resource_B,...
   polymorphic_on = resource.c.resource_type,   

   polymorphic_identity = ''B"


Now if I have inserted types 
A = Resource_A()
B = Resource_B()
C = Resource()
C.resource_type = "C"
D = Resource()
D.resource_type = "D"
session.add([A,B,C,D])

And then I do 
session.query(Resource)

Is there a way to specify the polymorphic_identity of Resource is default ?

Thanks.

   

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/CTUpcBdq-IUJ.
To post to this group, send email to sqlalchemy@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] Single table inheritance + join weirdness

[Michael Bayer]

heh :)   the thing about tests is that anyone who gets a couple of days of 
practice writing them in our style can be very productive...but everyone has 
just "one issue" they need so that threshold isn't passed ...

I have a boatload of tests to write for the 0.7.4 milestone so I'll be blocking 
out a day sometime soon to plow through a bunch.




On Nov 16, 2011, at 4:53 AM, Alex Grönholm wrote:

> Yeah my bad, the original query does indeed query for (Z.id, B.name). I had 
> just changed it to A.name to get the printout for the "workaround" query and 
> forgot to change it back before pasting here.
> If there's something I can do to contribute (not sure I'm qualified to write 
> those tests), do tell.
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/lGxM13xQhu8J.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] Single table inheritance + join weirdness

[Alex Grönholm]

Yeah my bad, the original query does indeed query for (Z.id, B.name). I had 
just changed it to A.name to get the printout for the "workaround" query 
and forgot to change it back before pasting here.
If there's something I can do to contribute (not sure I'm qualified to 
write those tests), do tell.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/lGxM13xQhu8J.
To post to this group, send email to sqlalchemy@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] Single table inheritance + join weirdness

[Michael Bayer]

On Nov 15, 2011, at 11:39 PM, Michael Bayer wrote:

> 
> On Nov 15, 2011, at 8:54 PM, Alex Grönholm wrote:
> 
>> query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
>> print query
>> 
>> # WORKAROUND: 
>> # query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
>> #  ^- use the superclass instead
> 
> I'd note that these two queries are written exactly the same, and I'd assume 
> the first one intends to ask for "B.name".   If you're telling the ORM that 
> you'd like "B" objects, the WHERE criterion is added (which in a significant 
> plurality of cases is necessary), since it would be invalid to return rows 
> that are of type A but not of type B, i.e. those where the value of "disc" is 
> not 'b'.
> 
> In this case, the outerjoin to Z.b also adds this same criteria to the ON 
> clause, thereby making the WHERE redundant but because its an outer join also 
> affecting the results, thus escalating it to "erroneous".SQLAlchemy can't 
> however figure out that the outer join elsewhere in the query is sufficient 
> in order to apply the necessary limiting of rows.Its a bug (ticket 
> http://www.sqlalchemy.org/trac/ticket/2328) but unfortunately it's extremely 
> difficult to track and correlate these two conditions accurately, else it 
> would already be doing so.

The ticket does have a patch that seems to resolve this but it needs a lot more 
tests before it can be committed.

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Single table inheritance + join weirdness

[Michael Bayer]

On Nov 15, 2011, at 8:54 PM, Alex Grönholm wrote:

> query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
> print query
> 
> # WORKAROUND: 
> # query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
> #  ^- use the superclass instead

I'd note that these two queries are written exactly the same, and I'd assume 
the first one intends to ask for "B.name".   If you're telling the ORM that 
you'd like "B" objects, the WHERE criterion is added (which in a significant 
plurality of cases is necessary), since it would be invalid to return rows that 
are of type A but not of type B, i.e. those where the value of "disc" is not 
'b'.

In this case, the outerjoin to Z.b also adds this same criteria to the ON 
clause, thereby making the WHERE redundant but because its an outer join also 
affecting the results, thus escalating it to "erroneous".SQLAlchemy can't 
however figure out that the outer join elsewhere in the query is sufficient in 
order to apply the necessary limiting of rows.Its a bug (ticket 
http://www.sqlalchemy.org/trac/ticket/2328) but unfortunately it's extremely 
difficult to track and correlate these two conditions accurately, else it would 
already be doing so.








-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Single table inheritance + join weirdness

[Alex Grönholm]

I encountered a little strangeness when joining to a class using single 
table inheritance.
I was wondering why I got no results for one particular query.
This was originally encountered with PostgreSQL but was successfully 
reproduced with SQLite.
Is this a bug or a user error?
SNIPSNIP

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.engine import create_engine
from sqlalchemy.schema import Column, ForeignKey
from sqlalchemy.types import Integer, Unicode
from sqlalchemy.orm.session import Session
from sqlalchemy.orm import relationship


Base = declarative_base(create_engine('sqlite:///'))

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
disc = Column(Unicode, nullable=False)
name = Column(Unicode)

__mapper_args__ = {'polymorphic_on': disc}


class B(A):
__mapper_args__ = {'polymorphic_identity': 'b'}


class Z(Base):
__tablename__ = 'z'
id = Column(Integer, primary_key=True)
b_id = Column(Integer, ForeignKey(B.id))

b = relationship(B)

Base.metadata.create_all()
session = Session()
query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
print query

#SELECT z.id AS z_id, z.b_id AS z_b_id, a.name AS a_name 
#FROM z LEFT OUTER JOIN a ON a.id = z.b_id AND a.disc IN (?) 
#WHERE z.id = ? AND a.disc IN (?)
#   ^- why is this condition here?
#
# WORKAROUND: 
# query = session.query(Z, A.name).outerjoin(Z.b).filter(Z.id == 1)
#  ^- use the superclass instead
#
#SELECT z.id AS z_id, z.b_id AS z_b_id, a.name AS a_name 
#FROM z LEFT OUTER JOIN a ON a.id = z.b_id AND a.disc IN (?) 
#WHERE z.id = ?
# ^- no extra WHERE condition this time around

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/RB_1UbBZRogJ.
To post to this group, send email to sqlalchemy@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] Single Table Inheritance with mult-column keys

[Michael Bayer]

here's a pastebin of it:

http://pastebin.com/z8XWsv2e


On Aug 16, 2011, at 7:42 PM, Michael Bayer wrote:

> 
> On Aug 16, 2011, at 5:37 PM, Mike Gilligan wrote:
> 
>> I have a single table that looks similar to the following:
>> 
>> class Equipment(Base):
>> type = Column(CHAR(1), primary_key=True)
>> sub_type = Column(CHAR(1), primary_key=True)
>> code = Column(CHAR(5), primary_key=True)
>> 
>> 
>> For historical purposes, I cannot modify this table. I would like to setup 
>> multi-level inheritance similar to this, however it does not work:
>> 
>> class Equipment(Base):
>> type = Column(CHAR(1), primary_key=True)
>> sub_type = Column(CHAR(1), primary_key=True)
>> code = Column(CHAR(5), primary_key=True)
>> __mapper_args__ = {'polymorphic_on': type}
>> 
>> 
>> class Vehicle(Equipment):
>>  __mapper_args__ = {'polymorphic_identity': 'V', 'polymorphic_on': 
>> sub_type}
>> 
>> 
>> class Bus(Vehicle)
>>  __mapper_args__ = {'polymorphic_identity': 'B'}
>> 
>> 
>> class Rail(Vehicle)
>>  __mapper_args__ = {'polymorphic_identity': 'R'}
>> 
>> 
>> I can concatenate the multiple column values into a single discriminator 
>> column_property but then I do not have an easy way to retrieve all vehicles. 
>> Any ideas?
> 
> The inheritance querying does handle multi-level inheritance so if your 
> discriminator was on a concatenation of both things would work just fine, 
> i.e. if you queried for Vehicle, etc.   Each object's "polymorphic_identity" 
> would need to include the concatenated value, of course.
> 
> Unfortunately we're just beginning to support inheritance discriminators on a 
> column_property(), and you need to use a very specific approach to make this 
> work right now.  There's some tickets in trac to allow this functionality out 
> of the box.Attached is an example script which exercises the above 
> mapping - it uses declarative to minimize the impact of the workaround.
> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To post to this group, send email to sqlalchemy@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 view this discussion on the web visit 
>> https://groups.google.com/d/msg/sqlalchemy/-/qNSg1VvOrWwJ.
>> To post to this group, send email to sqlalchemy@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 sqlalchemy@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] Single Table Inheritance with mult-column keys

[Michael Bayer]

On Aug 16, 2011, at 5:37 PM, Mike Gilligan wrote:

> I have a single table that looks similar to the following:
> 
> class Equipment(Base):
> type = Column(CHAR(1), primary_key=True)
> sub_type = Column(CHAR(1), primary_key=True)
> code = Column(CHAR(5), primary_key=True)
> 
> 
> For historical purposes, I cannot modify this table. I would like to setup 
> multi-level inheritance similar to this, however it does not work:
> 
> class Equipment(Base):
> type = Column(CHAR(1), primary_key=True)
> sub_type = Column(CHAR(1), primary_key=True)
> code = Column(CHAR(5), primary_key=True)
> __mapper_args__ = {'polymorphic_on': type}
> 
> 
> class Vehicle(Equipment):
>  __mapper_args__ = {'polymorphic_identity': 'V', 'polymorphic_on': 
> sub_type}
> 
> 
> class Bus(Vehicle)
>  __mapper_args__ = {'polymorphic_identity': 'B'}
> 
> 
> class Rail(Vehicle)
>  __mapper_args__ = {'polymorphic_identity': 'R'}
> 
> 
> I can concatenate the multiple column values into a single discriminator 
> column_property but then I do not have an easy way to retrieve all vehicles. 
> Any ideas?

The inheritance querying does handle multi-level inheritance so if your 
discriminator was on a concatenation of both things would work just fine, i.e. 
if you queried for Vehicle, etc.   Each object's "polymorphic_identity" would 
need to include the concatenated value, of course.

Unfortunately we're just beginning to support inheritance discriminators on a 
column_property(), and you need to use a very specific approach to make this 
work right now.  There's some tickets in trac to allow this functionality out 
of the box.Attached is an example script which exercises the above mapping 
- it uses declarative to minimize the impact of the workaround.







> 
> -- 
> You received this message because you are subscribed to the Google Groups 
> "sqlalchemy" group.
> To view this discussion on the web visit 
> https://groups.google.com/d/msg/sqlalchemy/-/qNSg1VvOrWwJ.
> To post to this group, send email to sqlalchemy@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 sqlalchemy@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 view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/qNSg1VvOrWwJ. 
To post to this group, send email to sqlalchemy@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.


from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

class Equipment(Base):
__tablename__ = 'equipment'
type = Column(CHAR(1), primary_key=True)
sub_type = Column(CHAR(1), primary_key=True)
code = Column(CHAR(5), primary_key=True)

_concat_type_col = type + "|" + sub_type
concat_type = column_property(_concat_type_col)

@declared_attr
def __mapper_args__(cls):
# note that the "polymorphic_on" argument must be 
# explicit with all subclass mappers as well, as currently
# a column_property()-based polymorphic_on is not propagated
# to single-table inh subclasses.  An interim patch 
# in ticket #2227 fixes this but ticket #2238 seeks to 
# address this more generally
return {
'polymorphic_on': cls._concat_type_col, 
'polymorphic_identity' : cls.cls_type + "|" + cls.cls_sub_type}
cls_type = 'equip'
cls_sub_type = ''

def __init__(self, code):
self.type = self.cls_type
self.sub_type = self.cls_sub_type
self.code = code

class Vehicle(Equipment):
cls_sub_type = 'V'

class Bus(Vehicle):
cls_sub_type = 'B'

class Rail(Vehicle):
cls_sub_type = 'R'

e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add_all([
Bus(code='bus'),
Rail(code='rail'),
Vehicle(code='veh'),
Equipment(code='equip')
])
s.commit()

assert set(e.__class__ for e in s.query(Vehicle)) == set([Vehicle, Bus, Rail])
assert set(e.__class__ for e in s.query(Equipment)) == set([Equipment, Vehicle, Bus, Rail])
assert set(e.__class__ for e in s.query(Rail)) == set([Rail])

[sqlalchemy] Single Table Inheritance with mult-column keys

[Mike Gilligan]

I have a single table that looks similar to the following:

class Equipment(Base):
type = Column(CHAR(1), primary_key=True)
sub_type = Column(CHAR(1), primary_key=True)
code = Column(CHAR(5), primary_key=True)


For historical purposes, I cannot modify this table. I would like to setup 
multi-level inheritance similar to this, however it does not work:

class Equipment(Base):
type = Column(CHAR(1), primary_key=True)
sub_type = Column(CHAR(1), primary_key=True)
code = Column(CHAR(5), primary_key=True)
__mapper_args__ = {'polymorphic_on': type}


class Vehicle(Equipment):
 __mapper_args__ = {'polymorphic_identity': 'V', 'polymorphic_on': 
sub_type}


class Bus(Vehicle)
 __mapper_args__ = {'polymorphic_identity': 'B'}


class Rail(Vehicle)
 __mapper_args__ = {'polymorphic_identity': 'R'}


I can concatenate the multiple column values into a single discriminator 
column_property but then I do not have an easy way to retrieve all vehicles. 
Any ideas?

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To view this discussion on the web visit 
https://groups.google.com/d/msg/sqlalchemy/-/qNSg1VvOrWwJ.
To post to this group, send email to sqlalchemy@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] Single Table Inheritance with same Column Name

[Dan Getelman]

Hi all,

I'm running SQLAlchemy 0.7.0, against MySQL 5.1. I'm having issues
with a field that I'd like shared between the classes in single table
inheritance that I would like to have referred to differently in the
subclasses:

class Post(Base):
__tablename__ = 'posts'

id = Column(Integer, primary_key = True)
type = Column(String(40))
data = Column(String(255))

__mapper_args__ = {'polymorphic_on': type}

class Link(Post):
__mapper_args__ = {'polymorphic_identity': 'link'}

url = Column('data', String(255))

When I run this, I get the following error:
sqlalchemy.exc.ArgumentError: Column 'data' on class  conflicts with existing column 'posts.data'

I searched for answers, and came across the following post:
http://www.mail-archive.com/sqlalchemy@googlegroups.com/msg20077.html

When I tried its solution, however, I get the error " TypeError:
getattr(): attribute name must be string" when the code actually gets
called.

The offending line of code is "return getattr(obj, self.name)" from
"Module sqlalchemy.orm.descriptor_props:53 in fget". In this case,
self.name is an sqlalchemy.orm.attributes.InstrumentedAttribute
object. If I cast it to a string, getattr returns the data that I
wanted.

Is there a better way to do what I'm trying to do, or is this a bug in
the code?

Thanks,
Dan

-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] Single table inheritance subclass relations

[Michael Bayer]

Conor wrote:
> I'm having trouble with many-to-one relationships to subclasses that use
> single table inheritance. I have tried this in 0.5.8 and 0.6beta1.


that's what I get for not trying to answer every single email, a 6 month
old bug which I've missed.  This is very small and I've created and closed
ticket #1731 for this in r5402dd9d21e8 .



>
> Here is my test case:
>
> import sqlalchemy as sa
> from sqlalchemy import orm
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> employee = sa.Table("employee", Base.metadata,
> sa.Column("id", sa.Integer, primary_key=True),
> sa.Column("type", sa.Integer, nullable=False))
>
> class Employee(Base):
> __table__ = employee
> __mapper_args__ = {"polymorphic_identity": 0,
>"polymorphic_on": employee.c.type}
>
> class Engineer(Employee):
> __mapper_args__ = {"polymorphic_identity": 1}
>
> class Other(Base):
> __tablename__ = "other"
>
> # Fields
> id = sa.Column(sa.Integer, primary_key=True)
> engineer_id = sa.Column(sa.Integer,
> sa.ForeignKey("employee.id"))
>
> # Relations
> engineer = orm.relation("Engineer")
>
> print Other.engineer.has()
>
> I get this exception:
>
> AttributeError: 'ClauseList' object has no attribute 'proxy_set'
>
> This appears to be the same issue described at
> http://groups.google.com/group/sqlalchemy/browse_thread/thread/7fcb0c0c6e1c809f.
> Is this a bug? Currently, I am working around the issue by changing
> Other.engineer to refer to Employee and using
> Other.engineer.of_type(Engineer).has(), but this is less than ideal.
>
> NOTE: In my real use case, I have compound foreign keys and check
> constraints on Other to ensure that its engineer_id only points to
> Engineer objects. However that was not necessary to demonstrate the
> problem, so it has been omitted here.
>
> Thanks,
> -Conor
>
> --
> 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] Single table inheritance subclass relations

[Conor]

I'm having trouble with many-to-one relationships to subclasses that use
single table inheritance. I have tried this in 0.5.8 and 0.6beta1.

Here is my test case:

import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

employee = sa.Table("employee", Base.metadata,
sa.Column("id", sa.Integer, primary_key=True),
sa.Column("type", sa.Integer, nullable=False))

class Employee(Base):
__table__ = employee
__mapper_args__ = {"polymorphic_identity": 0,
   "polymorphic_on": employee.c.type}

class Engineer(Employee):
__mapper_args__ = {"polymorphic_identity": 1}

class Other(Base):
__tablename__ = "other"

# Fields
id = sa.Column(sa.Integer, primary_key=True)
engineer_id = sa.Column(sa.Integer,
sa.ForeignKey("employee.id"))

# Relations
engineer = orm.relation("Engineer")

print Other.engineer.has()

I get this exception:

AttributeError: 'ClauseList' object has no attribute 'proxy_set'

This appears to be the same issue described at
http://groups.google.com/group/sqlalchemy/browse_thread/thread/7fcb0c0c6e1c809f.
Is this a bug? Currently, I am working around the issue by changing
Other.engineer to refer to Employee and using
Other.engineer.of_type(Engineer).has(), but this is less than ideal.

NOTE: In my real use case, I have compound foreign keys and check
constraints on Other to ensure that its engineer_id only points to
Engineer objects. However that was not necessary to demonstrate the
problem, so it has been omitted here.

Thanks,
-Conor

-- 
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] single table inheritance through declarative.

[rajasekhar911]

Hi

I am facing a different problem in inheritance.
I am using the single table inheritance through declarative.
http://www.sqlalchemy.org/docs/05/reference/ext/declarative.html#single-table-inheritance

b=Base()
b.id='xxx'
b.name='xxx'
b.type='type1'
I am manually setting the type column of my base class.
but when i try to commit,it gives the IntegrityError
base.type may not be NULL u'INSERT INTO bases (id, name, type,
address, login, password) VALUES (?, ?, ?, ?, ?, ?)' ['xxx', 'xxx',
None, None, 'xxx','xxx']

I checked this thread, 
http://groups.google.com/group/sqlalchemy/browse_thread/thread/c646007dce37b11a
but I dont have access to the subclass to set the __class__


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] single table inheritance and changing object's type

[Vladimir Iliev]

how can i change the type of mapped object using single table inheritance?

thanks


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] single table inheritance : query a subtype and its subtypes

[GustaV]

Hello!

In a configuration looking like this:
class Parent:
   pass

class Child(Parent):
   pass

class ChildChild(Child):
   pass

I would like to query the Childs and all classes that inherits it
(here: ChildChild), but not Parent instances.
session.query(Child).all() returns Child type only ( SQL : "WHERE type
IN (2)" ), and I'd like to avoid to manually list the classes that
inherit Child class.

Thanks

GustaV
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@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] single table inheritance mapping

[David Gardner]

I have a situation similar to 
http://www.sqlalchemy.org/docs/05/mappers.html#advdatamapping_mapper_inheritance_single
with the exception that I have about 50 different types, but only one of 
them I want to subclass. So I was wondering id there was some option to 
polymorphic_identity to give it a list of values.

Currently what I do is I map the subclass against a query:
sql_file_node = select ([nodehierarchy_table], 
asset_table.c.type=='file').alias ('file_node_query')
mapper(Node, nodehierarchy_table .)
mapper(FileNode, sql_file_node..)

what I would like to do:
node_mapper = mapper(Node, nodehierarchy_table, 
polymorphic_on=nodehierarchy_table.c.type, )
mapper(FileNode, inherits=node_mapper, 
polymorphic_on=asset_table.c.type, polymorphic_identity='file' )

However when I do this, SA complains that there "AssertionError: No such 
polymorphic_identity '' is defined"


To complicate matters slightely this is a self referential table, 
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/examples/adjacencytree/basic_tree.py

This isn't a huge problem because I could continue to use SA the way I 
currently am.

-- 
David Gardner
Pipeline Tools Programmer, "Sid the Science Kid"
Jim Henson Creature Shop
[EMAIL PROTECTED]



--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

[sqlalchemy] Single table inheritance and mapping against a selectable

[Gaetan de Menten]

Hi all,

The way single table inheritance works seem a bit "unclean" to me: all
columns are available as properties in all classes. Even if they map
to the same table, ideally each object should only have a subset of
columns available, right? SQLAlchemy can't guess which object has
which columns, so I naturally tried to map each class to a select
including only their respective columns. But I couldn't get that to
work. So I tried without inheritance and couldn't get it to work
either (see attached file).

First, it complained there was no alias, while I *think* aliases
should only be necessary when the query is used in a subquery, but
anyway, after giving it its alias, it still doesn't work: I can't save
any data. Worse, it doesn't complain in any way, it's just that no
insert is issued to the database. I suspect a limitation in SQLAlchemy
(ie no inserts when there isn't at least one complete table in the
selectable or something of the like), but it might be something I'm
doing wrong. Any idea?

BTW: it's probably related to the fact the generated select query is:

SELECT subset.id AS subset_id, subset.data AS subset_data
FROM (SELECT common.id AS id, common.data AS data FROM common) AS
subset ORDER BY subset.oid

while I'd like to be able to do is simply:

SELECT common.id AS common_id, common.data AS common_data
FROM common
ORDER BY subset.oid

-- 
Gaëtan de Menten
http://openhex.org

--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---

from sqlalchemy  import *
from sqlalchemy.orm  import *

metadata = MetaData('sqlite:///')
metadata.bind.echo = True

common_table = Table('common', metadata,
Column('id', Integer, primary_key=True),
Column('data', Integer),
Column('extra', String(45)),
)


class Subset(object):
pass

subset_select = select([common_table.c.id, common_table.c.data]).alias('subset')
subset_mapper = mapper(Subset, subset_select)

metadata.create_all()

#common_table.insert().execute(subset_no=1)
#subset_select.insert().execute(subset_no=1)
#print [dict(e) for e in subset_select.execute().fetchall()]

sess = create_session()
l = Subset()
l.data = 1
sess.save(l)
sess.flush()
sess.clear()

print sess.query(Subset).list()

[sqlalchemy] single table inheritance questions

[Randall Smith]

Is there a way to inherit more than one level for single table 
inheritance?  Take this relationship for example:

 Animal -> Dog -> German Shepard

Say there are 10 animals; 5 are dogs and 2 are German Shepard.

 session.query(Animal).select() # Should yield 10 results.
 session.query(Dog).select() # Should yield 5 results.
 session.query(GermanShepard).select() # Should yield 2 results.

Second question.  Can the "polymorphic_identity" be extracted 
functionally?  Say that in the "type" column I have data like this:

 type value
 ---  
 default_heaters  electric
 default_heaters  gas
 default_floors   vinyl
 default_floors   carpet

All 4 records are DefaultAmenity instances, 2 are DefaultHeater and 2 
are DefaultFloor.  The latter two inherit from the first.  Identifying a 
DefaultAmenity record using the type column would require something like 
  table.c.type.startswith('default').

Randall


--~--~-~--~~~---~--~~
 You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---