Issues

Issue #3129 resolved

automap: Please include documentation how to remap the relationship

Priit Laes
created an issue

When trying to automap legacy database which has some issues with foreign key naming I ran into following issue.

Having following two tables, it barfs because of naming conflict between column table_b.table_a and relationship table_b->table_a

table_a:
id

table_b:
id
table_a (foreign key to table_a.id)

Error message nicely tells me to To resolve this, map the column to the class under a different name in the 'properties' dictionary., but automap part of documentation doesn't mention much about this case.

I have tried to override the class generation using following constructs, but still fail with error above:

class Table_B(Base):
    __tablename__ = 'table_b'
    table_a = Column(ForeignKey('table_a.id'))

Comments (13)

  1. Mike Bayer repo owner

    automap allows all the naming conventions it uses to be customizable. have you tried modifying the naming conventions to work around this? note I havent run the example yet.

  2. Mike Bayer repo owner

    im not seeing the problem here, the docs are IMO extremely clear about what goes on in automap - from http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html#relationship-detection:

     The names of the relationships are determined using the
     :paramref:`.AutomapBase.prepare.name_for_scalar_relationship` and
     :paramref:`.AutomapBase.prepare.name_for_collection_relationship`
     callable functions.  It is important to note that the default relationship
     naming derives the name from the **the actual class name**.  If you've
     given a particular class an explicit name by declaring it, or specified an
     alternate class naming scheme, that's the name from which the relationship
     name will be derived.
    

    the functions which do the work here are named and linked to their docstrings. the naming scheme for the class is described right above at http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/automap.html#overriding-naming-schemes. It includes a complete example of how to change this scheme. We can cut-and-paste it directly into your test script and the error goes away:

    import re
    def camelize_classname(base, tablename, table):
        "Produce a 'camelized' class name, e.g. "
        "'words_and_underscores' -> 'WordsAndUnderscores'"
    
        return str(tablename[0].upper() + \
                re.sub(r'_(\w)', lambda m: m.group(1).upper(), tablename[1:]))
    
    Base = automap_base()
    
    
    class TableB(Base):
        __tablename__ = 'table_b'
        table_a = Column(ForeignKey('table_a.id'))
    
    Base.prepare(engine, reflect=True, classname_for_table=camelize_classname)
    

    I will note that the automap extension is not one of those things that "just works" with absolutely no planning, if I were using this for the first time and didn't have time to read any docs, I'd probably be annoyed, but if the docs are read fully I think everything that's happening here should be clear.

  3. Mike Bayer repo owner

    yeah the patch given has two issues - it makes a decision about the naming convention outside of the naming convention function itself, which isn't feasible, and also it's an arbitrary change in rules. the existing function is very simple:

    def name_for_scalar_relationship(base, local_cls, referred_cls, constraint):
        return referred_cls.__name__.lower()
    

    your patch can be emulated as follows:

    def name_for_scalar_relationship(base, local_cls, referred_cls, constraint):
        name = referred_cls.__name__.lower()
        local_table = local_cls.__table__
        if name in local_table.columns:
            newname = name + "_"
            util.warn("Already detected name %s present.  using %s" % (name, newname))
            return newname
        return name
    
    Base.prepare(engine, reflect=True, name_for_scalar_relationship=name_for_scalar_relationship)
    
  4. Log in to comment