automap: Please include documentation how to remap the relationship

[Priit Laes]

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'))

[Priit Laes]

Example testcase.

[Priit Laes]

Possible fix here: https://github.com/plaes/sqlalchemy/commit/fdd8245f57acbed0b80784628228b63890f212d4

[Priit Laes]

The title of this bug should be: ext.automap fails when foreignkey column name and target table names are equal

[Mike Bayer]

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.

[Mike Bayer]

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.

[Mike Bayer]

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)

[Mike Bayer]

• add a documentation section for naming conflicts, fixes #3129

→ <<cset 580d39be6ff2>>

[Mike Bayer]

• add a documentation section for naming conflicts, fixes #3129

→ <<cset 0083075b8e46>>

[Mike Bayer]

• also add the alternate approach, name column distinctly from attribute name, ref #3129

→ <<cset 4020f2f440b1>>

[Mike Bayer]

• also add the alternate approach, name column distinctly from attribute name, ref #3129

→ <<cset 71cb95ef9f88>>