[Python-ideas] Add recordlcass to collections module

Chris Angelico rosuav at gmail.com
Mon Sep 3 04:39:21 EDT 2018


On Mon, Sep 3, 2018 at 6:31 PM, Wes Turner <wes.turner at gmail.com> wrote:
>
>
> On Mon, Sep 3, 2018 at 4:17 AM Chris Angelico <rosuav at gmail.com> wrote:
>>
>> On Mon, Sep 3, 2018 at 5:23 PM, Jacco van Dorp <j.van.dorp at deonet.nl>
>> wrote:
>> > This feels really useful to me to make some quick changes to a database
>> > -
>> > perhaps a database layer could return an class of type Recordclass, and
>> > then
>> > you just simply mutate it and shove it back into the database.
>> > Pseudocode:
>> >
>> > record = database.execute("SELECT * FROM mytable WHERE primary_key =
>> > 15")
>> > record.mostRecentLoggedInTime = time.time()
>> > database.execute(f"UPDATE mytable SET mostRecentLoggedInTime =
>> > {record.mostRecentLoggedInTime} WHERE primary_key =
>> > {record.primary_key}":)
>> >
>> > Or any smart database wrapper might just go:
>> >
>> > database.updateOrInsert(table = mytable, record = record)
>> >
>> > And be smart enough to figure out that we already have a primary key
>> > unequal
>> > to some sentinel value like None, and do an update, while it could do an
>> > insert if the primary key WAS some kind of sentinel value.
>>
>> In its purest form, what you're asking for is an "upsert" or "merge"
>> operation:
>>
>> https://en.wikipedia.org/wiki/Merge_(SQL)
>>
>> In a multi-user transactional database, there are some fundamentally
>> hard problems to implementing a merge. I'm not 100% certain, so I
>> won't say "impossible", but it is certainly *extremely difficult* to
>> implement an operation like this in application-level software without
>> some form of race condition.
>
>
> http://docs.sqlalchemy.org/en/latest/orm/contextual.html#contextual-thread-local-sessions
> - scoped_session
>
> http://docs.sqlalchemy.org/en/latest/orm/session_state_management.html#merging
>
> http://docs.sqlalchemy.org/en/latest/orm/session_basics.html
>
> obj = ExampleObject(attr='value')
> assert obj.id is None
> session.add(obj)
> session.flush()
> assert obj.id is not None
> session.commit()

Yep. What does it do if it's on a back-end database that doesn't
provide a merge/upsort intrinsic? What if you have a multi-column
primary key? There are, of course, easier sub-forms of this (eg you
mandate that the PK be a single column and be immutable), but if there
is any chance that any other client might simultaneously be changing
the PK of your row, a perfectly reliable upsert/merge basically
depends on the DB itself providing that functionality.

ChrisA


More information about the Python-ideas mailing list