"Class already has a primary mapper defined" error with SQLAlchemy

Back in October 2010, I posted this question to the Sqlalchemy user list. At the time, I just used the clear_mappers workaround mentioned in the message, and didn't try to figure out what the problem was. That was very naughty of me. Today I ran into this bug again, and decided to construct a minimal example, which appears below. Michael also addressed what is probably the same issue back in 2006. I decided to follow up here, to give Michael a break from my dumb questions.

So, the upshot appears to be that for a given class definition, you can't have more than one mapper defined. In my case I have the Pheno class declared in module scope (I assume that is top level scope here) and each time make_tables runs, it tries to define another mapper.

Mike wrote "Based on the description of the problem above, you need to ensure your Python classes are declared in the same scope as your mappers. The error message you're getting suggests that 'Pheno' is declared at the module level." That would take care of the problem, but how do I manage that, without altering my current structure? What other options do I have, if any? Apparently mapper doesn't have an option like "if the mapper is already defined, exit without doing anything", which would take care of it nicely. I guess I could define a wrapper function, but that would be pretty ugly.

from sqlalchemy import *
from sqlalchemy.orm import *

def make_pheno_table(meta, schema, name='pheno'):
    pheno_table = Table(
        name, meta,
        Column('patientid', String(60), primary_key=True),
        schema=schema,
        )
 开发者_开发百科   return pheno_table

class Pheno(object):
    def __init__(self, patientid):
        self.patientid = patientid

def make_tables(schema):
    from sqlalchemy import MetaData
    meta = MetaData() 
    pheno_table = make_pheno_table(meta, schema)
    mapper(Pheno, pheno_table)
    table_dict = {'metadata': meta, 'pheno_table':pheno_table}
    return table_dict

table_dict = make_tables('foo')
table_dict = make_tables('bar')

Error message follows. Tested with SQLAlchemy 0.6.3-3 on Debian squeeze.

$ python test.py 
Traceback (most recent call last):
  File "test.py", line 25, in <module>
    table_dict = make_tables('bar')
  File "test.py", line 20, in make_tables
    mapper(Pheno, pheno_table)
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/orm/__init__.py", line 818, in mapper
    return Mapper(class_, local_table, *args, **params)
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py", line 209, in __init__
    self._configure_class_instrumentation()
  File "/usr/lib/python2.6/dist-packages/sqlalchemy/orm/mapper.py", line 381, in _configure_class_instrumentation
    self.class_)
sqlalchemy.exc.ArgumentError: Class '<class '__main__.Pheno'>' already has a primary mapper defined. Use non_primary=True to create a non primary Mapper.  clear_mappers() will remove *all* current mappers from all classes.

EDIT: Per the documentation in SQLAlchemy: The mapper() API, I could replace mapper(Pheno, pheno_table) above with

from sqlalchemy.orm.exc import UnmappedClassError

try:
    class_mapper(Pheno)
except UnmappedClassError:
    mapper(Pheno, pheno_table)

If a mapper is not defined for Pheno, it throws an UnmappedClassError. This at least doesn't return an error in my test script, but I haven't checked if it actually works. Comments?

EDIT2: Per Denis's suggestion, the following works:

class Tables(object):
    def make_tables(self, schema):
        class Pheno(object):
            def __init__(self, patientid):
                self.patientid = patientid

        from sqlalchemy import MetaData
        from sqlalchemy.orm.exc import UnmappedClassError
        meta = MetaData()
        pheno_table = make_pheno_table(meta, schema)
        mapper(Pheno, pheno_table)
        table_dict = {'metadata': meta, 'pheno_table':pheno_table, 'Pheno':Pheno}
        return table_dict

table_dict = Tables().make_tables('foo')
table_dict = Tables().make_tables('bar')

However, the superficially similar

# does not work                                                                                                                                                  
class Tables(object):
    class Pheno(object):
        def __init__(self, patientid):
            self.patientid = patientid

    def make_tables(self, schema):
        from sqlalchemy import MetaData
        from sqlalchemy.orm.exc import UnmappedClassError
        meta = MetaData()
        pheno_table = make_pheno_table(meta, schema)
        mapper(self.Pheno, pheno_table)
        table_dict = {'metadata': meta, 'pheno_table':pheno_table, 'Pheno':self.Pheno}
        return table_dict

table_dict = Tables().make_tables('foo')
table_dict = Tables().make_tables('bar')

does not. I get the same error message as before. I don't really understand the scoping issues well enough to say why. Isn't the Pheno class in both cases in some kind of local scope?

You are trying to map the same class Pheno to 2 different tables. SQLAlchemy allows only one primary mapper for each class, so that it knows what table to use for session.query(Pheno). It's not clear what do you wish to get from your question, so I can't propose solution. There are 2 obvious options:

• define separate class to map to second table,
• create non-primary mapper for second table by passing non_primary=True parameter and pass it (the value returned by mapper() function) to session.query() instead of class.

Update: to define separate class for each table you can put its definition into the make_tables():

def make_tables(schema):
    from sqlalchemy import MetaData
    meta = MetaData() 
    pheno_table = make_pheno_table(meta, schema)
    class Pheno(object):
        def __init__(self, patientid):
            self.patientid = patientid    
    mapper(Pheno, pheno_table)
    table_dict = {'metadata': meta, 
                  'pheno_class': Pheno, 
                  'pheno_table':pheno_table}
    return table_dict

maybe i didn't quite understand what you want, but this recipe create identical column in different __tablename__

class TBase(object):
    """Base class is a 'mixin'.
    Guidelines for declarative mixins is at:

    http://www.sqlalchemy.org/docs/orm/extensions/declarative.html#mixin-classes

    """
    id = Column(Integer, primary_key=True)
    data = Column(String(50))

    def __repr__(self):
        return "%s(data=%r)" % (
            self.__class__.__name__, self.data
        )

class T1Foo(TBase, Base):
    __tablename__ = 't1'

class T2Foo(TBase, Base):
    __tablename__ = 't2'

engine = create_engine('sqlite:///foo.db', echo=True)

Base.metadata.create_all(engine)

sess = sessionmaker(engine)()

sess.add_all([T1Foo(data='t1'), T1Foo(data='t2'), T2Foo(data='t3'),
         T1Foo(data='t4')])

print sess.query(T1Foo).all()
print sess.query(T2Foo).all()
sess.commit()

info in example sqlalchemy