Issue 1:
Not sure if Bug or question, but at least unexpected behavior.

Describe the bug

When querying duplicated results of the type (User, int, datetime), an attempted deduplication using .unique() did not have any effect, and returned multiple rows containing the exact same values.

The reason is that the default identification strategy amounts to an element-wise projection using id, and not hash.

This causes the int and the datetime columns of two rows – even though having the same value – to be recognized as different.

This is in contradiction to the “comparison by value“ assumption that I personally have of such a deduplication, and also in contradiction to what the docs state:

The algorithm used to determine this uniqueness is by default the Python hashing identity of the whole tuple.

In particular, this issue is “resolved” by using an identity strategy, i.e. .unique(lambda row: row) .

This works because the result of the strategy is collected in a set, whose notion of equality is based on hash.

If this is not considered a bug, it should be a warning explicitly stated in the aforelinked section in the docs.

To Reproduce

(I'm really sorry, but it's a bit late in the night, I can create an MCVE later if required.)

I can provide context from a debugging session. I'm breaking in ResultInternal._allrows in this line.

>>> type(self)
<class 'sqlalchemy.engine.result.ChunkedIteratorResult'>
>>> self._metadata._unique_filters
[<built-in function id>, <built-in function id>, <built-in function id>]
>>> [tuple(id(el) for el in r) for r in made_rows[:4]]
[(140281848273408, 140281848246224, 140281848184384),
(140281848273408, 140281848267056, 140281848184480),
(140281848273552, 140281848267344, 140281848184672),
(140281848273552, 140281848267632, 140281848270896)]
>>> [tuple(hash(el) for el in r) for r in made_rows[:4]]
[(8767615517088, 13812, -7323339362369131990),
(8767615517088, 13812, -7323339362369131990),
(8767615517097, 13815, -4473479583634129130),
(8767615517097, 13815, -4473479583634129130)]
>>> [tuple(type(el) for el in r) for r in made_rows[:4]]
[(<class 'pycroft.model.user.User'>, <class 'int'>, <class 'datetime.datetime'>),
(<class 'pycroft.model.user.User'>, <class 'int'>, <class 'datetime.datetime'>),
(<class 'pycroft.model.user.User'>, <class 'int'>, <class 'datetime.datetime'>),
(<class 'pycroft.model.user.User'>, <class 'int'>, <class 'datetime.datetime'>)]

Feel free to ask for other details.

Versions.

OS:

Python: 3.9.1

SQLAlchemy:1.4.18

Database: Postgres

Have a nice day night!

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

I'm requesting that ScopedSession.session_factory become a public API method.

This is currently implemented, but hidden and undocumented.

Rationale / use-case:

We have a transaction-safe PostgreSQL application. There exists some tasks that require a separate database connection to operate independent of the transaction (generally things like logging various bits of information, such as AmazonS3 Activity, Failures due to Ratelimits, some error logging).

This is often handled with a separate database setup, but that was a bit painful to handle in a situation... and after a bit of poking around on the internals, it seems that this works:

dbSessionAlternate = dbSession.session_factory()
dbSessionAutocommit = dbSession.session_factory(autocommit = True)

Originally I hoped that this would work, but they don't:

dbSessionA = dbSession()
dbSessionB = dbSession(autocommit = True)

dbSessionA creates a new Session, but it's underlying connection is local to the scope/thead ( ie, dbSession.connection() == dbSessionA.connection() )

dbSessionB raises an error because it is trying to duplicate the 'scoped_session', not just the underlying session,