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 ")],
)
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_,
)
def test_limit_offset_for_update(self):
metadata = self.metadata
# oracle can't actually do the ROWNUM thing with FOR UPDATE
# very well.
t = Table(
"t1",
metadata,
Column("id", Integer, primary_key=True),
Column("data", Integer),
)
metadata.create_all()
t.insert().execute(
{
"id": 1,
"data": 1
},
{
"id": 2,
"data": 7
},
{
"id": 3,
"data": 12
},
{
"id": 4,
"data": 15
},
{
"id": 5,
"data": 32
},
)
# here, we can't use ORDER BY.
eq_(
t.select().with_for_update().limit(2).execute().fetchall(),
[(1, 1), (2, 7)],
)
# here, its impossible. But we'd prefer it to raise ORA-02014
# instead of issuing a syntax error.
assert_raises_message(
exc.DatabaseError,
"ORA-02014",
t.select().with_for_update().limit(2).offset(3).execute,
)
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)
def _run_test(self, *arg, **kw):
metadata = self.metadata
implicit_returning = kw.pop("implicit_returning", True)
kw["primary_key"] = True
if kw.get("autoincrement", True):
kw["test_needs_autoincrement"] = True
t = Table(
"x",
metadata,
Column("y", self.MyInteger, *arg, **kw),
Column("data", Integer),
implicit_returning=implicit_returning,
)
with testing.db.connect() as conn:
t.create(conn)
r = conn.execute(t.insert().values(data=5))
# we don't pre-fetch 'server_default'.
if "server_default" in kw and (
not testing.db.dialect.implicit_returning
or not implicit_returning):
eq_(r.inserted_primary_key, [None])
else:
eq_(r.inserted_primary_key, ["INT_1"])
eq_(conn.execute(t.select()).first(), ("INT_1", 5))
def test_insert_from_select_fn_defaults(self, connection):
data = self.tables.data
counter = itertools.count(1)
def foo(ctx):
return next(counter)
table = Table(
"sometable",
self.metadata,
Column("x", Integer),
Column("foo", Integer, default=foo),
Column("y", Integer),
)
table.create(connection)
sel = select([data.c.x, data.c.y])
ins = table.insert().from_select(["x", "y"], sel)
connection.execute(ins)
# counter is only called once!
eq_(
list(connection.execute(table.select().order_by(table.c.x))),
[(2, 1, 5), (7, 1, 12)],
)
def test_int_default_none_on_insert(self, connection):
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", Integer, server_default="5", primary_key=True),
Column("data", String(10)),
implicit_returning=False,
)
assert t._autoincrement_column is None
metadata.create_all(connection)
r = connection.execute(t.insert(), dict(data="data"))
eq_(r.inserted_primary_key, [None])
if testing.against("sqlite"):
eq_(list(connection.execute(t.select())), [(1, "data")])
else:
eq_(list(connection.execute(t.select())), [(5, "data")])
def test_func_embedded_valuesbase(self, connection):
"""test can use next_value() in values() of _ValuesBase"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
s = Sequence("my_sequence")
connection.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(connection.scalar(t1.select()))
def define_tables(cls, metadata):
foo = Table(
"foo",
metadata,
Column("a", String(30), primary_key=1),
Column("b", String(30), nullable=0),
)
cls.tables.bar = foo.select(foo.c.b == "bar").alias("bar")
cls.tables.baz = foo.select(foo.c.b == "baz").alias("baz")
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")))
def test_table_round_trip(self):
oracle.RESERVED_WORDS.remove("UNION")
metadata = self.metadata
table = Table(
"t1",
metadata,
Column("option", Integer),
Column("plain", Integer, quote=True),
# test that quote works for a reserved word
# that the dialect isn't aware of when quote
# is set
Column("union", Integer, quote=True),
)
metadata.create_all()
table.insert().execute({"option": 1, "plain": 1, "union": 1})
eq_(testing.db.execute(table.select()).first(), (1, 1, 1))
table.update().values(option=2, plain=2, union=2).execute()
eq_(testing.db.execute(table.select()).first(), (2, 2, 2))
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()]
def test_int_default_none_on_insert_reflected(self, connection):
metadata = self.metadata
Table(
"x",
metadata,
Column("y", Integer, server_default="5", primary_key=True),
Column("data", String(10)),
implicit_returning=False,
)
metadata.create_all(connection)
m2 = MetaData()
t2 = Table("x", m2, autoload_with=connection, implicit_returning=False)
r = connection.execute(t2.insert(), dict(data="data"))
eq_(r.inserted_primary_key, [None])
if testing.against("sqlite"):
eq_(list(connection.execute(t2.select())), [(1, "data")])
else:
eq_(list(connection.execute(t2.select())), [(5, "data")])
def test_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)
def test_quoted_column_unicode(self):
metadata = self.metadata
table = Table(
"atable",
metadata,
Column(u("méil"), Unicode(255), primary_key=True),
)
metadata.create_all()
table.insert().execute({u("méil"): u("’é")})
result = testing.db.execute(
table.select().where(table.c[u("méil")] == u("’é"))).scalar()
eq_(result, u("’é"))
def test_empty_insert(self, connection):
t1 = Table(
"t1",
self.metadata,
Column("is_true", Boolean, server_default=("1")),
)
self.metadata.create_all(connection)
connection.execute(t1.insert())
eq_(
1,
connection.scalar(select([func.count(text("*"))]).select_from(t1)),
)
eq_(True, connection.scalar(t1.select()))
def test_quoted_column_non_unicode(self):
metadata = self.metadata
table = Table(
"atable",
metadata,
Column("_underscorecolumn", Unicode(255), primary_key=True),
)
metadata.create_all()
table.insert().execute({"_underscorecolumn": u("’é")})
result = testing.db.execute(table.select().where(
table.c._underscorecolumn == u("’é"))).scalar()
eq_(result, u("’é"))
def test_int_default_on_insert_with_returning(self, connection):
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", Integer, server_default="5", primary_key=True),
Column("data", String(10)),
)
metadata.create_all(connection)
r = connection.execute(t.insert(), dict(data="data"))
eq_(r.inserted_primary_key, [5])
eq_(list(connection.execute(t.select())), [(5, "data")])
def test_reflect(self):
t1.insert().execute({u("méil"): 2, ue("\u6e2c\u8a66"): 7})
t2.insert().execute({u("a"): 2, u("b"): 2})
t3.insert().execute({
ue("\u6e2c\u8a66_id"): 2,
ue("unitable1_\u6e2c\u8a66"): 7,
u("Unitéble2_b"): 2,
ue("\u6e2c\u8a66_self"): 2,
})
meta = MetaData(testing.db)
tt1 = Table(t1.name, meta, autoload=True)
tt2 = Table(t2.name, meta, autoload=True)
tt3 = Table(t3.name, meta, autoload=True)
tt1.insert().execute({u("méil"): 1, ue("\u6e2c\u8a66"): 5})
tt2.insert().execute({u("méil"): 1, ue("\u6e2c\u8a66"): 1})
tt3.insert().execute({
ue("\u6e2c\u8a66_id"): 1,
ue("unitable1_\u6e2c\u8a66"): 5,
u("Unitéble2_b"): 1,
ue("\u6e2c\u8a66_self"): 1,
})
self.assert_(
tt1.select(
order_by=desc(u("méil"))).execute().fetchall() == [(2,
7), (1,
5)])
self.assert_(
tt2.select(
order_by=desc(u("méil"))).execute().fetchall() == [(2,
2), (1,
1)])
self.assert_(
tt3.select(order_by=desc(ue(
"\u6e2c\u8a66_id"))).execute().fetchall() == [(2, 7, 2,
2), (1, 5, 1,
1)])
def test_create_same_names_implicit_schema(self):
meta = self.metadata
parent = Table("parent", meta, Column("pid", Integer,
primary_key=True))
child = Table(
"child",
meta,
Column("cid", Integer, primary_key=True),
Column("pid", Integer, ForeignKey("parent.pid")),
)
meta.create_all()
parent.insert().execute({"pid": 1})
child.insert().execute({"cid": 1, "pid": 1})
eq_(child.select().execute().fetchall(), [(1, 1)])
def test_func_embedded_whereclause(self, connection):
"""test can use next_value() in whereclause"""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer))
t1.create(testing.db)
connection.execute(t1.insert(), [{"x": 1}, {"x": 300}, {"x": 301}])
s = Sequence("my_sequence")
eq_(
list(
connection.execute(t1.select().where(t1.c.x > s.next_value()))
),
[(300,), (301,)],
)
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)
def test_reflect_alt_table_owner_local_synonym(self):
meta = MetaData(testing.db)
parent = Table(
"%s_pt" % testing.config.test_schema,
meta,
autoload=True,
oracle_resolve_synonyms=True,
)
self.assert_compile(
parent.select(),
"SELECT %(test_schema)s_pt.id, "
"%(test_schema)s_pt.data FROM %(test_schema)s_pt" %
{"test_schema": testing.config.test_schema},
)
select([parent]).execute().fetchall()
def test_string_default_on_insert_with_returning(self, connection):
"""With implicit_returning, we get a string PK default back no
problem."""
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", String(10), server_default="key_one",
primary_key=True),
Column("data", String(10)),
)
metadata.create_all(connection)
r = connection.execute(t.insert(), dict(data="data"))
eq_(r.inserted_primary_key, ["key_one"])
eq_(list(connection.execute(t.select())), [("key_one", "data")])
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))
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")
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_create_same_names_explicit_schema(self):
schema = testing.db.dialect.default_schema_name
meta = self.metadata
parent = Table(
"parent",
meta,
Column("pid", Integer, primary_key=True),
schema=schema,
)
child = Table(
"child",
meta,
Column("cid", Integer, primary_key=True),
Column("pid", Integer, ForeignKey("%s.parent.pid" % schema)),
schema=schema,
)
meta.create_all()
parent.insert().execute({"pid": 1})
child.insert().execute({"cid": 1, "pid": 1})
eq_(child.select().execute().fetchall(), [(1, 1)])
def test_insert_from_select_override_defaults(self, connection):
data = self.tables.data
table = Table(
"sometable",
self.metadata,
Column("x", Integer),
Column("foo", Integer, default=12),
Column("y", Integer),
)
table.create(connection)
sel = select([data.c.x, data.c.y])
ins = table.insert().from_select(["x", "y"], sel)
connection.execute(ins)
eq_(
list(connection.execute(table.select().order_by(table.c.x))),
[(2, 12, 5), (7, 12, 12)],
)
def test_string_default_none_on_insert(self, connection):
"""Test that without implicit returning, we return None for
a string server default.
That is, we don't want to attempt to pre-execute "server_default"
generically - the user should use a Python side-default for a case
like this. Testing that all backends do the same thing here.
"""
metadata = self.metadata
t = Table(
"x",
metadata,
Column("y", String(10), server_default="key_one",
primary_key=True),
Column("data", String(10)),
implicit_returning=False,
)
metadata.create_all(connection)
r = connection.execute(t.insert(), dict(data="data"))
eq_(r.inserted_primary_key, [None])
eq_(list(connection.execute(t.select())), [("key_one", "data")])
