def test_no_cast_pre_4(self):
self.assert_compile(cast(Column("foo", Integer), String),
"CAST(foo AS CHAR)")
dialect = mysql.dialect()
dialect.server_version_info = (3, 2, 3)
with expect_warnings("Current MySQL version does not support CAST;"):
self.assert_compile(cast(Column("foo", Integer), String),
"foo",
dialect=dialect)
def test_persistence(self):
addresses = self.tables.addresses
User, Address = self._user_address_fixture()
sess = create_session()
u1 = User(name="jack")
a1 = Address(email_address="foo")
sess.add_all([u1, a1])
sess.flush()
eq_(
testing.db.scalar(
select([func.count(cast(1, Integer))
]).where(addresses.c.user_id != None)), # noqa
0,
)
u1 = sess.query(User).get(u1.id)
u1.addresses.append(a1)
sess.flush()
eq_(
testing.db.execute(
select([addresses]).where(addresses.c.user_id != None) # noqa
).fetchall(),
[(a1.id, u1.id, "foo")],
)
u1.addresses.remove(a1)
sess.flush()
eq_(
testing.db.scalar(
select([func.count(cast(1, Integer))
]).where(addresses.c.user_id != None)), # noqa
0,
)
u1.addresses.append(a1)
sess.flush()
eq_(
testing.db.execute(
select([addresses]).where(addresses.c.user_id != None) # noqa
).fetchall(),
[(a1.id, u1.id, "foo")],
)
a2 = Address(email_address="bar")
u1.addresses.remove(a1)
u1.addresses.append(a2)
sess.flush()
eq_(
testing.db.execute(
select([addresses]).where(addresses.c.user_id != None) # noqa
).fetchall(),
[(a2.id, u1.id, "bar")],
)
def test_is_boolean_symbols_despite_no_native(self):
is_(
testing.db.scalar(select([cast(true().is_(true()), Boolean)])),
True,
)
is_(
testing.db.scalar(select([cast(true().isnot(true()), Boolean)])),
False,
)
is_(
testing.db.scalar(select([cast(false().is_(false()), Boolean)])),
True,
)
def test_cast_type_decorator(self):
class MyInteger(sqltypes.TypeDecorator):
impl = Integer
type_ = MyInteger()
t = sql.table("t", sql.column("col"))
self.assert_compile(cast(t.c.col, type_),
"CAST(t.col AS SIGNED INTEGER)")
def test_cast_grouped_expression_pre_4(self):
dialect = mysql.dialect()
dialect.server_version_info = (3, 2, 3)
with expect_warnings("Current MySQL version does not support CAST;"):
self.assert_compile(
cast(sql.column("x") + sql.column("y"), Integer),
"(x + y)",
dialect=dialect,
)
def test_type_compile(self):
from sqlalchemy_1_3.dialects.sqlite.base import dialect as SQLiteDialect
cast = sa.cast(column("x"), sa.Integer)
@profile_memory()
def go():
dialect = SQLiteDialect()
cast.compile(dialect=dialect)
go()
def setup_mappers(cls):
mapper(
cls.classes.Person,
cls.tables.person,
properties=dict(pets=relationship(
cls.classes.Pet,
primaryjoin=(
orm.foreign(cls.tables.pets.c.person_id) == sa.cast(
sa.type_coerce(cls.tables.person.c.id, Integer),
Integer,
)),
)),
)
mapper(cls.classes.Pet, cls.tables.pets)
def test_unsupported_cast_literal_bind(self):
expr = cast(column("foo", Integer) + 5, Float)
with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
self.assert_compile(expr, "(foo + 5)", literal_binds=True)
dialect = mysql.MySQLDialect()
dialect.server_version_info = (3, 9, 8)
with expect_warnings("Current MySQL version does not support CAST"):
eq_(
str(
expr.compile(dialect=dialect,
compile_kwargs={"literal_binds": True})),
"(foo + 5)",
)
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_quoted_name_bindparam_ok(self):
from sqlalchemy_1_3.sql.elements import quoted_name
with testing.db.connect() as conn:
eq_(
conn.scalar(
select(
[
cast(
literal(quoted_name("some_name", False)),
String,
)
]
)
),
"some_name",
)
def test_unsupported_casts(self, type_, expected):
t = sql.table("t", sql.column("col"))
with expect_warnings("Datatype .* does not support CAST on MySQL;"):
self.assert_compile(cast(t.c.col, type_), expected)
def test_cast_literal_bind(self):
expr = cast(column("foo", Integer) + 5, Integer())
self.assert_compile(expr,
"CAST(foo + 5 AS SIGNED INTEGER)",
literal_binds=True)
def test_cast(self, type_, expected):
t = sql.table("t", sql.column("col"))
self.assert_compile(cast(t.c.col, type_), expected)
def test_case(self):
inner = select(
[
case([
[info_table.c.pk < 3, "lessthan3"],
[
and_(info_table.c.pk >= 3, info_table.c.pk < 7),
"gt3",
],
]).label("x"),
info_table.c.pk,
info_table.c.info,
],
from_obj=[info_table],
)
inner_result = inner.execute().fetchall()
# Outputs:
# lessthan3 1 pk_1_data
# lessthan3 2 pk_2_data
# gt3 3 pk_3_data
# gt3 4 pk_4_data
# gt3 5 pk_5_data
# gt3 6 pk_6_data
assert inner_result == [
("lessthan3", 1, "pk_1_data"),
("lessthan3", 2, "pk_2_data"),
("gt3", 3, "pk_3_data"),
("gt3", 4, "pk_4_data"),
("gt3", 5, "pk_5_data"),
("gt3", 6, "pk_6_data"),
]
outer = select([inner.alias("q_inner")])
outer_result = outer.execute().fetchall()
assert outer_result == [
("lessthan3", 1, "pk_1_data"),
("lessthan3", 2, "pk_2_data"),
("gt3", 3, "pk_3_data"),
("gt3", 4, "pk_4_data"),
("gt3", 5, "pk_5_data"),
("gt3", 6, "pk_6_data"),
]
w_else = select(
[
case(
[
[info_table.c.pk < 3,
cast(3, Integer)],
[and_(info_table.c.pk >= 3, info_table.c.pk < 6), 6],
],
else_=0,
).label("x"),
info_table.c.pk,
info_table.c.info,
],
from_obj=[info_table],
)
else_result = w_else.execute().fetchall()
assert else_result == [
(3, 1, "pk_1_data"),
(3, 2, "pk_2_data"),
(6, 3, "pk_3_data"),
(6, 4, "pk_4_data"),
(6, 5, "pk_5_data"),
(0, 6, "pk_6_data"),
]
def column_expression(self, col):
return sa.cast(col, Integer)
def bind_expression(self, col):
return sa.cast(col, String)
def test_cast_grouped_expression_non_castable(self):
with expect_warnings("Datatype FLOAT does not support CAST on MySQL;"):
self.assert_compile(cast(sql.column("x") + sql.column("y"), Float),
"(x + y)")
def test_anonymous_expr(self):
table = self._fixture()
self.assert_compile(
select([cast(table.c.y, String)]),
"SELECT CAST(test_table.y AS VARCHAR) AS anon_1 FROM test_table",
)
def test_clause(self):
stmt = select([cast("INT_1", type_=self.MyInteger)]).as_scalar()
self._run_test(default=stmt)