on_conflict¶
Hint
This is an advanced topic, and first time learners of Piccolo can skip if they want.
You can use the on_conflict clause with the following queries:
Introduction¶
When inserting rows into a table, if a unique constraint fails on one or more of the rows, then the insertion fails.
Using the on_conflict clause, we can instead tell the database to ignore
the error (using DO NOTHING), or to update the row (using DO UPDATE).
This is sometimes called an upsert (update if it already exists else insert).
Example data¶
If we have the following table:
class Band(Table):
name = Varchar(unique=True)
popularity = Integer()
With this data:
id |
name |
popularity |
1 |
Pythonistas |
1000 |
Let’s try inserting another row with the same name, and we’ll get an error:
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... )
Unique constraint error!
DO NOTHING¶
To ignore the error:
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO NOTHING"
... )
If we fetch the data from the database, we’ll see that it hasn’t changed:
>>> await Band.select().where(Band.name == "Pythonistas").first()
{'id': 1, 'name': 'Pythonistas', 'popularity': 1000}
DO UPDATE¶
Instead, if we want to update the popularity:
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO UPDATE",
... values=[Band.popularity]
... )
If we fetch the data from the database, we’ll see that it was updated:
>>> await Band.select().where(Band.name == "Pythonistas").first()
{'id': 1, 'name': 'Pythonistas', 'popularity': 1200}
target¶
Using the target argument, we can specify which constraint we’re concerned
with. By specifying target=Band.name we’re only concerned with the unique
constraint for the band column. If you omit the target argument, then
it works for all constraints on the table.
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO NOTHING",
... target=Band.name
... )
If you want to target a composite unique constraint, you can do so by passing in a tuple of columns:
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO NOTHING",
... target=(Band.name, Band.popularity)
... )
You can also specify the name of a constraint using a string:
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO NOTHING",
... target='some_constraint'
... )
values¶
This lets us specify which values to update when a conflict occurs.
By specifying a Column, this means that
the new value for that column will be used:
# The new popularity will be 1200.
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO UPDATE",
... values=[Band.popularity]
... )
Instead, we can specify a custom value using a tuple:
# The new popularity will be 1111.
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO UPDATE",
... values=[(Band.popularity, 1111)]
... )
If we want to update all of the values, we can use all_columns.
>>> await Band.insert(
... Band(id=1, name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO UPDATE",
... values=Band.all_columns()
... )
where¶
This can be used with DO UPDATE. It gives us more control over whether the
update should be made:
>>> await Band.insert(
... Band(id=1, name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO UPDATE",
... values=[Band.popularity],
... where=Band.popularity < 1000
... )
Multiple on_conflict clauses¶
SQLite allows you to specify multiple ON CONFLICT clauses, but Postgres and
Cockroach don’t.
>>> await Band.insert(
... Band(name="Pythonistas", popularity=1200)
... ).on_conflict(
... action="DO UPDATE",
... ...
... ).on_conflict(
... action="DO NOTHING",
... ...
... )
Learn more¶
Source¶
- Insert.on_conflict(target: t.Optional[t.Union[str, Column, t.Tuple[Column, ...]]] = None, action: t.Union[OnConflictAction, t.Literal['DO NOTHING', 'DO UPDATE']] = OnConflictAction.do_nothing, values: t.Optional[t.Sequence[t.Union[Column, t.Tuple[Column, t.Any]]]] = None, where: t.Optional[Combinable] = None) Self¶