async def async_setup_entry(
hass: HomeAssistant, entry: ConfigEntry, async_add_entities: AddEntitiesCallback
) -> None:
"""Set up the SQL sensor entry."""
db_url: str = entry.options[CONF_DB_URL]
name: str = entry.options[CONF_NAME]
query_str: str = entry.options[CONF_QUERY]
unit: str | None = entry.options.get(CONF_UNIT_OF_MEASUREMENT)
template: str | None = entry.options.get(CONF_VALUE_TEMPLATE)
column_name: str = entry.options[CONF_COLUMN_NAME]
value_template: Template | None = None
if template is not None:
try:
value_template = Template(template)
value_template.ensure_valid()
except TemplateError:
value_template = None
if value_template is not None:
value_template.hass = hass
try:
engine = sqlalchemy.create_engine(db_url, future=True)
sessmaker = scoped_session(sessionmaker(bind=engine, future=True))
except SQLAlchemyError as err:
_LOGGER.error("Can not open database %s", {redact_credentials(str(err))})
return
# MSSQL uses TOP and not LIMIT
if not ("LIMIT" in query_str.upper() or "SELECT TOP" in query_str.upper()):
if "mssql" in db_url:
query_str = query_str.upper().replace("SELECT", "SELECT TOP 1")
else:
query_str = query_str.replace(";", "") + " LIMIT 1;"
async_add_entities(
[
SQLSensor(
name,
sessmaker,
query_str,
column_name,
unit,
value_template,
entry.entry_id,
)
],
True,
)
def _render_template(self, filename, variables):
try:
template = None
with open(filename, encoding="utf-8") as file:
template = Template(file.read(), self.hass)
template.ensure_valid()
rendered = template.render(
variables={
**variables, "_global": self.config.get("variables", {})
},
limited=True,
)
stream = io.StringIO(rendered)
stream.name = filename
return stream
except (FileNotFoundError, UnicodeDecodeError) as ex:
_LOGGER.error("Unable to read file %s: %s", filename, ex)
raise HomeAssistantError(ex) from ex
except TemplateError as ex:
_LOGGER.error("Unable to render file %s: %s", filename, ex)
raise HomeAssistantError(ex) from ex
