def test_row_case_sensitive(self):
row = testing.db.execute(
select([
literal_column("1").label("case_insensitive"),
literal_column("2").label("CaseSensitive")
])
).first()
eq_(list(row.keys()), ["case_insensitive", "CaseSensitive"])
in_("case_insensitive", row._keymap)
in_("CaseSensitive", row._keymap)
not_in_("casesensitive", row._keymap)
eq_(row["case_insensitive"], 1)
eq_(row["CaseSensitive"], 2)
assert_raises(
KeyError,
lambda: row["Case_insensitive"]
)
assert_raises(
KeyError,
lambda: row["casesensitive"]
)
def test_row_case_sensitive_unoptimized(self):
ins_db = engines.testing_engine(options={"case_sensitive": True})
row = ins_db.execute(
select([
literal_column("1").label("case_insensitive"),
literal_column("2").label("CaseSensitive"),
text("3 AS screw_up_the_cols")
])
).first()
eq_(
list(row.keys()),
["case_insensitive", "CaseSensitive", "screw_up_the_cols"])
in_("case_insensitive", row._keymap)
in_("CaseSensitive", row._keymap)
not_in_("casesensitive", row._keymap)
eq_(row["case_insensitive"], 1)
eq_(row["CaseSensitive"], 2)
eq_(row["screw_up_the_cols"], 3)
assert_raises(KeyError, lambda: row["Case_insensitive"])
assert_raises(KeyError, lambda: row["casesensitive"])
assert_raises(KeyError, lambda: row["screw_UP_the_cols"])
def test_column_label_overlap_fallback(self):
content = Table(
'content', self.metadata,
Column('type', String(30)),
)
bar = Table(
'bar', self.metadata,
Column('content_type', String(30))
)
self.metadata.create_all(testing.db)
testing.db.execute(content.insert().values(type="t1"))
row = testing.db.execute(content.select(use_labels=True)).first()
in_(content.c.type, row)
not_in_(bar.c.content_type, row)
in_(sql.column('content_type'), row)
row = testing.db.execute(
select([content.c.type.label("content_type")])).first()
in_(content.c.type, row)
not_in_(bar.c.content_type, row)
in_(sql.column('content_type'), row)
row = testing.db.execute(select([func.now().label("content_type")])). \
first()
not_in_(content.c.type, row)
not_in_(bar.c.content_type, row)
in_(sql.column('content_type'), row)
def test_column_label_overlap_fallback(self):
content, bar = self.tables.content, self.tables.bar
row = testing.db.execute(
select([content.c.type.label("content_type")])).first()
not_in_(content.c.type, row)
not_in_(bar.c.content_type, row)
in_(sql.column('content_type'), row)
row = testing.db.execute(select([func.now().label("content_type")])). \
first()
not_in_(content.c.type, row)
not_in_(bar.c.content_type, row)
in_(sql.column('content_type'), row)
def test_modified(self):
Json = self.classes.Json
s = Session(testing.db)
j = Json(json={})
s.add(j)
s.commit()
i = inspect(j)
is_(i.modified, False)
in_('json', i.unmodified)
j.other = 42
is_(i.modified, True)
not_in_('json', i.unmodified)
def test_modified(self):
from sqlalchemy import inspect
Array = self.classes.Array
s = Session(testing.db)
a = Array(array=[1, 2, 3])
s.add(a)
s.commit()
i = inspect(a)
is_(i.modified, False)
in_('array', i.unmodified)
a.first = 10
is_(i.modified, True)
not_in_('array', i.unmodified)
def test_columnclause_schema_column_two(self):
keyed2 = self.tables.keyed2
a, b = sql.column('a'), sql.column('b')
stmt = select([keyed2.c.a, keyed2.c.b])
row = testing.db.execute(stmt).first()
in_(keyed2.c.a, row)
in_(keyed2.c.b, row)
in_(a, row)
in_(b, row)
def test_columnclause_schema_column_five(self):
keyed2 = self.tables.keyed2
# this is also addressed by [ticket:2932]
stmt = text("select a AS keyed2_a, b AS keyed2_b from keyed2").columns(
keyed2_a=CHAR, keyed2_b=CHAR)
row = testing.db.execute(stmt).first()
in_(keyed2.c.a, row)
in_(keyed2.c.b, row)
in_(stmt.c.keyed2_a, row)
in_(stmt.c.keyed2_b, row)
def test_columnclause_schema_column_one(self):
keyed2 = self.tables.keyed2
# this is addressed by [ticket:2932]
# ColumnClause._compare_name_for_result allows the
# columns which the statement is against to be lightweight
# cols, which results in a more liberal comparison scheme
a, b = sql.column('a'), sql.column('b')
stmt = select([a, b]).select_from(table("keyed2"))
row = testing.db.execute(stmt).first()
in_(keyed2.c.a, row)
in_(keyed2.c.b, row)
in_(a, row)
in_(b, row)
def test_max_ident_in_varchar_not_present(self):
"""test [ticket:3504].
Here we are testing not just that the "max" token comes back
as None, but also that these types accept "max" as the value
of "length" on construction, which isn't a directly documented
pattern however is likely in common use.
"""
metadata = self.metadata
Table(
't', metadata,
Column('t1', types.String),
Column('t2', types.Text('max')),
Column('t3', types.Text('max')),
Column('t4', types.LargeBinary('max')),
Column('t5', types.VARBINARY('max')),
)
metadata.create_all()
for col in inspect(testing.db).get_columns('t'):
is_(col['type'].length, None)
in_('max', str(col['type'].compile(dialect=testing.db.dialect)))
def test_max_ident_in_varchar_not_present(self):
"""test [ticket:3504].
Here we are testing not just that the "max" token comes back
as None, but also that these types accept "max" as the value
of "length" on construction, which isn't a directly documented
pattern however is likely in common use.
"""
metadata = self.metadata
Table(
"t",
metadata,
Column("t1", types.String),
Column("t2", types.Text("max")),
Column("t3", types.Text("max")),
Column("t4", types.LargeBinary("max")),
Column("t5", types.VARBINARY("max")),
)
metadata.create_all()
for col in inspect(testing.db).get_columns("t"):
is_(col["type"].length, None)
in_("max", str(col["type"].compile(dialect=testing.db.dialect)))
def test_delete_fetch_returning(self):
User = self.classes.User
sess = Session()
john, jack, jill, jane = sess.query(User).order_by(User.id).all()
in_(john, sess)
in_(jack, sess)
with self.sql_execution_asserter() as asserter:
sess.query(User).filter(User.age > 29).delete(
synchronize_session="fetch")
if testing.db.dialect.full_returning:
asserter.assert_(
CompiledSQL(
"DELETE FROM users WHERE users.age_int > %(age_int_1)s "
"RETURNING users.id",
[{
"age_int_1": 29
}],
dialect="postgresql",
), )
else:
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users "
"WHERE users.age_int > :age_int_1",
[{
"age_int_1": 29
}],
),
CompiledSQL(
"DELETE FROM users WHERE users.age_int > :age_int_1",
[{
"age_int_1": 29
}],
),
)
in_(john, sess)
not_in_(jack, sess)
in_(jill, sess)
not_in_(jane, sess)
def test_delete_fetch_returning_lambda(self):
User = self.classes.User
sess = Session(future=True)
john, jack, jill, jane = (
sess.execute(select(User).order_by(User.id)).scalars().all()
)
in_(john, sess)
in_(jack, sess)
with self.sql_execution_asserter() as asserter:
stmt = lambda_stmt(lambda: delete(User).where(User.age > 29))
sess.execute(
stmt, execution_options={"synchronize_session": "fetch"}
)
if testing.db.dialect.full_returning:
asserter.assert_(
CompiledSQL(
"DELETE FROM users WHERE users.age_int > %(age_int_1)s "
"RETURNING users.id",
[{"age_int_1": 29}],
dialect="postgresql",
),
)
else:
asserter.assert_(
CompiledSQL(
"SELECT users.id FROM users "
"WHERE users.age_int > :age_int_1",
[{"age_int_1": 29}],
),
CompiledSQL(
"DELETE FROM users WHERE users.age_int > :age_int_1",
[{"age_int_1": 29}],
),
)
in_(john, sess)
not_in_(jack, sess)
in_(jill, sess)
not_in_(jane, sess)
def test_get_table_names(self):
fake_cursor.rowcount = 1
fake_cursor.fetchall = MagicMock(return_value=[["t1"], ["t2"]])
insp = inspect(self.character.metadata.bind)
self.engine.dialect.server_version_info = (2, 0, 0)
eq_(insp.get_table_names(self.connection, "doc"), ['t1', 't2'])
in_("AND table_type = 'BASE TABLE' ORDER BY", self.executed_statement)
insp = inspect(self.character.metadata.bind)
self.engine.dialect.server_version_info = (1, 0, 0)
eq_(insp.get_table_names(self.connection, "doc"), ['t1', 't2'])
in_("WHERE table_schema = ? ORDER BY", self.executed_statement)
insp = inspect(self.character.metadata.bind)
self.engine.dialect.server_version_info = (0, 56, 0)
eq_(insp.get_table_names(self.connection, "doc"), ['t1', 't2'])
in_("WHERE schema_name = ? ORDER BY", self.executed_statement)
def test_row_case_insensitive(self):
ins_db = engines.testing_engine(options={"case_sensitive": False})
row = ins_db.execute(
select([
literal_column("1").label("case_insensitive"),
literal_column("2").label("CaseSensitive")
])).first()
eq_(list(row.keys()), ["case_insensitive", "CaseSensitive"])
in_("case_insensitive", row._keymap)
in_("CaseSensitive", row._keymap)
in_("casesensitive", row._keymap)
eq_(row["case_insensitive"], 1)
eq_(row["CaseSensitive"], 2)
eq_(row["Case_insensitive"], 1)
eq_(row["casesensitive"], 2)
def test_row_case_insensitive(self):
ins_db = engines.testing_engine(options={"case_sensitive": False})
row = ins_db.execute(
select([
literal_column("1").label("case_insensitive"),
literal_column("2").label("CaseSensitive")
])
).first()
eq_(list(row.keys()), ["case_insensitive", "CaseSensitive"])
in_("case_insensitive", row._keymap)
in_("CaseSensitive", row._keymap)
in_("casesensitive", row._keymap)
eq_(row["case_insensitive"], 1)
eq_(row["CaseSensitive"], 2)
eq_(row["Case_insensitive"], 1)
eq_(row["casesensitive"], 2)
def test_case_sensitive(self):
reg = functions._registry["_default"]
cs_reg = functions._case_sensitive_registry["_default"]
class MYFUNC(GenericFunction):
type = DateTime
assert isinstance(func.MYFUNC().type, DateTime)
assert isinstance(func.MyFunc().type, DateTime)
assert isinstance(func.mYfUnC().type, DateTime)
assert isinstance(func.myfunc().type, DateTime)
in_("myfunc", reg)
not_in_("MYFUNC", reg)
not_in_("MyFunc", reg)
in_("myfunc", cs_reg)
eq_(set(cs_reg["myfunc"].keys()), set(["MYFUNC"]))
with testing.expect_deprecated(
"GenericFunction 'MyFunc' is already registered with"
" different letter case, so the previously registered function "
"'MYFUNC' is switched into case-sensitive mode. "
"GenericFunction objects will be fully case-insensitive in a "
"future release.",
regex=False,
):
class MyFunc(GenericFunction):
type = Integer
assert isinstance(func.MYFUNC().type, DateTime)
assert isinstance(func.MyFunc().type, Integer)
with pytest.raises(AssertionError):
assert isinstance(func.mYfUnC().type, Integer)
with pytest.raises(AssertionError):
assert isinstance(func.myfunc().type, Integer)
eq_(reg["myfunc"], functions._CASE_SENSITIVE)
not_in_("MYFUNC", reg)
not_in_("MyFunc", reg)
in_("myfunc", cs_reg)
eq_(set(cs_reg["myfunc"].keys()), set(["MYFUNC", "MyFunc"]))
def test_columnclause_schema_column_four(self):
keyed2 = self.tables.keyed2
# this is also addressed by [ticket:2932]
a, b = sql.column('keyed2_a'), sql.column('keyed2_b')
stmt = text("select a AS keyed2_a, b AS keyed2_b from keyed2").columns(
a, b)
row = testing.db.execute(stmt).first()
in_(keyed2.c.a, row)
in_(keyed2.c.b, row)
in_(a, row)
in_(b, row)
in_(stmt.c.keyed2_a, row)
in_(stmt.c.keyed2_b, row)
def test_columnclause_schema_column_three(self):
keyed2 = self.tables.keyed2
# this is also addressed by [ticket:2932]
a, b = sql.column('a'), sql.column('b')
stmt = text("select a, b from keyed2").columns(a=CHAR, b=CHAR)
row = testing.db.execute(stmt).first()
in_(keyed2.c.a, row)
in_(keyed2.c.b, row)
in_(a, row)
in_(b, row)
in_(stmt.c.a, row)
in_(stmt.c.b, row)
def test_column_label_overlap_fallback_2(self):
content, bar = self.tables.content, self.tables.bar
row = testing.db.execute(content.select(use_labels=True)).first()
in_(content.c.type, row)
not_in_(bar.c.content_type, row)
not_in_(sql.column('content_type'), row)
def test_column_label_overlap_fallback_2(self):
content, bar = self.tables.content, self.tables.bar
row = testing.db.execute(content.select(use_labels=True)).first()
in_(content.c.type, row)
not_in_(bar.c.content_type, row)
not_in_(sql.column('content_type'), row)
def test_replace_function_case_sensitive(self):
reg = functions._registry["_default"]
cs_reg = functions._case_sensitive_registry["_default"]
class replaceable_func(GenericFunction):
type = Integer
identifier = "REPLACEABLE_FUNC"
assert isinstance(func.REPLACEABLE_FUNC().type, Integer)
assert isinstance(func.Replaceable_Func().type, Integer)
assert isinstance(func.RePlAcEaBlE_fUnC().type, Integer)
assert isinstance(func.replaceable_func().type, Integer)
in_("replaceable_func", reg)
not_in_("REPLACEABLE_FUNC", reg)
not_in_("Replaceable_Func", reg)
in_("replaceable_func", cs_reg)
eq_(set(cs_reg["replaceable_func"].keys()), set(["REPLACEABLE_FUNC"]))
with testing.expect_deprecated(
"GenericFunction 'Replaceable_Func' is already registered with"
" different letter case, so the previously registered function "
"'REPLACEABLE_FUNC' is switched into case-sensitive mode. "
"GenericFunction objects will be fully case-insensitive in a "
"future release.",
regex=False,
):
class Replaceable_Func(GenericFunction):
type = DateTime
identifier = "Replaceable_Func"
assert isinstance(func.REPLACEABLE_FUNC().type, Integer)
assert isinstance(func.Replaceable_Func().type, DateTime)
assert isinstance(func.RePlAcEaBlE_fUnC().type, NullType)
assert isinstance(func.replaceable_func().type, NullType)
eq_(reg["replaceable_func"], functions._CASE_SENSITIVE)
not_in_("REPLACEABLE_FUNC", reg)
not_in_("Replaceable_Func", reg)
in_("replaceable_func", cs_reg)
eq_(
set(cs_reg["replaceable_func"].keys()),
set(["REPLACEABLE_FUNC", "Replaceable_Func"]),
)
with testing.expect_warnings(
"The GenericFunction 'REPLACEABLE_FUNC' is already registered and "
"is going to be overriden.",
regex=False,
):
class replaceable_func_override(GenericFunction):
type = DateTime
identifier = "REPLACEABLE_FUNC"
with testing.expect_deprecated(
"GenericFunction(s) '['REPLACEABLE_FUNC', 'Replaceable_Func']' "
"are already registered with different letter cases and might "
"interact with 'replaceable_func'. GenericFunction objects will "
"be fully case-insensitive in a future release.",
regex=False,
):
class replaceable_func_lowercase(GenericFunction):
type = String
identifier = "replaceable_func"
with testing.expect_warnings(
"The GenericFunction 'Replaceable_Func' is already registered and "
"is going to be overriden.",
regex=False,
):
class Replaceable_Func_override(GenericFunction):
type = Integer
identifier = "Replaceable_Func"
assert isinstance(func.REPLACEABLE_FUNC().type, DateTime)
assert isinstance(func.Replaceable_Func().type, Integer)
assert isinstance(func.RePlAcEaBlE_fUnC().type, NullType)
assert isinstance(func.replaceable_func().type, String)
eq_(reg["replaceable_func"], functions._CASE_SENSITIVE)
not_in_("REPLACEABLE_FUNC", reg)
not_in_("Replaceable_Func", reg)
in_("replaceable_func", cs_reg)
eq_(
set(cs_reg["replaceable_func"].keys()),
set(["REPLACEABLE_FUNC", "Replaceable_Func", "replaceable_func"]),
)
def test_columnclause_schema_column_three(self):
keyed2 = self.tables.keyed2
# originally addressed by [ticket:2932], however liberalized
# Column-targeting rules are deprecated
a, b = sql.column("a"), sql.column("b")
stmt = text("select a, b from keyed2").columns(a=CHAR, b=CHAR)
row = testing.db.execute(stmt).first()
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
in_(keyed2.c.a, row)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
in_(keyed2.c.b, row)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
in_(a, row)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
in_(b, row)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names",
"The SelectBase.c and SelectBase.columns",
):
in_(stmt.c.a, row)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names",
"The SelectBase.c and SelectBase.columns",
):
in_(stmt.c.b, row)