def test_implicit_execution(self):
metadata = MetaData()
table = Table(
"test_table",
metadata,
Column("foo", Integer),
test_needs_acid=True,
)
conn = testing.db.connect()
metadata.create_all(bind=conn)
try:
trans = conn.begin()
metadata.bind = conn
t = table.insert()
assert t.bind is conn
table.insert().execute(foo=5)
table.insert().execute(foo=6)
table.insert().execute(foo=7)
trans.rollback()
metadata.bind = None
assert (
conn.execute("select count(*) from test_table").scalar() == 0
)
finally:
metadata.drop_all(bind=conn)
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"),)],
)
def test_misordered_lastrow(self):
metadata = self.metadata
related = Table(
"related",
metadata,
Column("id", Integer, primary_key=True),
mysql_engine="MyISAM",
)
t6 = Table(
"t6",
metadata,
Column(
"manual_id",
Integer,
ForeignKey("related.id"),
primary_key=True,
),
Column(
"auto_id",
Integer,
primary_key=True,
test_needs_autoincrement=True,
),
mysql_engine="MyISAM",
)
metadata.create_all()
r = related.insert().values(id=12).execute()
id_ = r.inserted_primary_key[0]
eq_(id_, 12)
r = t6.insert().values(manual_id=id_).execute()
eq_(r.inserted_primary_key, [12, 1])
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"),)],
)
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",)],
)
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_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_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 setup(self):
self.engine = engines.reconnecting_engine()
self.meta = MetaData(self.engine)
table = Table(
"sometable",
self.meta,
Column("id", Integer, primary_key=True),
Column("name", String(50)),
)
self.meta.create_all()
table.insert().execute([{
"id": i,
"name": "row %d" % i
} for i in range(1, 100)])
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_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_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_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 _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_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_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_no_embed_in_sql(self):
"""Using a DefaultGenerator, Sequence, DefaultClause
in the columns, where clause of a select, or in the values
clause of insert, update, raises an informative error"""
t = Table(
"some_table",
MetaData(),
Column("id", Integer),
Column("col4", String()),
)
for const in (
sa.Sequence("y"),
sa.ColumnDefault("y"),
sa.DefaultClause("y"),
):
assert_raises_message(
sa.exc.ArgumentError,
"SQL expression object expected, got object of type "
"<.* 'list'> instead",
t.select,
[const],
)
assert_raises_message(
sa.exc.InvalidRequestError,
"cannot be used directly as a column expression.",
str,
t.insert().values(col4=const),
)
assert_raises_message(
sa.exc.InvalidRequestError,
"cannot be used directly as a column expression.",
str,
t.update().values(col4=const),
)
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 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)])
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_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 test_autoincrement_single_col(self, connection):
single = Table("single", self.metadata,
Column("id", Integer, primary_key=True))
self.metadata.create_all(connection)
r = connection.execute(single.insert())
id_ = r.inserted_primary_key[0]
eq_(id_, 1)
eq_(connection.scalar(sa.select([single.c.id])), 1)
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_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 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_select_doesnt_pollute_result(self):
class MyType(TypeDecorator):
impl = Integer
def process_result_value(self, value, dialect):
raise Exception("I have not been selected")
t1 = Table("t1", self.metadata, Column("x", MyType()))
t2 = Table("t2", self.metadata, Column("x", Integer))
self.metadata.create_all(testing.db)
with testing.db.connect() as conn:
conn.execute(t1.insert().values(x=5))
stmt = (t2.insert().values(
x=select([t1.c.x]).as_scalar()).returning(t2.c.x))
result = conn.execute(stmt)
eq_(result.scalar(), 5)
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_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_inserted_pk_no_returning_no_lastrowid(self):
"""test inserted_primary_key contains [None] when
pk_col=next_value(), implicit returning is not used."""
metadata = self.metadata
t1 = Table("t", metadata, Column("x", Integer, primary_key=True))
t1.create(testing.db)
e = engines.testing_engine(options={"implicit_returning": False})
s = Sequence("my_sequence")
with e.connect() as conn:
r = conn.execute(t1.insert().values(x=s.next_value()))
eq_(r.inserted_primary_key, [None])
def test_inserted_pk_implicit_returning(self):
"""test inserted_primary_key contains the result when
pk_col=next_value(), when implicit returning is used."""
metadata = self.metadata
s = Sequence("my_sequence")
t1 = Table("t", metadata, Column("x", Integer, primary_key=True))
t1.create(testing.db)
e = engines.testing_engine(options={"implicit_returning": True})
with e.connect() as conn:
r = conn.execute(t1.insert().values(x=s.next_value()))
self._assert_seq_result(r.inserted_primary_key[0])