[GH-ISSUE #71] Automapper gives errors on database unless prepare() is configured differently #48

New issue

Open

opened 2026-02-26 01:32:59 +03:00 by kerem · 1 comment

kerem commented

2026-02-26 01:32:59 +03:00

Owner

Originally created by @samuller on GitHub (Jun 20, 2018).
Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/71

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.

Originally created by @samuller on GitHub (Jun 20, 2018). Original GitHub issue: https://github.com/jeffknupp/sandman2/issues/71 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: ```python # 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.

kerem added the

research

label

2026-02-26 01:32:59 +03:00

kerem commented

2026-02-26 01:32:59 +03:00

Author

Owner

@jeffknupp commented on GitHub (Jun 22, 2018):

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

@jeffknupp commented on GitHub (Jun 22, 2018): 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).