On Mon, Sep 3, 2018 at 6:31 PM, Wes Turner <wes.tur...@gmail.com> wrote: > > > On Mon, Sep 3, 2018 at 4:17 AM Chris Angelico <ros...@gmail.com> wrote: >> >> On Mon, Sep 3, 2018 at 5:23 PM, Jacco van Dorp <j.van.d...@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 _______________________________________________ Python-ideas mailing list Python-ideas@python.org https://mail.python.org/mailman/listinfo/python-ideas Code of Conduct: http://python.org/psf/codeofconduct/