Re: [sqlalchemy] DeclaredAttr

[mike bayer]

On 11/07/2016 05:43 PM, de...@devinfee.com wrote:

When using concrete table inheritance in the complete example below:

 1. why must I return an empty `Column(Integer)` instead of `None`, or
receive the following error: /"ArgumentError: Mapper


AbstractConcreteBase works in an intricate way in order to get this very 
unique effect of a class that is mapped, then has it's mapping changed 
to be something totally different once all the descendant classes have 
been produced.  For this reason, it is more brittle than an ordinary 
inheriting base class.


In this case, there are four things being done that are not expected by 
declarative.


The first is that RefClass.__tablename__ returns a non-None value for 
the RefClass class itself.  This has the effect of a "refclass" table 
being added to the Base.metadata which is not how AbstractConcreteBase 
works; the "table" to be mapped will ultimately be a big SELECT 
statement that is a UNION of all the descendant tables.   So you don't 
want a "refclass" table if you're using AbstractConcreteBase.


The second is that the @declared_attr.cascading feature, which is fairly 
recent, expects that the attribute is being used to map columns in all 
cases, and it also expects that it is used on a mixin class only.  So in 
this case, it's called only once, it returns None which immediately 
replaces RefClass.id, and it's gone.   "cascading" here should either 
work on non-mixins or more likely for now report that it's being 
mis-used if on a non-mixin, 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3847/declared_attrcascading-does-not-warn-error 
is added.   The reason you are forced to refer to RefClass by name is 
because the id() def is called before RefClass exists as a symbol in 
your program.


These two behaviors work together in that one you have __tablename__ 
return None for RefClass, you no longer need to play those same games 
with the id() attribute, and the whole thing works.


The third is that @declared_attr overall works completely differently 
when it returns a non-Column/ mapped value like "None" vs. a 
column/mapper attribute.  The value None can't be used in a mapping so 
it's assumed that you want to set up RefClass.id = None, that blows away 
any chance of the attribute being called for ARefClass.   The reason 
this is usually never a problem is because the parent class is either a 
mixin/__abstract__, in which case it's not called, or a mapped class, in 
which case it's called and it would return a Column.  But here, we're 
using the the very special / edge-case / brittle AbstractConcreteBase 
class, which sort of acts like both, and returning None here totally 
throws it off.  So you need to return a column like it has in the 
example at 
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/api.html?highlight=abstractconcretebase#sqlalchemy.ext.declarative.AbstractConcreteBase. 
 The Column is thrown away for RefClass because RefClass gets mapped to 
a non-Table object later on.   I'm not sure how to make this more 
intuitive at the moment but I added 
https://bitbucket.org/zzzeek/sqlalchemy/issues/3848/returning-none-or-anything-from.


The fourth issue is that AbstractConcreteBase is only useful for 
polymorphic loading, and for it to be worth the effort here you need 
concrete=True and polymorphic discriminators on your classes.  If you 
aren't going for polymorphic loading using a giant UNION statement (see 
example below), don't use AbstractConcreteBase, just use a normal base 
with __abstract__ = True.



class RefClass(AbstractConcreteBase, Base):

@declared_attr
def __tablename__(cls):
if cls.__name__ == 'RefClass':
return None
return cls.__name__.lower()

@declared_attr
def id(cls):
if cls.__name__ == 'RefClass':
return Column(Integer)
column_name = '{}.id'.format(cls.ref.__tablename__)
return Column(ForeignKey(column_name), primary_key=True)


class ARefClass(RefClass):
ref = AClass

__mapper_args__ = {"concrete": True, "polymorphic_identity": 'a'}


class BRefClass(RefClass):
ref = BClass

__mapper_args__ = {"concrete": True, "polymorphic_identity": 'b'}


output of db.query(RefClass).all():

 SELECT pjoin.id AS pjoin_id, pjoin.type AS pjoin_type
FROM (SELECT brefclass.id AS id, 'b' AS type
FROM brefclass UNION ALL SELECT arefclass.id AS id, 'a' AS type
FROM arefclass) AS pjoin









Mapper|ARefClass|arefclass could not assemble any primary key
columns for mapped table 'arefclass'"/
 2. why must I reference the class by name and not type in
`@declarad_attr`, unlike in a `classmethod`?
 3. how does the `__tablename__` `declaredattr` actually behave any
differently than the `id` `declaradattr`?










from sqlalchemy import (
Column,
ForeignKey,
Integer,
create_engine,
)
from sqlalchemy.ext.declarative import (
AbstractConcreteBase,
declared_attr,
declarative_base,

[sqlalchemy] DeclaredAttr

[devin]

When using concrete table inheritance in the complete example below:

   1. why must I return an empty `Column(Integer)` instead of `None`, or 
   receive the following error: *"ArgumentError: Mapper 
   Mapper|ARefClass|arefclass could not assemble any primary key columns for 
   mapped table 'arefclass'"*
   2. why must I reference the class by name and not type in 
   `@declarad_attr`, unlike in a `classmethod`?
   3. how does the `__tablename__` `declaredattr` actually behave any 
   differently than the `id` `declaradattr`?


from sqlalchemy import (
Column,
ForeignKey,
Integer,
create_engine,
)
from sqlalchemy.ext.declarative import (
AbstractConcreteBase,
declared_attr,
declarative_base,
has_inherited_table,
)
from sqlalchemy.orm import Session

Base = declarative_base()


class AClass(Base):
__tablename__ = 'aclass'
id = Column(Integer, primary_key=True)


class BClass(Base):
__tablename__ = 'bclass'
id = Column(Integer, primary_key=True)


class RefClass(AbstractConcreteBase, Base):
@declared_attr
def __tablename__(cls):
return cls.__name__.lower()

@declared_attr.cascading
def id(cls):
if cls.__name__ != 'RefClass':
column_name = '{}.id'.format(cls.ref.__tablename__)
return Column(ForeignKey(column_name), primary_key=True)
else:
# return  # Fails as described in Q1
return Column(Integer)


class ARefClass(RefClass):
ref = AClass


class BRefClass(RefClass):
ref = BClass


engine = create_engine('sqlite://', echo=True)
Base.metadata.bind = engine
Base.metadata.create_all()
db = Session(engine)


Thanks!
Devin

-- 
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] .count() hangs indefinitely

[mike bayer]

On 11/07/2016 02:50 AM, James Burke wrote:

Thanks Mike for your response.

The query is run against a staging db and the table only contains some
500 records.

But I will check the query as you have suggested to see what is going on.


Well since it's just count() causing the problem here, count() is going 
to wrap the inner query inside of a subquery.  Postgresql shouldn't be 
thrown off by that too much but seeing the SQL here is key.






Cheers


--
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 - 
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.