Python sqlalchemy.testing.against() Examples

def test_bind_in(self, connection):
        """test calling IN against a bind parameter.

        this isn't allowed on several platforms since we
        generate ? = ?.

        """

        connection.execute(users.insert(), user_id=7, user_name="jack")
        connection.execute(users.insert(), user_id=8, user_name="fred")
        connection.execute(users.insert(), user_id=9, user_name=None)

        u = bindparam("search_key", type_=String)

        s = users.select(not_(u.in_([])))
        r = connection.execute(s, search_key="john").fetchall()
        assert len(r) == 3
        r = connection.execute(s, search_key=None).fetchall()
        assert len(r) == 3 

def define_tables(cls, metadata):
        # the actual function isn't reflected yet
        dv = Table(
            "data_values",
            metadata,
            Column("modulus", Integer, nullable=False),
            Column("data", String(30)),
            Column("q", Integer),
            postgresql_partition_by="range(modulus)",
        )

        # looks like this is reflected prior to #4237
        sa.event.listen(
            dv,
            "after_create",
            sa.DDL(
                "CREATE TABLE data_values_4_10 PARTITION OF data_values "
                "FOR VALUES FROM (4) TO (10)"
            ),
        )

        if testing.against("postgresql >= 11"):
            Index("my_index", dv.c.q) 

def test_reflect_unicode_no_nvarchar(self):
        metadata = self.metadata
        Table("tnv", metadata, Column("data", sqltypes.Unicode(255)))
        metadata.create_all()
        m2 = MetaData(testing.db)
        t2 = Table("tnv", m2, autoload=True)
        assert isinstance(t2.c.data.type, sqltypes.VARCHAR)

        if testing.against("oracle+cx_oracle"):
            assert isinstance(
                t2.c.data.type.dialect_impl(testing.db.dialect),
                cx_oracle._OracleString,
            )

        data = u("m’a réveillé.")
        t2.insert().execute(data=data)
        res = t2.select().execute().first()["data"]
        eq_(res, data)
        assert isinstance(res, util.text_type) 

def define_tables(cls, metadata):
        if testing.against("oracle"):
            fk_args = dict(deferrable=True, initially="deferred")
        elif testing.against("mysql"):
            fk_args = {}
        else:
            fk_args = dict(onupdate="cascade")

        Table(
            "users",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
        )
        Table(
            "addresses",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("user_id", Integer, ForeignKey("users.id", **fk_args)),
        ) 

def test_col_w_optional_sequence_non_autoinc_no_firing(
        self, dataset_no_autoinc, connection
    ):
        """this is testing that a Table which includes a Sequence, when
        run against a DB that does not support sequences, the Sequence
        does not get in the way.

        """
        dataset_no_autoinc.c.set_id.default.optional = True

        connection.execute(dataset_no_autoinc.insert())
        eq_(
            connection.scalar(
                select([func.count("*")]).select_from(dataset_no_autoinc)
            ),
            1,
        ) 

def test_int_default_none_on_insert(self, connection):
        metadata = self.metadata
        t = Table(
            "x",
            metadata,
            Column("y", Integer, server_default="5", primary_key=True),
            Column("data", String(10)),
            implicit_returning=False,
        )
        assert t._autoincrement_column is None
        metadata.create_all(connection)
        r = connection.execute(t.insert(), dict(data="data"))
        eq_(r.inserted_primary_key, (None,))
        if testing.against("sqlite"):
            eq_(list(connection.execute(t.select())), [(1, "data")])
        else:
            eq_(list(connection.execute(t.select())), [(5, "data")]) 

def test_int_default_none_on_insert_reflected(self, connection):
        metadata = self.metadata
        Table(
            "x",
            metadata,
            Column("y", Integer, server_default="5", primary_key=True),
            Column("data", String(10)),
            implicit_returning=False,
        )
        metadata.create_all(connection)

        m2 = MetaData()
        t2 = Table("x", m2, autoload_with=connection, implicit_returning=False)

        r = connection.execute(t2.insert(), dict(data="data"))
        eq_(r.inserted_primary_key, (None,))
        if testing.against("sqlite"):
            eq_(list(connection.execute(t2.select())), [(1, "data")])
        else:
            eq_(list(connection.execute(t2.select())), [(5, "data")]) 

def test_close_transaction_on_commit_fail(self):
        T2, t1 = self.classes.T2, self.tables.t1

        session = create_session(autocommit=True)

        # with a deferred constraint, this fails at COMMIT time instead
        # of at INSERT time.
        session.add(T2(t1_id=123))

        try:
            session.flush()
            assert False
        except Exception:
            # Flush needs to rollback also when commit fails
            assert session.transaction is None

        # todo: on 8.3 at least, the failed commit seems to close the cursor?
        # needs investigation.  leaving in the DDL above now to help verify
        # that the new deferrable support on FK isn't involved in this issue.
        if testing.against("postgresql"):
            t1.bind.engine.dispose() 

def test_empty_in_filtering_static(self, connection):
        """test the behavior of the in_() function when
        comparing against an empty collection, specifically
        that a proper boolean value is generated.

        """

        connection.execute(
            users.insert(),
            [
                {"user_id": 7, "user_name": "jack"},
                {"user_id": 8, "user_name": "ed"},
                {"user_id": 9, "user_name": None},
            ],
        )

        s = users.select(users.c.user_name.in_([]) == True)  # noqa
        r = connection.execute(s).fetchall()
        assert len(r) == 0
        s = users.select(users.c.user_name.in_([]) == False)  # noqa
        r = connection.execute(s).fetchall()
        assert len(r) == 3
        s = users.select(users.c.user_name.in_([]) == None)  # noqa
        r = connection.execute(s).fetchall()
        assert len(r) == 0 

def extras(self):
        # done this way so we don't get ImportErrors with
        # older psycopg2 versions.
        if testing.against("postgresql+psycopg2cffi"):
            from psycopg2cffi import extras
        else:
            from psycopg2 import extras
        return extras 

def define_tables(cls, metadata):
        Table(
            "multipk1",
            metadata,
            Column(
                "multi_id",
                Integer,
                primary_key=True,
                test_needs_autoincrement=not testing.against("sqlite"),
            ),
            Column("multi_rev", Integer, primary_key=True),
            Column("name", String(50), nullable=False),
            Column("value", String(100)),
        )

        Table(
            "multipk2",
            metadata,
            Column("pk_col_1", String(30), primary_key=True),
            Column("pk_col_2", String(30), primary_key=True),
            Column("data", String(30)),
        )
        Table(
            "multipk3",
            metadata,
            Column("pri_code", String(30), key="primary", primary_key=True),
            Column("sec_code", String(30), key="secondary", primary_key=True),
            Column("date_assigned", sa.Date, key="assigned", primary_key=True),
            Column("data", String(30)),
        ) 

def test_column_accessor_sqlite_raw(self, connection):
        users = self.tables.users

        connection.execute(users.insert(), dict(user_id=1, user_name="john"))

        r = connection.execute(
            text(
                "select users.user_id, users.user_name "
                "from users "
                "UNION select users.user_id, "
                "users.user_name from users",
                bind=testing.db,
            ).execution_options(sqlite_raw_colnames=True)
        ).first()

        if testing.against("sqlite < 3.10.0"):
            not_in_("user_id", r)
            not_in_("user_name", r)
            eq_(r["users.user_id"], 1)
            eq_(r["users.user_name"], "john")

            eq_(list(r._fields), ["users.user_id", "users.user_name"])
        else:
            not_in_("users.user_id", r._mapping)
            not_in_("users.user_name", r._mapping)
            eq_(r._mapping["user_id"], 1)
            eq_(r._mapping["user_name"], "john")

            eq_(list(r._fields), ["user_id", "user_name"]) 

def test_keyed_accessor_composite_conflict_2(self, connection):
        keyed1 = self.tables.keyed1
        keyed2 = self.tables.keyed2

        row = connection.execute(
            select([keyed1, keyed2]).select_from(keyed1.join(keyed2, true()))
        ).first()

        # column access is unambiguous
        eq_(row._mapping[self.tables.keyed2.c.b], "b2")

        # row.a is ambiguous
        assert_raises_message(
            exc.InvalidRequestError, "Ambig", getattr, row, "a"
        )

        # for "b" we have kind of a choice.  the name "b" is not ambiguous in
        # cursor.description in this case.  It is however ambiguous as far as
        # the objects we have queried against, because keyed1.c.a has key="b"
        # and keyed1.c.b is "b".   historically this was allowed as
        # non-ambiguous, however the column it targets changes based on
        # whether or not the dupe is present so it's ambiguous
        # eq_(row.b, "b2")
        assert_raises_message(
            exc.InvalidRequestError, "Ambig", getattr, row, "b"
        )

        # illustrate why row.b above is ambiguous, and not "b2"; because
        # if we didn't have keyed2, now it matches row.a.  a new column
        # shouldn't be able to grab the value from a previous column.
        row = connection.execute(select([keyed1])).first()
        eq_(row.b, "a1") 

def test_literal_returning(self, connection):
        if testing.against("postgresql"):
            literal_true = "true"
        else:
            literal_true = "1"

        result4 = connection.exec_driver_sql(
            'insert into tables (id, persons, "full") '
            "values (5, 10, %s) returning persons" % literal_true
        )
        eq_([dict(row._mapping) for row in result4], [{"persons": 10}]) 

def test_adapt_result_columns(self, connection, stmt_fn):
        """test adaptation of a CursorResultMetadata to another one.


        This copies the _keymap from one to the other in terms of the
        selected columns of a target selectable.

        This is used by the statement caching process to re-use the
        CursorResultMetadata from the cached statement against the same
        statement sent separately.

        """

        stmt1 = stmt_fn(self)
        stmt2 = stmt_fn(self)

        eq_(stmt1._generate_cache_key(), stmt2._generate_cache_key())

        column_linkage = dict(
            zip(stmt1.selected_columns, stmt2.selected_columns)
        )

        result = connection.execute(stmt1)

        mock_context = Mock(
            compiled=result.context.compiled, invoked_statement=stmt2
        )
        existing_metadata = result._metadata
        adapted_metadata = existing_metadata._adapt_to_context(mock_context)

        eq_(existing_metadata.keys, adapted_metadata.keys)

        for k in existing_metadata._keymap:
            if isinstance(k, ColumnElement) and k in column_linkage:
                other_k = column_linkage[k]
            else:
                other_k = k

            is_(
                existing_metadata._keymap[k], adapted_metadata._keymap[other_k]
            ) 

def test_aliasing(self):
        """test that eager loading uses aliases to insulate the eager
        load from regular criterion against those tables."""

        Address, addresses, users, User = (
            self.classes.Address,
            self.tables.addresses,
            self.tables.users,
            self.classes.User,
        )

        mapper(
            User,
            users,
            properties=dict(
                addresses=relationship(
                    mapper(Address, addresses),
                    lazy="joined",
                    order_by=addresses.c.id,
                )
            ),
        )
        q = create_session().query(User)
        result = (
            q.filter(addresses.c.email_address == "ed@lala.com")
            .filter(Address.user_id == User.id)
            .order_by(User.id)
        )
        eq_(self.static.user_address_result[1:2], result.all()) 

def test_against_select(self):
        """test eager loading of a mapper which is against a select"""

        users, items, order_items, orders, Item, User, Order = (
            self.tables.users,
            self.tables.items,
            self.tables.order_items,
            self.tables.orders,
            self.classes.Item,
            self.classes.User,
            self.classes.Order,
        )

        s = sa.select([orders], orders.c.isopen == 1).alias("openorders")

        mapper(
            Order, s, properties={"user": relationship(User, lazy="joined")}
        )
        mapper(User, users)
        mapper(Item, items)

        q = create_session().query(Order)
        eq_(
            [Order(id=3, user=User(id=7)), Order(id=4, user=User(id=9))],
            q.all(),
        )

        q = q.select_from(s.join(order_items).join(items)).filter(
            ~Item.id.in_([1, 2, 5])
        )
        eq_([Order(id=3, user=User(id=7))], q.all()) 

def test_literal_round_trip(self, connection):
        # in particular, this tests that the array index
        # operator against the function is handled by PG; with some
        # array functions it requires outer parenthezisation on the left and
        # we may not be doing that here
        expr = hstore(
            postgresql.array(["1", "2"]), postgresql.array(["3", None])
        )["1"]
        eq_(connection.scalar(select([expr])), "3") 

def define_temp_tables(cls, metadata):
        # cheat a bit, we should fix this with some dialect-level
        # temp table fixture
        if testing.against("oracle"):
            kw = {
                'prefixes': ["GLOBAL TEMPORARY"],
                'oracle_on_commit': 'PRESERVE ROWS'
            }
        else:
            kw = {
                'prefixes': ["TEMPORARY"],
            }

        user_tmp = Table(
            "user_tmp", metadata,
            Column("id", sa.INT, primary_key=True),
            Column('name', sa.VARCHAR(50)),
            Column('foo', sa.INT),
            sa.UniqueConstraint('name', name='user_tmp_uq'),
            sa.Index("user_tmp_ix", "foo"),
            **kw
        )
        if testing.requires.view_reflection.enabled and \
                testing.requires.temporary_views.enabled:
            event.listen(
                user_tmp, "after_create",
                DDL("create temporary view user_tmp_v as "
                    "select * from user_tmp")
            )
            event.listen(
                user_tmp, "before_drop",
                DDL("drop view user_tmp_v")
            ) 

def define_temp_tables(cls, metadata):
        # the definition of temporary tables in the temporary table tests needs to be overwritten,
        # because similar to oracle, in HANA one needs to mention GLOBAL or LOCAL in the temporary table definition

        if testing.against("hana"):
            kw = {
                'prefixes': ["GLOBAL TEMPORARY"],
            }
        else:
            kw = {
                'prefixes': ["TEMPORARY"],
            }

        user_tmp = Table(
            "user_tmp", metadata,
            Column("id", sa.INT, primary_key=True),
            Column('name', sa.VARCHAR(50)),
            Column('foo', sa.INT),
            sa.UniqueConstraint('name', name='user_tmp_uq'),
            sa.Index("user_tmp_ix", "foo"),
            **kw
        )
        if testing.requires.view_reflection.enabled and \
                testing.requires.temporary_views.enabled:
            event.listen(
                user_tmp, "after_create",
                DDL("create temporary view user_tmp_v as "
                    "select * from user_tmp")
            )
            event.listen(
                user_tmp, "before_drop",
                DDL("drop view user_tmp_v")
            ) 

def define_temp_tables(cls, metadata):
        # cheat a bit, we should fix this with some dialect-level
        # temp table fixture
        if testing.against("oracle"):
            kw = {
                'prefixes': ["GLOBAL TEMPORARY"],
                'oracle_on_commit': 'PRESERVE ROWS'
            }
        else:
            kw = {
                'prefixes': ["TEMPORARY"],
            }

        user_tmp = Table(
            "user_tmp", metadata,
            Column("id", sa.INT, primary_key=True),
            Column('name', sa.VARCHAR(50)),
            Column('foo', sa.INT),
            sa.UniqueConstraint('name', name='user_tmp_uq'),
            sa.Index("user_tmp_ix", "foo"),
            **kw
        )
        if testing.requires.view_reflection.enabled and \
                testing.requires.temporary_views.enabled:
            event.listen(
                user_tmp, "after_create",
                DDL("create temporary view user_tmp_v as "
                    "select * from user_tmp")
            )
            event.listen(
                user_tmp, "before_drop",
                DDL("drop view user_tmp_v")
            ) 

def define_temp_tables(cls, metadata):
        # cheat a bit, we should fix this with some dialect-level
        # temp table fixture
        if testing.against("oracle"):
            kw = {
                'prefixes': ["GLOBAL TEMPORARY"],
                'oracle_on_commit': 'PRESERVE ROWS'
            }
        else:
            kw = {
                'prefixes': ["TEMPORARY"],
            }

        user_tmp = Table(
            "user_tmp", metadata,
            Column("id", sa.INT, primary_key=True),
            Column('name', sa.VARCHAR(50)),
            Column('foo', sa.INT),
            sa.UniqueConstraint('name', name='user_tmp_uq'),
            sa.Index("user_tmp_ix", "foo"),
            **kw
        )
        if testing.requires.view_reflection.enabled and \
                testing.requires.temporary_views.enabled:
            event.listen(
                user_tmp, "after_create",
                DDL("create temporary view user_tmp_v as "
                    "select * from user_tmp")
            )
            event.listen(
                user_tmp, "before_drop",
                DDL("drop view user_tmp_v")
            ) 

def define_temp_tables(cls, metadata):
        # cheat a bit, we should fix this with some dialect-level
        # temp table fixture
        if testing.against("oracle"):
            kw = {
                'prefixes': ["GLOBAL TEMPORARY"],
                'oracle_on_commit': 'PRESERVE ROWS'
            }
        else:
            kw = {
                'prefixes': ["TEMPORARY"],
            }

        user_tmp = Table(
            "user_tmp", metadata,
            Column("id", sa.INT, primary_key=True),
            Column('name', sa.VARCHAR(50)),
            Column('foo', sa.INT),
            sa.UniqueConstraint('name', name='user_tmp_uq'),
            sa.Index("user_tmp_ix", "foo"),
            **kw
        )
        if testing.requires.view_reflection.enabled and \
                testing.requires.temporary_views.enabled:
            event.listen(
                user_tmp, "after_create",
                DDL("create temporary view user_tmp_v as "
                    "select * from user_tmp")
            )
            event.listen(
                user_tmp, "before_drop",
                DDL("drop view user_tmp_v")
            ) 

def define_temp_tables(cls, metadata):
        # cheat a bit, we should fix this with some dialect-level
        # temp table fixture
        if testing.against("oracle"):
            kw = {
                'prefixes': ["GLOBAL TEMPORARY"],
                'oracle_on_commit': 'PRESERVE ROWS'
            }
        else:
            kw = {
                'prefixes': ["TEMPORARY"],
            }

        user_tmp = Table(
            "user_tmp", metadata,
            Column("id", sa.INT, primary_key=True),
            Column('name', sa.VARCHAR(50)),
            Column('foo', sa.INT),
            sa.UniqueConstraint('name', name='user_tmp_uq'),
            sa.Index("user_tmp_ix", "foo"),
            **kw
        )
        if testing.requires.view_reflection.enabled and \
                testing.requires.temporary_views.enabled:
            event.listen(
                user_tmp, "after_create",
                DDL("create temporary view user_tmp_v as "
                    "select * from user_tmp")
            )
            event.listen(
                user_tmp, "before_drop",
                DDL("drop view user_tmp_v")
            ) 

def define_temp_tables(cls, metadata):
        # cheat a bit, we should fix this with some dialect-level
        # temp table fixture
        if testing.against("oracle"):
            kw = {
                'prefixes': ["GLOBAL TEMPORARY"],
                'oracle_on_commit': 'PRESERVE ROWS'
            }
        else:
            kw = {
                'prefixes': ["TEMPORARY"],
            }

        user_tmp = Table(
            "user_tmp", metadata,
            Column("id", sa.INT, primary_key=True),
            Column('name', sa.VARCHAR(50)),
            Column('foo', sa.INT),
            sa.UniqueConstraint('name', name='user_tmp_uq'),
            sa.Index("user_tmp_ix", "foo"),
            **kw
        )
        if testing.requires.view_reflection.enabled and \
                testing.requires.temporary_views.enabled:
            event.listen(
                user_tmp, "after_create",
                DDL("create temporary view user_tmp_v as "
                    "select * from user_tmp")
            )
            event.listen(
                user_tmp, "before_drop",
                DDL("drop view user_tmp_v")
            ) 

def test_include_columns(self):
        meta = self.metadata
        foo = Table(
            "foo",
            meta,
            *[Column(n, sa.String(30)) for n in ["a", "b", "c", "d", "e", "f"]]
        )
        meta.create_all()
        meta2 = MetaData(testing.db)
        foo = Table(
            "foo", meta2, autoload=True, include_columns=["b", "f", "e"]
        )
        # test that cols come back in original order
        eq_([c.name for c in foo.c], ["b", "e", "f"])
        for c in ("b", "f", "e"):
            assert c in foo.c
        for c in ("a", "c", "d"):
            assert c not in foo.c

        # test against a table which is already reflected
        meta3 = MetaData(testing.db)
        foo = Table("foo", meta3, autoload=True)

        foo = Table(
            "foo", meta3, include_columns=["b", "f", "e"], extend_existing=True
        )
        eq_([c.name for c in foo.c], ["b", "e", "f"])
        for c in ("b", "f", "e"):
            assert c in foo.c
        for c in ("a", "c", "d"):
            assert c not in foo.c 

def test_outer_joinedload_wo_limit(self):
        User = self.classes.User
        sess = Session()
        q = sess.query(User).options(
            joinedload(User.addresses, innerjoin=False)
        )

        if testing.against("postgresql"):
            q = q.with_for_update(of=User)
        else:
            q = q.with_for_update()

        q.all()
        sess.close() 

def test_inner_joinedload_w_limit(self):
        User = self.classes.User
        sess = Session()
        q = (
            sess.query(User)
            .options(joinedload(User.addresses, innerjoin=True))
            .with_for_update()
            .limit(1)
        )

        if testing.against("oracle"):
            assert_raises_message(exc.DatabaseError, "ORA-02014", q.all)
        else:
            q.all()
        sess.close() 

def test_explicit_default_schema_metadata(self):
        engine = testing.db

        if testing.against("sqlite"):
            # Works for CREATE TABLE main.foo, SELECT FROM main.foo, etc.,
            # but fails on:
            #   FOREIGN KEY(col2) REFERENCES main.table1 (col1)
            schema = "main"
        else:
            schema = engine.dialect.default_schema_name

        assert bool(schema)

        metadata = MetaData(engine, schema=schema)
        Table(
            "table1",
            metadata,
            Column("col1", sa.Integer, primary_key=True),
            test_needs_fk=True,
        )
        Table(
            "table2",
            metadata,
            Column("col1", sa.Integer, primary_key=True),
            Column("col2", sa.Integer, sa.ForeignKey("table1.col1")),
            test_needs_fk=True,
        )
        try:
            metadata.create_all()
            metadata.create_all(checkfirst=True)
            assert len(metadata.tables) == 2
            metadata.clear()

            Table("table1", metadata, autoload=True)
            Table("table2", metadata, autoload=True)
            assert len(metadata.tables) == 2
        finally:
            metadata.drop_all() 

def test_many_discarded_relationships(self):
        """a use case that really isn't supported, nonetheless we can
        guard against memleaks here so why not"""

        m1 = MetaData()
        t1 = Table("t1", m1, Column("id", Integer, primary_key=True))
        t2 = Table(
            "t2",
            m1,
            Column("id", Integer, primary_key=True),
            Column("t1id", ForeignKey("t1.id")),
        )

        class T1(object):
            pass

        t1_mapper = mapper(T1, t1)

        @testing.emits_warning()
        @profile_memory()
        def go():
            class T2(object):
                pass

            t2_mapper = mapper(T2, t2)
            t1_mapper.add_property("bar", relationship(t2_mapper))
            s1 = Session()
            # this causes the path_registry to be invoked
            s1.query(t1_mapper)._compile_context()

        go()

    # fails on newer versions of pysqlite due to unusual memory behavior
    # in pysqlite itself. background at:
    # http://thread.gmane.org/gmane.comp.python.db.pysqlite.user/2290