def test_on_conflict_do_nothing_target(self):
users = self.tables.users
with testing.db.connect() as conn:
result = conn.execute(
insert(users).on_conflict_do_nothing(
index_elements=users.primary_key.columns
),
dict(id=1, name="name1"),
)
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, None)
result = conn.execute(
insert(users).on_conflict_do_nothing(
index_elements=users.primary_key.columns
),
dict(id=1, name="name2"),
)
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, None)
eq_(
conn.execute(users.select().where(users.c.id == 1)).fetchall(),
[(1, "name1")],
)
def test_on_conflict_do_nothing_connectionless(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
result = conn.execute(
insert(users).on_conflict_do_nothing(
constraint="uq_login_email"
),
dict(name="name1", login_email="email1"),
)
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, (1,))
result = testing.db.execute(
insert(users).on_conflict_do_nothing(constraint="uq_login_email"),
dict(name="name2", login_email="email1"),
)
eq_(result.inserted_primary_key, None)
eq_(result.returned_defaults, None)
eq_(
testing.db.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1", "email1", None)],
)
def test_on_conflict_do_update_no_row_actually_affected(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
self._exotic_targets_fixture(conn)
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.login_email],
set_=dict(name="new_name"),
where=(i.excluded.name == "other_name"),
)
result = conn.execute(
i, dict(name="name2", login_email="*****@*****.**")
)
eq_(result.returned_defaults, None)
eq_(result.inserted_primary_key, None)
eq_(
conn.execute(users.select()).fetchall(),
[
(1, "name1", "*****@*****.**", "not"),
(2, "name2", "*****@*****.**", "not"),
],
)
def test_on_conflict_do_update_exotic_targets_five(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
self._exotic_targets_fixture(conn)
# try bogus index
i = insert(users)
i = i.on_conflict_do_update(
index_elements=self.bogus_index.columns,
index_where=self.bogus_index.dialect_options["postgresql"][
"where"
],
set_=dict(
name=i.excluded.name, login_email=i.excluded.login_email
),
)
assert_raises(
exc.ProgrammingError,
conn.execute,
i,
dict(
id=1,
name="namebogus",
login_email="*****@*****.**",
lets_index_this="bogus",
),
)
def test_on_conflict_do_update_exotic_targets_four_no_pk(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
self._exotic_targets_fixture(conn)
# try unique constraint by name: cause an
# upsert on target login_email, not id
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.login_email],
set_=dict(
id=i.excluded.id,
name=i.excluded.name,
login_email=i.excluded.login_email,
),
)
result = conn.execute(
i, dict(name="name3", login_email="*****@*****.**")
)
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, (1,))
eq_(
conn.execute(users.select().order_by(users.c.id)).fetchall(),
[
(1, "name3", "*****@*****.**", "not"),
(2, "name2", "*****@*****.**", "not"),
],
)
def test_on_conflict_do_update_exotic_targets_two(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
self._exotic_targets_fixture(conn)
# try primary key constraint: cause an upsert on unique id column
i = insert(users)
i = i.on_conflict_do_update(
index_elements=users.primary_key.columns,
set_=dict(
name=i.excluded.name, login_email=i.excluded.login_email
),
)
result = conn.execute(
i,
dict(
id=1,
name="name2",
login_email="*****@*****.**",
lets_index_this="not",
),
)
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, None)
eq_(
conn.execute(users.select().where(users.c.id == 1)).fetchall(),
[(1, "name2", "*****@*****.**", "not")],
)
def _exotic_targets_fixture(self, conn):
users = self.tables.users_xtra
conn.execute(
insert(users),
dict(
id=1,
name="name1",
login_email="*****@*****.**",
lets_index_this="not",
),
)
conn.execute(
users.insert(),
dict(
id=2,
name="name2",
login_email="*****@*****.**",
lets_index_this="not",
),
)
eq_(
conn.execute(users.select().where(users.c.id == 1)).fetchall(),
[(1, "name1", "*****@*****.**", "not")],
)
def test_on_conflict_do_update_multivalues(self):
users = self.tables.users
with testing.db.connect() as conn:
conn.execute(users.insert(), dict(id=1, name="name1"))
conn.execute(users.insert(), dict(id=2, name="name2"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=users.primary_key.columns,
set_=dict(name="updated"),
where=(i.excluded.name != "name12"),
).values(
[
dict(id=1, name="name11"),
dict(id=2, name="name12"),
dict(id=3, name="name13"),
dict(id=4, name="name14"),
]
)
result = conn.execute(i)
eq_(result.inserted_primary_key, [None])
eq_(result.returned_defaults, None)
eq_(
conn.execute(users.select().order_by(users.c.id)).fetchall(),
[(1, "updated"), (2, "name2"), (3, "name13"), (4, "name14")],
)
def test_bad_args(self):
assert_raises(
ValueError,
insert(self.tables.users).on_conflict_do_nothing,
constraint="id",
index_elements=["id"],
)
assert_raises(
ValueError,
insert(self.tables.users).on_conflict_do_update,
constraint="id",
index_elements=["id"],
)
assert_raises(
ValueError,
insert(self.tables.users).on_conflict_do_update,
constraint="id",
)
assert_raises(
ValueError, insert(self.tables.users).on_conflict_do_update
)
def test_on_conflict_do_update_exotic_targets_six(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
conn.execute(
insert(users),
dict(
id=1,
name="name1",
login_email="*****@*****.**",
lets_index_this="unique_name",
),
)
i = insert(users)
i = i.on_conflict_do_update(
index_elements=self.unique_partial_index.columns,
index_where=self.unique_partial_index.dialect_options[
"postgresql"
]["where"],
set_=dict(
name=i.excluded.name, login_email=i.excluded.login_email
),
)
conn.execute(
i,
[
dict(
name="name1",
login_email="*****@*****.**",
lets_index_this="unique_name",
)
],
)
eq_(
conn.execute(users.select()).fetchall(),
[(1, "name1", "*****@*****.**", "unique_name")],
)
def test_on_conflict_do_update_special_types_in_set(self):
bind_targets = self.tables.bind_targets
with testing.db.connect() as conn:
i = insert(bind_targets)
conn.execute(i, {"id": 1, "data": "initial data"})
eq_(
conn.scalar(sql.select([bind_targets.c.data])),
"initial data processed",
)
i = insert(bind_targets)
i = i.on_conflict_do_update(
index_elements=[bind_targets.c.id],
set_=dict(data="new updated data"),
)
conn.execute(i, {"id": 1, "data": "new inserted data"})
eq_(
conn.scalar(sql.select([bind_targets.c.data])),
"new updated data processed",
)
def test_on_conflict_do_update_three(self):
users = self.tables.users
with testing.db.connect() as conn:
conn.execute(users.insert(), dict(id=1, name="name1"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=users.primary_key.columns,
set_=dict(name=i.excluded.name),
)
result = conn.execute(i, dict(id=1, name="name3"))
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, None)
eq_(
conn.execute(users.select().where(users.c.id == 1)).fetchall(),
[(1, "name3")],
)
def test_on_conflict_do_update_five(self):
users = self.tables.users
with testing.db.connect() as conn:
conn.execute(users.insert(), dict(id=1, name="name1"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=users.primary_key.columns,
set_=dict(id=10, name="I'm a name"),
).values(id=1, name="name4")
result = conn.execute(i)
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, None)
eq_(
conn.execute(
users.select().where(users.c.id == 10)
).fetchall(),
[(10, "I'm a name")],
)
def test_on_conflict_do_update_exotic_targets_four(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
self._exotic_targets_fixture(conn)
# try unique constraint by name: cause an
# upsert on target login_email, not id
i = insert(users)
i = i.on_conflict_do_update(
constraint=self.unique_constraint.name,
set_=dict(
id=i.excluded.id,
name=i.excluded.name,
login_email=i.excluded.login_email,
),
)
# note: lets_index_this value totally ignored in SET clause.
result = conn.execute(
i,
dict(
id=43,
name="nameunique2",
login_email="*****@*****.**",
lets_index_this="unique",
),
)
eq_(result.inserted_primary_key, [43])
eq_(result.returned_defaults, None)
eq_(
conn.execute(
users.select().where(
users.c.login_email == "*****@*****.**"
)
).fetchall(),
[(43, "nameunique2", "*****@*****.**", "not")],
)