Automapper gives errors on database unless prepare() is configured differently

[samuller]

When running sandman2ctl, I got the following errors:

sqlalchemy.exc.ArgumentError: WARNING: when configuring property 'coding_system' on Mapper|category|category, column 'coding_system' conflicts with property '<RelationshipProperty at 0x2c49c987908; coding_system>'.

Which I could fix, but then I also got (and fixed) this:

sqlalchemy.exc.ArgumentError: Error creating backref 'organisation_collection' on relationship 'organisation.product_collection': property of that name exists on mapper 'Mapper|product|product'

While SQLAlchemy's automapper is probably capable of mapping any type of schema, it seems that some weird edge cases can cause issues when only using AutomapModel's default naming conventions.

For our Postgresql database it seems there were issues when:

• a table contains two columns with foreign keys that both reference the same table
• a table contains a foreign key to a table and also has a column with the same name as the referenced table

To get it working in our case I had to change app._reflect_all() to use the following:

    # http://docs.sqlalchemy.org/en/latest/orm/extensions/automap.html#sqlalchemy.ext.automap.name_for_scalar_relationship
    def prepend_name(base, local_cls, referred_cls, constraint):
        return 'tbl_' + referred_cls.__name__.lower()
    # https://stackoverflow.com/questions/37797140/sqlalchemy-automap-backref-errors
    def _name_for_collection_relationship(base, local_cls, referred_cls, constraint):
        if constraint.name:
            return constraint.name.lower() + '_' + referred_cls.__name__.lower()
        # if this didn't work, revert to the default behavior
        return name_for_collection_relationship(base, local_cls, referred_cls, constraint)

    AutomapModel.prepare(  # pylint:disable=maybe-no-member
        db.engine, reflect=True, schema=schema,
        name_for_scalar_relationship=prepend_name,
        name_for_collection_relationship=_name_for_collection_relationship)

This does have some side effects such as a "Tbl" prefix for foreign key columns in the admin interface, but at least it works.

Hopefully there's a cleaner way to handle this use case automatically, but even an option with some configuration through the CLI would be helpful.

[jeffknupp]

Yep, the problem is obvious but the tbl_ solution will break other things as the user can refer to the tables (really, the classes) discovered by AutomapModel.prepare to do things like exclude tables from the API or make them read-only, so even adding it as a command-line flag could create problems (or at the very least a lot of confusion). I'll try to create a test database that has models/tables with the properties you listed and see if I can figure out anything else (I've used the code you pasted from SO in the past for similar issues).