Python sqlalchemy.delete方法代码示例

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_stmt_joinedwhereclause2():
    del_stmt = sa.delete(
        orders
    ).where(
        orders.c.customer_id == customers.c.id
    ).where(
        orders.c.id == items.c.order_id
    ).where(
        customers.c.email.endswith('test.com')
    ).where(
        items.c.name == 'test product'
    )
    expected = """
      DELETE FROM orders
      USING customers, items
      WHERE orders.customer_id = customers.id
      AND orders.id = items.order_id
      AND (customers.email LIKE '%%' || 'test.com')
      AND items.name = 'test product'"""

    assert clean(compile_query(del_stmt)) == clean(expected) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_stmt_subquery():
    del_stmt = sa.delete(
        orders
    ).where(
        orders.c.customer_id.in_(
            sa.select(
                [customers.c.id]
            ).where(customers.c.email.endswith('test.com'))
        )
    )
    expected = """
        DELETE FROM orders
        WHERE orders.customer_id IN
        (SELECT customers.id
        FROM customers
        WHERE (customers.email LIKE '%%' || 'test.com'))"""
    assert clean(compile_query(del_stmt)) == clean(expected) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_stmt_on_subquerycomma():
    del_stmt = sa.delete(
        ham
    ).where(
        ham.c.id.in_(
            sa.select(
                [hammy_spam.c.ham_id]
            )
        )
    )
    expected = """
        DELETE FROM ham
        WHERE ham.id IN
        (SELECT "ham, spam".ham_id
        FROM "ham, spam")"""
    assert clean(compile_query(del_stmt)) == clean(expected) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def read_only_read_sa_transaction(conn, deferrable):
    await conn.execute(sa.insert(users).values(id=1, name='test1'))
    t1 = await conn.begin(
        isolation_level='SERIALIZABLE',
        readonly=True,
        deferrable=deferrable
    )

    where = users.c.id == 1

    try:
        await conn.execute(sa.update(users).values({'name': 't'}).where(where))
    except InternalError as e:
        assert e.pgcode == '25006'

    await t1.commit()

    await conn.execute(sa.delete(users))
    assert len(await (await conn.execute(users.select())).fetchall()) == 0 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def success_nested_transaction(conn):
    await check_count_users(conn, count=0)

    async with conn.begin_nested():
        await conn.execute(sa.insert(users).values(id=1, name='test1'))

        async with conn.begin_nested():
            await conn.execute(sa.insert(users).values(id=2, name='test2'))

    await check_count_users(conn, count=2)

    async with conn.begin():
        await conn.execute(sa.delete(users).where(users.c.id == 1))
        await conn.execute(sa.delete(users).where(users.c.id == 2))

    await check_count_users(conn, count=0) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_schema(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="paj",
        )
        self.assert_compile(
            tbl.delete(tbl.c.id == 1),
            "DELETE FROM paj.test WHERE paj.test.id = " ":id_1",
        )
        s = select([tbl.c.id]).where(tbl.c.id == 1)
        self.assert_compile(
            tbl.delete().where(tbl.c.id.in_(s)),
            "DELETE FROM paj.test WHERE paj.test.id IN "
            "(SELECT paj.test.id FROM paj.test "
            "WHERE paj.test.id = :id_1)",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_schema_multipart(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="banana.paj",
        )
        self.assert_compile(
            tbl.delete(tbl.c.id == 1),
            "DELETE FROM banana.paj.test WHERE " "banana.paj.test.id = :id_1",
        )
        s = select([tbl.c.id]).where(tbl.c.id == 1)
        self.assert_compile(
            tbl.delete().where(tbl.c.id.in_(s)),
            "DELETE FROM banana.paj.test WHERE "
            "banana.paj.test.id IN (SELECT banana.paj.test.id "
            "FROM banana.paj.test WHERE "
            "banana.paj.test.id = :id_1)",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_schema_multipart_needs_quoting(self):
        metadata = MetaData()
        tbl = Table(
            "test",
            metadata,
            Column("id", Integer, primary_key=True),
            schema="banana split.paj",
        )
        self.assert_compile(
            tbl.delete(tbl.c.id == 1),
            "DELETE FROM [banana split].paj.test WHERE "
            "[banana split].paj.test.id = :id_1",
        )
        s = select([tbl.c.id]).where(tbl.c.id == 1)
        self.assert_compile(
            tbl.delete().where(tbl.c.id.in_(s)),
            "DELETE FROM [banana split].paj.test WHERE "
            "[banana split].paj.test.id IN ("
            "SELECT [banana split].paj.test.id FROM "
            "[banana split].paj.test WHERE "
            "[banana split].paj.test.id = :id_1)",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_returning(self):
        table1 = table(
            "mytable",
            column("myid", Integer),
            column("name", String(128)),
            column("description", String(128)),
        )
        d = delete(table1).returning(table1.c.myid, table1.c.name)
        self.assert_compile(
            d, "DELETE FROM mytable OUTPUT deleted.myid, " "deleted.name"
        )
        d = (
            delete(table1)
            .where(table1.c.name == "bar")
            .returning(table1.c.myid, table1.c.name)
        )
        self.assert_compile(
            d,
            "DELETE FROM mytable OUTPUT deleted.myid, "
            "deleted.name WHERE mytable.name = :name_1",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete(self):
        table1 = self.tables.mytable

        self.assert_compile(
            delete(table1, table1.c.myid == 7),
            "DELETE FROM mytable WHERE mytable.myid = :myid_1",
        )

        self.assert_compile(
            table1.delete().where(table1.c.myid == 7),
            "DELETE FROM mytable WHERE mytable.myid = :myid_1",
        )

        self.assert_compile(
            table1.delete()
            .where(table1.c.myid == 7)
            .where(table1.c.name == "somename"),
            "DELETE FROM mytable "
            "WHERE mytable.myid = :myid_1 "
            "AND mytable.name = :name_1",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_correlation_to_extra(self):
        table1, table2 = self.tables.mytable, self.tables.myothertable

        stmt = (
            table1.delete()
            .where(table1.c.myid == table2.c.otherid)
            .where(
                ~exists()
                .where(table2.c.otherid == table1.c.myid)
                .where(table2.c.othername == "x")
                .correlate(table2)
            )
        )

        self.assert_compile(
            stmt,
            "DELETE FROM mytable , myothertable WHERE mytable.myid = "
            "myothertable.otherid AND NOT (EXISTS "
            "(SELECT * FROM mytable WHERE myothertable.otherid = "
            "mytable.myid AND myothertable.othername = :othername_1))",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_dont_correlate_to_extra(self):
        table1, table2 = self.tables.mytable, self.tables.myothertable

        stmt = (
            table1.delete()
            .where(table1.c.myid == table2.c.otherid)
            .where(
                ~exists()
                .where(table2.c.otherid == table1.c.myid)
                .where(table2.c.othername == "x")
                .correlate()
            )
        )

        self.assert_compile(
            stmt,
            "DELETE FROM mytable , myothertable WHERE mytable.myid = "
            "myothertable.otherid AND NOT (EXISTS "
            "(SELECT * FROM myothertable, mytable "
            "WHERE myothertable.otherid = "
            "mytable.myid AND myothertable.othername = :othername_1))",
        ) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_exec_two_table(self):
        users, addresses = self.tables.users, self.tables.addresses
        dingalings = self.tables.dingalings

        with testing.db.connect() as conn:
            conn.execute(dingalings.delete())  # fk violation otherwise

            conn.execute(
                addresses.delete()
                .where(users.c.id == addresses.c.user_id)
                .where(users.c.name == "ed")
            )

            expected = [
                (1, 7, "x", "jack@bean.com"),
                (5, 9, "x", "fred@fred.com"),
            ]
        self._assert_table(addresses, expected) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_exec_two_table_plus_alias(self):
        users, addresses = self.tables.users, self.tables.addresses
        dingalings = self.tables.dingalings

        with testing.db.connect() as conn:
            conn.execute(dingalings.delete())  # fk violation otherwise
            a1 = addresses.alias()
            conn.execute(
                addresses.delete()
                .where(users.c.id == addresses.c.user_id)
                .where(users.c.name == "ed")
                .where(a1.c.id == addresses.c.id)
            )

        expected = [(1, 7, "x", "jack@bean.com"), (5, 9, "x", "fred@fred.com")]
        self._assert_table(addresses, expected) 

# 需要导入模块: import sqlalchemy [as 别名]
# 或者: from sqlalchemy import delete [as 别名]
def test_delete_invalid_evaluation(self):
        User = self.classes.User

        sess = Session()

        john, jack, jill, jane = sess.query(User).order_by(User.id).all()

        assert_raises(
            exc.InvalidRequestError,
            sess.query(User)
            .filter(
                User.name == select([func.max(User.name)]).scalar_subquery()
            )
            .delete,
            synchronize_session="evaluate",
        )

        sess.query(User).filter(
            User.name == select([func.max(User.name)]).scalar_subquery()
        ).delete(synchronize_session="fetch")

        assert john not in sess

        eq_(sess.query(User).order_by(User.id).all(), [jack, jill, jane])