A reactive notebook for Python — run reproducible experiments, query with SQL, execute as a script, deploy as an app, and version with git. Stored as pure Python. All in a modern, AI-native editor.
Python artificial-intelligencedagdata-sciencedata-visualizationdataflowdeveloper-toolsmachine-learningnotebookspipelinepythonreactivesqlweb-app
# Copyright 2026 Marimo. All rights reserved.
from __future__ import annotations
from contextlib import contextmanager
from typing import TYPE_CHECKING
from unittest import mock
import pytest
from marimo._data.models import Database, DataTable, DataTableColumn, Schema
from marimo._dependencies.dependencies import DependencyManager
from marimo._sql.engines.sqlalchemy import (
SQLAlchemyEngine,
_index_by_table_name,
safe_execute,
)
from marimo._sql.engines.types import (
EngineCatalog,
QueryEngine,
default_inference_config,
)
from marimo._sql.sql import sql
from marimo._types.ids import VariableName
HAS_SQLALCHEMY = DependencyManager.sqlalchemy.has()
HAS_POLARS = DependencyManager.polars.has()
HAS_PANDAS = DependencyManager.pandas.has()
if TYPE_CHECKING:
import sqlalchemy as sa
UNUSED_DB_NAME = "unused_db_name"
@pytest.fixture
def empty_sqlite_engine() -> sa.Engine:
"""Create an empty SQLite database for testing."""
import sqlalchemy as sa
engine = sa.create_engine("sqlite:///:memory:")
return engine
@pytest.fixture
def sqlite_engine() -> sa.Engine:
"""Create a temporary SQLite database for testing."""
import sqlalchemy as sa
from sqlalchemy import text
engine = sa.create_engine("sqlite:///:memory:")
# Test if standard syntax works
with engine.begin() as conn:
conn.execute(
text(
"""
CREATE TABLE test (
id INTEGER PRIMARY KEY,
name TEXT
)
"""
)
)
conn.execute(
text(
"""
INSERT INTO test (id, name) VALUES
(1, 'Alice'),
(2, 'Bob'),
(3, 'Charlie')
"""
)
)
# Add another table in another schema
conn.execute(text("ATTACH ':memory:' AS my_schema"))
conn.execute(
text(
"""
CREATE TABLE my_schema.test2 (
id INTEGER PRIMARY KEY,
name TEXT
)
"""
)
)
# Test if mo.sql works
sql("INSERT INTO test (id, name) VALUES (4, 'Rose')", engine=engine)
return engine
@pytest.fixture
def sqlite_engine_meta() -> sa.Engine:
"""Create a temporary SQLite database with information_schema.
Use a mock information_schema"""
import sqlalchemy as sa
engine = sa.create_engine("sqlite:///:memory:")
sql("ATTACH ':memory:' AS information_schema", engine=engine)
sql(
"CREATE TABLE information_schema.tables (table_name TEXT)",
engine=engine,
)
sql(
"INSERT INTO information_schema.tables VALUES ('tables')",
engine=engine,
)
return engine
@pytest.fixture
def sqlite_engine_with_view() -> sa.Engine:
"""Create a SQLite database with tables and views in two schemas."""
import sqlalchemy as sa
from sqlalchemy import text
engine = sa.create_engine("sqlite:///:memory:")
with engine.begin() as conn:
conn.execute(
text(
"""
CREATE TABLE test (
id INTEGER PRIMARY KEY,
name TEXT
)
"""
)
)
conn.execute(
text("CREATE VIEW test_view AS SELECT id, name FROM test")
)
conn.execute(text("ATTACH ':memory:' AS my_schema"))
conn.execute(
text(
"""
CREATE TABLE my_schema.test2 (
id INTEGER PRIMARY KEY,
name TEXT
)
"""
)
)
conn.execute(
text(
"CREATE VIEW my_schema.test2_view AS "
"SELECT id, name FROM my_schema.test2"
)
)
return engine
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_dialect(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine dialect property."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
assert engine.dialect == "sqlite"
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_uses_default_inference_config(
sqlite_engine: sa.Engine,
) -> None:
"""SQLAlchemy shares the default discovery config (see #9775)."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
assert engine.inference_config == default_inference_config()
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_is_instance(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine is an instance of the correct types."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("sqlite")
)
assert isinstance(engine, SQLAlchemyEngine)
assert isinstance(engine, EngineCatalog)
assert isinstance(engine, QueryEngine)
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_invalid_engine() -> None:
"""Test SQLAlchemyEngine with an invalid engine and inspector does not raise errors."""
engine = SQLAlchemyEngine(connection=None, engine_name=None) # type: ignore
assert engine.inspector is None
assert engine.default_database is None
assert engine.default_schema is None
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_empty_engine(empty_sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine with an empty engine."""
engine = SQLAlchemyEngine(
connection=empty_sqlite_engine, engine_name=VariableName("sqlite")
)
databases = engine.get_databases(
include_schemas=True, include_tables=True, include_table_details=True
)
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[Schema(name="main", tables=[])],
engine=VariableName("sqlite"),
)
]
tables = engine.get_tables_in_schema(
schema="main", database=UNUSED_DB_NAME, include_table_details=False
)
assert tables == []
table_info = engine.get_table_details(
table_name="test", schema_name="main", database_name=UNUSED_DB_NAME
)
assert table_info is None
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_sql_types() -> None:
import sqlalchemy as sa
sqlite_engine = sa.create_engine("sqlite:///:memory:")
sql(
"""
CREATE TABLE all_types (
col_integer INTEGER,
col_real REAL,
col_numeric NUMERIC,
col_text TEXT,
col_blob BLOB,
col_json JSON
);
""",
engine=sqlite_engine,
)
sql(
"""
INSERT INTO all_types (
col_integer,
col_real,
col_numeric,
col_text,
col_blob,
col_json
) VALUES
(
1,
1.0,
1.0,
'text',
X'01',
'{"key": "value"}'
);
""",
engine=sqlite_engine,
)
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
tables = engine.get_tables_in_schema(
schema="main", database=UNUSED_DB_NAME, include_table_details=True
)
assert len(tables) == 1
table = tables[0]
assert table.source == "sqlite"
assert table.source_type == "connection"
assert table.name == "all_types"
assert table.num_columns == 6
assert table.num_rows is None
columns = table.columns
assert columns[0].name == "col_integer"
assert columns[0].type == "integer"
assert columns[0].external_type == "INTEGER"
assert columns[0].sample_values == [] # not implemented
assert columns[1].name == "col_real"
assert columns[1].type == "number"
assert columns[1].external_type == "REAL"
assert columns[2].name == "col_numeric"
assert columns[2].type == "number"
assert columns[2].external_type == "NUMERIC"
assert columns[3].name == "col_text"
assert columns[3].type == "string"
assert columns[3].external_type == "TEXT"
assert columns[4].name == "col_blob"
assert columns[4].type == "string"
assert columns[4].external_type == "BLOB"
assert columns[5].name == "col_json"
assert columns[5].type == "string"
assert columns[5].external_type == "JSON"
def get_expected_table(
table_name: str, include_table_details: bool = True
) -> DataTable:
"This test can be reused for sqlite_engine"
return DataTable(
source_type="connection",
source="sqlite",
name=table_name,
num_rows=None,
num_columns=2 if include_table_details else None,
variable_name=None,
engine=VariableName("test_sqlite"),
primary_keys=["id"] if include_table_details else [],
indexes=[],
columns=[
DataTableColumn(
name="id",
type="integer",
external_type="INTEGER",
sample_values=[],
),
DataTableColumn(
name="name",
type="string",
external_type="TEXT",
sample_values=[],
),
]
if include_table_details
else [],
)
def get_expected_schema(schema_name: str, table_name: str) -> Schema:
return Schema(
name=schema_name,
tables=[get_expected_table(table_name)],
)
def get_expected_view(view_name: str) -> DataTable:
"Expected reflection of a view over a `get_expected_table` table."
view = get_expected_table(view_name)
view.type = "view"
view.primary_keys = []
return view
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_table_details(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine get_table method."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
table = engine.get_table_details(
table_name="test", schema_name="main", database_name=UNUSED_DB_NAME
)
assert table == get_expected_table("test")
# different schema
table = engine.get_table_details(
table_name="test2",
schema_name="my_schema",
database_name=UNUSED_DB_NAME,
)
assert table == get_expected_table("test2")
# non-existent table
assert (
engine.get_table_details(
table_name="non_existent",
schema_name="main",
database_name=UNUSED_DB_NAME,
)
is None
)
# non-existent schema
assert (
engine.get_table_details(
table_name="test",
schema_name="non_existent",
database_name=UNUSED_DB_NAME,
)
is None
)
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_tables_in_schema(
sqlite_engine: sa.Engine,
) -> None:
"""Test SQLAlchemyEngine get_tables method."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
tables = engine.get_tables_in_schema(
schema="main", database=UNUSED_DB_NAME, include_table_details=True
)
assert isinstance(tables, list)
assert len(tables) == 1
assert tables[0] == get_expected_table("test")
# Test with other schema
tables = engine.get_tables_in_schema(
schema="my_schema", database=UNUSED_DB_NAME, include_table_details=True
)
assert isinstance(tables, list)
assert len(tables) == 1
assert tables[0] == get_expected_table("test2")
# Test with non-existent schema
assert (
engine.get_tables_in_schema(
schema="non_existent",
database=UNUSED_DB_NAME,
include_table_details=True,
)
== []
)
# Test with include_table_details false
tables = engine.get_tables_in_schema(
schema="main", database=UNUSED_DB_NAME, include_table_details=False
)
assert isinstance(tables, list)
assert len(tables) == 1
expected_table = get_expected_table("test", False)
assert tables[0] == expected_table
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_tables_with_views(
sqlite_engine_with_view: sa.Engine,
) -> None:
"""Views keep type='view' and full details on the batched path."""
engine = SQLAlchemyEngine(
sqlite_engine_with_view, engine_name=VariableName("test_sqlite")
)
tables = engine.get_tables_in_schema(
schema="main", database=UNUSED_DB_NAME, include_table_details=True
)
assert tables == [
get_expected_table("test"),
get_expected_view("test_view"),
]
tables = engine.get_tables_in_schema(
schema="my_schema",
database=UNUSED_DB_NAME,
include_table_details=True,
)
assert tables == [
get_expected_table("test2"),
get_expected_view("test2_view"),
]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_match_per_table_details(
sqlite_engine_with_view: sa.Engine,
) -> None:
"""Batched reflection returns the same tables as per-table reflection."""
engine = SQLAlchemyEngine(
sqlite_engine_with_view, engine_name=VariableName("test_sqlite")
)
batched = engine.get_tables_in_schema(
schema="main", database=UNUSED_DB_NAME, include_table_details=True
)
with mock.patch.object(
engine, "_get_batched_table_details", return_value=None
):
per_table = engine.get_tables_in_schema(
schema="main",
database=UNUSED_DB_NAME,
include_table_details=True,
)
assert batched == per_table
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_avoid_per_table_reflection(
sqlite_engine: sa.Engine,
) -> None:
"""No per-table reflection runs when the batch covers every table."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
with mock.patch.object(
engine, "_get_columns", wraps=engine._get_columns
) as spy:
tables = engine.get_tables_in_schema(
schema="main",
database=UNUSED_DB_NAME,
include_table_details=True,
)
assert tables == [get_expected_table("test")]
spy.assert_not_called()
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_fall_back_when_multi_reflection_raises(
sqlite_engine: sa.Engine,
) -> None:
"""A failing batch logs one warning and falls back per table."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
with (
mock.patch.object(
engine.inspector,
"get_multi_columns",
side_effect=RuntimeError("boom"),
),
mock.patch("marimo._sql.engines.sqlalchemy.LOGGER") as mock_logger,
):
tables = engine.get_tables_in_schema(
schema="main",
database=UNUSED_DB_NAME,
include_table_details=True,
)
assert tables == [get_expected_table("test")]
mock_logger.warning.assert_called_once_with(
"Failed to get batched columns", exc_info=True
)
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_nonexistent_schema_skips_batched_reflection(
sqlite_engine: sa.Engine,
) -> None:
"""A schema with no tables returns [] without attempting a batch."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
with mock.patch.object(engine, "_get_batched_table_details") as batched:
tables = engine.get_tables_in_schema(
schema="non_existent",
database=UNUSED_DB_NAME,
include_table_details=True,
)
assert tables == []
batched.assert_not_called()
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_skip_meta_schemas(
sqlite_engine_meta: sa.Engine,
) -> None:
"""Test SQLAlchemyEngine get_tables method."""
engine = SQLAlchemyEngine(
sqlite_engine_meta, engine_name=VariableName("test_sqlite")
)
databases = engine.get_databases(
include_schemas=True, include_tables=True, include_table_details=True
)
assert len(databases[0].schemas) == 2
assert databases[0].schemas[0].name == "main"
assert databases[0].schemas[1].name == "information_schema"
information_schema = databases[0].schemas[1]
assert information_schema.tables == []
# Eager discovery was skipped for the meta schema, so the empty table
# list is not authoritative — `tables_resolved` must be False so the
# frontend doesn't treat it as "known empty".
assert information_schema.tables_resolved is False
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_schemas(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine get_schemas method."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
schemas = engine.get_schemas(
database=UNUSED_DB_NAME,
include_tables=True,
include_table_details=True,
)
assert isinstance(schemas, list)
assert len(schemas) == 2
schema = schemas[0]
assert schema == get_expected_schema("main", "test")
schema = schemas[1]
assert schema == get_expected_schema("my_schema", "test2")
# Test with include_table_details false
schemas = engine.get_schemas(
database=UNUSED_DB_NAME,
include_tables=True,
include_table_details=False,
)
assert isinstance(schemas, list)
assert len(schemas) == 2
schema = schemas[0]
assert schema.name == "main"
assert len(schema.tables) == 1
expected_table = get_expected_table("test", include_table_details=False)
assert schema.tables[0] == expected_table
# Test with include_tables false
schemas = engine.get_schemas(
database=UNUSED_DB_NAME,
include_tables=False,
include_table_details=True,
)
assert isinstance(schemas, list)
assert len(schemas) == 2
schema = schemas[0]
assert schema.name == "main"
assert len(schema.tables) == 0
schema = schemas[1]
assert schema.name == "my_schema"
assert len(schema.tables) == 0
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_get_databases(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine get_databases method."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
databases = engine.get_databases(
include_schemas=True, include_tables=True, include_table_details=True
)
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[
get_expected_schema("main", "test"),
get_expected_schema("my_schema", "test2"),
],
engine=VariableName("test_sqlite"),
)
]
# Test with include_table_details false
databases = engine.get_databases(
include_schemas=True, include_tables=True, include_table_details=False
)
tables_main = get_expected_table("test", include_table_details=False)
tables_my_schema = get_expected_table("test2", include_table_details=False)
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[
Schema(name="main", tables=[tables_main]),
Schema(name="my_schema", tables=[tables_my_schema]),
],
engine=VariableName("test_sqlite"),
)
]
# Test with include_tables false
databases = engine.get_databases(
include_schemas=True, include_tables=False, include_table_details=True
)
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[
Schema(name="main", tables=[], tables_resolved=False),
Schema(name="my_schema", tables=[], tables_resolved=False),
],
engine=VariableName("test_sqlite"),
)
]
# Test with include_schemas false
databases = engine.get_databases(
include_schemas=False, include_tables=True, include_table_details=True
)
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[],
schemas_resolved=False,
engine=VariableName("test_sqlite"),
)
]
# Test with include_schemas and include_tables false
databases = engine.get_databases(
include_schemas=False, include_tables=False, include_table_details=True
)
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[],
schemas_resolved=False,
engine=VariableName("test_sqlite"),
)
]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_get_databases_auto(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine get_databases method with 'auto' option."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
# For SQLite, _is_cheap_discovery() returns True, so 'auto' should behave like True
databases = engine.get_databases(
include_schemas="auto",
include_tables="auto",
include_table_details="auto",
)
# Should be equivalent to setting all params to True since sqlite is a "cheap" dialect
tables_main = get_expected_table("test", include_table_details=True)
tables_my_schema = get_expected_table("test2", include_table_details=True)
assert tables_main.columns == tables_my_schema.columns
assert tables_main.primary_keys == tables_my_schema.primary_keys
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[
get_expected_schema("main", "test"),
get_expected_schema("my_schema", "test2"),
],
engine=VariableName("test_sqlite"),
)
]
# Test with a mock to simulate a non-cheap dialect
with mock.patch.object(
SQLAlchemyEngine, "_is_cheap_discovery", return_value=False
):
# For a non-cheap dialect, 'auto' should behave like False
databases = engine.get_databases(
include_schemas="auto",
include_tables="auto",
include_table_details="auto",
)
assert databases == [
Database(
name=":memory:",
dialect="sqlite",
schemas=[],
schemas_resolved=False,
engine=VariableName("test_sqlite"),
)
]
@pytest.mark.skipif(
not HAS_SQLALCHEMY or not HAS_PANDAS,
reason="SQLAlchemy and Pandas not installed",
)
def test_sqlalchemy_engine_execute(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine execute."""
import pandas as pd
import polars as pl
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
result = engine.execute("SELECT * FROM test ORDER BY id")
assert isinstance(result, (pd.DataFrame, pl.DataFrame))
assert len(result) == 4
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_get_database_name(sqlite_engine: sa.Engine) -> None:
"""Test SQLAlchemyEngine get_database_name."""
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
assert engine.get_default_database() == ":memory:"
import sqlalchemy as sa
# Test with no database name
engine = SQLAlchemyEngine(
sa.create_engine("sqlite:///"), engine_name=VariableName("test_sqlite")
)
assert engine.get_default_database() == ""
@pytest.mark.skipif(
not HAS_SQLALCHEMY or not HAS_POLARS or not HAS_PANDAS,
reason="SQLAlchemy, Polars, and Pandas not installed",
)
def test_sqlalchemy_engine_sql_output_formats(
sqlite_engine: sa.Engine,
) -> None:
"""Test SQLAlchemyEngine execute with different SQL output formats."""
from unittest import mock
import pandas as pd
import polars as pl
# Test with polars output format
with mock.patch.object(
SQLAlchemyEngine, "sql_output_format", return_value="polars"
):
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
result = engine.execute("SELECT * FROM test ORDER BY id")
assert isinstance(result, pl.DataFrame)
assert len(result) == 4
# Test with lazy-polars output format
with mock.patch.object(
SQLAlchemyEngine, "sql_output_format", return_value="lazy-polars"
):
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
result = engine.execute("SELECT * FROM test ORDER BY id")
assert isinstance(result, pl.LazyFrame)
assert len(result.collect()) == 4
# Test with pandas output format
with mock.patch.object(
SQLAlchemyEngine, "sql_output_format", return_value="pandas"
):
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
result = engine.execute("SELECT * FROM test ORDER BY id")
assert isinstance(result, pd.DataFrame)
assert len(result) == 4
# Test with native output format
with mock.patch.object(
SQLAlchemyEngine, "sql_output_format", return_value="native"
):
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
result = engine.execute("SELECT * FROM test ORDER BY id")
assert not isinstance(
result, (pd.DataFrame, pl.DataFrame, pl.LazyFrame)
)
assert hasattr(result, "fetchall")
# Test with auto output format (should use polars if available)
with mock.patch.object(
SQLAlchemyEngine, "sql_output_format", return_value="auto"
):
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
result = engine.execute("SELECT * FROM test ORDER BY id")
assert isinstance(result, (pd.DataFrame, pl.DataFrame))
assert len(result) == 4
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_sqlalchemy_engine_get_cursor_metadata(
sqlite_engine: sa.Engine,
) -> None:
"""Test SQLAlchemyEngine get_cursor_metadata."""
from sqlalchemy.engine import CursorResult
with mock.patch.object(
SQLAlchemyEngine, "sql_output_format", return_value="native"
):
engine = SQLAlchemyEngine(
sqlite_engine, engine_name=VariableName("test_sqlite")
)
result = engine.execute("SELECT * FROM test ORDER BY id")
assert isinstance(result, CursorResult)
assert SQLAlchemyEngine.is_cursor_result(result)
metadata = SQLAlchemyEngine.get_cursor_metadata(result)
assert metadata is not None
assert metadata["sql_statement_type"] == "Query / Unknown"
@pytest.mark.requires("sqlalchemy", "polars")
@pytest.mark.parametrize("output_format", ["polars", "lazy-polars", "auto"])
@pytest.mark.parametrize(
"values",
[
[None] * 100 + [0.5],
[None] * 1000 + ["late", None],
[1] * 100 + ["late"],
[1] * 100 + [1e100],
],
ids=[
"late-float",
"late-string-after-nulls",
"late-string-after-ints",
"integer-overflow",
],
)
def test_sqlalchemy_schema_inference_fallback(
empty_sqlite_engine: sa.Engine,
output_format: str,
values: list[float | str | None],
) -> None:
import polars as pl
import sqlalchemy as sa
from polars.testing import assert_frame_equal
with empty_sqlite_engine.begin() as connection:
connection.execute(sa.text("CREATE TABLE source (value)"))
connection.execute(sa.text("CREATE TABLE destination (value)"))
connection.execute(
sa.text("INSERT INTO source VALUES (:value)"),
[{"value": value} for value in values],
)
rows = connection.execute(
sa.text("SELECT value FROM source ORDER BY rowid")
).fetchall()
with pytest.raises(
pl.exceptions.ComputeError, match="infer_schema_length"
):
pl.DataFrame(rows)
expected = pl.DataFrame(rows, infer_schema_length=None)
engine = SQLAlchemyEngine(empty_sqlite_engine)
executed = mock.Mock()
sa.event.listen(empty_sqlite_engine, "before_cursor_execute", executed)
try:
with mock.patch.object(
engine, "sql_output_format", return_value=output_format
):
result = engine.execute(
"INSERT INTO destination SELECT value FROM source ORDER BY rowid "
"RETURNING value"
)
executed.assert_called_once()
finally:
sa.event.remove(empty_sqlite_engine, "before_cursor_execute", executed)
if output_format == "lazy-polars":
assert isinstance(result, pl.LazyFrame)
result = result.collect()
assert_frame_equal(result, expected)
with empty_sqlite_engine.connect() as connection:
assert connection.execute(
sa.text("SELECT COUNT(*) FROM destination")
).scalar_one() == len(values)
@pytest.mark.requires("sqlalchemy", "polars")
@pytest.mark.parametrize(
"query",
[
"SELECT 1 AS value",
"SELECT NULL AS value",
"SELECT 1 AS value WHERE 0",
],
ids=["valid", "all-null", "empty"],
)
def test_sqlalchemy_schema_inference_fast_path(
empty_sqlite_engine: sa.Engine, query: str
) -> None:
import polars as pl
import sqlalchemy as sa
from polars.testing import assert_frame_equal
with empty_sqlite_engine.connect() as connection:
expected = pl.DataFrame(connection.execute(sa.text(query)).fetchall())
engine = SQLAlchemyEngine(empty_sqlite_engine)
with (
mock.patch.object(engine, "sql_output_format", return_value="polars"),
mock.patch.object(pl, "DataFrame", wraps=pl.DataFrame) as constructor,
):
result = engine.execute(query)
constructor.assert_called_once()
assert constructor.call_args.kwargs == {}
assert_frame_equal(result, expected)
@pytest.mark.requires("sqlalchemy", "polars")
@pytest.mark.parametrize(
"error_type", ["ComputeError", "SchemaError", "ValueError"]
)
def test_sqlalchemy_schema_inference_unrelated_error(
empty_sqlite_engine: sa.Engine, error_type: str
) -> None:
import polars as pl
error = (
ValueError("unrelated error")
if error_type == "ValueError"
else getattr(pl.exceptions, error_type)("unrelated error")
)
engine = SQLAlchemyEngine(empty_sqlite_engine)
with (
mock.patch.object(engine, "sql_output_format", return_value="polars"),
mock.patch.object(pl, "DataFrame", side_effect=error) as constructor,
pytest.raises(type(error)) as exc_info,
):
engine.execute("SELECT 1 AS value")
assert exc_info.value is error
constructor.assert_called_once()
@pytest.mark.requires("sqlalchemy", "polars", "pandas")
@pytest.mark.parametrize("output_format", ["polars", "lazy-polars", "auto"])
@pytest.mark.parametrize(
"message",
["cannot construct a common type", "increase infer_schema_length"],
)
def test_sqlalchemy_schema_inference_retry_failure(
empty_sqlite_engine: sa.Engine, output_format: str, message: str
) -> None:
import pandas as pd
import polars as pl
from pandas.testing import assert_frame_equal
error = pl.exceptions.ComputeError(message)
engine = SQLAlchemyEngine(empty_sqlite_engine)
with (
mock.patch.object(
engine, "sql_output_format", return_value=output_format
),
mock.patch.object(
pl,
"DataFrame",
side_effect=[
pl.exceptions.ComputeError(
"consider increasing `infer_schema_length`"
),
error,
],
) as constructor,
):
if output_format == "auto":
result = engine.execute("SELECT 1 AS value")
assert_frame_equal(result, pd.DataFrame({"value": [1]}))
else:
with pytest.raises(pl.exceptions.ComputeError) as exc_info:
engine.execute("SELECT 1 AS value")
assert exc_info.value is error
assert constructor.call_count == 2
assert constructor.call_args.kwargs == {"infer_schema_length": None}
assert (
constructor.call_args_list[0].args[0] is constructor.call_args.args[0]
)
@pytest.mark.requires("sqlalchemy", "polars", "pandas")
@pytest.mark.parametrize("output_format", ["polars", "lazy-polars", "auto"])
def test_sqlalchemy_schema_inference_incompatible_types(
empty_sqlite_engine: sa.Engine, output_format: str
) -> None:
import pandas as pd
import polars as pl
from pandas.testing import assert_frame_equal
query = """
WITH RECURSIVE numbers(n) AS (
SELECT 0 UNION ALL SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT CASE WHEN n < 100 THEN 1 ELSE X'FF' END AS value
FROM numbers ORDER BY n
"""
engine = SQLAlchemyEngine(empty_sqlite_engine)
with mock.patch.object(
engine, "sql_output_format", return_value=output_format
):
if output_format == "auto":
result = engine.execute(query)
assert_frame_equal(
result, pd.DataFrame({"value": [1] * 100 + [b"\xff"]})
)
else:
with pytest.raises(
pl.exceptions.ComputeError, match="infer_schema_length"
) as exc_info:
engine.execute(query)
assert isinstance(
exc_info.value.__cause__, pl.exceptions.SchemaError
)
# ------------------------
# Decoracotors
# ------------------------
def test_returns_function_result_on_success():
@safe_execute(fallback=None)
def add(a, b):
return a + b
assert add(1, 2) == 3
def test_returns_fallback_on_exception():
@safe_execute(fallback="oops")
def fail():
raise ValueError("boom")
assert fail() == "oops"
@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_logs_on_exception(mock_logger):
@safe_execute(
fallback=None, message="something broke", log_level="warning"
)
def fail():
raise TypeError("bad type")
fail()
mock_logger.warning.assert_called_once_with(
"something broke", exc_info=True
)
@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_respects_log_level_debug(mock_logger):
@safe_execute(fallback=None, message="debug msg", log_level="debug")
def fail():
raise RuntimeError
fail()
mock_logger.debug.assert_called_once_with("debug msg", exc_info=True)
@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_respects_log_level_error(mock_logger):
@safe_execute(fallback=None, message="error msg", log_level="error")
def fail():
raise RuntimeError
fail()
mock_logger.error.assert_called_once_with("error msg", exc_info=True)
@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_silent_exceptions_no_logging(mock_logger):
@safe_execute(
fallback=[],
silent_exceptions=(NotImplementedError,),
)
def fail():
raise NotImplementedError
result = fail()
assert result == []
mock_logger.warning.assert_not_called()
mock_logger.debug.assert_not_called()
mock_logger.error.assert_not_called()
mock_logger.info.assert_not_called()
@mock.patch("marimo._sql.engines.sqlalchemy.LOGGER")
def test_silent_exceptions_still_logs_other_errors(mock_logger):
@safe_execute(
fallback=-1,
message="caught",
log_level="error",
silent_exceptions=(NotImplementedError,),
)
def fail():
raise ValueError("not silent")
result = fail()
assert result == -1
mock_logger.error.assert_called_once_with("caught", exc_info=True)
def test_preserves_function_metadata():
@safe_execute(fallback=None)
def my_func():
"""My docstring."""
assert my_func.__name__ == "my_func"
assert my_func.__doc__ == "My docstring."
def test_passes_args_and_kwargs():
@safe_execute(fallback=None)
def greet(name, greeting="Hello"):
return f"{greeting}, {name}!"
assert greet("Alice") == "Hello, Alice!"
assert greet("Bob", greeting="Hi") == "Hi, Bob!"
def test_fallback_can_be_any_type():
for fallback in (0, False, "", [], {}):
@safe_execute(fallback=fallback)
def fail():
raise RuntimeError
assert fail() == fallback
# ------------------------
# Tests for inspector methods
# ------------------------
@pytest.fixture
def make_engine():
"""Factory to create a SQLAlchemyEngine with a mocked Snowflake inspector."""
def _factory(
*,
dialect="snowflake",
default_database="MY_DB",
schema_names=None,
table_names=None,
view_names=None,
table_columns=None,
columns=None,
rows=None,
pk_constraint=None,
indexes=None,
multi_columns=None,
multi_pk_constraint=None,
multi_indexes=None,
supports_multi=True,
inspector=True,
):
if columns is None:
columns = [
"created_on",
"name",
"is_default",
"is_current",
"origin",
]
if rows is None:
rows = []
# -- Mock query results --
mock_result = mock.MagicMock()
mock_result.keys.return_value = columns
mock_result.fetchall.return_value = rows
mock_conn = mock.MagicMock()
mock_conn.execute.return_value = mock_result
# -- Mock inspector --
mock_inspector = None
if inspector:
mock_inspector = mock.MagicMock()
mock_inspector.get_schema_names.return_value = schema_names or []
mock_inspector.get_table_names.return_value = table_names or []
mock_inspector.get_view_names.return_value = view_names or []
mock_inspector.get_columns.return_value = table_columns or []
mock_inspector.get_pk_constraint.return_value = pk_constraint or {
"constrained_columns": []
}
mock_inspector.get_indexes.return_value = indexes or []
if supports_multi:
# Empty dicts by default so tests exercise the per-table
# fallback instead of passing on bare MagicMock returns.
mock_inspector.get_multi_columns.return_value = (
multi_columns or {}
)
mock_inspector.get_multi_pk_constraint.return_value = (
multi_pk_constraint or {}
)
mock_inspector.get_multi_indexes.return_value = (
multi_indexes or {}
)
else:
# Simulate SQLAlchemy < 2.0, where hasattr(...) is False
del mock_inspector.get_multi_columns
del mock_inspector.get_multi_pk_constraint
del mock_inspector.get_multi_indexes
# -- Mock SQLAlchemy engine --
mock_sa_engine = mock.MagicMock()
mock_sa_engine.dialect.name = dialect
mock_sa_engine.url.database = default_database
mock_sa_engine.connect.return_value = mock.MagicMock(
__enter__=mock.MagicMock(return_value=mock_conn),
__exit__=mock.MagicMock(return_value=False),
)
# Patch inspect() so __init__ assigns our configured mock
with mock.patch("sqlalchemy.inspect", return_value=mock_inspector):
engine = SQLAlchemyEngine(
mock_sa_engine, engine_name=VariableName("test")
)
# Point _connection to the mock so execute() calls work
engine._connection = mock_sa_engine
# Override _get_inspector to yield our mock inspector.
# Uses *_args, **_kwargs to match any signature the real
# method may have (e.g. database parameter) without
# global side effects.
inspector_contexts = []
@contextmanager
def fake_get_inspector(database=None):
inspector_contexts.append(database)
yield mock_inspector
engine._get_inspector = fake_get_inspector
# Expose mocks for test assertions
engine._mock_inspector = mock_inspector
engine._mock_conn = mock_conn
engine._mock_inspector_contexts = inspector_contexts
return engine
return _factory
# ------------------------------------------------------------------ #
# _get_inspector
# ------------------------------------------------------------------ #
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_inspector_executes_use_database():
"""executes dialect command and inspects the connection."""
mock_conn = mock.MagicMock()
mock_command = mock.MagicMock()
mock_sa_engine = mock.MagicMock()
mock_sa_engine.dialect.name = "snowflake"
mock_sa_engine.url.database = "MY_DB"
mock_sa_engine.connect.return_value.__enter__ = mock.MagicMock(
return_value=mock_conn
)
mock_sa_engine.connect.return_value.__exit__ = mock.MagicMock(
return_value=False
)
engine = SQLAlchemyEngine(mock_sa_engine, engine_name=VariableName("test"))
with (
mock.patch(
"sqlalchemy.inspect", return_value=mock_command
) as patched_inspect,
engine._get_inspector("my_db") as inspector,
):
assert inspector is mock_command
patched_inspect.assert_called_once_with(mock_conn)
# Verify USE DATABASE was executed
executed = mock_conn.execute.call_args[0][0]
assert str(executed) == "USE DATABASE my_db"
with (
mock.patch(
"sqlalchemy.inspect", return_value=mock_command
) as patched_inspect,
engine._get_inspector("MY_DB@MYDB") as inspector,
):
assert inspector is mock_command
patched_inspect.assert_called_once_with(mock_conn)
# Verify USE DATABASE was executed
executed = mock_conn.execute.call_args[0][0]
assert str(executed) == 'USE DATABASE "MY_DB@MYDB"'
with (
mock.patch(
"sqlalchemy.inspect", return_value=mock_command
) as patched_inspect,
engine._get_inspector("MY_db") as inspector,
):
assert inspector is mock_command
patched_inspect.assert_called_once_with(mock_conn)
# Verify USE DATABASE was executed
executed = mock_conn.execute.call_args[0][0]
assert str(executed) == 'USE DATABASE "MY_db"'
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_inspector_yields_existing_inspector_for_non_use_database_dialect():
"""For dialects without USE DATABASE, yield self.inspector directly."""
mock_sa_engine = mock.MagicMock()
mock_sa_engine.dialect.name = "sqlite"
mock_sa_engine.url.database = ":memory:"
engine = SQLAlchemyEngine(mock_sa_engine, engine_name=VariableName("test"))
mock_inspector = mock.MagicMock()
engine.inspector = mock_inspector
with engine._get_inspector("any_database") as inspector:
assert inspector is mock_inspector
# Verify that NO connection was opened and no command was executed
mock_sa_engine.connect.assert_not_called()
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_tables_in_schema_reuses_connection():
import sqlalchemy as sa
mock_inspector = mock.MagicMock()
mock_inspector.get_table_names.return_value = ["users"]
mock_inspector.get_view_names.return_value = []
mock_inspector.get_multi_columns.return_value = {
("public", "users"): [{"name": "id", "type": sa.INTEGER()}]
}
mock_inspector.get_multi_pk_constraint.return_value = {
("public", "users"): {"constrained_columns": ["id"]}
}
mock_inspector.get_multi_indexes.return_value = {("public", "users"): []}
mock_connection = mock.MagicMock()
mock_sa_engine = mock.MagicMock()
mock_sa_engine.dialect.name = "snowflake"
mock_sa_engine.url.database = "MY_DB"
mock_sa_engine.connect.return_value.__enter__.return_value = (
mock_connection
)
with mock.patch("sqlalchemy.inspect", return_value=mock_inspector):
engine = SQLAlchemyEngine(
mock_sa_engine, engine_name=VariableName("test")
)
tables = engine.get_tables_in_schema(
schema="public",
database="MY_DB",
include_table_details=True,
)
assert [table.name for table in tables] == ["users"]
mock_sa_engine.connect.assert_called_once_with()
executed = mock_connection.execute.call_args[0][0]
assert str(executed) == 'USE DATABASE "MY_DB"'
# ------------------------------------------------------------------ #
# _get_schema_names
# ------------------------------------------------------------------ #
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_schema_names(make_engine):
engine = make_engine(schema_names=["public", "staging"])
assert engine._get_schema_names("MY_DB") == ["public", "staging"]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_schema_names_no_inspector(make_engine):
engine = make_engine(inspector=False)
assert engine._get_schema_names("MY_DB") == []
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_table_details_reuses_inspector_context(make_engine):
import sqlalchemy as sa
engine = make_engine(
table_columns=[{"name": "id", "type": sa.INTEGER()}],
pk_constraint={"constrained_columns": ["id"]},
)
table = engine.get_table_details(
table_name="users",
schema_name="public",
database_name="MY_DB",
)
assert table is not None
assert table.primary_keys == ["id"]
assert len(engine._mock_inspector_contexts) == 1
# ------------------------------------------------------------------ #
# _get_batched_table_details
# ------------------------------------------------------------------ #
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_uses_multi_reflection(make_engine):
"""Details come from get_multi_* calls on a single inspector."""
import sqlalchemy as sa
from sqlalchemy.engine.reflection import ObjectKind, ObjectScope
engine = make_engine(
table_names=["users"],
view_names=["active_users"],
multi_columns={
("public", "users"): [{"name": "id", "type": sa.INTEGER()}],
("public", "active_users"): [{"name": "id", "type": sa.INTEGER()}],
},
multi_pk_constraint={
("public", "users"): {"constrained_columns": ["id"]}
},
multi_indexes={
("public", "users"): [{"column_names": ["id"], "name": "idx"}]
},
)
tables = engine.get_tables_in_schema(
schema="public", database="MY_DB", include_table_details=True
)
assert [(t.name, t.type) for t in tables] == [
("users", "table"),
("active_users", "view"),
]
assert tables[0].primary_keys == ["id"]
assert tables[0].indexes == ["id"]
# Missing PK/index entries fall back to [] like per-table reflection
assert tables[1].primary_keys == []
assert tables[1].indexes == []
engine._mock_inspector.get_multi_columns.assert_called_once_with(
schema="public",
filter_names=["users", "active_users"],
kind=ObjectKind.ANY,
scope=ObjectScope.ANY,
)
for multi_method in (
engine._mock_inspector.get_multi_pk_constraint,
engine._mock_inspector.get_multi_indexes,
):
multi_method.assert_called_once_with(
schema="public",
filter_names=["users"],
kind=ObjectKind.ANY,
scope=ObjectScope.ANY,
)
engine._mock_inspector.get_columns.assert_not_called()
engine._mock_inspector.get_pk_constraint.assert_not_called()
engine._mock_inspector.get_indexes.assert_not_called()
assert len(engine._mock_inspector_contexts) == 1
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_without_multi_support(make_engine):
"""Inspectors without get_multi_* fall back per table, silently."""
import sqlalchemy as sa
engine = make_engine(
table_names=["users"],
table_columns=[{"name": "id", "type": sa.INTEGER()}],
pk_constraint={"constrained_columns": ["id"]},
supports_multi=False,
)
with mock.patch("marimo._sql.engines.sqlalchemy.LOGGER") as mock_logger:
tables = engine.get_tables_in_schema(
schema="public", database="MY_DB", include_table_details=True
)
assert [(t.name, t.type) for t in tables] == [("users", "table")]
assert tables[0].primary_keys == ["id"]
engine._mock_inspector.get_columns.assert_called_once_with(
"users", schema="public"
)
mock_logger.warning.assert_not_called()
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_isolates_metadata_failures(make_engine):
"""Bulk metadata failures reuse batched columns and the same inspector."""
import sqlalchemy as sa
engine = make_engine(
table_names=["users"],
view_names=["active_users"],
multi_columns={
("public", "users"): [{"name": "id", "type": sa.INTEGER()}],
("public", "active_users"): [{"name": "id", "type": sa.INTEGER()}],
},
pk_constraint={"constrained_columns": ["id"]},
indexes=[{"column_names": ["id"], "name": "idx"}],
)
engine._mock_inspector.get_multi_pk_constraint.side_effect = RuntimeError
engine._mock_inspector.get_multi_indexes.side_effect = RuntimeError
tables = engine.get_tables_in_schema(
schema="public", database="MY_DB", include_table_details=True
)
assert [(table.name, table.type) for table in tables] == [
("users", "table"),
("active_users", "view"),
]
assert tables[0].primary_keys == ["id"]
assert tables[0].indexes == ["id"]
assert tables[1].primary_keys == []
assert tables[1].indexes == []
engine._mock_inspector.get_columns.assert_not_called()
engine._mock_inspector.get_pk_constraint.assert_called_once_with(
"users", schema="public"
)
engine._mock_inspector.get_indexes.assert_called_once_with(
"users", schema="public"
)
assert len(engine._mock_inspector_contexts) == 1
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_batched_details_straggler_falls_back_per_table(make_engine):
"""A table missing from the batch is fetched per table."""
import sqlalchemy as sa
engine = make_engine(
table_names=["users", "orders"],
table_columns=[{"name": "id", "type": sa.INTEGER()}],
multi_columns={
("public", "users"): [{"name": "id", "type": sa.INTEGER()}]
},
)
tables = engine.get_tables_in_schema(
schema="public", database="MY_DB", include_table_details=True
)
assert [t.name for t in tables] == ["users", "orders"]
engine._mock_inspector.get_columns.assert_called_once_with(
"orders", schema="public"
)
assert len(engine._mock_inspector_contexts) == 1
# ------------------------------------------------------------------ #
# _index_by_table_name (module-level, no mocking needed)
# ------------------------------------------------------------------ #
def test_index_by_table_name():
reflected = {
("main", "users"): [{"name": "id"}],
(None, "orders"): [{"name": "total"}],
}
assert _index_by_table_name(reflected) == {
"users": [{"name": "id"}],
"orders": [{"name": "total"}],
}
def test_index_by_table_name_empty():
assert _index_by_table_name({}) == {}
# ------------------------------------------------------------------ #
# _extract_index_columns (static, no mocking needed)
# ------------------------------------------------------------------ #
def test_extract_index_columns_deduplicates():
indexes = [
{"column_names": ["col_a", "col_b"]},
{"column_names": ["col_b", "col_c"]},
]
assert SQLAlchemyEngine._extract_index_columns(indexes) == [
"col_a",
"col_b",
"col_c",
]
def test_extract_index_columns_skips_none():
indexes = [{"column_names": [None, "col_a", None]}]
assert SQLAlchemyEngine._extract_index_columns(indexes) == ["col_a"]
def test_extract_index_columns_missing_key():
indexes = [{"name": "idx"}, {"column_names": ["col_a"]}]
assert SQLAlchemyEngine._extract_index_columns(indexes) == ["col_a"]
def test_extract_index_columns_empty():
assert SQLAlchemyEngine._extract_index_columns([]) == []
# ---------------------------------
# Snowflake-specific GET databases
# ---------------------------------
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_all_in_lower_case_when_no_default(make_engine):
engine = make_engine(
default_database=None,
rows=[
("2025-01-01", "DB_A", "N", "N", ""),
("2025-01-02", "DB_B", "N", "N", ""),
],
)
assert engine._get_snowflake_database_names() == ["db_a", "db_b"]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_only_default_when_present(make_engine):
engine = make_engine(
default_database="db_B",
rows=[
("2025-01-01", "DB_A", "N", "N", ""),
("2025-01-02", "DB_B", "N", "Y", ""),
],
)
assert engine._get_snowflake_database_names() == ["db_b"]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_get_snowflake_database_names_lowercases_unless_special_characters(
make_engine,
):
engine = make_engine(
default_database=None,
rows=[
("2025-01-01", "DB_A", "N", "N", ""),
("2025-01-02", "DB_B", "N", "Y", ""),
("2025-01-02", "USER@snow", "N", "Y", ""),
],
)
assert engine._get_snowflake_database_names() == [
"db_a",
"db_b",
"USER@snow",
]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_all_when_default_not_in_results(make_engine):
engine = make_engine(
default_database="MISSING",
rows=[
("2025-01-01", "DB_A", "N", "N", ""),
("2025-01-02", "DB_B", "N", "Y", ""),
],
)
assert engine._get_snowflake_database_names() == ["db_a", "db_b"]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_returns_empty_list_when_no_rows(make_engine):
engine = make_engine(rows=[])
assert engine._get_snowflake_database_names() == []
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_raises_when_name_column_missing(make_engine):
engine = make_engine(columns=["id", "owner", "comment"])
with pytest.raises(RuntimeError, match="'name' column not found"):
engine._get_snowflake_database_names()
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_executes_show_databases(make_engine):
engine = make_engine()
engine._get_snowflake_database_names()
executed_sql = engine._mock_conn.execute.call_args[0][0]
assert str(executed_sql) == "SHOW DATABASES"
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_name_column_at_different_index(make_engine):
engine = make_engine(
columns=["id", "owner", "name"],
rows=[(1, "ADMIN", "MY_DB")],
)
assert engine._get_snowflake_database_names() == ["my_db"]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_database_names_coerced_to_str(make_engine):
engine = make_engine(columns=["name"], rows=[(123,), (456,)])
assert engine._get_snowflake_database_names() == ["123", "456"]
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_get_database_names_delegates(make_engine):
"""Snowflake dialect delegates to _get_snowflake_database_names."""
engine = make_engine(default_database="MY_DB")
expected = ["DB_A", "DB_B"]
with mock.patch.object(
engine, "_get_snowflake_database_names", return_value=expected
) as mocked:
result = engine._get_database_names()
mocked.assert_called_once()
assert result == expected
@pytest.mark.skipif(not HAS_SQLALCHEMY, reason="SQLAlchemy not installed")
def test_snowflake_default_database_queried_when_not_in_url(make_engine):
"""Snowflake falls back to querying the current database when the
connection URL has no database (e.g. engines built with `creator=`)."""
engine = make_engine(default_database=None)
engine._mock_conn.execute.return_value.fetchone.return_value = ("MY_DB",)
assert engine.get_default_database() == "MY_DB"
executed_sql = engine._mock_conn.execute.call_args[0][0]
assert str(executed_sql) == "SELECT current_database()"