Python sqlalchemy.dialects.oracle.cx_oracle.dialect() Examples

def test_use_binds_for_limits_enabled_two(self):
        t = table("sometable", column("col1"), column("col2"))
        with testing.expect_deprecated(
            "The ``use_binds_for_limits`` Oracle dialect parameter is "
            "deprecated."
        ):
            dialect = oracle.OracleDialect(use_binds_for_limits=True)

        self.assert_compile(
            select([t]).offset(10),
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT anon_2.col1 AS col1, anon_2.col2 AS col2, "
            "ROWNUM AS ora_rn "
            "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
            "FROM sometable) anon_2) anon_1 "
            "WHERE ora_rn > [POSTCOMPILE_param_1]",
            dialect=dialect,
        ) 

def test_limit_one(self):
        t = table("sometable", column("col1"), column("col2"))
        s = select([t])
        c = s.compile(dialect=oracle.OracleDialect())
        assert t.c.col1 in set(c._create_result_map()["col1"][1])
        s = select([t]).limit(10).offset(20)
        self.assert_compile(
            s,
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT anon_2.col1 AS col1, "
            "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
            "sometable.col1 AS col1, sometable.col2 AS "
            "col2 FROM sometable) anon_2 WHERE ROWNUM <= "
            "[POSTCOMPILE_param_1]) anon_1 WHERE ora_rn > "
            "[POSTCOMPILE_param_2]",
            checkparams={"param_1": 30, "param_2": 20},
        )

        c = s.compile(dialect=oracle.OracleDialect())
        eq_(len(c._result_columns), 2)
        assert t.c.col1 in set(c._create_result_map()["col1"][1]) 

def test_limit_one_firstrows(self):
        t = table("sometable", column("col1"), column("col2"))
        s = select([t])
        s = select([t]).limit(10).offset(20)
        self.assert_compile(
            s,
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT /*+ FIRST_ROWS([POSTCOMPILE_ora_frow_1]) */ "
            "anon_2.col1 AS col1, "
            "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
            "sometable.col1 AS col1, sometable.col2 AS "
            "col2 FROM sometable) anon_2 WHERE ROWNUM <= "
            "[POSTCOMPILE_param_1]) anon_1 WHERE ora_rn > "
            "[POSTCOMPILE_param_2]",
            checkparams={"ora_frow_1": 10, "param_1": 30, "param_2": 20},
            dialect=oracle.OracleDialect(optimize_limits=True),
        ) 

def test_limit_three(self):
        t = table("sometable", column("col1"), column("col2"))

        s = select([t]).limit(10).offset(20).order_by(t.c.col2)
        self.assert_compile(
            s,
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT anon_2.col1 AS col1, "
            "anon_2.col2 AS col2, ROWNUM AS ora_rn FROM (SELECT "
            "sometable.col1 AS col1, sometable.col2 AS "
            "col2 FROM sometable ORDER BY "
            "sometable.col2) anon_2 WHERE ROWNUM <= "
            "[POSTCOMPILE_param_1]) anon_1 "
            "WHERE ora_rn > [POSTCOMPILE_param_2]",
            checkparams={"param_1": 30, "param_2": 20},
        )
        c = s.compile(dialect=oracle.OracleDialect())
        eq_(len(c._result_columns), 2)
        assert t.c.col1 in set(c._create_result_map()["col1"][1]) 

def test_no_clobs_for_string_params(self):
        """test that simple string params get a DBAPI type of
        VARCHAR, not CLOB. This is to prevent setinputsizes
        from setting up cx_oracle.CLOBs on
        string-based bind params [ticket:793]."""

        class FakeDBAPI(object):
            def __getattr__(self, attr):
                return attr

        dialect = oracle.OracleDialect()
        dbapi = FakeDBAPI()

        b = bindparam("foo", "hello world!")
        eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING")

        b = bindparam("foo", "hello world!")
        eq_(b.type.dialect_impl(dialect).get_dbapi_type(dbapi), "STRING") 

def test_max_ident_122_11compat_vparam_cant_parse(self):
        dialect = self._dialect((12, 2, 0))

        def c122():
            return "12.thisiscrap.0"

        conn = mock.Mock(
            exec_driver_sql=mock.Mock(return_value=mock.Mock(scalar=c122))
        )
        dialect.initialize(conn)
        eq_(dialect.server_version_info, (12, 2, 0))
        eq_(
            dialect._get_effective_compat_server_version_info(conn), (12, 2, 0)
        )
        eq_(
            dialect.max_identifier_length,
            oracle.OracleDialect.max_identifier_length,
        ) 

def test_max_ident_122_11compat_vparam_raises(self):
        dialect = self._dialect((12, 2, 0))

        def c122():
            raise exc.DBAPIError(
                "statement", None, "no such table", None, None
            )

        conn = mock.Mock(
            exec_driver_sql=mock.Mock(return_value=mock.Mock(scalar=c122))
        )
        dialect.initialize(conn)
        eq_(dialect.server_version_info, (12, 2, 0))
        eq_(
            dialect._get_effective_compat_server_version_info(conn), (12, 2, 0)
        )
        eq_(
            dialect.max_identifier_length,
            oracle.OracleDialect.max_identifier_length,
        ) 

def test_use_binds_for_limits_disabled_one(self):
        t = table("sometable", column("col1"), column("col2"))
        with testing.expect_deprecated(
            "The ``use_binds_for_limits`` Oracle dialect parameter is "
            "deprecated."
        ):
            dialect = oracle.OracleDialect(use_binds_for_limits=False)

        self.assert_compile(
            select([t]).limit(10),
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT sometable.col1 AS col1, "
            "sometable.col2 AS col2 FROM sometable) anon_1 "
            "WHERE ROWNUM <= [POSTCOMPILE_param_1]",
            dialect=dialect,
        ) 

def test_max_ident_122(self):
        dialect = self._dialect((12, 2, 0))

        conn = mock.Mock(
            exec_driver_sql=mock.Mock(
                return_value=mock.Mock(scalar=lambda: "12.2.0")
            )
        )
        dialect.initialize(conn)
        eq_(dialect.server_version_info, (12, 2, 0))
        eq_(
            dialect._get_effective_compat_server_version_info(conn), (12, 2, 0)
        )
        eq_(
            dialect.max_identifier_length,
            oracle.OracleDialect.max_identifier_length,
        ) 

def test_use_binds_for_limits_disabled_two(self):
        t = table("sometable", column("col1"), column("col2"))
        with testing.expect_deprecated(
            "The ``use_binds_for_limits`` Oracle dialect parameter is "
            "deprecated."
        ):
            dialect = oracle.OracleDialect(use_binds_for_limits=False)

        self.assert_compile(
            select([t]).offset(10),
            "SELECT anon_1.col1, anon_1.col2 FROM (SELECT "
            "anon_2.col1 AS col1, anon_2.col2 AS col2, ROWNUM AS ora_rn "
            "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
            "FROM sometable) anon_2) anon_1 "
            "WHERE ora_rn > [POSTCOMPILE_param_1]",
            dialect=dialect,
        ) 

def test_use_binds_for_limits_disabled_three(self):
        t = table("sometable", column("col1"), column("col2"))
        with testing.expect_deprecated(
            "The ``use_binds_for_limits`` Oracle dialect parameter is "
            "deprecated."
        ):
            dialect = oracle.OracleDialect(use_binds_for_limits=False)

        self.assert_compile(
            select([t]).limit(10).offset(10),
            "SELECT anon_1.col1, anon_1.col2 FROM (SELECT "
            "anon_2.col1 AS col1, anon_2.col2 AS col2, ROWNUM AS ora_rn "
            "FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
            "FROM sometable) anon_2 "
            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
            "WHERE ora_rn > [POSTCOMPILE_param_2]",
            dialect=dialect,
        ) 

def test_use_binds_for_limits_enabled_one(self):
        t = table("sometable", column("col1"), column("col2"))
        with testing.expect_deprecated(
            "The ``use_binds_for_limits`` Oracle dialect parameter is "
            "deprecated."
        ):
            dialect = oracle.OracleDialect(use_binds_for_limits=True)

        self.assert_compile(
            select([t]).limit(10),
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT sometable.col1 AS col1, "
            "sometable.col2 AS col2 FROM sometable) anon_1 WHERE ROWNUM "
            "<= [POSTCOMPILE_param_1]",
            dialect=dialect,
        ) 

def test_ora8_flags(self):
        dialect = self._dialect((8, 2, 5))

        # before connect, assume modern DB
        assert dialect._supports_char_length
        assert dialect.use_ansi
        assert not dialect._use_nchar_for_unicode

        dialect.initialize(Mock())
        assert not dialect.implicit_returning
        assert not dialect._supports_char_length
        assert not dialect.use_ansi
        self.assert_compile(String(50), "VARCHAR2(50)", dialect=dialect)
        self.assert_compile(Unicode(50), "VARCHAR2(50)", dialect=dialect)
        self.assert_compile(UnicodeText(), "CLOB", dialect=dialect)

        dialect = self._dialect((8, 2, 5), implicit_returning=True)
        dialect.initialize(testing.db.connect())
        assert dialect.implicit_returning 

def test_no_encoding_errors_cx_oracle_py3k(
        self, cx_Oracle, cx_oracle_type,
    ):
        plain_dialect = cx_oracle.dialect(dbapi=cx_Oracle)

        plain_outputhandler = (
            plain_dialect._generate_connection_outputtype_handler()
        )

        cursor = mock.Mock()
        plain_outputhandler(cursor, "foo", cx_oracle_type, None, None, None)

        eq_(
            cursor.mock_calls,
            [mock.call.var(mock.ANY, None, cursor.arraysize)],
        ) 

def test_encoding_errors_cx_oracle_py3k(
        self, cx_Oracle, cx_oracle_type,
    ):
        ignore_dialect = cx_oracle.dialect(
            dbapi=cx_Oracle, encoding_errors="ignore"
        )

        ignore_outputhandler = (
            ignore_dialect._generate_connection_outputtype_handler()
        )

        cursor = mock.Mock()
        ignore_outputhandler(cursor, "foo", cx_oracle_type, None, None, None)

        eq_(
            cursor.mock_calls,
            [
                mock.call.var(
                    mock.ANY, None, cursor.arraysize, encodingErrors="ignore",
                )
            ],
        ) 

def test_outer_join_three(self):
        table1, table2, table3 = self._test_outer_join_fixture()

        query = table1.outerjoin(
            table2, table1.c.myid == table2.c.otherid
        ).outerjoin(table3, table3.c.userid == table2.c.otherid)

        self.assert_compile(
            query.select(),
            "SELECT mytable.myid, mytable.name, "
            "mytable.description, myothertable.otherid,"
            " myothertable.othername, "
            "thirdtable.userid, thirdtable.otherstuff "
            "FROM mytable, myothertable, thirdtable "
            "WHERE thirdtable.userid(+) = "
            "myothertable.otherid AND mytable.myid = "
            "myothertable.otherid(+)",
            dialect=oracle.dialect(use_ansi=False),
        ) 

def test_older_cx_oracle_warning(self, cx_Oracle, cx_oracle_type):
        cx_Oracle.version = "6.3"

        ignore_dialect = cx_oracle.dialect(
            dbapi=cx_Oracle, encoding_errors="ignore"
        )
        ignore_outputhandler = (
            ignore_dialect._generate_connection_outputtype_handler()
        )

        cursor = mock.Mock()

        with testing.expect_warnings(
            r"cx_oracle version \(6, 3\) does not support encodingErrors"
        ):
            ignore_outputhandler(
                cursor, "foo", cx_oracle_type, None, None, None
            ) 

def test_outer_join_four(self):
        table1, table2, table3 = self._test_outer_join_fixture()

        query = table1.join(table2, table1.c.myid == table2.c.otherid).join(
            table3, table3.c.userid == table2.c.otherid
        )
        self.assert_compile(
            query.select(),
            "SELECT mytable.myid, mytable.name, "
            "mytable.description, myothertable.otherid,"
            " myothertable.othername, "
            "thirdtable.userid, thirdtable.otherstuff "
            "FROM mytable, myothertable, thirdtable "
            "WHERE thirdtable.userid = "
            "myothertable.otherid AND mytable.myid = "
            "myothertable.otherid",
            dialect=oracle.dialect(use_ansi=False),
        ) 

def test_were_getting_a_comma(self):
        connection = self.engine.pool._creator()
        cursor = connection.cursor()
        try:
            cx_Oracle = self.engine.dialect.dbapi

            def output_type_handler(
                cursor, name, defaultType, size, precision, scale
            ):
                return cursor.var(
                    cx_Oracle.STRING, 255, arraysize=cursor.arraysize
                )

            cursor.outputtypehandler = output_type_handler
            cursor.execute("SELECT 1.1 FROM DUAL")
            row = cursor.fetchone()
            eq_(row[0], "1,1")
        finally:
            cursor.close()
            connection.close() 

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 test_outer_join_eight(self):
        table1, table2, table3 = self._test_outer_join_fixture()
        subq = (
            select([table3.c.otherstuff])
            .where(table3.c.otherstuff == table1.c.name)
            .label("bar")
        )
        q = select([table1.c.name, subq])
        self.assert_compile(
            q,
            "SELECT mytable.name, (SELECT "
            "thirdtable.otherstuff FROM thirdtable "
            "WHERE thirdtable.otherstuff = "
            "mytable.name) AS bar FROM mytable",
            dialect=oracle.dialect(use_ansi=False),
        ) 

def test_returning_insert_functional(self):
        t1 = table(
            "t1", column("c1"), column("c2", String()), column("c3", String())
        )
        fn = func.lower(t1.c.c2, type_=String())
        stmt = t1.insert().values(c1=1).returning(fn, t1.c.c3)
        compiled = stmt.compile(dialect=oracle.dialect())
        eq_(
            compiled._create_result_map(),
            {
                "c3": ("c3", (t1.c.c3, "c3", "c3"), t1.c.c3.type),
                "lower": ("lower", (fn, "lower", None), fn.type),
            },
        )

        self.assert_compile(
            stmt,
            "INSERT INTO t1 (c1) VALUES (:c1) RETURNING "
            "lower(t1.c2), t1.c3 INTO :ret_0, :ret_1",
        ) 

def _test_db_opt(self, url_string, key, value):
        import cx_Oracle

        url_obj = url.make_url(url_string)
        dialect = cx_oracle.dialect(dbapi=cx_Oracle)
        arg, kw = dialect.create_connect_args(url_obj)
        eq_(kw[key], value) 

def test_outer_join_one(self):
        table1, table2, table3 = self._test_outer_join_fixture()

        query = select(
            [table1, table2],
            or_(
                table1.c.name == "fred",
                table1.c.myid == 10,
                table2.c.othername != "jack",
                text("EXISTS (select yay from foo where boo = lar)"),
            ),
            from_obj=[
                outerjoin(table1, table2, table1.c.myid == table2.c.otherid)
            ],
        )
        self.assert_compile(
            query,
            "SELECT mytable.myid, mytable.name, "
            "mytable.description, myothertable.otherid,"
            " myothertable.othername FROM mytable, "
            "myothertable WHERE (mytable.name = "
            ":name_1 OR mytable.myid = :myid_1 OR "
            "myothertable.othername != :othername_1 OR "
            "EXISTS (select yay from foo where boo = "
            "lar)) AND mytable.myid = "
            "myothertable.otherid(+)",
            dialect=oracle.OracleDialect(use_ansi=False),
        ) 

def test_outer_join_five(self):
        table1, table2, table3 = self._test_outer_join_fixture()

        query = table1.join(
            table2, table1.c.myid == table2.c.otherid
        ).outerjoin(table3, table3.c.userid == table2.c.otherid)
        self.assert_compile(
            query.select().order_by(table1.c.name).limit(10).offset(5),
            "SELECT anon_1.myid, anon_1.name, anon_1.description, "
            "anon_1.otherid, "
            "anon_1.othername, anon_1.userid, anon_1.otherstuff FROM "
            "(SELECT anon_2.myid AS myid, anon_2.name AS name, "
            "anon_2.description AS description, anon_2.otherid AS otherid, "
            "anon_2.othername AS othername, anon_2.userid AS userid, "
            "anon_2.otherstuff AS otherstuff, ROWNUM AS "
            "ora_rn FROM (SELECT mytable.myid AS myid, "
            "mytable.name AS name, mytable.description "
            "AS description, myothertable.otherid AS "
            "otherid, myothertable.othername AS "
            "othername, thirdtable.userid AS userid, "
            "thirdtable.otherstuff AS otherstuff FROM "
            "mytable, myothertable, thirdtable WHERE "
            "thirdtable.userid(+) = "
            "myothertable.otherid AND mytable.myid = "
            "myothertable.otherid ORDER BY mytable.name) anon_2 "
            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_1 "
            "WHERE ora_rn > [POSTCOMPILE_param_2]",
            checkparams={"param_1": 15, "param_2": 5},
            dialect=oracle.dialect(use_ansi=False),
        ) 

def test_limit_four_firstrows(self):
        t = table("sometable", column("col1"), column("col2"))

        s = select([t]).with_for_update().limit(10).order_by(t.c.col2)
        self.assert_compile(
            s,
            "SELECT /*+ FIRST_ROWS([POSTCOMPILE_ora_frow_1]) */ "
            "anon_1.col1, anon_1.col2 FROM (SELECT "
            "sometable.col1 AS col1, sometable.col2 AS "
            "col2 FROM sometable ORDER BY "
            "sometable.col2) anon_1 WHERE ROWNUM <= [POSTCOMPILE_param_1] "
            "FOR UPDATE",
            checkparams={"param_1": 10, "ora_frow_1": 10},
            dialect=oracle.OracleDialect(optimize_limits=True),
        ) 

def test_outer_join_six(self):
        table1, table2, table3 = self._test_outer_join_fixture()

        subq = (
            select([table1])
            .select_from(
                table1.outerjoin(table2, table1.c.myid == table2.c.otherid)
            )
            .alias()
        )
        q = select([table3]).select_from(
            table3.outerjoin(subq, table3.c.userid == subq.c.myid)
        )

        self.assert_compile(
            q,
            "SELECT thirdtable.userid, "
            "thirdtable.otherstuff FROM thirdtable "
            "LEFT OUTER JOIN (SELECT mytable.myid AS "
            "myid, mytable.name AS name, "
            "mytable.description AS description FROM "
            "mytable LEFT OUTER JOIN myothertable ON "
            "mytable.myid = myothertable.otherid) "
            "anon_1 ON thirdtable.userid = anon_1.myid",
            dialect=oracle.dialect(use_ansi=True),
        )

        self.assert_compile(
            q,
            "SELECT thirdtable.userid, "
            "thirdtable.otherstuff FROM thirdtable, "
            "(SELECT mytable.myid AS myid, "
            "mytable.name AS name, mytable.description "
            "AS description FROM mytable, myothertable "
            "WHERE mytable.myid = myothertable.otherid("
            "+)) anon_1 WHERE thirdtable.userid = "
            "anon_1.myid(+)",
            dialect=oracle.dialect(use_ansi=False),
        ) 

def test_nonansi_nested_right_join(self):
        a = table("a", column("a"))
        b = table("b", column("b"))
        c = table("c", column("c"))

        j = a.join(b.join(c, b.c.b == c.c.c), a.c.a == b.c.b)

        self.assert_compile(
            select([j]),
            "SELECT a.a, b.b, c.c FROM a, b, c "
            "WHERE a.a = b.b AND b.b = c.c",
            dialect=oracle.OracleDialect(use_ansi=False),
        )

        j = a.outerjoin(b.join(c, b.c.b == c.c.c), a.c.a == b.c.b)

        self.assert_compile(
            select([j]),
            "SELECT a.a, b.b, c.c FROM a, b, c "
            "WHERE a.a = b.b(+) AND b.b = c.c",
            dialect=oracle.OracleDialect(use_ansi=False),
        )

        j = a.join(b.outerjoin(c, b.c.b == c.c.c), a.c.a == b.c.b)

        self.assert_compile(
            select([j]),
            "SELECT a.a, b.b, c.c FROM a, b, c "
            "WHERE a.a = b.b AND b.b = c.c(+)",
            dialect=oracle.OracleDialect(use_ansi=False),
        ) 

def test_limit_two(self):
        t = table("sometable", column("col1"), column("col2"))
        s = select([t]).limit(10).offset(20).subquery()

        s2 = select([s.c.col1, s.c.col2])
        self.assert_compile(
            s2,
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT anon_2.col1 AS col1, "
            "anon_2.col2 AS col2 "
            "FROM (SELECT anon_3.col1 AS col1, anon_3.col2 AS col2, "
            "ROWNUM AS ora_rn "
            "FROM (SELECT sometable.col1 AS col1, "
            "sometable.col2 AS col2 FROM sometable) anon_3 "
            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_2 "
            "WHERE ora_rn > [POSTCOMPILE_param_2]) anon_1",
            checkparams={"param_1": 30, "param_2": 20},
        )

        self.assert_compile(
            s2,
            "SELECT anon_1.col1, anon_1.col2 FROM "
            "(SELECT anon_2.col1 AS col1, "
            "anon_2.col2 AS col2 "
            "FROM (SELECT anon_3.col1 AS col1, anon_3.col2 AS col2, "
            "ROWNUM AS ora_rn "
            "FROM (SELECT sometable.col1 AS col1, "
            "sometable.col2 AS col2 FROM sometable) anon_3 "
            "WHERE ROWNUM <= [POSTCOMPILE_param_1]) anon_2 "
            "WHERE ora_rn > [POSTCOMPILE_param_2]) anon_1",
        )
        c = s2.compile(dialect=oracle.OracleDialect())
        eq_(len(c._result_columns), 2)
        assert s.c.col1 in set(c._create_result_map()["col1"][1]) 

def test_column_computed_persisted_true(self):
        m = MetaData()
        t = Table(
            "t",
            m,
            Column("x", Integer),
            Column("y", Integer, Computed("x + 2", persisted=True)),
        )
        assert_raises_message(
            exc.CompileError,
            r".*Oracle computed columns do not support 'stored' ",
            schema.CreateTable(t).compile,
            dialect=oracle.dialect(),
        )