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(self):
t = table('sometable', column('col1'), column('col2'))
s = select([t])
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([t]).limit(10).offset(20)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable) WHERE ROWNUM <= '
':ROWNUM_1) WHERE ora_rn > :ora_rn_1')
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([s.c.col1, s.c.col2])
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, col2 '
'FROM (SELECT col1, col2, ROWNUM AS ora_rn '
'FROM (SELECT sometable.col1 AS col1, '
'sometable.col2 AS col2 FROM sometable) '
'WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > '
':ora_rn_1)')
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, col2 '
'FROM (SELECT col1, col2, ROWNUM AS ora_rn '
'FROM (SELECT sometable.col1 AS col1, '
'sometable.col2 AS col2 FROM sometable) '
'WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > '
':ora_rn_1)')
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= '
':ROWNUM_1) WHERE ora_rn > :ora_rn_1')
s = select([t], for_update=True).limit(10).order_by(t.c.col2)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= :ROWNUM_1 '
'FOR UPDATE')
s = select([t],
for_update=True).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= '
':ROWNUM_1) WHERE ora_rn > :ora_rn_1 FOR '
'UPDATE')
def test_use_binds_for_limits_disabled(self):
t = table("sometable", column("col1"), column("col2"))
dialect = oracle.OracleDialect(use_binds_for_limits=False)
self.assert_compile(
select([t]).limit(10),
"SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) WHERE ROWNUM <= 10",
dialect=dialect,
)
self.assert_compile(
select([t]).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable)) WHERE ora_rn > 10",
dialect=dialect,
)
self.assert_compile(
select([t]).limit(10).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable) WHERE ROWNUM <= 20) WHERE ora_rn > 10",
dialect=dialect,
)
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_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_use_binds_for_limits_enabled(self):
t = table("sometable", column("col1"), column("col2"))
dialect = oracle.OracleDialect(use_binds_for_limits=True)
self.assert_compile(
select([t]).limit(10),
"SELECT col1, col2 FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) WHERE ROWNUM "
"<= :param_1",
dialect=dialect,
)
self.assert_compile(
select([t]).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable)) WHERE ora_rn > :param_1",
dialect=dialect,
)
self.assert_compile(
select([t]).limit(10).offset(10),
"SELECT col1, col2 FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, sometable.col2 AS col2 "
"FROM sometable) WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2",
dialect=dialect,
checkparams={
"param_1": 10,
"param_2": 10
},
)
def test_basic(self):
seq = Sequence('my_seq_no_schema')
dialect = oracle.OracleDialect()
assert dialect.identifier_preparer.format_sequence(seq) \
== 'my_seq_no_schema'
seq = Sequence('my_seq', schema='some_schema')
assert dialect.identifier_preparer.format_sequence(seq) \
== 'some_schema.my_seq'
seq = Sequence('My_Seq', schema='Some_Schema')
assert dialect.identifier_preparer.format_sequence(seq) \
== '"Some_Schema"."My_Seq"'
def test_basic(self):
seq = Sequence("my_seq_no_schema")
dialect = oracle.OracleDialect()
assert (dialect.identifier_preparer.format_sequence(seq) ==
"my_seq_no_schema")
seq = Sequence("my_seq", schema="some_schema")
assert (dialect.identifier_preparer.format_sequence(seq) ==
"some_schema.my_seq")
seq = Sequence("My_Seq", schema="Some_Schema")
assert (dialect.identifier_preparer.format_sequence(seq) ==
'"Some_Schema"."My_Seq"')
def test_use_binds_for_limits_disabled_one(self):
t = table("sometable", column("col1"), column("col2"))
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 <= 10",
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_use_binds_for_limits_disabled_two(self):
t = table("sometable", column("col1"), column("col2"))
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 > 10",
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_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_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_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_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_use_binds_for_limits_enabled_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=True)
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,
checkparams={"param_1": 20, "param_2": 10},
)
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 <= "
":param_1 + :param_2) anon_1 WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"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_two(self):
t = table("sometable", column("col1"), column("col2"))
s = select([t]).limit(10).offset(20)
# TODO: this will require a subquery
s2 = select([s.c.col1, s.c.col2])
self.assert_compile(
s2,
"SELECT col1, col2 FROM (SELECT anon_1.col1 AS col1, "
"anon_1.col2 AS 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 <= :param_1 + :param_2) anon_1 "
"WHERE ora_rn > :param_2)",
checkparams={
"param_1": 10,
"param_2": 20
},
)
self.assert_compile(
s2,
"SELECT col1, col2 FROM (SELECT anon_1.col1 AS col1, "
"anon_1.col2 AS 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 <= :param_1 + :param_2) anon_1 "
"WHERE ora_rn > :param_2)",
)
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_outer_join(self):
table1 = table(
"mytable",
column("myid", Integer),
column("name", String),
column("description", String),
)
table2 = table(
"myothertable",
column("otherid", Integer),
column("othername", String),
)
table3 = table(
"thirdtable",
column("userid", Integer),
column("otherstuff", String),
)
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),
)
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 LEFT OUTER JOIN myothertable "
"ON mytable.myid = myothertable.otherid "
"LEFT OUTER JOIN thirdtable ON "
"thirdtable.userid = myothertable.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),
)
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),
)
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 myid, name, description, otherid, "
"othername, userid, otherstuff FROM "
"(SELECT myid, name, description, otherid, "
"othername, userid, 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) "
"WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"param_2": 5
},
dialect=oracle.dialect(use_ansi=False),
)
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),
)
q = select([table1.c.name]).where(table1.c.name == "foo")
self.assert_compile(
q,
"SELECT mytable.name FROM mytable WHERE "
"mytable.name = :name_1",
dialect=oracle.dialect(use_ansi=False),
)
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_outer_join(self):
table1 = table('mytable',
column('myid', Integer),
column('name', String),
column('description', String),
)
table2 = table(
'myothertable',
column('otherid', Integer),
column('othername', String),
)
table3 = table(
'thirdtable',
column('userid', Integer),
column('otherstuff', String),
)
query = select([table1, table2], or_(table1.c.name == 'fred',
table1.c.myid == 10, table2.c.othername != 'jack'
, '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))
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 LEFT OUTER JOIN myothertable '
'ON mytable.myid = myothertable.otherid '
'LEFT OUTER JOIN thirdtable ON '
'thirdtable.userid = myothertable.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))
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))
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 myid, name, description, otherid, '
'othername, userid, otherstuff FROM '
'(SELECT myid, name, description, otherid, '
'othername, userid, 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) WHERE ROWNUM <= :ROWNUM_1) '
'WHERE ora_rn > :ora_rn_1',
dialect=oracle.dialect(use_ansi=False))
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))
q = select([table1.c.name]).where(table1.c.name == 'foo')
self.assert_compile(q,
'SELECT mytable.name FROM mytable WHERE '
'mytable.name = :name_1',
dialect=oracle.dialect(use_ansi=False))
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))
class CompileTest(TestBase, AssertsCompiledSQL):
__dialect__ = oracle.OracleDialect()
def test_owner(self):
meta = MetaData()
parent = Table('parent', meta, Column('id', Integer,
primary_key=True), Column('name', String(50)),
schema='ed')
child = Table('child', meta, Column('id', Integer,
primary_key=True), Column('parent_id', Integer,
ForeignKey('ed.parent.id')), schema='ed')
self.assert_compile(parent.join(child),
'ed.parent JOIN ed.child ON ed.parent.id = '
'ed.child.parent_id')
def test_subquery(self):
t = table('sometable', column('col1'), column('col2'))
s = select([t])
s = select([s.c.col1, s.c.col2])
self.assert_compile(s, "SELECT col1, col2 FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 "
"AS col2 FROM sometable)")
def test_limit(self):
t = table('sometable', column('col1'), column('col2'))
s = select([t])
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([t]).limit(10).offset(20)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable) WHERE ROWNUM <= '
':ROWNUM_1) WHERE ora_rn > :ora_rn_1')
c = s.compile(dialect=oracle.OracleDialect())
assert t.c.col1 in set(c.result_map['col1'][1])
s = select([s.c.col1, s.c.col2])
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, col2 '
'FROM (SELECT col1, col2, ROWNUM AS ora_rn '
'FROM (SELECT sometable.col1 AS col1, '
'sometable.col2 AS col2 FROM sometable) '
'WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > '
':ora_rn_1)')
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, col2 '
'FROM (SELECT col1, col2, ROWNUM AS ora_rn '
'FROM (SELECT sometable.col1 AS col1, '
'sometable.col2 AS col2 FROM sometable) '
'WHERE ROWNUM <= :ROWNUM_1) WHERE ora_rn > '
':ora_rn_1)')
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= '
':ROWNUM_1) WHERE ora_rn > :ora_rn_1')
s = select([t], for_update=True).limit(10).order_by(t.c.col2)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= :ROWNUM_1 '
'FOR UPDATE')
s = select([t],
for_update=True).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(s,
'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= '
':ROWNUM_1) WHERE ora_rn > :ora_rn_1 FOR '
'UPDATE')
def test_long_labels(self):
dialect = default.DefaultDialect()
dialect.max_identifier_length = 30
ora_dialect = oracle.dialect()
m = MetaData()
a_table = Table(
'thirty_characters_table_xxxxxx',
m,
Column('id', Integer, primary_key=True)
)
other_table = Table(
'other_thirty_characters_table_',
m,
Column('id', Integer, primary_key=True),
Column('thirty_characters_table_id',
Integer,
ForeignKey('thirty_characters_table_xxxxxx.id'),
primary_key=True
)
)
anon = a_table.alias()
self.assert_compile(select([other_table,
anon]).
select_from(
other_table.outerjoin(anon)).apply_labels(),
'SELECT other_thirty_characters_table_.id '
'AS other_thirty_characters__1, '
'other_thirty_characters_table_.thirty_char'
'acters_table_id AS other_thirty_characters'
'__2, thirty_characters_table__1.id AS '
'thirty_characters_table__3 FROM '
'other_thirty_characters_table_ LEFT OUTER '
'JOIN thirty_characters_table_xxxxxx AS '
'thirty_characters_table__1 ON '
'thirty_characters_table__1.id = '
'other_thirty_characters_table_.thirty_char'
'acters_table_id', dialect=dialect)
self.assert_compile(select([other_table,
anon]).select_from(
other_table.outerjoin(anon)).apply_labels(),
'SELECT other_thirty_characters_table_.id '
'AS other_thirty_characters__1, '
'other_thirty_characters_table_.thirty_char'
'acters_table_id AS other_thirty_characters'
'__2, thirty_characters_table__1.id AS '
'thirty_characters_table__3 FROM '
'other_thirty_characters_table_ LEFT OUTER '
'JOIN thirty_characters_table_xxxxxx '
'thirty_characters_table__1 ON '
'thirty_characters_table__1.id = '
'other_thirty_characters_table_.thirty_char'
'acters_table_id', dialect=ora_dialect)
def test_outer_join(self):
table1 = table('mytable',
column('myid', Integer),
column('name', String),
column('description', String),
)
table2 = table(
'myothertable',
column('otherid', Integer),
column('othername', String),
)
table3 = table(
'thirdtable',
column('userid', Integer),
column('otherstuff', String),
)
query = select([table1, table2], or_(table1.c.name == 'fred',
table1.c.myid == 10, table2.c.othername != 'jack'
, '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))
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 LEFT OUTER JOIN myothertable '
'ON mytable.myid = myothertable.otherid '
'LEFT OUTER JOIN thirdtable ON '
'thirdtable.userid = myothertable.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))
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))
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 myid, name, description, otherid, '
'othername, userid, otherstuff FROM '
'(SELECT myid, name, description, otherid, '
'othername, userid, 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) WHERE ROWNUM <= :ROWNUM_1) '
'WHERE ora_rn > :ora_rn_1',
dialect=oracle.dialect(use_ansi=False))
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))
q = select([table1.c.name]).where(table1.c.name == 'foo')
self.assert_compile(q,
'SELECT mytable.name FROM mytable WHERE '
'mytable.name = :name_1',
dialect=oracle.dialect(use_ansi=False))
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_alias_outer_join(self):
address_types = table('address_types', column('id'),
column('name'))
addresses = table('addresses', column('id'), column('user_id'),
column('address_type_id'),
column('email_address'))
at_alias = address_types.alias()
s = select([at_alias,
addresses]).select_from(addresses.outerjoin(at_alias,
addresses.c.address_type_id
== at_alias.c.id)).where(addresses.c.user_id
== 7).order_by(addresses.c.id, address_types.c.id)
self.assert_compile(s,
'SELECT address_types_1.id, '
'address_types_1.name, addresses.id, '
'addresses.user_id, addresses.address_type_'
'id, addresses.email_address FROM '
'addresses LEFT OUTER JOIN address_types '
'address_types_1 ON addresses.address_type_'
'id = address_types_1.id WHERE '
'addresses.user_id = :user_id_1 ORDER BY '
'addresses.id, address_types.id')
def test_compound(self):
t1 = table('t1', column('c1'), column('c2'), column('c3'))
t2 = table('t2', column('c1'), column('c2'), column('c3'))
self.assert_compile(union(t1.select(), t2.select()),
'SELECT t1.c1, t1.c2, t1.c3 FROM t1 UNION '
'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
self.assert_compile(except_(t1.select(), t2.select()),
'SELECT t1.c1, t1.c2, t1.c3 FROM t1 MINUS '
'SELECT t2.c1, t2.c2, t2.c3 FROM t2')
class TypesTest(TestBase, AssertsCompiledSQL):
__only_on__ = 'oracle'
__dialect__ = oracle.OracleDialect()
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!")
assert b.type.dialect_impl(dialect).get_dbapi_type(dbapi) == 'STRING'
b = bindparam("foo", u"hello world!")
assert b.type.dialect_impl(dialect).get_dbapi_type(dbapi) == 'STRING'
@testing.fails_on('+zxjdbc', 'zxjdbc lacks the FIXED_CHAR dbapi type')
def test_fixed_char(self):
m = MetaData(testing.db)
t = Table('t1', m,
Column('id', Integer, primary_key=True),
Column('data', CHAR(30), nullable=False)
)
t.create()
try:
t.insert().execute(
dict(id=1, data="value 1"),
dict(id=2, data="value 2"),
dict(id=3, data="value 3")
)
eq_(t.select().where(t.c.data=='value 2').execute().fetchall(),
[(2, 'value 2 ')]
)
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
assert type(t2.c.data.type) is CHAR
eq_(t2.select().where(t2.c.data=='value 2').execute().fetchall(),
[(2, 'value 2 ')]
)
finally:
t.drop()
def test_type_adapt(self):
dialect = cx_oracle.dialect()
for start, test in [
(Date(), cx_oracle._OracleDate),
(oracle.OracleRaw(), cx_oracle._OracleRaw),
(String(), String),
(VARCHAR(), cx_oracle._OracleString),
(DATE(), DATE),
(String(50), cx_oracle._OracleString),
(Unicode(), cx_oracle._OracleNVarChar),
(Text(), cx_oracle._OracleText),
(UnicodeText(), cx_oracle._OracleUnicodeText),
(NCHAR(), cx_oracle._OracleNVarChar),
(oracle.RAW(50), cx_oracle._OracleRaw),
]:
assert isinstance(start.dialect_impl(dialect), test), \
"wanted %r got %r" % (test, start.dialect_impl(dialect))
@testing.requires.returning
def test_int_not_float(self):
m = MetaData(testing.db)
t1 = Table('t1', m, Column('foo', Integer))
t1.create()
try:
r = t1.insert().values(foo=5).returning(t1.c.foo).execute()
x = r.scalar()
assert x == 5
assert isinstance(x, int)
x = t1.select().scalar()
assert x == 5
assert isinstance(x, int)
finally:
t1.drop()
@testing.fails_on('+zxjdbc',
'Not yet known how to pass values of the '
'INTERVAL type')
def test_interval(self):
for type_, expected in [(oracle.INTERVAL(),
'INTERVAL DAY TO SECOND'),
(oracle.INTERVAL(day_precision=3),
'INTERVAL DAY(3) TO SECOND'),
(oracle.INTERVAL(second_precision=5),
'INTERVAL DAY TO SECOND(5)'),
(oracle.INTERVAL(day_precision=2,
second_precision=5),
'INTERVAL DAY(2) TO SECOND(5)')]:
self.assert_compile(type_, expected)
metadata = MetaData(testing.db)
interval_table = Table('intervaltable', metadata, Column('id',
Integer, primary_key=True,
test_needs_autoincrement=True),
Column('day_interval',
oracle.INTERVAL(day_precision=3)))
metadata.create_all()
try:
interval_table.insert().\
execute(day_interval=datetime.timedelta(days=35,
seconds=5743))
row = interval_table.select().execute().first()
eq_(row['day_interval'], datetime.timedelta(days=35,
seconds=5743))
finally:
metadata.drop_all()
def test_numerics(self):
m = MetaData(testing.db)
t1 = Table('t1', m,
Column('intcol', Integer),
Column('numericcol', Numeric(precision=9, scale=2)),
Column('floatcol1', Float()),
Column('floatcol2', FLOAT()),
Column('doubleprec', oracle.DOUBLE_PRECISION),
Column('numbercol1', oracle.NUMBER(9)),
Column('numbercol2', oracle.NUMBER(9, 3)),
Column('numbercol3', oracle.NUMBER),
)
t1.create()
try:
t1.insert().execute(
intcol=1,
numericcol=5.2,
floatcol1=6.5,
floatcol2 = 8.5,
doubleprec = 9.5,
numbercol1=12,
numbercol2=14.85,
numbercol3=15.76
)
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
for row in (
t1.select().execute().first(),
t2.select().execute().first()
):
for i, (val, type_) in enumerate((
(1, int),
(Decimal("5.2"), Decimal),
(6.5, float),
(8.5, float),
(9.5, float),
(12, int),
(Decimal("14.85"), Decimal),
(15.76, float),
)):
eq_(row[i], val)
assert isinstance(row[i], type_), '%r is not %r' \
% (row[i], type_)
finally:
t1.drop()
@testing.provide_metadata
def test_numerics_broken_inspection(self):
"""Numeric scenarios where Oracle type info is 'broken',
returning us precision, scale of the form (0, 0) or (0, -127).
We convert to Decimal and let int()/float() processors take over.
"""
# this test requires cx_oracle 5
foo = Table('foo', metadata,
Column('idata', Integer),
Column('ndata', Numeric(20, 2)),
Column('ndata2', Numeric(20, 2)),
Column('nidata', Numeric(5, 0)),
Column('fdata', Float()),
)
foo.create()
foo.insert().execute(
{'idata':5, 'ndata':Decimal("45.6"), 'ndata2':Decimal("45.0"),
'nidata':Decimal('53'), 'fdata':45.68392},
)
stmt = """
SELECT
idata,
ndata,
ndata2,
nidata,
fdata
FROM foo
"""
row = testing.db.execute(stmt).fetchall()[0]
eq_([type(x) for x in row], [int, Decimal, Decimal, int, float])
eq_(
row,
(5, Decimal('45.6'), Decimal('45'), 53, 45.683920000000001)
)
# with a nested subquery,
# both Numeric values that don't have decimal places, regardless
# of their originating type, come back as ints with no useful
# typing information beyond "numeric". So native handler
# must convert to int.
# this means our Decimal converters need to run no matter what.
# totally sucks.
stmt = """
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) FROM DUAL)
AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata
FROM dual
"""
row = testing.db.execute(stmt).fetchall()[0]
eq_([type(x) for x in row], [int, Decimal, int, int, Decimal])
eq_(
row,
(5, Decimal('45.6'), 45, 53, Decimal('45.68392'))
)
row = testing.db.execute(text(stmt,
typemap={
'idata':Integer(),
'ndata':Numeric(20, 2),
'ndata2':Numeric(20, 2),
'nidata':Numeric(5, 0),
'fdata':Float()
})).fetchall()[0]
eq_([type(x) for x in row], [int, Decimal, Decimal, Decimal, float])
eq_(row,
(5, Decimal('45.6'), Decimal('45'), Decimal('53'), 45.683920000000001)
)
stmt = """
SELECT
anon_1.idata AS anon_1_idata,
anon_1.ndata AS anon_1_ndata,
anon_1.ndata2 AS anon_1_ndata2,
anon_1.nidata AS anon_1_nidata,
anon_1.fdata AS anon_1_fdata
FROM (SELECT idata, ndata, ndata2, nidata, fdata
FROM (
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0))
FROM DUAL) AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL)
AS fdata
FROM dual
)
WHERE ROWNUM >= 0) anon_1
"""
row =testing.db.execute(stmt).fetchall()[0]
eq_([type(x) for x in row], [int, Decimal, int, int, Decimal])
eq_(row, (5, Decimal('45.6'), 45, 53, Decimal('45.68392')))
row = testing.db.execute(text(stmt,
typemap={
'anon_1_idata':Integer(),
'anon_1_ndata':Numeric(20, 2),
'anon_1_ndata2':Numeric(20, 2),
'anon_1_nidata':Numeric(5, 0),
'anon_1_fdata':Float()
})).fetchall()[0]
eq_([type(x) for x in row], [int, Decimal, Decimal, Decimal, float])
eq_(row,
(5, Decimal('45.6'), Decimal('45'), Decimal('53'), 45.683920000000001)
)
row = testing.db.execute(text(stmt,
typemap={
'anon_1_idata':Integer(),
'anon_1_ndata':Numeric(20, 2, asdecimal=False),
'anon_1_ndata2':Numeric(20, 2, asdecimal=False),
'anon_1_nidata':Numeric(5, 0, asdecimal=False),
'anon_1_fdata':Float(asdecimal=True)
})).fetchall()[0]
eq_([type(x) for x in row], [int, float, float, float, Decimal])
eq_(row,
(5, 45.6, 45, 53, Decimal('45.68392'))
)
def test_reflect_dates(self):
metadata = MetaData(testing.db)
Table(
"date_types", metadata,
Column('d1', DATE),
Column('d2', TIMESTAMP),
Column('d3', TIMESTAMP(timezone=True)),
Column('d4', oracle.INTERVAL(second_precision=5)),
)
metadata.create_all()
try:
m = MetaData(testing.db)
t1 = Table(
"date_types", m,
autoload=True)
assert isinstance(t1.c.d1.type, DATE)
assert isinstance(t1.c.d2.type, TIMESTAMP)
assert not t1.c.d2.type.timezone
assert isinstance(t1.c.d3.type, TIMESTAMP)
assert t1.c.d3.type.timezone
assert isinstance(t1.c.d4.type, oracle.INTERVAL)
finally:
metadata.drop_all()
def test_reflect_raw(self):
types_table = Table('all_types', MetaData(testing.db),
Column('owner', String(30), primary_key=True),
Column('type_name', String(30), primary_key=True),
autoload=True, oracle_resolve_synonyms=True
)
for row in types_table.select().execute().fetchall():
[row[k] for k in row.keys()]
def test_reflect_nvarchar(self):
metadata = MetaData(testing.db)
t = Table('t', metadata,
Column('data', sqltypes.NVARCHAR(255))
)
metadata.create_all()
try:
m2 = MetaData(testing.db)
t2 = Table('t', m2, autoload=True)
assert isinstance(t2.c.data.type, sqltypes.NVARCHAR)
if testing.against('oracle+cx_oracle'):
# nvarchar returns unicode natively. cx_oracle
# _OracleNVarChar type should be at play here.
assert isinstance(
t2.c.data.type.dialect_impl(testing.db.dialect),
cx_oracle._OracleNVarChar)
data = u'm’a réveillé.'
t2.insert().execute(data=data)
res = t2.select().execute().first()['data']
eq_(res, data)
assert isinstance(res, unicode)
finally:
metadata.drop_all()
def test_char_length(self):
self.assert_compile(VARCHAR(50),"VARCHAR(50 CHAR)")
oracle8dialect = oracle.dialect()
oracle8dialect.server_version_info = (8, 0)
self.assert_compile(VARCHAR(50),"VARCHAR(50)",dialect=oracle8dialect)
self.assert_compile(NVARCHAR(50),"NVARCHAR2(50)")
self.assert_compile(CHAR(50),"CHAR(50)")
metadata = MetaData(testing.db)
t1 = Table('t1', metadata,
Column("c1", VARCHAR(50)),
Column("c2", NVARCHAR(250)),
Column("c3", CHAR(200))
)
t1.create()
try:
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
eq_(t2.c.c1.type.length, 50)
eq_(t2.c.c2.type.length, 250)
eq_(t2.c.c3.type.length, 200)
finally:
t1.drop()
def test_longstring(self):
metadata = MetaData(testing.db)
testing.db.execute("""
CREATE TABLE Z_TEST
(
ID NUMERIC(22) PRIMARY KEY,
ADD_USER VARCHAR2(20) NOT NULL
)
""")
try:
t = Table("z_test", metadata, autoload=True)
t.insert().execute(id=1.0, add_user='******')
assert t.select().execute().fetchall() == [(1, 'foobar')]
finally:
testing.db.execute("DROP TABLE Z_TEST")
@testing.fails_on('+zxjdbc', 'auto_convert_lobs not applicable')
def test_raw_lobs(self):
engine = testing_engine(options=dict(auto_convert_lobs=False))
metadata = MetaData()
t = Table("z_test", metadata, Column('id', Integer, primary_key=True),
Column('data', Text), Column('bindata', LargeBinary))
t.create(engine)
try:
engine.execute(t.insert(), id=1,
data='this is text',
bindata='this is binary')
row = engine.execute(t.select()).first()
eq_(row['data'].read(), 'this is text')
eq_(row['bindata'].read(), 'this is binary')
finally:
t.drop(engine)
def test_limit(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 col1, col2 FROM (SELECT col1, "
"col2, ROWNUM AS ora_rn FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable) WHERE ROWNUM <= "
":param_1 + :param_2) WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"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])
s2 = select([s.c.col1, s.c.col2])
self.assert_compile(
s2,
"SELECT col1, col2 FROM (SELECT col1, col2 "
"FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) "
"WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2)",
checkparams={
"param_1": 10,
"param_2": 20
},
)
self.assert_compile(
s2,
"SELECT col1, col2 FROM (SELECT col1, col2 "
"FROM (SELECT col1, col2, ROWNUM AS ora_rn "
"FROM (SELECT sometable.col1 AS col1, "
"sometable.col2 AS col2 FROM sometable) "
"WHERE ROWNUM <= :param_1 + :param_2) "
"WHERE ora_rn > :param_2)",
)
c = s2.compile(dialect=oracle.OracleDialect())
eq_(len(c._result_columns), 2)
assert s.c.col1 in set(c._create_result_map()["col1"][1])
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, "
"col2, ROWNUM AS ora_rn FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable ORDER BY "
"sometable.col2) WHERE ROWNUM <= "
":param_1 + :param_2) WHERE ora_rn > :param_2",
checkparams={
"param_1": 10,
"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])
s = select([t]).with_for_update().limit(10).order_by(t.c.col2)
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable ORDER BY "
"sometable.col2) WHERE ROWNUM <= :param_1 "
"FOR UPDATE",
)
s = (select([t]).with_for_update().limit(10).offset(20).order_by(
t.c.col2))
self.assert_compile(
s,
"SELECT col1, col2 FROM (SELECT col1, "
"col2, ROWNUM AS ora_rn FROM (SELECT "
"sometable.col1 AS col1, sometable.col2 AS "
"col2 FROM sometable ORDER BY "
"sometable.col2) WHERE ROWNUM <= "
":param_1 + :param_2) WHERE ora_rn > :param_2 FOR "
"UPDATE",
)
def test_limit(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 col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable) WHERE ROWNUM <= '
':param_1 + :param_2) WHERE ora_rn > :param_2',
checkparams={
'param_1': 10,
'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])
s2 = select([s.c.col1, s.c.col2])
self.assert_compile(s2, 'SELECT col1, col2 FROM (SELECT col1, col2 '
'FROM (SELECT col1, col2, ROWNUM AS ora_rn '
'FROM (SELECT sometable.col1 AS col1, '
'sometable.col2 AS col2 FROM sometable) '
'WHERE ROWNUM <= :param_1 + :param_2) '
'WHERE ora_rn > :param_2)',
checkparams={
'param_1': 10,
'param_2': 20
})
self.assert_compile(
s2, 'SELECT col1, col2 FROM (SELECT col1, col2 '
'FROM (SELECT col1, col2, ROWNUM AS ora_rn '
'FROM (SELECT sometable.col1 AS col1, '
'sometable.col2 AS col2 FROM sometable) '
'WHERE ROWNUM <= :param_1 + :param_2) '
'WHERE ora_rn > :param_2)')
c = s2.compile(dialect=oracle.OracleDialect())
eq_(len(c._result_columns), 2)
assert s.c.col1 in set(c._create_result_map()['col1'][1])
s = select([t]).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(s, 'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= '
':param_1 + :param_2) WHERE ora_rn > :param_2',
checkparams={
'param_1': 10,
'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])
s = select([t], for_update=True).limit(10).order_by(t.c.col2)
self.assert_compile(
s, 'SELECT col1, col2 FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= :param_1 '
'FOR UPDATE')
s = select([t],
for_update=True).limit(10).offset(20).order_by(t.c.col2)
self.assert_compile(
s, 'SELECT col1, col2 FROM (SELECT col1, '
'col2, ROWNUM AS ora_rn FROM (SELECT '
'sometable.col1 AS col1, sometable.col2 AS '
'col2 FROM sometable ORDER BY '
'sometable.col2) WHERE ROWNUM <= '
':param_1 + :param_2) WHERE ora_rn > :param_2 FOR '
'UPDATE')
class TypesTest(fixtures.TestBase):
__only_on__ = "oracle"
__dialect__ = oracle.OracleDialect()
__backend__ = True
@testing.combinations((CHAR,), (NCHAR,))
@testing.provide_metadata
def test_fixed_char(self, char_type):
m = self.metadata
t = Table(
"t1",
m,
Column("id", Integer, primary_key=True),
Column("data", char_type(30), nullable=False),
)
if py2k and char_type is NCHAR:
v1, v2, v3 = u"value 1", u"value 2", u"value 3"
else:
v1, v2, v3 = "value 1", "value 2", "value 3"
with testing.db.begin() as conn:
t.create(conn)
conn.execute(
t.insert(),
dict(id=1, data=v1),
dict(id=2, data=v2),
dict(id=3, data=v3),
)
eq_(
conn.execute(t.select().where(t.c.data == v2)).fetchall(),
[(2, "value 2 ")],
)
m2 = MetaData()
t2 = Table("t1", m2, autoload_with=conn)
is_(type(t2.c.data.type), char_type)
eq_(
conn.execute(t2.select().where(t2.c.data == v2)).fetchall(),
[(2, "value 2 ")],
)
@testing.requires.returning
@testing.provide_metadata
def test_int_not_float(self):
m = self.metadata
t1 = Table("t1", m, Column("foo", Integer))
t1.create()
r = t1.insert().values(foo=5).returning(t1.c.foo).execute()
x = r.scalar()
assert x == 5
assert isinstance(x, int)
x = t1.select().scalar()
assert x == 5
assert isinstance(x, int)
@testing.requires.returning
@testing.provide_metadata
def test_int_not_float_no_coerce_decimal(self):
engine = testing_engine(options=dict(coerce_to_decimal=False))
m = self.metadata
t1 = Table("t1", m, Column("foo", Integer))
with engine.begin() as conn:
t1.create()
r = conn.execute(t1.insert().values(foo=5).returning(t1.c.foo))
x = r.scalar()
assert x == 5
assert isinstance(x, int)
x = conn.execute(t1.select()).scalar()
assert x == 5
assert isinstance(x, int)
@testing.provide_metadata
def test_rowid(self):
metadata = self.metadata
t = Table("t1", metadata, Column("x", Integer))
with testing.db.begin() as conn:
t.create(conn)
conn.execute(t.insert(), {"x": 5})
s1 = select([t]).subquery()
s2 = select([column("rowid")]).select_from(s1)
rowid = conn.scalar(s2)
# the ROWID type is not really needed here,
# as cx_oracle just treats it as a string,
# but we want to make sure the ROWID works...
rowid_col = column("rowid", oracle.ROWID)
s3 = select([t.c.x, rowid_col]).where(
rowid_col == cast(rowid, oracle.ROWID)
)
eq_(conn.execute(s3).fetchall(), [(5, rowid)])
@testing.provide_metadata
def test_interval(self):
metadata = self.metadata
interval_table = Table(
"intervaltable",
metadata,
Column(
"id", Integer, primary_key=True, test_needs_autoincrement=True
),
Column("day_interval", oracle.INTERVAL(day_precision=3)),
)
metadata.create_all()
interval_table.insert().execute(
day_interval=datetime.timedelta(days=35, seconds=5743)
)
row = interval_table.select().execute().first()
eq_(row["day_interval"], datetime.timedelta(days=35, seconds=5743))
@testing.provide_metadata
def test_numerics(self):
m = self.metadata
t1 = Table(
"t1",
m,
Column("intcol", Integer),
Column("numericcol", Numeric(precision=9, scale=2)),
Column("floatcol1", Float()),
Column("floatcol2", FLOAT()),
Column("doubleprec", oracle.DOUBLE_PRECISION),
Column("numbercol1", oracle.NUMBER(9)),
Column("numbercol2", oracle.NUMBER(9, 3)),
Column("numbercol3", oracle.NUMBER),
)
t1.create()
t1.insert().execute(
intcol=1,
numericcol=5.2,
floatcol1=6.5,
floatcol2=8.5,
doubleprec=9.5,
numbercol1=12,
numbercol2=14.85,
numbercol3=15.76,
)
m2 = MetaData(testing.db)
t2 = Table("t1", m2, autoload=True)
for row in (
t1.select().execute().first(),
t2.select().execute().first(),
):
for i, (val, type_) in enumerate(
(
(1, int),
(decimal.Decimal("5.2"), decimal.Decimal),
(6.5, float),
(8.5, float),
(9.5, float),
(12, int),
(decimal.Decimal("14.85"), decimal.Decimal),
(15.76, float),
)
):
eq_(row[i], val)
assert isinstance(row[i], type_), "%r is not %r" % (
row[i],
type_,
)
@testing.provide_metadata
def test_numeric_infinity_float(self, connection):
m = self.metadata
t1 = Table(
"t1",
m,
Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)),
)
t1.create()
t1.insert().execute(
[
dict(intcol=1, numericcol=float("inf")),
dict(intcol=2, numericcol=float("-inf")),
]
)
eq_(
select([t1.c.numericcol])
.order_by(t1.c.intcol)
.execute()
.fetchall(),
[(float("inf"),), (float("-inf"),)],
)
eq_(
exec_sql(
connection, "select numericcol from t1 order by intcol"
).fetchall(),
[(float("inf"),), (float("-inf"),)],
)
@testing.provide_metadata
def test_numeric_infinity_decimal(self, connection):
m = self.metadata
t1 = Table(
"t1",
m,
Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=True)),
)
t1.create()
t1.insert().execute(
[
dict(intcol=1, numericcol=decimal.Decimal("Infinity")),
dict(intcol=2, numericcol=decimal.Decimal("-Infinity")),
]
)
eq_(
select([t1.c.numericcol])
.order_by(t1.c.intcol)
.execute()
.fetchall(),
[(decimal.Decimal("Infinity"),), (decimal.Decimal("-Infinity"),)],
)
eq_(
exec_sql(
connection, "select numericcol from t1 order by intcol"
).fetchall(),
[(decimal.Decimal("Infinity"),), (decimal.Decimal("-Infinity"),)],
)
@testing.provide_metadata
def test_numeric_nan_float(self, connection):
m = self.metadata
t1 = Table(
"t1",
m,
Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)),
)
t1.create()
t1.insert().execute(
[
dict(intcol=1, numericcol=float("nan")),
dict(intcol=2, numericcol=float("-nan")),
]
)
eq_(
[
tuple(str(col) for col in row)
for row in select([t1.c.numericcol])
.order_by(t1.c.intcol)
.execute()
],
[("nan",), ("nan",)],
)
eq_(
[
tuple(str(col) for col in row)
for row in exec_sql(
connection, "select numericcol from t1 order by intcol"
)
],
[("nan",), ("nan",)],
)
# needs https://github.com/oracle/python-cx_Oracle/
# issues/184#issuecomment-391399292
@testing.provide_metadata
def _dont_test_numeric_nan_decimal(self, connection):
m = self.metadata
t1 = Table(
"t1",
m,
Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=True)),
)
t1.create()
t1.insert().execute(
[
dict(intcol=1, numericcol=decimal.Decimal("NaN")),
dict(intcol=2, numericcol=decimal.Decimal("-NaN")),
]
)
eq_(
select([t1.c.numericcol])
.order_by(t1.c.intcol)
.execute()
.fetchall(),
[(decimal.Decimal("NaN"),), (decimal.Decimal("NaN"),)],
)
eq_(
exec_sql(
connection, "select numericcol from t1 order by intcol"
).fetchall(),
[(decimal.Decimal("NaN"),), (decimal.Decimal("NaN"),)],
)
@testing.provide_metadata
def test_numerics_broken_inspection(self, connection):
"""Numeric scenarios where Oracle type info is 'broken',
returning us precision, scale of the form (0, 0) or (0, -127).
We convert to Decimal and let int()/float() processors take over.
"""
metadata = self.metadata
# this test requires cx_oracle 5
foo = Table(
"foo",
metadata,
Column("idata", Integer),
Column("ndata", Numeric(20, 2)),
Column("ndata2", Numeric(20, 2)),
Column("nidata", Numeric(5, 0)),
Column("fdata", Float()),
)
foo.create(connection)
connection.execute(
foo.insert(),
{
"idata": 5,
"ndata": decimal.Decimal("45.6"),
"ndata2": decimal.Decimal("45.0"),
"nidata": decimal.Decimal("53"),
"fdata": 45.68392,
},
)
stmt = "SELECT idata, ndata, ndata2, nidata, fdata FROM foo"
row = exec_sql(connection, stmt).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, int, float],
)
eq_(
row,
(
5,
decimal.Decimal("45.6"),
decimal.Decimal("45"),
53,
45.683920000000001,
),
)
# with a nested subquery,
# both Numeric values that don't have decimal places, regardless
# of their originating type, come back as ints with no useful
# typing information beyond "numeric". So native handler
# must convert to int.
# this means our Decimal converters need to run no matter what.
# totally sucks.
stmt = """
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) FROM DUAL)
AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata
FROM dual
"""
row = exec_sql(connection, stmt).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, int, int, decimal.Decimal],
)
eq_(
row,
(5, decimal.Decimal("45.6"), 45, 53, decimal.Decimal("45.68392")),
)
row = connection.execute(
text(stmt).columns(
idata=Integer(),
ndata=Numeric(20, 2),
ndata2=Numeric(20, 2),
nidata=Numeric(5, 0),
fdata=Float(),
)
).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float],
)
eq_(
row,
(
5,
decimal.Decimal("45.6"),
decimal.Decimal("45"),
decimal.Decimal("53"),
45.683920000000001,
),
)
stmt = """
SELECT
anon_1.idata AS anon_1_idata,
anon_1.ndata AS anon_1_ndata,
anon_1.ndata2 AS anon_1_ndata2,
anon_1.nidata AS anon_1_nidata,
anon_1.fdata AS anon_1_fdata
FROM (SELECT idata, ndata, ndata2, nidata, fdata
FROM (
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0))
FROM DUAL) AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL)
AS fdata
FROM dual
)
WHERE ROWNUM >= 0) anon_1
"""
row = exec_sql(connection, stmt).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, int, int, decimal.Decimal],
)
eq_(
row,
(5, decimal.Decimal("45.6"), 45, 53, decimal.Decimal("45.68392")),
)
row = connection.execute(
text(stmt).columns(
anon_1_idata=Integer(),
anon_1_ndata=Numeric(20, 2),
anon_1_ndata2=Numeric(20, 2),
anon_1_nidata=Numeric(5, 0),
anon_1_fdata=Float(),
)
).fetchall()[0]
eq_(
[type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float],
)
eq_(
row,
(
5,
decimal.Decimal("45.6"),
decimal.Decimal("45"),
decimal.Decimal("53"),
45.683920000000001,
),
)
row = connection.execute(
text(stmt).columns(
anon_1_idata=Integer(),
anon_1_ndata=Numeric(20, 2, asdecimal=False),
anon_1_ndata2=Numeric(20, 2, asdecimal=False),
anon_1_nidata=Numeric(5, 0, asdecimal=False),
anon_1_fdata=Float(asdecimal=True),
)
).fetchall()[0]
eq_(
[type(x) for x in row], [int, float, float, float, decimal.Decimal]
)
eq_(row, (5, 45.6, 45, 53, decimal.Decimal("45.68392")))
def test_numeric_no_coerce_decimal_mode(self):
engine = testing_engine(options=dict(coerce_to_decimal=False))
with engine.connect() as conn:
# raw SQL no longer coerces to decimal
value = exec_sql(conn, "SELECT 5.66 FROM DUAL").scalar()
assert isinstance(value, float)
# explicit typing still *does* coerce to decimal
# (change in 1.2)
value = conn.scalar(
text("SELECT 5.66 AS foo FROM DUAL").columns(
foo=Numeric(4, 2, asdecimal=True)
)
)
assert isinstance(value, decimal.Decimal)
def test_numeric_coerce_decimal_mode(self, connection):
# default behavior is raw SQL coerces to decimal
value = exec_sql(connection, "SELECT 5.66 FROM DUAL").scalar()
assert isinstance(value, decimal.Decimal)
@testing.combinations(
(
"Max 32-bit Number",
"SELECT CAST(2147483647 AS NUMBER(19,0)) FROM dual",
),
(
"Min 32-bit Number",
"SELECT CAST(-2147483648 AS NUMBER(19,0)) FROM dual",
),
(
"32-bit Integer Overflow",
"SELECT CAST(2147483648 AS NUMBER(19,0)) FROM dual",
),
(
"32-bit Integer Underflow",
"SELECT CAST(-2147483649 AS NUMBER(19,0)) FROM dual",
),
(
"Max Number with Precision 19",
"SELECT CAST(9999999999999999999 AS NUMBER(19,0)) FROM dual",
),
(
"Min Number with Precision 19",
"SELECT CAST(-9999999999999999999 AS NUMBER(19,0)) FROM dual",
),
)
@testing.only_on("oracle+cx_oracle", "cx_oracle-specific feature")
def test_raw_numerics(self, title, stmt):
with testing.db.connect() as conn:
# get a brand new connection that definitely is not
# in the pool to avoid any outputtypehandlers
cx_oracle_raw = testing.db.pool._creator()
cursor = cx_oracle_raw.cursor()
cursor.execute(stmt)
cx_oracle_result = cursor.fetchone()[0]
cursor.close()
sqla_result = conn.exec_driver_sql(stmt).scalar()
eq_(sqla_result, cx_oracle_result)
@testing.only_on("oracle+cx_oracle", "cx_oracle-specific feature")
@testing.fails_if(
testing.requires.python3, "cx_oracle always returns unicode on py3k"
)
def test_coerce_to_unicode(self, connection):
engine = testing_engine(options=dict(coerce_to_unicode=False))
with engine.connect() as conn_no_coerce:
value = exec_sql(
conn_no_coerce, "SELECT 'hello' FROM DUAL"
).scalar()
assert isinstance(value, util.binary_type)
value = exec_sql(connection, "SELECT 'hello' FROM DUAL").scalar()
assert isinstance(value, util.text_type)
@testing.provide_metadata
def test_reflect_dates(self):
metadata = self.metadata
Table(
"date_types",
metadata,
Column("d1", sqltypes.DATE),
Column("d2", oracle.DATE),
Column("d3", TIMESTAMP),
Column("d4", TIMESTAMP(timezone=True)),
Column("d5", oracle.INTERVAL(second_precision=5)),
)
metadata.create_all()
m = MetaData(testing.db)
t1 = Table("date_types", m, autoload=True)
assert isinstance(t1.c.d1.type, oracle.DATE)
assert isinstance(t1.c.d1.type, DateTime)
assert isinstance(t1.c.d2.type, oracle.DATE)
assert isinstance(t1.c.d2.type, DateTime)
assert isinstance(t1.c.d3.type, TIMESTAMP)
assert not t1.c.d3.type.timezone
assert isinstance(t1.c.d4.type, TIMESTAMP)
assert t1.c.d4.type.timezone
assert isinstance(t1.c.d5.type, oracle.INTERVAL)
def _dont_test_reflect_all_types_schema(self):
types_table = Table(
"all_types",
MetaData(testing.db),
Column("owner", String(30), primary_key=True),
Column("type_name", String(30), primary_key=True),
autoload=True,
oracle_resolve_synonyms=True,
)
for row in types_table.select().execute().fetchall():
[row[k] for k in row.keys()]
@testing.provide_metadata
def test_raw_roundtrip(self, connection):
metadata = self.metadata
raw_table = Table(
"raw",
metadata,
Column("id", Integer, primary_key=True),
Column("data", oracle.RAW(35)),
)
metadata.create_all()
connection.execute(raw_table.insert(), id=1, data=b("ABCDEF"))
eq_(connection.execute(raw_table.select()).first(), (1, b("ABCDEF")))
@testing.provide_metadata
def test_reflect_nvarchar(self):
metadata = self.metadata
Table(
"tnv",
metadata,
Column("nv_data", sqltypes.NVARCHAR(255)),
Column("c_data", sqltypes.NCHAR(20)),
)
metadata.create_all()
m2 = MetaData(testing.db)
t2 = Table("tnv", m2, autoload=True)
assert isinstance(t2.c.nv_data.type, sqltypes.NVARCHAR)
assert isinstance(t2.c.c_data.type, sqltypes.NCHAR)
if testing.against("oracle+cx_oracle"):
assert isinstance(
t2.c.nv_data.type.dialect_impl(testing.db.dialect),
cx_oracle._OracleUnicodeStringNCHAR,
)
assert isinstance(
t2.c.c_data.type.dialect_impl(testing.db.dialect),
cx_oracle._OracleNChar,
)
data = u("m’a réveillé.")
with testing.db.connect() as conn:
conn.execute(t2.insert(), dict(nv_data=data, c_data=data))
nv_data, c_data = conn.execute(t2.select()).first()
eq_(nv_data, data)
eq_(c_data, data + (" " * 7)) # char is space padded
assert isinstance(nv_data, util.text_type)
assert isinstance(c_data, util.text_type)
@testing.provide_metadata
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)
@testing.provide_metadata
def test_char_length(self):
metadata = self.metadata
t1 = Table(
"t1",
metadata,
Column("c1", VARCHAR(50)),
Column("c2", NVARCHAR(250)),
Column("c3", CHAR(200)),
Column("c4", NCHAR(180)),
)
t1.create()
m2 = MetaData(testing.db)
t2 = Table("t1", m2, autoload=True)
eq_(t2.c.c1.type.length, 50)
eq_(t2.c.c2.type.length, 250)
eq_(t2.c.c3.type.length, 200)
eq_(t2.c.c4.type.length, 180)
@testing.provide_metadata
def test_long_type(self, connection):
metadata = self.metadata
t = Table("t", metadata, Column("data", oracle.LONG))
metadata.create_all(testing.db)
connection.execute(t.insert(), data="xyz")
eq_(connection.scalar(select([t.c.data])), "xyz")
@testing.provide_metadata
def test_longstring(self, connection):
metadata = self.metadata
exec_sql(
connection,
"""
CREATE TABLE Z_TEST
(
ID NUMERIC(22) PRIMARY KEY,
ADD_USER VARCHAR2(20) NOT NULL
)
""",
)
try:
t = Table("z_test", metadata, autoload_with=connection)
connection.execute(t.insert(), id=1.0, add_user="******")
assert connection.execute(t.select()).fetchall() == [(1, "foobar")]
finally:
exec_sql(connection, "DROP TABLE Z_TEST")
class DialectTypesTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = oracle.OracleDialect()
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_long(self):
self.assert_compile(oracle.LONG(), "LONG")
@testing.combinations(
(Date(), cx_oracle._OracleDate),
(oracle.OracleRaw(), cx_oracle._OracleRaw),
(String(), String),
(VARCHAR(), cx_oracle._OracleString),
(DATE(), cx_oracle._OracleDate),
(oracle.DATE(), oracle.DATE),
(String(50), cx_oracle._OracleString),
(Unicode(), cx_oracle._OracleUnicodeStringCHAR),
(Text(), cx_oracle._OracleText),
(UnicodeText(), cx_oracle._OracleUnicodeTextCLOB),
(CHAR(), cx_oracle._OracleChar),
(NCHAR(), cx_oracle._OracleNChar),
(NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR),
(oracle.RAW(50), cx_oracle._OracleRaw),
)
def test_type_adapt(self, start, test):
dialect = cx_oracle.dialect()
assert isinstance(
start.dialect_impl(dialect), test
), "wanted %r got %r" % (test, start.dialect_impl(dialect))
@testing.combinations(
(String(), String),
(VARCHAR(), cx_oracle._OracleString),
(String(50), cx_oracle._OracleString),
(Unicode(), cx_oracle._OracleUnicodeStringNCHAR),
(Text(), cx_oracle._OracleText),
(UnicodeText(), cx_oracle._OracleUnicodeTextNCLOB),
(NCHAR(), cx_oracle._OracleNChar),
(NVARCHAR(), cx_oracle._OracleUnicodeStringNCHAR),
)
def test_type_adapt_nchar(self, start, test):
dialect = cx_oracle.dialect(use_nchar_for_unicode=True)
assert isinstance(
start.dialect_impl(dialect), test
), "wanted %r got %r" % (test, start.dialect_impl(dialect))
def test_raw_compile(self):
self.assert_compile(oracle.RAW(), "RAW")
self.assert_compile(oracle.RAW(35), "RAW(35)")
def test_char_length(self):
self.assert_compile(VARCHAR(50), "VARCHAR(50 CHAR)")
oracle8dialect = oracle.dialect()
oracle8dialect.server_version_info = (8, 0)
self.assert_compile(VARCHAR(50), "VARCHAR(50)", dialect=oracle8dialect)
self.assert_compile(NVARCHAR(50), "NVARCHAR2(50)")
self.assert_compile(CHAR(50), "CHAR(50)")
@testing.combinations(
(String(50), "VARCHAR2(50 CHAR)"),
(Unicode(50), "VARCHAR2(50 CHAR)"),
(NVARCHAR(50), "NVARCHAR2(50)"),
(VARCHAR(50), "VARCHAR(50 CHAR)"),
(oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
(oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
(String(), "VARCHAR2"),
(Unicode(), "VARCHAR2"),
(NVARCHAR(), "NVARCHAR2"),
(VARCHAR(), "VARCHAR"),
(oracle.NVARCHAR2(), "NVARCHAR2"),
(oracle.VARCHAR2(), "VARCHAR2"),
)
def test_varchar_types(self, typ, exp):
dialect = oracle.dialect()
self.assert_compile(typ, exp, dialect=dialect)
@testing.combinations(
(String(50), "VARCHAR2(50 CHAR)"),
(Unicode(50), "NVARCHAR2(50)"),
(NVARCHAR(50), "NVARCHAR2(50)"),
(VARCHAR(50), "VARCHAR(50 CHAR)"),
(oracle.NVARCHAR2(50), "NVARCHAR2(50)"),
(oracle.VARCHAR2(50), "VARCHAR2(50 CHAR)"),
(String(), "VARCHAR2"),
(Unicode(), "NVARCHAR2"),
(NVARCHAR(), "NVARCHAR2"),
(VARCHAR(), "VARCHAR"),
(oracle.NVARCHAR2(), "NVARCHAR2"),
(oracle.VARCHAR2(), "VARCHAR2"),
)
def test_varchar_use_nchar_types(self, typ, exp):
dialect = oracle.dialect(use_nchar_for_unicode=True)
self.assert_compile(typ, exp, dialect=dialect)
@testing.combinations(
(oracle.INTERVAL(), "INTERVAL DAY TO SECOND"),
(oracle.INTERVAL(day_precision=3), "INTERVAL DAY(3) TO SECOND"),
(oracle.INTERVAL(second_precision=5), "INTERVAL DAY TO SECOND(5)"),
(
oracle.INTERVAL(day_precision=2, second_precision=5),
"INTERVAL DAY(2) TO SECOND(5)",
),
)
def test_interval(self, type_, expected):
self.assert_compile(type_, expected)
class TypesTest(fixtures.TestBase):
__only_on__ = 'oracle'
__dialect__ = oracle.OracleDialect()
__backend__ = True
@testing.fails_on('+zxjdbc', 'zxjdbc lacks the FIXED_CHAR dbapi type')
def test_fixed_char(self):
m = MetaData(testing.db)
t = Table('t1', m, Column('id', Integer, primary_key=True),
Column('data', CHAR(30), nullable=False))
t.create()
try:
t.insert().execute(dict(id=1, data="value 1"),
dict(id=2, data="value 2"),
dict(id=3, data="value 3"))
eq_(t.select().where(t.c.data == 'value 2').execute().fetchall(),
[(2, 'value 2 ')])
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
assert type(t2.c.data.type) is CHAR
eq_(t2.select().where(t2.c.data == 'value 2').execute().fetchall(),
[(2, 'value 2 ')])
finally:
t.drop()
@testing.requires.returning
@testing.provide_metadata
def test_int_not_float(self):
m = self.metadata
t1 = Table('t1', m, Column('foo', Integer))
t1.create()
r = t1.insert().values(foo=5).returning(t1.c.foo).execute()
x = r.scalar()
assert x == 5
assert isinstance(x, int)
x = t1.select().scalar()
assert x == 5
assert isinstance(x, int)
@testing.requires.returning
@testing.provide_metadata
def test_int_not_float_no_coerce_decimal(self):
engine = testing_engine(options=dict(coerce_to_decimal=False))
m = self.metadata
t1 = Table('t1', m, Column('foo', Integer))
t1.create()
r = engine.execute(t1.insert().values(foo=5).returning(t1.c.foo))
x = r.scalar()
assert x == 5
assert isinstance(x, int)
x = t1.select().scalar()
assert x == 5
assert isinstance(x, int)
@testing.provide_metadata
def test_rowid(self):
metadata = self.metadata
t = Table('t1', metadata, Column('x', Integer))
t.create()
t.insert().execute(x=5)
s1 = select([t])
s2 = select([column('rowid')]).select_from(s1)
rowid = s2.scalar()
# the ROWID type is not really needed here,
# as cx_oracle just treats it as a string,
# but we want to make sure the ROWID works...
rowid_col = column('rowid', oracle.ROWID)
s3 = select([t.c.x, rowid_col]) \
.where(rowid_col == cast(rowid, oracle.ROWID))
eq_(s3.select().execute().fetchall(), [(5, rowid)])
@testing.fails_on('+zxjdbc', 'Not yet known how to pass values of the '
'INTERVAL type')
@testing.provide_metadata
def test_interval(self):
metadata = self.metadata
interval_table = Table(
'intervaltable', metadata,
Column('id',
Integer,
primary_key=True,
test_needs_autoincrement=True),
Column('day_interval', oracle.INTERVAL(day_precision=3)))
metadata.create_all()
interval_table.insert().\
execute(day_interval=datetime.timedelta(days=35, seconds=5743))
row = interval_table.select().execute().first()
eq_(row['day_interval'], datetime.timedelta(days=35, seconds=5743))
@testing.provide_metadata
def test_numerics(self):
m = self.metadata
t1 = Table('t1', m, Column('intcol', Integer),
Column('numericcol', Numeric(precision=9, scale=2)),
Column('floatcol1', Float()), Column('floatcol2', FLOAT()),
Column('doubleprec', oracle.DOUBLE_PRECISION),
Column('numbercol1', oracle.NUMBER(9)),
Column('numbercol2', oracle.NUMBER(9, 3)),
Column('numbercol3', oracle.NUMBER))
t1.create()
t1.insert().execute(intcol=1,
numericcol=5.2,
floatcol1=6.5,
floatcol2=8.5,
doubleprec=9.5,
numbercol1=12,
numbercol2=14.85,
numbercol3=15.76)
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
for row in (t1.select().execute().first(),
t2.select().execute().first()):
for i, (val, type_) in enumerate((
(1, int),
(decimal.Decimal("5.2"), decimal.Decimal),
(6.5, float),
(8.5, float),
(9.5, float),
(12, int),
(decimal.Decimal("14.85"), decimal.Decimal),
(15.76, float),
)):
eq_(row[i], val)
assert isinstance(row[i], type_), '%r is not %r' \
% (row[i], type_)
@testing.provide_metadata
def test_numeric_infinity_float(self):
m = self.metadata
t1 = Table('t1', m, Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)))
t1.create()
t1.insert().execute([
dict(intcol=1, numericcol=float("inf")),
dict(intcol=2, numericcol=float("-inf")),
])
eq_(
select([t1.c.numericcol
]).order_by(t1.c.intcol).execute().fetchall(),
[(float('inf'), ), (float('-inf'), )])
eq_(
testing.db.execute(
"select numericcol from t1 order by intcol").fetchall(),
[(float('inf'), ), (float('-inf'), )])
@testing.provide_metadata
def test_numeric_infinity_decimal(self):
m = self.metadata
t1 = Table('t1', m, Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=True)))
t1.create()
t1.insert().execute([
dict(intcol=1, numericcol=decimal.Decimal("Infinity")),
dict(intcol=2, numericcol=decimal.Decimal("-Infinity")),
])
eq_(
select([t1.c.numericcol
]).order_by(t1.c.intcol).execute().fetchall(),
[(decimal.Decimal("Infinity"), ),
(decimal.Decimal("-Infinity"), )])
eq_(
testing.db.execute(
"select numericcol from t1 order by intcol").fetchall(),
[(decimal.Decimal("Infinity"), ),
(decimal.Decimal("-Infinity"), )])
@testing.provide_metadata
def test_numeric_nan_float(self):
m = self.metadata
t1 = Table('t1', m, Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=False)))
t1.create()
t1.insert().execute([
dict(intcol=1, numericcol=float("nan")),
dict(intcol=2, numericcol=float("-nan")),
])
eq_([
tuple(str(col) for col in row) for row in select(
[t1.c.numericcol]).order_by(t1.c.intcol).execute()
], [('nan', ), ('nan', )])
eq_([
tuple(str(col) for col in row) for row in testing.db.execute(
"select numericcol from t1 order by intcol")
], [('nan', ), ('nan', )])
# needs https://github.com/oracle/python-cx_Oracle/issues/184#issuecomment-391399292
@testing.provide_metadata
def _dont_test_numeric_nan_decimal(self):
m = self.metadata
t1 = Table('t1', m, Column("intcol", Integer),
Column("numericcol", oracle.BINARY_DOUBLE(asdecimal=True)))
t1.create()
t1.insert().execute([
dict(intcol=1, numericcol=decimal.Decimal("NaN")),
dict(intcol=2, numericcol=decimal.Decimal("-NaN")),
])
eq_(
select([t1.c.numericcol
]).order_by(t1.c.intcol).execute().fetchall(),
[(decimal.Decimal("NaN"), ), (decimal.Decimal("NaN"), )])
eq_(
testing.db.execute(
"select numericcol from t1 order by intcol").fetchall(),
[(decimal.Decimal("NaN"), ), (decimal.Decimal("NaN"), )])
@testing.provide_metadata
def test_numerics_broken_inspection(self):
"""Numeric scenarios where Oracle type info is 'broken',
returning us precision, scale of the form (0, 0) or (0, -127).
We convert to Decimal and let int()/float() processors take over.
"""
metadata = self.metadata
# this test requires cx_oracle 5
foo = Table('foo', metadata, Column('idata', Integer),
Column('ndata', Numeric(20, 2)),
Column('ndata2', Numeric(20, 2)),
Column('nidata', Numeric(5, 0)), Column('fdata', Float()))
foo.create()
foo.insert().execute({
'idata': 5,
'ndata': decimal.Decimal("45.6"),
'ndata2': decimal.Decimal("45.0"),
'nidata': decimal.Decimal('53'),
'fdata': 45.68392
})
stmt = "SELECT idata, ndata, ndata2, nidata, fdata FROM foo"
row = testing.db.execute(stmt).fetchall()[0]
eq_([type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, int, float])
eq_(row, (5, decimal.Decimal('45.6'), decimal.Decimal('45'), 53,
45.683920000000001))
# with a nested subquery,
# both Numeric values that don't have decimal places, regardless
# of their originating type, come back as ints with no useful
# typing information beyond "numeric". So native handler
# must convert to int.
# this means our Decimal converters need to run no matter what.
# totally sucks.
stmt = """
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2)) FROM DUAL)
AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0)) FROM DUAL)
AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL) AS fdata
FROM dual
"""
row = testing.db.execute(stmt).fetchall()[0]
eq_([type(x) for x in row],
[int, decimal.Decimal, int, int, decimal.Decimal])
eq_(row,
(5, decimal.Decimal('45.6'), 45, 53, decimal.Decimal('45.68392')))
row = testing.db.execute(
text(stmt,
typemap={
'idata': Integer(),
'ndata': Numeric(20, 2),
'ndata2': Numeric(20, 2),
'nidata': Numeric(5, 0),
'fdata': Float()
})).fetchall()[0]
eq_([type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float])
eq_(row, (5, decimal.Decimal('45.6'), decimal.Decimal('45'),
decimal.Decimal('53'), 45.683920000000001))
stmt = """
SELECT
anon_1.idata AS anon_1_idata,
anon_1.ndata AS anon_1_ndata,
anon_1.ndata2 AS anon_1_ndata2,
anon_1.nidata AS anon_1_nidata,
anon_1.fdata AS anon_1_fdata
FROM (SELECT idata, ndata, ndata2, nidata, fdata
FROM (
SELECT
(SELECT (SELECT idata FROM foo) FROM DUAL) AS idata,
(SELECT CAST((SELECT ndata FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata,
(SELECT CAST((SELECT ndata2 FROM foo) AS NUMERIC(20, 2))
FROM DUAL) AS ndata2,
(SELECT CAST((SELECT nidata FROM foo) AS NUMERIC(5, 0))
FROM DUAL) AS nidata,
(SELECT CAST((SELECT fdata FROM foo) AS FLOAT) FROM DUAL)
AS fdata
FROM dual
)
WHERE ROWNUM >= 0) anon_1
"""
row = testing.db.execute(stmt).fetchall()[0]
eq_([type(x) for x in row],
[int, decimal.Decimal, int, int, decimal.Decimal])
eq_(row,
(5, decimal.Decimal('45.6'), 45, 53, decimal.Decimal('45.68392')))
row = testing.db.execute(
text(stmt,
typemap={
'anon_1_idata': Integer(),
'anon_1_ndata': Numeric(20, 2),
'anon_1_ndata2': Numeric(20, 2),
'anon_1_nidata': Numeric(5, 0),
'anon_1_fdata': Float()
})).fetchall()[0]
eq_([type(x) for x in row],
[int, decimal.Decimal, decimal.Decimal, decimal.Decimal, float])
eq_(row, (5, decimal.Decimal('45.6'), decimal.Decimal('45'),
decimal.Decimal('53'), 45.683920000000001))
row = testing.db.execute(
text(stmt,
typemap={
'anon_1_idata': Integer(),
'anon_1_ndata': Numeric(20, 2, asdecimal=False),
'anon_1_ndata2': Numeric(20, 2, asdecimal=False),
'anon_1_nidata': Numeric(5, 0, asdecimal=False),
'anon_1_fdata': Float(asdecimal=True)
})).fetchall()[0]
eq_([type(x) for x in row],
[int, float, float, float, decimal.Decimal])
eq_(row, (5, 45.6, 45, 53, decimal.Decimal('45.68392')))
def test_numeric_no_coerce_decimal_mode(self):
engine = testing_engine(options=dict(coerce_to_decimal=False))
# raw SQL no longer coerces to decimal
value = engine.scalar("SELECT 5.66 FROM DUAL")
assert isinstance(value, float)
# explicit typing still *does* coerce to decimal
# (change in 1.2)
value = engine.scalar(
text("SELECT 5.66 AS foo FROM DUAL").columns(
foo=Numeric(4, 2, asdecimal=True)))
assert isinstance(value, decimal.Decimal)
# default behavior is raw SQL coerces to decimal
value = testing.db.scalar("SELECT 5.66 FROM DUAL")
assert isinstance(value, decimal.Decimal)
@testing.only_on("oracle+cx_oracle", "cx_oracle-specific feature")
@testing.fails_if(testing.requires.python3,
"cx_oracle always returns unicode on py3k")
def test_coerce_to_unicode(self):
engine = testing_engine(options=dict(coerce_to_unicode=False))
value = engine.scalar("SELECT 'hello' FROM DUAL")
assert isinstance(value, util.binary_type)
value = testing.db.scalar("SELECT 'hello' FROM DUAL")
assert isinstance(value, util.text_type)
@testing.provide_metadata
def test_reflect_dates(self):
metadata = self.metadata
Table(
"date_types",
metadata,
Column('d1', sqltypes.DATE),
Column('d2', oracle.DATE),
Column('d3', TIMESTAMP),
Column('d4', TIMESTAMP(timezone=True)),
Column('d5', oracle.INTERVAL(second_precision=5)),
)
metadata.create_all()
m = MetaData(testing.db)
t1 = Table("date_types", m, autoload=True)
assert isinstance(t1.c.d1.type, oracle.DATE)
assert isinstance(t1.c.d1.type, DateTime)
assert isinstance(t1.c.d2.type, oracle.DATE)
assert isinstance(t1.c.d2.type, DateTime)
assert isinstance(t1.c.d3.type, TIMESTAMP)
assert not t1.c.d3.type.timezone
assert isinstance(t1.c.d4.type, TIMESTAMP)
assert t1.c.d4.type.timezone
assert isinstance(t1.c.d5.type, oracle.INTERVAL)
def _dont_test_reflect_all_types_schema(self):
types_table = Table('all_types',
MetaData(testing.db),
Column('owner', String(30), primary_key=True),
Column('type_name', String(30), primary_key=True),
autoload=True,
oracle_resolve_synonyms=True)
for row in types_table.select().execute().fetchall():
[row[k] for k in row.keys()]
@testing.provide_metadata
def test_raw_roundtrip(self):
metadata = self.metadata
raw_table = Table('raw', metadata,
Column('id', Integer, primary_key=True),
Column('data', oracle.RAW(35)))
metadata.create_all()
testing.db.execute(raw_table.insert(), id=1, data=b("ABCDEF"))
eq_(testing.db.execute(raw_table.select()).first(), (1, b("ABCDEF")))
@testing.provide_metadata
def test_reflect_nvarchar(self):
metadata = self.metadata
Table('tnv', metadata, Column('data', sqltypes.NVARCHAR(255)))
metadata.create_all()
m2 = MetaData(testing.db)
t2 = Table('tnv', m2, autoload=True)
assert isinstance(t2.c.data.type, sqltypes.NVARCHAR)
if testing.against('oracle+cx_oracle'):
assert isinstance(t2.c.data.type.dialect_impl(testing.db.dialect),
cx_oracle._OracleUnicodeStringNCHAR)
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)
@testing.provide_metadata
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)
@testing.provide_metadata
def test_char_length(self):
metadata = self.metadata
t1 = Table('t1', metadata, Column("c1", VARCHAR(50)),
Column("c2", NVARCHAR(250)), Column("c3", CHAR(200)))
t1.create()
m2 = MetaData(testing.db)
t2 = Table('t1', m2, autoload=True)
eq_(t2.c.c1.type.length, 50)
eq_(t2.c.c2.type.length, 250)
eq_(t2.c.c3.type.length, 200)
@testing.provide_metadata
def test_long_type(self):
metadata = self.metadata
t = Table('t', metadata, Column('data', oracle.LONG))
metadata.create_all(testing.db)
testing.db.execute(t.insert(), data='xyz')
eq_(testing.db.scalar(select([t.c.data])), "xyz")
def test_longstring(self):
metadata = MetaData(testing.db)
testing.db.execute("""
CREATE TABLE Z_TEST
(
ID NUMERIC(22) PRIMARY KEY,
ADD_USER VARCHAR2(20) NOT NULL
)
""")
try:
t = Table("z_test", metadata, autoload=True)
t.insert().execute(id=1.0, add_user='******')
assert t.select().execute().fetchall() == [(1, 'foobar')]
finally:
testing.db.execute("DROP TABLE Z_TEST")