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