title: Mapping a class against multiple tables in SQLAlchemy
body: <pre><code># ! /usr/bin/env python
  # -*- coding: utf-8 -*-
  # login_frontend.py
  
  """ Python        2.7.3
      Cherrypy      3.2.2
      PostgreSQL    9.1
      psycopy2      2.4.5
      SQLAlchemy    0.7.10
  """
  </code></pre>
  
  <p>I'm having a problem joining four tables in one Python/SQLAlchemy class. I'm trying this, so I can iterate the instance of this class, instead of the named tuple, which I get from joining tables with the ORM.</p>
  
  <p>Why all of this? Because I already started that way and I came too far, to just leave it. Also, it has to be possible, so I want to know how it's done.</p>
  
  <p>For this project (cherrypy web-frontend) I got an already completed module with the table classes. I moved it to the bottom of this post, because maybe it isn't even necessary for you.</p>
  
  <p>The following is just one example of a joined multiple tables class attempt. I picked a simple case with more than only two tables and a junction table. Here I don't write into these joined tables, but it is necessary somewhere else. That's why classes would be a nice solution to this problem.</p>
  
  <hr>
  
  <h2>My attempt of a join class,</h2>
  
  <p>which is a combination of the given table classes module and the examples from these two websites:</p>
  
  <p>-<a href="http://docs.sqlalchemy.org/en/rel_0_7/orm/mapper_config.html#mapping-a-class-against-multiple-tables" rel="nofollow noreferrer">Mapping a Class against Multiple Tables</a><br>
  -<a href="http://parijatmishra.wordpress.com/2009/01/18/sqlalchemy-one-classes-two-tables/" rel="nofollow noreferrer">SQLAlchemy: one classes – two tables</a>  </p>
  
  <pre><code>class JoinUserGroupPerson (Base):
  
      persons = md.tables['persons']
      users = md.tables['users']
      user_groups = md.tables['user_groups']
      groups = md.tables['groups']
  
      user_group_person =(
          join(persons, users, persons.c.id == users.c.id).
          join(user_groups, users.c.id == user_groups.c.user_id).
          join(groups, groups.c.id == user_groups.c.group_id))
  
      __table__ = user_group_person
  
      """ I expanded the redefinition of 'id' to three tables,
          and removed this following one, since it made no difference:
          users_id = column_property(users.c.id, user_groups.c.user_id)
      """
  
      id = column_property(persons.c.id, users.c.id, user_groups.c.user_id)
      groups_id = column_property(groups.c.id, user_groups.c.group_id)
      groups_name = groups.c.name
  
      def __init__(self, group_name, login, name, email=None, phone=None):
          self.groups_name = group_name
          self.login = login
          self.name = name
          self.email = email
          self.phone = phone
  
      def __repr__(self):
          return(
              "&lt;JoinUserGroupPerson('%s', '%s', '%s', '%s', '%s')&gt;" %(
              self.groups_name, self.login, self.name, self.email, self.phone))
  </code></pre>
  
  <hr>
  
  <h2>Different table accesses with this join class</h2>
  
  <ul>
  <li><p>This is how I tried to query this class in another module:</p>
  
  <pre><code>pg = sqlalchemy.create_engine(
      'postgresql://{}:{}@{}:{}/{}'.
      format(user, password, server, port, data))
  Session = sessionmaker(bind=pg)
  s1 = Session()
  
  query = (s1.query(JoinUserGroupPerson).
      filter(JoinUserGroupPerson.login==user).
      order_by(JoinUserGroupPerson.id))
  
      record = {}
      for rowX in query:
          for colX in rowX.__table__.columns:
              record[column.name] = getattr(rowX,colX.name)
  
  
  """ RESULT:
  """
  
  
  Traceback (most recent call last):
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/_cprequest.py", line 656, in respond
      response.body = self.handler()
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/lib/encoding.py", line 228, in __call__
      ct.params['charset'] = self.find_acceptable_charset()
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/lib/encoding.py", line 134, in find_acceptable_charset
      if encoder(encoding):
    File "/usr/local/lib/python2.7/dist-packages/cherrypy/lib/encoding.py", line 86, in encode_string
      for chunk in self.body:
    File "XXX.py", line YYY, in ZZZ
      record[colX.name] = getattr(rowX,colX.name)
  AttributeError: 'JoinUserGroupPerson' object has no attribute 'user_id'
  </code></pre></li>
  <li><p>Then I checked the table attributes:</p>
  
  <pre><code>for rowX in query:
      return (u'{}'.format(rowX.__table__.columns))
  
  
  """ RESULT:
  """
  
  
  ['persons.id',
   'persons.name',
   'persons.email',
   'persons.phone',
   'users.id',
   'users.login',
   'user_groups.user_id',
   'user_groups.group_id',
   'groups.id',
   'groups.name']
  </code></pre></li>
  <li><p>Then I checked, if the query or my class isn't working at all, by using a counter.
  I got up to (count == 5), so the first two joined tables. But when I set the condition to (count == 6), I got the first error message again. AttributeError: 'JoinUserGroupPerson' object has no attribute 'user_id'.:</p>
  
  <pre><code>list = []
  for rowX in query:
      for count, colX in enumerate(rowX.__table__.columns):
          list.append(getattr(rowX,colX.name))
          if count == 5:
              break
  return (u'{}'.format(list))
  
  
  """ RESULT:
  """
  
  
  [4, u'user real name', None, None, 4, u'user']
  
  
  """ which are these following six columns:
      persons[id, name, email, phone], users[id, login]
  """
  </code></pre></li>
  <li><p>Then I checked each column:</p>
  
  <pre><code>list = []
  for rowX in query:
      for colX in rowX.__table__.columns:
          list.append(colX)
  return (u'{}'.format(list))
  
  
  """ RESULT:
  """
  
  
  [Column(u'id', INTEGER(), table=, primary_key=True, nullable=False, server_default=DefaultClause(, for_update=False)),
   Column(u'name', VARCHAR(length=252), table=, nullable=False),
   Column(u'email', VARCHAR(), table=),
   Column(u'phone', VARCHAR(), table=),
   Column(u'id', INTEGER(), ForeignKey(u'persons.id'), table=, primary_key=True, nullable=False),
   Column(u'login', VARCHAR(length=60), table=, nullable=False),
   Column(u'user_id', INTEGER(), ForeignKey(u'users.id'), table=, primary_key=True, nullable=False),
   Column(u'group_id', INTEGER(), ForeignKey(u'groups.id'), table=, primary_key=True, nullable=False),
   Column(u'id', INTEGER(), table=, primary_key=True, nullable=False),
   Column(u'name', VARCHAR(length=60), table=, nullable=False)]
  </code></pre></li>
  <li><p>Then I tried another two direct accesses, which got me both KeyErrors for 'id' and 'persons.id':</p>
  
  <pre><code>for rowX in query:
      return (u'{}'.format(rowX.__table__.columns['id'].name))
  
  for rowX in query:
      return (u'{}'.format(rowX.__table__.columns['persons.id'].name))
  </code></pre></li>
  </ul>
  
  <hr>
  
  <h2>Conclusion</h2>
  
  <p>I tried a few other things, which were even more confusing. Since they didn't reveal any more information, I didn't add them. I don't see where my class is wrong.</p>
  
  <p>I guess, somehow I must have set the class in a way, which would only correctly join the first two tables. But the join works at least partially, because when the 'user_groups' table was empty, I got an empty query as well.</p>
  
  <p>Or maybe I did something wrong with the mapping of this 'user_groups' table. Since with the join some columns are double, they need an additional definition. And the 'user_id' is already part of the persons and users table, so I had to map it twice.</p>
  
  <p>I even tried to remove the 'user_groups' table from the join, because it's in the relationships (with secondary). It got me a foreign key error message. But maybe I just did it wrong.</p>
  
  <p>Admittedly, I even don't know why ...</p>
  
  <pre><code>rowX.__table__.columns                  # column names as table name suffix 
  </code></pre>
  
  <p>... has different attribute names than ...</p>
  
  <pre><code>colX in rowX.__table__.columns        # column names without table names
  </code></pre>
  
  <hr>
  
  <h2>Extra Edits</h2>
  
  <ul>
  <li><p>Another thought! Would all of this be possible with inheritance? Each class has its own mapping, but then the user_groups class may be necessary. The joins had to be between the single classes instead. The init() and repr() still had to be redefined.</p></li>
  <li><p>It probably has something to do with the 'user_groups' table, because I even couldn't join it with the 'groups' or 'users' table. And it always says, that the class object has no attribute 'user_id'. Maybe it's something about the many-to-many relationship.</p></li>
  </ul>
  
  <hr>
  
  <h2>Attachment</h2>
  
  <p>Here is the already given SQLAlchemy module, with header, without specific information about the database, and the classes of the joined tables:</p>
  
  <pre><code>#!/usr/bin/python
  # vim: set fileencoding=utf-8 :
  
  import sqlalchemy
  from sqlalchemy import join
  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.orm import relationship, backref, column_property
  
  pg = sqlalchemy.create_engine(
      'postgresql://{}@{}:{}/{}'.format(user, host, port, data))
  
  md = sqlalchemy.MetaData(pg, True)
  Base = declarative_base()
  
  
  
  """ ... following, three of the four joined tables.
      UserGroups isn't necessary, so it wasn't part of the module.
      And the other six classes shouldn't be important for this ...
  """
  
  
  class Person(Base):
      __table__ = md.tables['persons']
  
      def __init__(self, name, email=None, phone=None):
          self.name = name
          self.email = email
          self.phone = phone
  
      def __repr__(self):
          return(
              "&lt;Person(%s, '%s', '%s', '%s')&gt;" %(
              self.id, self.name, self.email, self.phone))
  
  class Group(Base):
      __table__ = md.tables['groups']
  
      def __init__(self, name):
          self.name = name
  
      def __repr__(self):
          return("&lt;Group(%s, '%s')&gt;" %(self.id, self.name))
  
  class User(Base):
      __table__ = md.tables['users']
  
      person = relationship('Person')
      groups = relationship(
          'Group', secondary=md.tables['user_groups'], order_by='Group.id',
          backref=backref('users', order_by='User.login'))
  
      def __init__(self, person, login):
          if isinstance(person, Person):
              self.person = person
          else:
              self.id = person
          self.login = login
  
      def __repr__(self):
          return("&lt;User(%s, '%s')&gt;" %(self.id, self.login))
  </code></pre>
  
  <p>Maybe the following script, which created the database, and also was already given, will prove useful here. As last part of it comes some test data - but between the columns are supposed to be tabs, no spaces. Because of that, this script also can be found as <a href="https://gist.github.com/Jonathan2Root/1a87d8fc9dbeb80c30ce" rel="nofollow noreferrer">gist on github</a>:  </p>
  
  <pre><code>-- file create_str.sql
  -- database creation script
  -- central script for creating all database objects
  
  -- set the database name
  \set strdbname logincore
  
  \c admin
  
  BEGIN;
  \i str_roles.sql
  COMMIT;
  
  DROP DATABASE IF EXISTS :strdbname;
  CREATE DATABASE :strdbname TEMPLATE template1 OWNER str_db_owner
      ENCODING 'UTF8';
  \c :strdbname
  
  SET ROLE str_db_owner;
  
  BEGIN;
  \i str.sql
  COMMIT;
  RESET ROLE;
  
  
  
  
  
  -- file str_roles.sql
  -- create roles for the database
  
  -- owner of the database objects
  SELECT create_role('str_db_owner', 'NOINHERIT');
  
  -- role for using
  SELECT create_role('str_user');
  
  -- make str_db_owner member in all relevant roles
  GRANT str_user TO str_db_owner WITH ADMIN OPTION;
  
  
  
  
  
  -- file str.sql
  -- creation of database
  
  -- prototypes
  \i str_prototypes.sql
  
  -- domain for non empty text
  CREATE DOMAIN ntext AS text CHECK (VALUE&lt;&gt;'');
  
  -- domain for email addresses
  CREATE DOMAIN email AS varchar(252) CHECK (is_email_address(VALUE));
  
  -- domain for phone numbers
  CREATE DOMAIN phone AS varchar(60) CHECK (is_phone_number(VALUE));
  
  -- persons
  CREATE TABLE persons (
      id    serial       PRIMARY KEY,
      name  varchar(252) NOT NULL,
      email email,
      phone phone
  );
  
  GRANT SELECT, INSERT, UPDATE, DELETE ON persons TO str_user;
  GRANT USAGE ON SEQUENCE persons_id_seq TO str_user;
  
  CREATE TABLE groups (
      id   integer     PRIMARY KEY,
      name varchar(60) UNIQUE NOT NULL
  );
  
  GRANT SELECT ON groups TO str_user;
  
  -- database users
  CREATE TABLE users (
      id    integer     PRIMARY KEY REFERENCES persons(id) ON UPDATE CASCADE,
      login varchar(60) UNIQUE NOT NULL
  );
  
  GRANT SELECT ON users TO str_user;
  
  -- user &lt;-&gt; groups
  CREATE TABLE user_groups (
      user_id  integer NOT NULL REFERENCES users(id)
                                ON UPDATE CASCADE ON DELETE CASCADE,
      group_id integer NOT NULL REFERENCES groups(id)
                                ON UPDATE CASCADE ON DELETE CASCADE,
      PRIMARY KEY (user_id, group_id)
  );
  
  -- functions
  \i str_functions.sql
  
  
  
  
  
  -- file str_prototypes.sql
  -- prototypes for database
  
  -- simple check for correct email address
  CREATE FUNCTION is_email_address(email varchar) RETURNS boolean
      AS $CODE$
      SELECT FALSE
      $CODE$ LANGUAGE sql IMMUTABLE STRICT;
  
  -- simple check for correct phone number
  CREATE FUNCTION is_phone_number(nr varchar) RETURNS boolean
      AS $CODE$
      SELECT FALSE
      $CODE$ LANGUAGE sql IMMUTABLE STRICT;
  
  
  
  
  
  -- file str_functions.sql
  -- functions for database
  
  -- simple check for correct email address
  CREATE OR REPLACE FUNCTION is_email_address(email varchar) RETURNS boolean
      AS $CODE$
      SELECT $1 ~ E'^[A-Za-z0-9.!#$%&amp;\'\*\+\-/=\?\^_\`{\|}\~\.]+@[-a-z0-9\.]+$'
      $CODE$ LANGUAGE sql IMMUTABLE STRICT;
  
  -- simple check for correct phone number
  CREATE OR REPLACE FUNCTION is_phone_number(nr varchar) RETURNS boolean
      AS $CODE$
      SELECT $1 ~ E'^[-+0-9\(\)/ ]+$'
      $CODE$ LANGUAGE sql IMMUTABLE STRICT;
  
  
  
  
  
  -- file fill_str_test.sql
  -- test data for database
  -- between the columns are supposed to be tabs, no spaces !!!
  
  BEGIN;
  
  COPY persons (id, name, email) FROM STDIN;
  1   Joseph Schneider    jschneid@lab.uni.de
  2   Test User   jschneid@lab.uni.de
  3   Hans Dampf  \N
  \.
  SELECT setval('persons_id_seq', (SELECT max(id) FROM persons));
  
  COPY groups (id, name) FROM STDIN;
  1   IT
  2   SSG
  \.
  
  COPY users (id, login) FROM STDIN;
  1   jschneid
  2   tuser
  3   dummy
  \.
  
  COPY user_groups (user_id, group_id) FROM STDIN;
  1   1
  2   1
  3   2
  \.
  
  COMMIT;
  </code></pre>
  
  <hr>