Jul 2 09

Using synonyms with SQLAlchemy and subclasses

by mandel

Sometimes you want to be able to use synonyms in a SQLAlchemy mappers that point to columns that are present in the subclass rather than in the super class when using a polymorphic mapping. Unfortunately there is a bug in map_column=True and a workaround has to be used. The following workaround tells the mapper to map a column to a private variable and later adds the sysnonym to it.

Tables definition

In this example I will use the code I am currently working with, in this case the tables describe two different classes. One is the contact class which contains all the basic contact operations, while the second one is a person which describes the particular information stored about people and not other objects (like company).

self.__tables['contact'] = Table(
    DataManager.TablesNames['contact'], self.__metadata,
    Column('id', Integer, primary_key=True),
    Column('telephone_number', None, ForeignKey(\
    DataManager.TablesNames["telephone_numbers"] + '.id'),nullable=True),
    Column('address', None,
        ForeignKey(DataManager.TablesNames['address'] + '.id'),nullable=True),
     Column('email_address', None,
         ForeignKey(DataManager.TablesNames['email_address'] + '.id'),nullable=True),
     Column('web', None,
         ForeignKey(DataManager.TablesNames['web_page'] + '.id'),nullable=True),
     Column('social_account', None,
         ForeignKey(DataManager.TablesNames['social_account'] + '.id'),nullable=True),
     Column('instant_messenger_account', None, ForeignKey(\
         DataManager.TablesNames['instant_messenger'] + '.id'),nullable=True),
     Column('logo', Text, nullable=True, default=""),
     Column('comments', Text, nullable=True, default=""),
     Column('type', String(30), nullable=False))
 
self.__tables['contact_numbers'] = Table(
      DataManager.TablesNames['contact_number'], self.__metadata,
      Column('contact', None, ForeignKey(DataManager.TablesNames['contact'] + '.id'),
          primary_key=True),
      Column('number', None, ForeignKey(
          DataManager.TablesNames["telephone_numbers"] + '.id'),
          primary_key=True))
 
self.__tables['contact_addresses'] = Table(
      DataManager.TablesNames['contact_addresses'], self.__metadata,
      Column('contact', None, ForeignKey(\
         DataManager.TablesNames['contact'] + '.id'), primary_key=True),
      Column('address', None, ForeignKey(\
         DataManager.TablesNames['address'] + '.id'), primary_key=True))
 
self.__tables['contact_emails'] = Table(
       DataManager.TablesNames['contact_emails'], self.__metadata,
       Column('contact', None, ForeignKey(\
       DataManager.TablesNames['contact'] + '.id'), primary_key=True),
      Column('email', None,
           ForeignKey(DataManager.TablesNames['email_address'] + '.id'),
            primary_key=True))
 
self.__tables['contact_webs'] = Table(
       DataManager.TablesNames['contact_webs'], self.__metadata,
       Column('contact', None, ForeignKey(\
       DataManager.TablesNames['contact'] + '.id'), primary_key=True),
       Column('web', None, ForeignKey(\
       DataManager.TablesNames['web_page'] + '.id'), primary_key=True))
 
self.__tables['contact_social_accounts'] = Table(
       DataManager.TablesNames['contact_social_accounts'], self.__metadata,
       Column('contact', None, ForeignKey(\
       DataManager.TablesNames['contact'] + '.id'), primary_key=True),
       Column('account', None,
       ForeignKey(DataManager.TablesNames['social_account'] + '.id'),
       primary_key=True))
 
self.__tables['contact_ims'] = Table(
       DataManager.TablesNames['contact_ims'], self.__metadata,
       Column('contact', None, ForeignKey(\
       DataManager.TablesNames['contact'] + '.id'), primary_key=True),
       Column('im', None, ForeignKey(\
       DataManager.TablesNames['instant_messenger'] + '.id'),
       primary_key=True))
 
self.__tables['person'] = Table(
       DataManager.TablesNames['person'], self.__metadata,
       Column('contact_id', None,
       ForeignKey(DataManager.TablesNames['contact'] + '.id'),
       primary_key=True),
       Column('title', TitleEnum, nullable=True),
       Column('name', String(255), nullable=True, default="", index=True),
       Column('middle_name', String(255), nullable=True,
       default="", index=True),
       Column('surname', String(255), nullable=True,
       default="", index=True),
      Column('birthday', DateTime),
      Column('print_mode', PrintModeEnum, default=1)
)

The mappings

The below mapping are just a simple set of mapping that allow to use a inheritance mapping between the contact table and the person table using a join.

mapper(Contact, self.__tables['contact'],
       polymorphic_on=self.__tables['contact'].c.type,
       polymorphic_identity='contact', with_polymorphic='*', properties={
           'logo' : synonym('_logo', map_column=True),
           'numbers' : relation(TelephoneNumber,
           secondary=self.__tables['contact_numbers']),
           'addresses' : relation(Address,
           secondary=self.__tables['contact_addresses']),
           'emails' : relation(EmailAddress,
           secondary=self.__tables['contact_emails']),
           'webs' : relation(Webpage,
           secondary=self.__tables['contact_webs']),
           'social_accounts':relation(SocialAccount,
           secondary=self.__tables['contact_social_accounts']),
           'instant_messengers':relation(InstantMessenger,
           secondary=self.__tables['contact_ims']),
           'comments' : synonym('_comments', map_column=True),
           'preferred_number' : relation(TelephoneNumber, uselist=False,
           primaryjoin=self.__tables['contact'].c.telephone_number==self.__tables['telephone_numbers'].c.id),
           'preferred_address': relation(Address, uselist=False,
           primaryjoin=self.__tables['contact'].c.address==self.__tables['address'].c.id),
           'preferred_email' : relation(EmailAddress, uselist=False,
           primaryjoin=self.__tables['contact'].c.email_address==self.__tables['email_address'].c.id),
           'preferred_web' : relation(Webpage, uselist=False,
           primaryjoin=self.__tables['contact'].c.web==self.__tables['web_page'].c.id),
           'preferred_social_account' : relation(SocialAccount,
           uselist=False, primaryjoin=self.__tables['contact'].c.social_account==self.__tables['social_account'].c.id),
           'preferred_instant_messenger' : relation(InstantMessenger,
           uselist=False, primaryjoin=self.__tables['contact'].c.instant_messenger_account==self.__tables['instant_messenger'].c.id)
  })
 
mapper(Person, self.__tables['person'],inherits=Contact,
      polymorphic_identity='person',
      properties={
          '_name': self.__tables['person'].c.name,
          'name': synonym('_name'),
          '_middle_name': self.__tables['person'].c.middle_name,
          'middle_name': synonym('_middle_name'),
          '_surname': self.__tables['person'].c.surname,
          'surname': synonym('_surname'),
          '_title': self.__tables['person'].c.title,
          'title': synonym('_title'),
          '_birthday': self.__tables['person'].c.birthday,
          'birthday': synonym('_birthday'),
          '_print_mode': self.__tables['person'].c.print_mode,
          'print_mode': synonym('_print_mode')
  })

In the above example we can see how we are mapping the column in the person mapper. The first step:

'_name': self.__tables['person'].c.name

maps the column with the private variable of the class, the second part of the “declaration”:

'name': synonym('_name')

makes use of the synonym allowing the class to use the property you declared in your class. This took some time to find out and I hope the post is useful to others :D