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_accessor_sqlite_raw(self):
users = self.tables.users
users.insert().execute(
dict(user_id=1, user_name='john'),
)
r = text(
"select users.user_id, users.user_name "
"from users "
"UNION select users.user_id, "
"users.user_name from users",
bind=testing.db).execution_options(sqlite_raw_colnames=True). \
execute().first()
if testing.against("sqlite < 3.10.0"):
not_in_('user_id', r)
not_in_('user_name', r)
eq_(r['users.user_id'], 1)
eq_(r['users.user_name'], "john")
eq_(list(r.keys()), ["users.user_id", "users.user_name"])
else:
not_in_('users.user_id', r)
not_in_('users.user_name', r)
eq_(r['user_id'], 1)
eq_(r['user_name'], "john")
eq_(list(r.keys()), ["user_id", "user_name"])
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 = 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_accessor_labels_w_dots(self):
users = self.tables.users
users.insert().execute(dict(user_id=1, user_name='john'), )
# test using literal tablename.colname
r = text(
'select users.user_id AS "users.user_id", '
'users.user_name AS "users.user_name" '
'from users', bind=testing.db).\
execution_options(sqlite_raw_colnames=True).execute().first()
eq_(r['users.user_id'], 1)
eq_(r['users.user_name'], "john")
not_in_("user_name", r)
eq_(list(r.keys()), ["users.user_id", "users.user_name"])
def test_column_accessor_labels_w_dots(self):
users = self.tables.users
users.insert().execute(
dict(user_id=1, user_name='john'),
)
# test using literal tablename.colname
r = text(
'select users.user_id AS "users.user_id", '
'users.user_name AS "users.user_name" '
'from users', bind=testing.db).\
execution_options(sqlite_raw_colnames=True).execute().first()
eq_(r['users.user_id'], 1)
eq_(r['users.user_name'], "john")
not_in_("user_name", r)
eq_(list(r.keys()), ["users.user_id", "users.user_name"])
def test_delete_fetch_returning_lambda(self):
User = self.classes.User
sess = Session(testing.db, 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_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):
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_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_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_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_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_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_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_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)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
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)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
in_(sql.column("content_type"), row)
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)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
in_(sql.column("content_type"), row)
row = testing.db.execute(select([content.c.type.label("content_type")
])).first()
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
in_(content.c.type, row)
not_in_(bar.c.content_type, row)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
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)
with testing.expect_deprecated(
"Retreiving row values using Column objects "
"with only matching names"):
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_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)
