Extending sqlalchemy.schema.Table

[tiadobatima]

Hello there,

When this application starts, we reflect the DB into a MetaData() object and this is made available for everyone to use.

I'd like to add a few more methods to the table objects within that MetaData(). Is there any easy way to extend these already instantiated sqlalchemy.schema.Table objects?

Thanks! :)

[Michael Bayer]

You'd probably implement your own reflect_all function/method:

from sqlalchemy import inspect

def reflect(metadata, bind):

    inspector = inspect(bind)

    for tname in inspector.get_table_names():

        MySpecialTable(tname, metadata, autoload=True, autoload_with=bind)

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Michael Bayer]

oh, except you might have problems with tables in there that are reflected due to a foreign key.    Table is not really intended for subclassing, unless you want to do SQLAlchemy-Migrate's approach of monkeypatching Table.__bases__ at the global level, I'd seek some other way to achieve what you want.

[Gustavo Baratto]

Thanks for the reply Michael... I had a hunch this wouldn't be easy to tackle, but this is more than I can chew  at the moment: )

For now, I'll just keeping doing what I'm already doing which is to instantiate a new class taking the table as an argument, and then within my class reference the table's attributes most likely to be used (See below). If there is a more elegant way of doing this, I'm all ears ;)

Thanks!

Class MyGenericTable(object):
    def __init__(self, table):
        self.tablename = table

    @property
    def metadata(self):
        return DB.instance().metadata

    @property
    def table(self):
        return self.metadata.tables[self.tablename]

   @property

   def columns(self):
       return self.table.c


   @property
  def c(self):
      return self.columns


@property
def primary_key(self):
    return self.table.primary_key

   def select(self, data):
       """ my select """

       some custom code


   def insert(self, data):
       """ my insert """

       some custom code

[askel]

There is "column_reflect" event triggered for each column during table reflection. Most likely your extra functionality depends on some columns so I'd look at something like:


    from sqlalchemy.event import listens_for


    columns_to_reflect = set(('id', 'name', 'real_name', 'really_real_name'))

    @listens_for(my_table, 'column_reflect'):
    def column_reflected(inspector, table, column_info):
        columns_to_reflect.remove(column_info['name'])
        if not columns_to_reflect:
            do_my_dirty_job(table)

It does not help to have no "table_reflected" event though. May be this should become a feature request.

[Simon King]

It's a horrible hack, but did you know that you can change the class
of an instance by assigning to its __class__ attribute? I've no idea
if SA does anything that would stop this from working, but you could
try it. Start by creating a subclass of Table with your extra methods,
then iterate over each table in the metadata, setting the __class__
attribute to your subclass.

Hope that helps,

Simon

[askel]

No offence but it feels like direct route to the hell. Monkeypatching is acknowledged to be really bad practise in general with few exemptions.

[Michael Bayer]

as I mentioned earlier, SQLAlchemy-Migrate has done it this way for years.

I'm not a fan of it which was one of several reasons I wrote Alembic, but it does work for them.

[Gustavo Baratto]

I did this to quickly test my own extended ResultProxy object, but as soon figured out how easy was to properly instantiate a new ResultProxy subclass from an existing ResultProxy object, I got rid of this monkeypatching. I'd like to avoid that route if possible.

Thanks! :)

g.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/EoTA-H1s-bQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Gustavo Baratto]

Hi Askel...

What I'd like to accomplish is to add few more attributes and methods to Table (or maybe override "insert/update/delete/select") to do post processing of the Result/Row proxies and return them in different formats such as json, yaml, xml, etc... so, while you suggestion is really neat, I'm not sure it's enough to do what I want... Is it?

I could create my metadata obj from a bunch of extended Table object instead of populating it from reflection, but I feel its a lot nicer to use the real DB schema as the source for the metadata (thru reflection), than trusting the table definitions in code to correspond exactly with the DB schema. I have a hard time trusting that nobody will ever run an alter table out-of-band, and not update the definitions... hehehe

Thanks!

g.

[askel]

Please disregard what I suggested. I must have some mentality problems but I completely missed part about table being already reflected. In that case all events have already happened and my suggestion does not make any sense.

[Gustavo Baratto]

Thanks Askel... You still made me think about doing the reflection of all tables upfront when the app starts... When there are too many tables, this can add up to startup time.

It would be great if we could easily subclass Table though :)

Cheers,

g.