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_foreignkey_missing_insert(self):
Table("t1", self.metadata, Column("id", Integer, primary_key=True))
t2 = Table(
"t2",
self.metadata,
Column("id", Integer, ForeignKey("t1.id"), primary_key=True),
)
self.metadata.create_all()
# want to ensure that "null value in column "id" violates not-
# null constraint" is raised (IntegrityError on psycoopg2, but
# ProgrammingError on pg8000), and not "ProgrammingError:
# (ProgrammingError) relationship "t2_id_seq" does not exist".
# the latter corresponds to autoincrement behavior, which is not
# the case here due to the foreign key.
for eng in [
engines.testing_engine(options={"implicit_returning": False}),
engines.testing_engine(options={"implicit_returning": True}),
]:
with expect_warnings(
".*has no Python-side or server-side default.*"):
assert_raises(
(exc.IntegrityError, exc.ProgrammingError),
eng.execute,
t2.insert(),
)
def test_no_default_isolation_level(self):
from sqlalchemy_1_3.testing import mock
engine = engines.testing_engine()
real_isolation_level = testing.db.dialect.get_isolation_level
def fake_isolation_level(connection):
connection = mock.Mock(
cursor=mock.Mock(
return_value=mock.Mock(
fetchone=mock.Mock(return_value=None)
)
)
)
return real_isolation_level(connection)
with mock.patch.object(
engine.dialect, "get_isolation_level", fake_isolation_level
):
with expect_warnings(
"Could not retrieve transaction isolation level for MySQL "
"connection."
):
engine.connect()
def test_skip_not_describable(self):
@event.listens_for(self.metadata, "before_drop")
def cleanup(*arg, **kw):
with testing.db.connect() as conn:
conn.execute("DROP TABLE IF EXISTS test_t1")
conn.execute("DROP TABLE IF EXISTS test_t2")
conn.execute("DROP VIEW IF EXISTS test_v")
with testing.db.connect() as conn:
conn.execute("CREATE TABLE test_t1 (id INTEGER)")
conn.execute("CREATE TABLE test_t2 (id INTEGER)")
conn.execute("CREATE VIEW test_v AS SELECT id FROM test_t1")
conn.execute("DROP TABLE test_t1")
m = MetaData()
with expect_warnings(
"Skipping .* Table or view named .?test_v.? could not be "
"reflected: .* references invalid table"):
m.reflect(views=True, bind=conn)
eq_(m.tables["test_t2"].name, "test_t2")
assert_raises_message(
exc.UnreflectableTableError,
"references invalid table",
Table,
"test_v",
MetaData(),
autoload_with=conn,
)
def test_reconnect_on_reentrant_plus_closewresult(self):
conn = self.db.connect(close_with_result=True)
self.dbapi.shutdown("rollback")
# raises error
with expect_warnings(
"An exception has occurred during handling .*"
"something broke on execute but we didn't lose the connection",
py2konly=True,
):
assert_raises_message(
tsa.exc.DBAPIError,
"Lost the DB connection on rollback",
conn.execute,
select([1]),
)
assert conn.closed
assert conn.invalidated
assert_raises_message(
tsa.exc.StatementError,
"This Connection is closed",
conn.execute,
select([1]),
)
def test_trans_reset_agent_broken_ensure(self):
eng = testing_engine()
conn = eng.connect()
trans = conn.begin()
assert conn.connection._reset_agent is trans
trans.is_active = False
with expect_warnings("Reset agent is not active"):
conn.close()
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_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_trans_commit_reset_agent_broken_ensure(self):
eng = testing_engine(options={"pool_reset_on_return": "commit"})
conn = eng.connect()
trans = conn.begin()
assert conn.connection._reset_agent is trans
trans.is_active = False
with expect_warnings("Reset agent is not active"):
conn.close()
def test_anticipate_no_pk_lower_case_table(self):
t = table(
"t",
Column("id", Integer, primary_key=True, autoincrement=False),
Column("notpk", String(10), nullable=True),
)
with expect_warnings("Column 't.id' is marked as a member.*"
"may not store NULL.$"):
self.assert_compile(t.insert(),
"INSERT INTO t () VALUES ()",
params={})
def test_mariadb_check_warning(self, expect_, version):
dialect = mysql.dialect()
dialect.server_version_info = version
if expect_:
with expect_warnings(
".*before 10.2.9 has known issues regarding "
"CHECK constraints"
):
dialect._warn_for_known_db_issues()
else:
dialect._warn_for_known_db_issues()
def test_warning_skip_locked(self, connection):
stuff = self.tables.stuff
stmt = stuff.select().with_for_update(skip_locked=True)
with expect_warnings(
"SKIP LOCKED ignored on non-supporting MariaDB backend. "
"This will raise an error in SQLAlchemy 1.4."
):
connection.execute(stmt).fetchall()
def test_anticipate_no_pk_non_composite_pk(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True, autoincrement=False),
Column("q", Integer),
)
with expect_warnings("Column 't.x' is marked as a member.*"
"may not store NULL.$"):
self.assert_compile(t.insert(),
"INSERT INTO t (q) VALUES (:q)",
params={"q": 5})
def test_anticipate_no_pk_composite_pk(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True),
Column("y", Integer, primary_key=True),
)
with expect_warnings("Column 't.y' is marked as a member.*"
"Note that as of SQLAlchemy 1.1,"):
self.assert_compile(t.insert(),
"INSERT INTO t (x) VALUES (:x)",
params={"x": 5})
def test_ensure_is_disconnect_gets_connection(self):
def is_disconnect(e, conn, cursor):
# connection is still present
assert conn.connection is not None
# the error usually occurs on connection.cursor(),
# though MySQLdb we get a non-working cursor.
# assert cursor is None
self.engine.dialect.is_disconnect = is_disconnect
conn = self.engine.connect()
self.engine.test_shutdown()
with expect_warnings("An exception has occurred during handling .*",
py2konly=True):
assert_raises(tsa.exc.DBAPIError, conn.execute, select([1]))
def test_older_cx_oracle_warning(self, cx_Oracle, cx_oracle_type):
cx_Oracle.version = "6.3"
ignore_dialect = cx_oracle.dialect(dbapi=cx_Oracle,
encoding_errors="ignore")
ignore_outputhandler = (
ignore_dialect._generate_connection_outputtype_handler())
cursor = mock.Mock()
with testing.expect_warnings(
r"cx_oracle version \(6, 3\) does not support encodingErrors"):
ignore_outputhandler(cursor, "foo", cx_oracle_type, None, None,
None)
def test_computed_update_warning(self):
test = self.tables.test
with testing.db.connect() as conn:
conn.execute(test.insert(), {"id": 1, "foo": 5})
with testing.expect_warnings(
"Computed columns don't work with Oracle UPDATE"):
result = conn.execute(
test.update().values(foo=10).return_defaults())
# returns the *old* value
eq_(result.returned_defaults, (47, ))
eq_(conn.scalar(select([test.c.bar])), 52)
def test_eq_non_path(self):
umapper = inspect(self.classes.User)
amapper = inspect(self.classes.Address)
u_alias = inspect(aliased(self.classes.User))
p1 = PathRegistry.coerce((umapper,))
p2 = PathRegistry.coerce((umapper, umapper.attrs.addresses))
p3 = PathRegistry.coerce((u_alias, umapper.attrs.addresses))
p4 = PathRegistry.coerce((u_alias, umapper.attrs.addresses, amapper))
p5 = PathRegistry.coerce((u_alias,)).token(":*")
non_object = 54.1432
for obj in [p1, p2, p3, p4, p5]:
with expect_warnings(
"Comparison of PathRegistry to "
"<.* 'float'> is not supported"
):
is_(obj == non_object, False)
with expect_warnings(
"Comparison of PathRegistry to "
"<.* 'float'> is not supported"
):
is_(obj != non_object, True)
def test_non_autoincrement(self, connection):
# sqlite INT primary keys can be non-unique! (only for ints)
nonai = Table(
"nonaitest",
self.metadata,
Column("id", Integer, autoincrement=False, primary_key=True),
Column("data", String(20)),
)
nonai.create(connection)
# just testing SQLite for now, it passes
with expect_warnings(".*has no Python-side or server-side default.*"):
# postgresql + mysql strict will fail on first row,
# mysql in legacy mode fails on second row
connection.execute(nonai.insert(), dict(data="row 1"))
connection.execute(nonai.insert(), dict(data="row 2"))
def test_returning_update_computed_warning(self):
m = MetaData()
t1 = Table(
"t1",
m,
Column("id", Integer, primary_key=True),
Column("foo", Integer),
Column("bar", Integer, Computed("foo + 42")),
)
with testing.expect_warnings(
"Computed columns don't work with Oracle UPDATE"):
self.assert_compile(
t1.update().values(id=1, foo=5).returning(t1.c.bar),
"UPDATE t1 SET id=:id, foo=:foo RETURNING t1.bar INTO :ret_0",
)
def test_anticipate_no_pk_non_composite_pk_implicit_returning(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True, autoincrement=False),
Column("q", Integer),
)
d = postgresql.dialect()
d.implicit_returning = True
with expect_warnings("Column 't.x' is marked as a member.*"
"may not store NULL.$"):
self.assert_compile(
t.insert(),
"INSERT INTO t (q) VALUES (%(q)s)",
params={"q": 5},
dialect=d,
)
def test_anticipate_no_pk_composite_pk_prefetch(self):
t = Table(
"t",
MetaData(),
Column("x", Integer, primary_key=True),
Column("y", Integer, primary_key=True),
)
d = postgresql.dialect()
d.implicit_returning = False
with expect_warnings("Column 't.y' is marked as a member.*"
"Note that as of SQLAlchemy 1.1,"):
self.assert_compile(
t.insert(),
"INSERT INTO t (x) VALUES (%(x)s)",
params={"x": 5},
dialect=d,
)
def test_no_show_variables(self):
from sqlalchemy_1_3.testing import mock
engine = engines.testing_engine()
def my_execute(self, statement, *args, **kw):
if statement.startswith("SHOW VARIABLES"):
statement = "SELECT 1 FROM DUAL WHERE 1=0"
return real_exec(self, statement, *args, **kw)
real_exec = engine._connection_cls._execute_text
with mock.patch.object(
engine._connection_cls, "_execute_text", my_execute
):
with expect_warnings(
"Could not retrieve SQL_MODE; please ensure the "
"MySQL user has permissions to SHOW VARIABLES"
):
engine.connect()
def test_savepoint_release_fails_warning(self):
with testing.db.connect() as connection:
connection.begin()
with expect_warnings(
"An exception has occurred during handling of a previous "
"exception. The previous exception "
r"is:.*..SQL\:.*RELEASE SAVEPOINT"
):
def go():
with connection.begin_nested() as savepoint:
connection.dialect.do_release_savepoint(
connection, savepoint._savepoint
)
assert_raises_message(
exc.DBAPIError, r".*SQL\:.*ROLLBACK TO SAVEPOINT", go
)
def test_warning_in_transaction(self):
eng = testing_engine()
c1 = eng.connect()
with expect_warnings(
"Connection is already established with a Transaction; "
"setting isolation_level may implicitly rollback or commit "
"the existing transaction, or have no effect until next "
"transaction"
):
with c1.begin():
c1 = c1.execution_options(
isolation_level=self._non_default_isolation_level()
)
eq_(
eng.dialect.get_isolation_level(c1.connection),
self._non_default_isolation_level(),
)
# stays outside of transaction
eq_(
eng.dialect.get_isolation_level(c1.connection),
self._non_default_isolation_level(),
)
def test_mariadb_for_update(self):
dialect = mysql.dialect()
dialect.server_version_info = (10, 1, 1, "MariaDB")
table1 = table("mytable", column("myid"), column("name"),
column("description"))
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(of=table1),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"FOR UPDATE",
dialect=dialect,
)
with testing.expect_warnings("SKIP LOCKED ignored on non-supporting"):
self.assert_compile(
table1.select(table1.c.myid == 7).with_for_update(
skip_locked=True),
"SELECT mytable.myid, mytable.name, mytable.description "
"FROM mytable WHERE mytable.myid = %s "
"FOR UPDATE",
dialect=dialect,
)
def test_reconnect_on_reentrant(self):
conn = self.db.connect()
conn.execute(select([1]))
assert len(self.dbapi.connections) == 1
self.dbapi.shutdown("rollback")
# raises error
with expect_warnings(
"An exception has occurred during handling .*"
"something broke on execute but we didn't lose the connection",
py2konly=True,
):
assert_raises_message(
tsa.exc.DBAPIError,
"Lost the DB connection on rollback",
conn.execute,
select([1]),
)
assert not conn.closed
assert conn.invalidated
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_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_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)")
