sqlalchemy.dialects.postgresql.insert

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 7

3 Source : postgres_utils.py
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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

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

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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

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

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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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

    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