Here are the examples of the python api sqlalchemy.dialects.postgresql.insert taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
66 Examples
3
Source : postgres_utils.py
with MIT License
from blockchain-etl
with MIT License
from blockchain-etl
def create_insert_statement_for_table(table):
insert_stmt = insert(table)
primary_key_fields = [column.name for column in table.columns if column.primary_key]
if primary_key_fields:
insert_stmt = insert_stmt.on_conflict_do_update(
index_elements=primary_key_fields,
set_={
column.name: insert_stmt.excluded[column.name] for column in table.columns if not column.primary_key
}
)
return insert_stmt
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_str_index_elements_target_two(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
index_elements=["myid"], set_=dict(name=i.excluded.name)
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (myid) "
"DO UPDATE SET name = excluded.name",
)
def test_do_update_col_index_elements_target(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_col_index_elements_target(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
index_elements=[self.table1.c.myid],
set_=dict(name=i.excluded.name),
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (myid) "
"DO UPDATE SET name = excluded.name",
)
def test_do_update_unnamed_pk_constraint_target(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_unnamed_pk_constraint_target(self):
i = insert(self.table_with_metadata, values=dict(myid=1, name="foo"))
i = i.on_conflict_do_update(
constraint=self.table_with_metadata.primary_key,
set_=dict(name=i.excluded.name),
)
self.assert_compile(
i,
"INSERT INTO mytable (myid, name) VALUES "
"(%(myid)s, %(name)s) ON CONFLICT (myid) "
"DO UPDATE SET name = excluded.name",
)
def test_do_update_pk_constraint_index_elements_target(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_pk_constraint_index_elements_target(self):
i = insert(self.table_with_metadata, values=dict(myid=1, name="foo"))
i = i.on_conflict_do_update(
index_elements=self.table_with_metadata.primary_key,
set_=dict(name=i.excluded.name),
)
self.assert_compile(
i,
"INSERT INTO mytable (myid, name) VALUES "
"(%(myid)s, %(name)s) ON CONFLICT (myid) "
"DO UPDATE SET name = excluded.name",
)
def test_do_update_named_unique_constraint_target(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_named_unique_constraint_target(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
constraint=self.unique_constr, set_=dict(myid=i.excluded.myid)
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT ON CONSTRAINT uq_name "
"DO UPDATE SET myid = excluded.myid",
)
def test_do_update_string_constraint_target(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_string_constraint_target(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
constraint=self.unique_constr.name, set_=dict(myid=i.excluded.myid)
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT ON CONSTRAINT uq_name "
"DO UPDATE SET myid = excluded.myid",
)
def test_do_update_index_elements_where_target(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_index_elements_where_target(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
index_elements=self.goofy_index.expressions,
index_where=self.goofy_index.dialect_options["postgresql"][
"where"
],
set_=dict(name=i.excluded.name),
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (name) "
"WHERE name > %(name_1)s "
"DO UPDATE SET name = excluded.name",
)
def test_do_update_index_elements_where_target_multivalues(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_unnamed_index_target(self):
i = insert(self.table1, values=dict(name="foo"))
unnamed_goofy = Index(
None, self.table1.c.name, postgresql_where=self.table1.c.name > "m"
)
i = i.on_conflict_do_update(
constraint=unnamed_goofy, set_=dict(name=i.excluded.name)
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (name) "
"WHERE name > %(name_1)s "
"DO UPDATE SET name = excluded.name",
)
def test_do_update_unnamed_exclude_constraint_target(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_unnamed_exclude_constraint_target(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
constraint=self.excl_constr_anon, set_=dict(name=i.excluded.name)
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (name, description) "
"WHERE description != %(description_1)s "
"DO UPDATE SET name = excluded.name",
)
def test_do_update_add_whereclause(self):
3
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_add_whereclause_references_excluded(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
constraint=self.excl_constr_anon,
set_=dict(name=i.excluded.name),
where=((self.table1.c.name != i.excluded.name)),
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (name, description) "
"WHERE description != %(description_1)s "
"DO UPDATE SET name = excluded.name "
"WHERE mytable.name != excluded.name",
)
def test_do_update_additional_colnames(self):
3
Source : core_postgres.py
with MIT License
from tulir
with MIT License
from tulir
def process_entities(self, tlo: Any) -> None:
rows = self._entities_to_rows(tlo)
if not rows:
return
t = self.Entity.__table__
ins = insert(t)
upsert = ins.on_conflict_do_update(constraint=t.primary_key, set_={
"hash": ins.excluded.hash,
"username": ins.excluded.username,
"phone": ins.excluded.phone,
"name": ins.excluded.name,
})
with self.engine.begin() as conn:
conn.execute(upsert, [dict(session_id=self.session_id, id=row[0], hash=row[1],
username=row[2], phone=row[3], name=row[4])
for row in rows])
def cache_file(self, md5_digest: str, file_size: int,
0
Source : __init__.py
with GNU General Public License v3.0
from Codin-Nerds
with GNU General Public License v3.0
from Codin-Nerds
async def upsert(session: AsyncSession, model: Base, conflict_columns: list, values: dict) -> None:
"""
SQLAlchemy lacks postgres specific upsert function, this is
it's implementation to avoid code repetition in the database models.
"""
table = model.__table__
stmt = postgresql.insert(table)
affected_columns = {
col.name: col for col in stmt.excluded
if col.name in values and col.name not in conflict_columns
}
if not affected_columns:
raise ValueError("Couldn't find any columns to update.")
stmt = stmt.on_conflict_do_update(
index_elements=conflict_columns,
set_=affected_columns
)
await session.execute(stmt, values)
# region: Common getters for tables
def get_str_guild(guild: t.Union[str, int, Guild]) -> str:
0
Source : models.py
with MIT License
from covid-projections
with MIT License
from covid-projections
def build_insert_from_temp(
insert_op: str,
cls: Union[Type[TemptableOfficialNoLocation], Type[TemptableOfficialHasLocation]],
engine: Engine,
):
print("Have insert_op = ", insert_op)
columns = [
cls.dt,
Location.id.label("location_id"),
CovidVariable.id.label("variable_id"),
CovidDemographic.id.label("demographic_id"),
cls.value,
CovidProvider.id.label("provider_id"),
cls.last_updated,
cls.source_url,
cls.source_name,
]
selector = (
select(columns)
.where(cls.insert_op == insert_op)
.select_from(
(
cls.__table__.join(Location, isouter=True)
.join(CovidProvider, isouter=True)
.join(CovidDemographic, isouter=True)
.join(
CovidVariable,
and_(
cls.category == CovidVariable.category,
cls.measurement == CovidVariable.measurement,
cls.unit == CovidVariable.unit,
),
isouter=True,
)
)
)
)
covid_table = CovidObservation.__table__
if "postgres" in engine.dialect.name:
from sqlalchemy.dialects.postgresql import insert
ins = insert(covid_table)
statement = ins.from_select([x.name for x in columns], selector)
return statement.on_conflict_do_update(
index_elements=[x.name for x in covid_table.primary_key.columns],
set_=dict(
value=statement.excluded.value,
last_updated=statement.excluded.last_updated,
provider_id=statement.excluded.provider_id,
deleted=False,
delete_batch_id=None,
),
)
ins = covid_table.insert()
return ins.from_select([x.name for x in columns], selector)
def _bootstrap_csv_to_orm(cls: Type[Base], engine: Engine):
0
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_index_elements_where_target_multivalues(self):
i = insert(
self.table1,
values=[dict(name="foo"), dict(name="bar"), dict(name="bat")],
)
i = i.on_conflict_do_update(
index_elements=self.goofy_index.expressions,
index_where=self.goofy_index.dialect_options["postgresql"][
"where"
],
set_=dict(name=i.excluded.name),
)
self.assert_compile(
i,
"INSERT INTO mytable (name) "
"VALUES (%(name_m0)s), (%(name_m1)s), (%(name_m2)s) "
"ON CONFLICT (name) "
"WHERE name > %(name_1)s "
"DO UPDATE SET name = excluded.name",
checkparams={
"name_1": "m",
"name_m0": "foo",
"name_m1": "bar",
"name_m2": "bat",
},
)
def test_do_update_unnamed_index_target(self):
0
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_add_whereclause(self):
i = insert(self.table1, values=dict(name="foo"))
i = i.on_conflict_do_update(
constraint=self.excl_constr_anon,
set_=dict(name=i.excluded.name),
where=(
(self.table1.c.name != "brah")
& (self.table1.c.description != "brah")
),
)
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (name, description) "
"WHERE description != %(description_1)s "
"DO UPDATE SET name = excluded.name "
"WHERE mytable.name != %(name_1)s "
"AND mytable.description != %(description_2)s",
)
def test_do_update_add_whereclause_references_excluded(self):
0
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_do_update_additional_colnames(self):
i = insert(self.table1, values=dict(name="bar"))
i = i.on_conflict_do_update(
constraint=self.excl_constr_anon,
set_=dict(name="somename", unknown="unknown"),
)
with expect_warnings(
"Additional column names not matching any "
"column keys in table 'mytable': 'unknown'"
):
self.assert_compile(
i,
"INSERT INTO mytable (name) VALUES "
"(%(name)s) ON CONFLICT (name, description) "
"WHERE description != %(description_1)s "
"DO UPDATE SET name = %(param_1)s, "
"unknown = %(param_2)s",
checkparams={
"name": "bar",
"description_1": "foo",
"param_1": "somename",
"param_2": "unknown",
},
)
def test_on_conflict_as_cte(self):
0
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_on_conflict_as_cte(self):
i = insert(self.table1, values=dict(name="foo"))
i = (
i.on_conflict_do_update(
constraint=self.excl_constr_anon,
set_=dict(name=i.excluded.name),
where=((self.table1.c.name != i.excluded.name)),
)
.returning(literal_column("1"))
.cte("i_upsert")
)
stmt = select([i])
self.assert_compile(
stmt,
"WITH i_upsert AS "
"(INSERT INTO mytable (name) VALUES (%(name)s) "
"ON CONFLICT (name, description) "
"WHERE description != %(description_1)s "
"DO UPDATE SET name = excluded.name "
"WHERE mytable.name != excluded.name RETURNING 1) "
"SELECT i_upsert.1 "
"FROM i_upsert",
)
def test_quote_raw_string_col(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_on_conflict_do_nothing(self):
users = self.tables.users
with testing.db.connect() as conn:
result = conn.execute(
insert(users).on_conflict_do_nothing(),
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(),
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):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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)],
)
@testing.provide_metadata
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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_update_one(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_on_conflict_do_update_one(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.c.id], set_=dict(name=i.excluded.name)
)
result = conn.execute(i, dict(id=1, name="name1"))
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_update_two(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_on_conflict_do_update_two(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.c.id],
set_=dict(id=i.excluded.id, name=i.excluded.name),
)
result = conn.execute(i, 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, "name2")],
)
def test_on_conflict_do_update_three(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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_four(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_on_conflict_do_update_four(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=i.excluded.id, name=i.excluded.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 == 1)).fetchall(),
[(1, "name4")],
)
def test_on_conflict_do_update_five(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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_multivalues(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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 _exotic_targets_fixture(self, conn):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def _exotic_targets_fixture(self, conn):
users = self.tables.users_xtra
conn.execute(
insert(users),
dict(
id=1,
name="name1",
login_email="[email protected]",
lets_index_this="not",
),
)
conn.execute(
users.insert(),
dict(
id=2,
name="name2",
login_email="[email protected]",
lets_index_this="not",
),
)
eq_(
conn.execute(users.select().where(users.c.id == 1)).fetchall(),
[(1, "name1", "[email protected]", "not")],
)
def test_on_conflict_do_update_exotic_targets_two(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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="[email protected]",
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", "[email protected]", "not")],
)
def test_on_conflict_do_update_exotic_targets_three(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_on_conflict_do_update_exotic_targets_three(self):
users = self.tables.users_xtra
with testing.db.connect() as conn:
self._exotic_targets_fixture(conn)
# try unique constraint: cause an upsert on target
# login_email, not id
i = insert(users)
i = i.on_conflict_do_update(
constraint=self.unique_constraint,
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=42,
name="nameunique",
login_email="[email protected]",
lets_index_this="unique",
),
)
eq_(result.inserted_primary_key, [42])
eq_(result.returned_defaults, None)
eq_(
conn.execute(
users.select().where(
users.c.login_email == "[email protected]"
)
).fetchall(),
[(42, "nameunique", "[email protected]", "not")],
)
def test_on_conflict_do_update_exotic_targets_four(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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="[email protected]",
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 == "[email protected]"
)
).fetchall(),
[(43, "nameunique2", "[email protected]", "not")],
)
def test_on_conflict_do_update_exotic_targets_four_no_pk(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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="[email protected]")
)
eq_(result.inserted_primary_key, [1])
eq_(result.returned_defaults, (1,))
eq_(
conn.execute(users.select().order_by(users.c.id)).fetchall(),
[
(1, "name3", "[email protected]", "not"),
(2, "name2", "[email protected]", "not"),
],
)
def test_on_conflict_do_update_exotic_targets_five(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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="[email protected]",
lets_index_this="bogus",
),
)
def test_on_conflict_do_update_exotic_targets_six(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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="[email protected]",
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="[email protected]",
lets_index_this="unique_name",
)
],
)
eq_(
conn.execute(users.select()).fetchall(),
[(1, "name1", "[email protected]", "unique_name")],
)
def test_on_conflict_do_update_no_row_actually_affected(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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="[email protected]")
)
eq_(result.returned_defaults, None)
eq_(result.inserted_primary_key, None)
eq_(
conn.execute(users.select()).fetchall(),
[
(1, "name1", "[email protected]", "not"),
(2, "name2", "[email protected]", "not"),
],
)
def test_on_conflict_do_update_special_types_in_set(self):
0
Source : test_on_conflict.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
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",
)
0
Source : games.py
with MIT License
from lexicalunit
with MIT License
from lexicalunit
def record_plays(self) -> None:
assert self.game
assert self.game.status == GameStatus.STARTED.value
rows = DatabaseSession.query(User.xid).filter(User.game_id == self.game.id)
player_xids = [int(row[0]) for row in rows]
game_id = self.game.id
guild_xid = self.game.guild_xid
# upsert into plays
DatabaseSession.execute(
insert(Play)
.values(
[
dict(user_xid=player_xid, game_id=game_id)
for player_xid in player_xids
],
)
.on_conflict_do_nothing(),
)
# upsert into user_awards
DatabaseSession.execute(
insert(UserAward)
.values(
[
dict(guild_xid=guild_xid, user_xid=player_xid)
for player_xid in player_xids
],
)
.on_conflict_do_nothing(),
)
DatabaseSession.commit()
@sync_to_async
0
Source : on_conflict.py
with GNU Affero General Public License v3.0
from OpenQueue
with GNU Affero General Public License v3.0
from OpenQueue
def on_scoreboard_conflict() -> Any:
"""Used for updating a player on a scoreboard on conflict.
"""
if Config.database.engine == "mysql":
query_insert = mysql_insert(scoreboard_table)
return query_insert.on_duplicate_key_update(
captain=scoreboard_table.c.captain,
team=query_insert.inserted.team,
alive=query_insert.inserted.alive,
ping=query_insert.inserted.ping,
kills=scoreboard_table.c.kills + query_insert.inserted.kills,
headshots=scoreboard_table.c.headshots +
query_insert.inserted.headshots,
assists=scoreboard_table.c.assists + query_insert.inserted.assists,
deaths=scoreboard_table.c.deaths + query_insert.inserted.deaths,
shots_fired=scoreboard_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=scoreboard_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=scoreboard_table.c.mvps + query_insert.inserted.mvps,
score=scoreboard_table.c.score + query_insert.inserted.score,
disconnected=query_insert.inserted.disconnected
)
elif Config.database.engine == "psycopg2":
query_insert = postgresql_insert(scoreboard_table)
return query_insert.on_conflict_do_update(
set_=dict(
captain=scoreboard_table.c.captain,
team=query_insert.inserted.team,
alive=query_insert.inserted.alive,
ping=query_insert.inserted.ping,
kills=scoreboard_table.c.kills + query_insert.inserted.kills,
headshots=scoreboard_table.c.headshots +
query_insert.inserted.headshots,
assists=scoreboard_table.c.assists
+ query_insert.inserted.assists,
deaths=scoreboard_table.c.deaths
+ query_insert.inserted.deaths,
shots_fired=scoreboard_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=scoreboard_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=scoreboard_table.c.mvps + query_insert.inserted.mvps,
score=scoreboard_table.c.score + query_insert.inserted.score,
disconnected=query_insert.inserted.disconnected
)
)
else:
return scoreboard_table.insert
def on_statistic_conflict() -> Any:
0
Source : on_conflict.py
with GNU Affero General Public License v3.0
from OpenQueue
with GNU Affero General Public License v3.0
from OpenQueue
def on_statistic_conflict() -> Any:
"""Used for updating a statistics on conflict.
"""
if Config.database.engine == "mysql":
query_insert = mysql_insert(statistic_table)
return query_insert.on_duplicate_key_update(
kills=statistic_table.c.kills + query_insert.inserted.kills,
headshots=statistic_table.c.headshots +
query_insert.inserted.headshots,
assists=statistic_table.c.assists + query_insert.inserted.assists,
deaths=statistic_table.c.deaths + query_insert.inserted.deaths,
shots_fired=statistic_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=statistic_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=statistic_table.c.mvps + query_insert.inserted.mvps,
elo=statistic_table.c.elo + query_insert.inserted.elo
)
elif Config.database.engine == "psycopg2":
query_insert = postgresql_insert(statistic_table)
return query_insert.on_conflict_do_update(
set_=dict(
kills=statistic_table.c.kills + query_insert.inserted.kills,
headshots=statistic_table.c.headshots +
query_insert.inserted.headshots,
assists=statistic_table.c.assists +
query_insert.inserted.assists,
deaths=statistic_table.c.deaths + query_insert.inserted.deaths,
shots_fired=statistic_table.c.shots_fired +
query_insert.inserted.shots_fired,
shots_hit=statistic_table.c.shots_hit +
query_insert.inserted.shots_hit,
mvps=statistic_table.c.mvps + query_insert.inserted.mvps,
elo=statistic_table.c.elo + query_insert.inserted.elo
)
)
else:
return statistic_table.insert
0
Source : test_compiler.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_no_call_twice(self):
users = self.table1
for stmt in (
insert(users).on_conflict_do_nothing(),
insert(users).on_conflict_do_update(
index_elements=[users.c.myid], set_=dict(name="foo")
),
):
for meth in (
stmt.on_conflict_do_nothing,
stmt.on_conflict_do_update,
):
with testing.expect_raises_message(
exc.InvalidRequestError,
"This Insert construct already has an "
"ON CONFLICT clause established",
):
meth()
def test_on_conflict_cte_plus_textual(self):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_nothing(self, connection):
users = self.tables.users
result = connection.execute(
insert(users).on_conflict_do_nothing(),
dict(id=1, name="name1"),
)
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
result = connection.execute(
insert(users).on_conflict_do_nothing(),
dict(id=1, name="name2"),
)
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
eq_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1")],
)
def test_on_conflict_do_nothing_connectionless(self, connection):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_nothing_connectionless(self, connection):
users = self.tables.users_xtra
result = connection.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 = connection.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_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1", "email1", None)],
)
@testing.provide_metadata
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_nothing_target(self, connection):
users = self.tables.users
result = connection.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 = connection.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_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1")],
)
@testing.combinations(
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_update_one(self, connection, with_dict):
users = self.tables.users
connection.execute(users.insert(), dict(id=1, name="name1"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.id],
set_=dict(name=i.excluded.name) if with_dict else i.excluded,
)
result = connection.execute(i, dict(id=1, name="name1"))
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
eq_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1")],
)
def test_on_conflict_do_update_set_executemany(self, connection):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_update_set_executemany(self, connection):
"""test #6581"""
users = self.tables.users
connection.execute(
users.insert(),
[dict(id=1, name="name1"), dict(id=2, name="name2")],
)
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.id],
set_={"id": i.excluded.id, "name": i.excluded.name + ".5"},
)
connection.execute(
i,
[
dict(id=1, name="name1"),
dict(id=2, name="name2"),
dict(id=3, name="name3"),
],
)
eq_(
connection.execute(users.select().order_by(users.c.id)).fetchall(),
[(1, "name1.5"), (2, "name2.5"), (3, "name3")],
)
def test_on_conflict_do_update_schema(self, connection):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_update_schema(self, connection):
users = self.tables.get("%s.users_schema" % config.test_schema)
connection.execute(users.insert(), dict(id=1, name="name1"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.id], set_=dict(name=i.excluded.name)
)
result = connection.execute(i, dict(id=1, name="name1"))
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
eq_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1")],
)
def test_on_conflict_do_update_column_as_key_set(self, connection):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_update_column_as_key_set(self, connection):
users = self.tables.users
connection.execute(users.insert(), dict(id=1, name="name1"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.id],
set_={users.c.name: i.excluded.name},
)
result = connection.execute(i, dict(id=1, name="name1"))
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
eq_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1")],
)
def test_on_conflict_do_update_clauseelem_as_key_set(self, connection):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_update_clauseelem_as_key_set(self, connection):
users = self.tables.users
class MyElem:
def __init__(self, expr):
self.expr = expr
def __clause_element__(self):
return self.expr
connection.execute(
users.insert(),
{"id": 1, "name": "name1"},
)
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.id],
set_={MyElem(users.c.name): i.excluded.name},
).values({MyElem(users.c.id): 1, MyElem(users.c.name): "name1"})
result = connection.execute(i)
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
eq_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1")],
)
def test_on_conflict_do_update_column_as_key_set_schema(self, connection):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_update_column_as_key_set_schema(self, connection):
users = self.tables.get("%s.users_schema" % config.test_schema)
connection.execute(users.insert(), dict(id=1, name="name1"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.id],
set_={users.c.name: i.excluded.name},
)
result = connection.execute(i, dict(id=1, name="name1"))
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
eq_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name1")],
)
def test_on_conflict_do_update_two(self, connection):
0
Source : test_on_conflict.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_on_conflict_do_update_two(self, connection):
users = self.tables.users
connection.execute(users.insert(), dict(id=1, name="name1"))
i = insert(users)
i = i.on_conflict_do_update(
index_elements=[users.c.id],
set_=dict(id=i.excluded.id, name=i.excluded.name),
)
result = connection.execute(i, dict(id=1, name="name2"))
eq_(result.inserted_primary_key, (1,))
eq_(result.returned_defaults, None)
eq_(
connection.execute(
users.select().where(users.c.id == 1)
).fetchall(),
[(1, "name2")],
)
def test_on_conflict_do_update_three(self, connection):
See More Examples