async def async_setup_platform(
hass: HomeAssistant,
config: ConfigType,
async_add_entities: AddEntitiesCallback,
discovery_info: DiscoveryInfoType | None = None,
) -> None:
"""Set up the SQL sensor platform."""
_LOGGER.warning(
# SQL config flow added in 2022.4 and should be removed in 2022.6
"Configuration of the SQL sensor platform in YAML is deprecated and "
"will be removed in Home Assistant 2022.6; Your existing configuration "
"has been imported into the UI automatically and can be safely removed "
"from your configuration.yaml file"
)
default_db_url = DEFAULT_URL.format(
hass_config_path=hass.config.path(DEFAULT_DB_FILE)
)
for query in config[CONF_QUERIES]:
new_config = {
CONF_DB_URL: config.get(CONF_DB_URL, default_db_url),
CONF_NAME: query[CONF_NAME],
CONF_QUERY: query[CONF_QUERY],
CONF_UNIT_OF_MEASUREMENT: query.get(CONF_UNIT_OF_MEASUREMENT),
CONF_VALUE_TEMPLATE: query.get(CONF_VALUE_TEMPLATE),
CONF_COLUMN_NAME: query[CONF_COLUMN_NAME],
}
hass.async_create_task(
hass.config_entries.flow.async_init(
DOMAIN,
context={"source": SOURCE_IMPORT},
data=new_config,
)
)
def setup_platform(
hass: HomeAssistant,
config: ConfigType,
add_entities: AddEntitiesCallback,
discovery_info: DiscoveryInfoType | None = None,
) -> None:
"""Set up the SQL sensor platform."""
if not (db_url := config.get(CONF_DB_URL)):
db_url = DEFAULT_URL.format(hass_config_path=hass.config.path(DEFAULT_DB_FILE))
def __init__(self, hass, db_url=None):
try:
if not db_url:
db_url = DEFAULT_URL.format(hass_config_path=hass.config.path(DEFAULT_DB_FILE))
engine = sqlalchemy.create_engine(db_url)
self.sessmaker = scoped_session(sessionmaker(bind=engine))
# 初始化表
self.init_db()
except sqlalchemy.exc.SQLAlchemyError as err:
_LOGGER.error("Couldn't connect using %s DB_URL: %s", db_url, err)
def setup_platform(hass, config, add_entities, discovery_info=None):
"""Set up the SQL sensor platform."""
db_url = config.get(CONF_DB_URL)
if not db_url:
db_url = DEFAULT_URL.format(hass_config_path=hass.config.path(DEFAULT_DB_FILE))
sess = None
try:
engine = sqlalchemy.create_engine(db_url)
sessmaker = scoped_session(sessionmaker(bind=engine))
# Run a dummy query just to test the db_url
sess = sessmaker()
sess.execute("SELECT 1;")
except sqlalchemy.exc.SQLAlchemyError as err:
_LOGGER.error(
"Couldn't connect using %s DB_URL: %s",
redact_credentials(db_url),
redact_credentials(str(err)),
)
return
finally:
if sess:
sess.close()
queries = []
for query in config.get(CONF_QUERIES):
name = query.get(CONF_NAME)
query_str = query.get(CONF_QUERY)
unit = query.get(CONF_UNIT_OF_MEASUREMENT)
value_template = query.get(CONF_VALUE_TEMPLATE)
column_name = query.get(CONF_COLUMN_NAME)
if value_template is not None:
value_template.hass = hass
# MSSQL uses TOP and not LIMIT
if not ("LIMIT" in query_str or "SELECT TOP" in query_str):
query_str = (
query_str.replace("SELECT", "SELECT TOP 1")
if "mssql" in db_url
else query_str.replace(";", " LIMIT 1;")
)
sensor = SQLSensor(
name, sessmaker, query_str, column_name, unit, value_template
)
queries.append(sensor)
add_entities(queries, True)
async def async_step_user(
self, user_input: dict[str, Any] | None = None
) -> FlowResult:
"""Handle the user step."""
errors = {}
db_url_default = DEFAULT_URL.format(
hass_config_path=self.hass.config.path(DEFAULT_DB_FILE)
)
if user_input is not None:
db_url = user_input.get(CONF_DB_URL, db_url_default)
query = user_input[CONF_QUERY]
column = user_input[CONF_COLUMN_NAME]
uom = user_input.get(CONF_UNIT_OF_MEASUREMENT)
value_template = user_input.get(CONF_VALUE_TEMPLATE)
name = user_input[CONF_NAME]
try:
validate_sql_select(query)
await self.hass.async_add_executor_job(
validate_query, db_url, query, column
)
except SQLAlchemyError:
errors["db_url"] = "db_url_invalid"
except ValueError:
errors["query"] = "query_invalid"
if not errors:
return self.async_create_entry(
title=name,
data={},
options={
CONF_DB_URL: db_url,
CONF_QUERY: query,
CONF_COLUMN_NAME: column,
CONF_UNIT_OF_MEASUREMENT: uom,
CONF_VALUE_TEMPLATE: value_template,
CONF_NAME: name,
},
)
return self.async_show_form(
step_id="user",
data_schema=DATA_SCHEMA,
errors=errors,
)
def setup_platform(hass, config, add_devices, discovery_info=None):
"""Setup the sensor platform."""
db_url = config.get(CONF_DB_URL, None)
if not db_url:
db_url = DEFAULT_URL.format(
hass_config_path=hass.config.path(DEFAULT_DB_FILE))
import sqlalchemy
from sqlalchemy.orm import sessionmaker, scoped_session
try:
engine = sqlalchemy.create_engine(db_url)
sessionmaker = scoped_session(sessionmaker(bind=engine))
# run a dummy query just to test the db_url
sess = sessionmaker()
sess.execute("SELECT 1;")
except sqlalchemy.exc.SQLAlchemyError as err:
_LOGGER.error("Couldn't connect using %s DB_URL: %s", db_url, err)
return
queries = []
for query in config.get(CONF_QUERIES):
name = query.get(CONF_NAME)
query_str = query.get(CONF_QUERY)
unit = query.get(CONF_UNIT_OF_MEASUREMENT)
value_template = query.get(CONF_VALUE_TEMPLATE)
column_name = query.get(CONF_COLUMN_NAME)
if value_template is not None:
value_template.hass = hass
sensor = SQLSensor(
name, sessionmaker, query_str, column_name, unit, value_template
)
queries.append(sensor)
add_devices(queries, True)
def setup_platform(hass, config, add_devices, discovery_info=None):
"""Setup the sensor platform."""
db_url = config.get(CONF_DB_URL, None)
if not db_url:
db_url = DEFAULT_URL.format(
hass_config_path=hass.config.path(DEFAULT_DB_FILE))
import sqlalchemy
from sqlalchemy.orm import sessionmaker, scoped_session
try:
engine = sqlalchemy.create_engine(db_url)
sessionmaker = scoped_session(sessionmaker(bind=engine))
# run a dummy query just to test the db_url
sess = sessionmaker()
sess.execute("SELECT 1;")
except sqlalchemy.exc.SQLAlchemyError as err:
_LOGGER.error("Couldn't connect using %s DB_URL: %s", db_url, err)
return
queries = []
for query in config.get(CONF_QUERIES):
name = query.get(CONF_NAME)
query_str = query.get(CONF_QUERY)
unit = query.get(CONF_UNIT_OF_MEASUREMENT)
value_template = query.get(CONF_VALUE_TEMPLATE)
column_name = query.get(CONF_COLUMN_NAME)
if value_template is not None:
value_template.hass = hass
sensor = SQLSensor(
name, sessionmaker, query_str, column_name, unit, value_template
)
queries.append(sensor)
add_devices(queries, True)
def setup_platform(hass, config, add_entities, discovery_info=None):
"""Set up the SQL sensor platform."""
if not (db_url := config.get(CONF_DB_URL)):
db_url = DEFAULT_URL.format(hass_config_path=hass.config.path(DEFAULT_DB_FILE))