Single abstract declarative base, multiple inheriting children in one module...

[jkmacc]

Hi all,

I've got a module defining a number of abstract declarative classes.  They represent standard generic classes/tables.  

I'd like to take one of these abstract classes and subclass more than once, adding a 'schema' name to '__table_args__' and a '__tablename__'.  This to me is like making a specific 'realization' of the abstract class structure.  I want to keep the columns (with info dictionaries) and constraints in the abstract class, but make a 'realization' of the abstract class as different tables with different names and schema.  

When I do this, however, I get warnings and errors I don't understand. Here's an example:

% ------------------------  code  --------------------------------------------------------------------------

from sqlalchemy import Column, Numeric, String, Date, PrimaryKeyConstraint

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Students(Base):

    __abstract__ = True

    __table_args__ = (PrimaryKeyConstraint(u'stid', u'last_name'),)

    stid = Column(Numeric(9, 0, False), nullable=False, info={'format': '9.2f'})

    first_name = Column(String(30), info={'format': '30.30s'})

    last_name = Column(String(30), info={'format': '30.30s'})

    description = Column(String(80), info={'format': '80.80s'})

    lddate = Column(Date, info={'format': '%Y-%m-%d %H:%M:%S'})

class MyStudents(Students):

    __tablename__ = 'students'

    __table_args__ = Students.__table_args__ + ({'__schema__': 'me'},)

class OldStudents(Students):

    __tablename__ = 'oldstudents'

    __table_args__ = Students.__table_args__ + ({'__schema__': 'me'},)

class OtherStudents(Students):

    __tablename__ = 'students'

    __table_args__ = Students.__table_args__ + ({'__schema__': 'other'},)

% ------------------------  end code  -----------------------------------------------------------------------

1) The abstract declaration goes fine.  So does declaring MyStudents.

2) When I declare OldStudents, I get the following warning.  Why do I get this? I thought I was creating things, not replacing anything.

/Library/Frameworks/EPD64.framework/Versions/7.3/lib/python2.7/site-packages/sqlalchemy/sql/expression.py:2477: SAWarning: Column 'stid' on table Table('students', MetaData(bind=None), Column('stid', Numeric(precision=9, scale=0, asdecimal=False), table=<students>, primary_key=True, nullable=False), Column('first_name', String(length=30), table=<students>), Column('last_name', String(length=30), table=<students>, primary_key=True), Column('description', String(length=80), table=<students>), Column('lddate', Date(), table=<students>), schema=None) being replaced by Column('stid', Numeric(precision=9, scale=0, asdecimal=False), table=<oldstudents>, nullable=False), which has the same key.  Consider use_labels for select() statements.

  self[column.key] = column

/Library/Frameworks/EPD64.framework/Versions/7.3/lib/python2.7/site-packages/sqlalchemy/sql/expression.py:2477: SAWarning: Column 'last_name' on table Table('students', MetaData(bind=None), Column('stid', Numeric(precision=9, scale=0, asdecimal=False), table=<students>, primary_key=True, nullable=False), Column('first_name', String(length=30), table=<students>), Column('last_name', String(length=30), table=<students>, primary_key=True), Column('description', String(length=80), table=<students>), Column('lddate', Date(), table=<students>), schema=None) being replaced by Column('last_name', String(length=30), table=<oldstudents>), which has the same key.  Consider use_labels for select() statements.

  self[column.key] = column

3) When I declare OtherStudents (it was just a warning, after all), I get the following error.  Again, I thought I was creating things, not replacing anything.  Is 'schema' not part of the registry?

InvalidRequestError: Table 'students' is already defined for this MetaData instance.  Specify 'extend_existing=True' to redefine options and columns on an existing Table object.

I just want to be able to create & query a class called MyStudents that points to a table called 'students' on the 'me' Oracle account, OldStudents that points to the 'oldstudents' table on the 'me' account, and OtherStudents that points to the 'students' table on the 'other' account.  All these tables look the same, and I thought this is what abstract classes where for.

Am I using the wrong bit of SQLAlchemy machinery?  If so, could someone point me to the right bit?  I feel like I'm not quite grokking abstract/concrete table inheritance.

Thanks for your time,

Jon

[Michael Bayer]

% ------------------------  end code  ———————————————————————————————————

well the “schema” argument on Table is called “schema”, not “__schema__”, and the __table_args__ are not copied to each subclass (e.g. you’re using the same PrimaryKeyConstraint object on three different Table objects) so you’d want to use @declared_attr + def __table_args__(cls) for that.

[jkmacc]

Hmm...  The 'schema' problem was my dumb mistake, but I still haven't found a use of @declared_attr that solves my problem.  It looks like, by the time I get to my OldStudents or anything afterwards, I have to redeclare almost everything in __table_args__.  I've tried modifying the base with a @declared_attr that merges  __table_args__ down the class hierarchy, like in https://groups.google.com/forum/#!topic/sqlalchemy/KybuUktY3t8, but I just get recursion problems.  I'd hoped that I could declare most everything once in the abstract class, but it seems that what you're saying is that this isn't true.  

The closest thing I've found is something like a Table copy function from https://groups.google.com/forum/#!topic/sqlalchemy/f1D1LBkaWCE, where I'd copy and tweak the __table__ name and schema from a non-abstract version of Students, and do an on-the-fly "hybrid" declaration using the new __table__.

This use case seemed common enough that I thought I was missing something obvious, but the answer may be that it'll take a bit more hacking on my part.

Thanks again,

Jon

[Michael Bayer]

On Oct 30, 2013, at 1:43 PM, jkmacc <jkm...@gmail.com> wrote:

Hmm...  The 'schema' problem was my dumb mistake, but I still haven't found a use of @declared_attr that solves my problem.  It looks like, by the time I get to my OldStudents or anything afterwards, I have to redeclare almost everything in __table_args__.  I've tried modifying the base with a @declared_attr that merges  __table_args__ down the class hierarchy, like in https://groups.google.com/forum/#!topic/sqlalchemy/KybuUktY3t8, but I just get recursion problems.  I'd hoped that I could declare most everything once in the abstract class, but it seems that what you're saying is that this isn't true.  

I’m not seeing that as the case, I can apply the @declared_attr just to the top __table_args__ and that’s all that’s needed, everything works out fine:

from sqlalchemy import Column, Numeric, String, Date, PrimaryKeyConstraint

from sqlalchemy.ext.declarative import declarative_base, declared_attr

Base = declarative_base()

class Students(Base):

    __abstract__ = True

    @declared_attr

    def __table_args__(cls):

        return (PrimaryKeyConstraint(u'stid', u'last_name'),)

    stid = Column(Numeric(9, 0, False), nullable=False, info={'format': '9.2f'})

    first_name = Column(String(30), info={'format': '30.30s'})

    last_name = Column(String(30), info={'format': '30.30s'})

    description = Column(String(80), info={'format': '80.80s'})

    lddate = Column(Date, info={'format': '%Y-%m-%d %H:%M:%S'})

class MyStudents(Students):

    __tablename__ = 'students'

    __table_args__ = Students.__table_args__ + ({'schema': 'me'},)

class OldStudents(Students):

    __tablename__ = 'oldstudents'

    __table_args__ = Students.__table_args__ + ({'schema': 'me'},)

class OtherStudents(Students):

    __tablename__ = 'students'

    __table_args__ = Students.__table_args__ + ({'schema': 'other'},)

# schema works

assert OtherStudents.__table__.schema == “other"

# primary key works

assert list(OtherStudents.__table__.primary_key) == \

        [OtherStudents.__table__.c.stid, OtherStudents.__table__.c.last_name]

# what’s not working?

[jkmacc]

Oh, _that's_ how you use @declared_attr.  Fantastic.  This works wonderfully!

Now, to implement some that merge all __table_args__ in the hierarchy, like http://www.sqlalchemy.org/trac/ticket/2700, but will also take a dictionary.  If so, perhaps one could avoid the "Students.__table_args__ +" step and just say "__table_args__" = {'schema': 'other'}" for the children.

Thanks for your help!

-Jon