Upserts not honoring Column onupdate values

[DefiDebauchery]

Describe the bug
When updates happen via on_conflict_do_update, the onupdate values in Column definitions do not apply.

Expected behavior
Any action that effectively updates a row should always consider onupdate data, especially in databases like SQLite that do not otherwise have proper support for things like auto-updating timestamps.

To Reproduce

import time
from sqlalchemy import create_engine
from sqlalchemy.orm import Session
from sqlalchemy import *
from sqlalchemy.sql import func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects.sqlite import insert

engine = create_engine('sqlite://', echo=True)
session = Session(engine)

Base = declarative_base()

class MyPrices(Base):
    __tablename__ = 'price_ath'

    id = Column(Integer, primary_key=True, autoincrement=True)
    token = Column(String, nullable=False, unique=True)
    price = Column(Float, nullable=False)
    timestamp = Column(DateTime, nullable=False, default=func.now(), onupdate=func.now())

Base.metadata.create_all(engine)	

session.add(MyPrices(token='1234', price=1.234))
session.commit()

print(session.query(MyPrices).first())

time.sleep(5)

stmt = insert(MyPrices).values(token='1234', price=3.14)
stmt = stmt.on_conflict_do_update(index_elements=['token'], set_=dict(price=stmt.excluded.price))
session.execute(stmt)
session.commit()

print(session.query(MyPrices).first())

Versions.

• OS: MacOS
• Python: 3.8
• SQLAlchemy: 1.4.0b2
• Database: SQLite 3.32.3
• DBAPI:

Additional context
Please forgive any potential syntactical oddities; documentation and online examples are very inconsistent. I've yet to find a complete tutorial for a single version using every useful feature (especially with upserts).

If there's a way to achieve a single-method "insert or update" in 1.3, I'm happy to use that. For simple situations, users should be able to use a single command (e.g. session.add(MyPrice(token='1234', price=2.34)) to add and update data in a single database row.

Have a nice day!

[zzzeek]

Describe the bug
When updates happen via on_conflict_do_update, the onupdate values in Column definitions do not apply.
Any action that effectively updates a row should always consider onupdate data, especially in databases like SQLite that do not otherwise have proper support for things like auto-updating timestamps.

This is a documented limitation of the various "on conflict" constructs:

The Insert.on_conflict_do_update() method does not take into account Python-side default UPDATE values or generation functions, e.g. those specified using Column.onupdate. These values will not be exercised for an ON CONFLICT style of UPDATE, unless they are manually specified in the Insert.on_conflict_do_update.set_ dictionary.

SQLAlchemy has an assortment of "merge/upsert"-style constructs for various backends and they vary considerably in how they work, so for simplicity and avoidance of regressions / misleading behaviors the "onupdate" functions are currently omitted from the flow of these special-use constructs. The workaround is that one can add the "onupdate" function explicitly to the parameters passed in the set clause.

Of particular concern is the notion of an "onupdate" function that would be inappropriate to invoke if an UPDATE does not actually take place. if someone passes an UPDATE statement with many parameter sets, SQLAlchemy's behavior right now is to run the Python-side "onupdate" construct once for each row, not just once. It wasn't clear if this behavior would be surprising or not for a series of parameters where many of the individual statement executions would ignore this generated value entirely. in the interests of making "upsert" constructs available without getting too bogged down with issues like these early on, "onupdate" and "default" were omitted from these constructs.

Now that we have several "merge/upsert" constructs available we can look into beginning to support Python/SQL function onupdate/default constructs but I would want this to be considered for all supporting backends at once, and this would be a feature add. I would also not want it to be turned on by default until probably version 2.1, so there would need to be an option of some kind indicating these should be used.

Additional context
Please forgive any potential syntactical oddities; documentation and online examples are very inconsistent. I've yet to find a complete tutorial for a single version using every useful feature (especially with upserts).

SQLAlchemy has only added support for "upsert" for MySQL and PosgreSQL in very recent releases, and SQLite's is only in pre-release. There is no support for such constructs in other backends such as Oracle and SQL Server; these databases do support "upsert" but they do so via a very different syntax called MERGE, e.g. https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-ver15 . It's the complexity of MERGE as well as how dramatically differently every backend does this that SQLAlchemy has been held back for many years in providing some kind of "abstraction" over this pattern, as users typically want to use the specific "upsert" construct of just one database. That's what we've provided so far, as it was decided that providing backend-specific constructs was better than providing no construct at all.

If there's a way to achieve a single-method "insert or update" in 1.3, I'm happy to use that. For simple situations, users should be able to use a single command (e.g. session.add(MyPrice(token='1234', price=2.34)) to add and update data in a single database row.

this would be the session.merge() feature. However, merge() does not make use of a SQL "upsert". #5441 addresses this feature request of providing for an ORM feature that integrates with those "upsert" features that are currently available.

Have a nice day!

[DefiDebauchery]

Hi @zzzeek, thank you very much for your detailed response. I appreciate you taking the time.

While I did mention upserts, not finding "a complete tutorial for a single version using every useful feature" was about SQLA in general. Much of the content on StackOverflow, for instance, is several years old and often only tiny snippets. Similarly, the docs make a lot of assumptions about familiarity, and it's made implementation incredibly difficult. I feel like my code example was patchwork... I admit I was hoping you might point out where I might be going wrong. This package would really benefit by having One True Way:tm: to do things.

Speaking of going wrong, session.merge() is not working the way I'm expecting. I first tried it with a new model, but also attempted to update an existing model:

token = MyPrices(token='1234', price=1.234)
session.merge(token)
session.commit()

# session.merge(MyPrices(token='1234', price=3.14))  # didn't work, as I sort of expected
token.price = 3.14
session.merge(token)
session.commit()

I notice the docs for merge mention "reconciling with a primary key" - would changing my schema to have "token" as a PK make this process easier? I just really want to have a single call I can use that adds or updates a given row without a lot of cruft. Is there a way to accomplish this?

[zzzeek]

Hi @zzzeek, thank you very much for your detailed response. I appreciate you taking the time.

While I did mention upserts, not finding "a complete tutorial for a single version using every useful feature" was about SQLA in general. Much of the content on StackOverflow, for instance, is several years old and often only tiny snippets. Similarly, the docs make a lot of assumptions about familiarity, and it's made implementation incredibly difficult. I feel like my code example was patchwork... I admit I was hoping you might point out where I might be going wrong.

Everyone learning SQLAlchemy should read the tutorials. in 1.3 and historically, it was these two:

https://docs.sqlalchemy.org/en/13/orm/tutorial.html

https://docs.sqlalchemy.org/en/13/core/tutorial.html

in SQLAlchemy 1.4, I spent many weeks writing an all new tutorial that is hoped to be even better:

https://docs.sqlalchemy.org/en/14/tutorial/index.html

The first two tutorials are mostly the same for many years (like ten years or more). We have thousands of users who have gotten their start by reading these tutorials, as they are basically providing that "familiarity" you speak of. If I were to start reading a page of documentation from any large software project without understanding the basic concepts, I'd be lost. But it's not feasible for a tutorial to cover the entire API of something like SQLAlchemy, not any more than you'd see a PostgreSQL tutorial that includes every single PostgreSQL syntax. Tutorials are about the concepts and the basics, and from there the hope is that the reference documentation is navigable.

So overall to make your critique more useful it would be helpful in sharing your experience of the tutorials, and specifically the 1.4 tutorial since that's where things are going. Once someone reads and fully digests this tutorial, there should no longer be any kind of gap in familiarity when reading API reference documentation.

as far as StackOverflow, unfortunately I have no control over the quality of that site. We now are using github discussions as you see here which hopefully will allow the SQLAlchemy-related questions to be handled more effectively than they are on StackOverflow which I think to most people feels like a ghost town for most non-trivial subjects.

Speaking of going wrong, session.merge() is not working the way I'm expecting. I first tried it with a new model, but also attempted to update an existing model:

token = MyPrices(token='1234', price=1.234)
session.merge(token)
session.commit()

well you are not providing a primary key value, so unfortunately, merge() can't be used that way; merge only works by merging on primary key and all the various features we are talking about adding to the ORM would work in this way as well.

I see that "token" in your case is unique so is acting like a "primary key", so one option would be to make that the actual primary key, though I am a fan of surrogate integer PKs as well so I likely wouldn't use merge() for this.

I notice the docs for merge mention "reconciling with a primary key" - would changing my schema to have "token" as a PK make this process easier? I just really want to have a single call I can use that adds or updates a given row without a lot of cruft. Is there a way to accomplish this?

as mentioned above, using "token" as primary key would be one way to go.

when you say "a single call" it depends on if your emphasis is on fewer round trips or if it's just on code verbosity. if code verbosity is the only issue then you would make a function of your own that either 1. does the upsert statement or 2. loads a MyPrices() with the given token, or creates new one if not found.

There is a wiki recipe that is often used for this called UniqueObject. You might lament that this recipe is not in the main docs and it's also not that simple. The reason is that I have not found the "load an object that filters on X, Y, Z and update it, else make a new one" use case is actually all that simple in the first place, as far as how to do it in a generalized way, so there's no SQLAlchemy feature I've yet conceived of (or anyone else has) that we'd want to propose for this. On top of all that, UniqueObject doesn't scale well to many rows, and in fact when I personally have to solve the UniqueObject problem I do it in an entirely different way, where I load many rows in chunks and put them in a dictionary so I can process thousands of records quickly; the UniqueObject recipe in my experience is often used in terms of ETL and I think it's the wrong approach for ETL that wants to scale.

I think probably the biggest cultural idiosyncracy in SQLAlchemy is that we really don't like to add features that are not going to work in every case they claim to solve or do not scale well, or that make something that is very simple to write out as three lines of code (like SELECT object, if None, then new) into something more opaque and difficult to learn and predict. This is very different from libraries like Django where they have a little widget to do just about everything, each one loaded with limitations and surprises like not supporting composite primary keys, not scaling well, and such. We don't really like to go there, and that's sort of how SQLAlchemy has earned it's place. We would prefer people solve simple problems using their own functions, rather than creating another swiss-army function on our side which would be vastly more complicated (witness the UniqueObject recipe) and difficult to scale.

So we often have to deal with certain longstanding requests that there is either not a clear way to solve that isn't going to become an overcomplicated beast, or there might be some way to solve it but it would require many days /weeks of careful thought and attention where there are otherwise easy workarounds for users to achieve the feature on their own, so often things go for a long time until a good way to do it comes around.

for this one, UniqueObject is sort of the beginning of things but nobody has ever come around trying to propose it for inclusion in SQLAlchemy. I think that's probably because by the time people get it working, they realize they've just written the necessary infrastructure for their own project to solve the problem in the way they need, and that's actually what procedural / object oriented programming is about.

Anyway, long rant, but TLDR for now I think your use of upsert is fine but if you want database agnosticism, you'd want to be emitting a SELECT and then an INSERT/UPDATE , which I would then encapsulate into a function.