Issue 1:
I've just encountered this issue and after a few hours spent investigating I'm think I know what's going on.

But first, some context:

Let's say in my database I've got the following two tables:

base.customer

report.customer

I need to automap them both and, crucially, I want both classes to just be called 'customer'.

I don't want to have to convert them to a python identifier using custom logic passed to the AutomapBase.prepare(classname_for_table=) argument. For example, the seemingly obvious solution would be to replace the dot with an underscore and name them base_customer, and report_customer respectively, to make them valid python identifiers. But this is inelegant, and there will always be ambiguity.

What if I have a schema called customer_view with a table called customer, and another schema called customer with a table called view_customer? Both classes then get mapped as customer_view_customer. Maybe not the best example, but it should showcase the issue.

You can spend forever coming up with ways to try to avoid this (maybe use 2 underscores instead of 1), and you still won't guarantee it works in every situation (some database schemas use double underscores in their object names as well to represent m2m relationships).

And it will just make everything more needlessly complicated. If you do this you now have to change the AutomapBase.prepare(name_for_scalar_relationship=, name_for_collection_relationship=) arguments so that you can still reference relationships nicely: for example: customer1.email_address_collection rather than customer.base_email_address_collection, and some_email.customer rather than some_email.base_customer.

The more elegant and idiomatic way to handle this seems to me to separate out the namespaces of schemas, to then allow duplication of names across different namespaces. As the Zen of Python states: "Namespaces are one honking great idea -- let's do more of those!"

So what I've done is I've created a new AutomapBase for every schema in my database in a loop, and called AutomapBase.prepare(schema=) individually for each, passing the specific schema to each one. I've also used the AutomapBase.prepare(classname_for_table=) argument to ensure that the schema name is not included in the name of the newly mapped class.

Now I can assign my base_automap.classes as base, my report_automap.classes as report, and then simply reference my classes as base.customer and report.customer, and it's so clean!

The part that gets me is that this ALMOST works. In fact, it works like a dream so long as there are no tables across the whole database that have duplicate names.

However, as soon as there are duplicates, the root _ModuleMarker (the one referenced as _sa_module_registry within each base's _decl_class_registry), through the _ModuleMarker of whichever module your AutomapBase was declared in (I think it's sqlalchemy.ext.automap unless you use a custom base), now has a _MultipleClassMarker against that table name in its _ModuleMarker.contents attribute, which will kill the old weakref as soon as a duplicate table name comes along.

And without this weakref being alive, several downstream parts of the automapping machinery (such as the _relationships_for_fks() method) just fail gracelessly, because they rely on this weakref being alive to work.

While googling around for this before posting this issue I've found this StackOverflow question (https://stackoverflow.com/questions/57118047/typeerror-issubclass-arg-2-must-be-a-class-or-tuple-of-classes) which I believe stems from the same cause. While I can't be 100% certain, the poster of this question probably had a schema change be made to his/her database that caused there to be multiple tables with the same name across different schemas, so their automapping broke.

This error is basically the same one I've been getting. The weakref-invoking property _DeferredMapperConfig.cls returns None from its dead weakref, which then causes an issubclass() check to receive None as its second argument, rather than a type. Many other methods called downstream of AutomapBase.prepare() also fail in similar ways due to the unexpected presence of None, rather than a base.

And the part that's kind of hilarious is that just as the poster in that StackOverflow issue mentioned, sometimes it just randomly works! This does actually make sense, since Python makes no guarantees about when it garbage-collects unreferenced objects. So if you get lucky your reference might still be alive by the time the flow of execution gets to that point. Will it work? Won't it? Who knows! :)

I'm wondering if we can get a patch in to the way _MultipleClassMarker works, so that it can keep track of multiple database objects with identical name stems (the part of the object name after the schema) simultaneously in a schema-aware manner?

If that's too much effort then at minimum a small change keeping the weakrefs cached in this situation rather than killing them off should be a decent compromise, since it will mean the automapper can still finish doing its job, and the only additional memory costs incurred will be for tables with duplicate names, which should hopefully not be too many.

In the long-term I think it would be preferable to add in better multi-schema support to AutomapBase.prepare() just in general, but that's a much bigger job. It's kind of silly that in order to namespace my automapped classes the same way we namespace our objects everywhere else in both Python and SQL, we need to use duplicate AutomapBase objects as a hacky workaround.

Sorry for this wall of text, this really got away from me a bit.

I'd be crazy grateful if this could be considered.

Thanks for your time.

Issue 2:
Migrated issue, originally created by jek (@jek)

Implement generic MERGE, aka 'upsert'. In ANSI, it looks like:

MERGE INTO table_name1 USING table_name2 ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [column2 = value2 ...](,)
WHEN NOT MATCHED THEN INSERT columns VALUES (values)

Dialect support differs pretty widely. A quick & likely inaccurate poll:

Oracle 9+ has a MERGE

t-sql 2008 has a MERGE, earlier can maybe do IF EXISTS(SELECT ...)

MySQL is limited to a key violation condition, and can do either INSERT ... ON DUPLICATE KEY UPDATE or REPLACE INTO, INSERT being preferable

SQLite is limited to a key violation condition, and has INSERT .. ON CONFLICT REPLACE