Error registering a new weather sensor when location already used by another asset: sqlalchemy.exc.MultipleResultsFound: Multiple rows were found when one or none was required

[beralt85]

Description

Adding a sensor with coordinates (latitude, longitude) of an existing asset throws an SQAlchemy Error:
sqlalchemy.exc.MultipleResultsFound: Multiple rows were found when one or none was required

Details

An asset with latitude=52.374 and longitude=4.88969 exists in the toy-account from the tutorial.
Adding an owm/weather sensor with exactly the same coordinates throws the error.

flexmeasures owm register-weather-sensor --name "wind speed" --latitude 52.374 --longitude 4.88969
[FLEXMEASURES][2025-06-03 18:29:01,098] INFO: Loading config from /usr/var/flexmeasures-instance/flexmeasures.cfg ...
Warning: Without complete mail settings, FlexMeasures will not be able to send mails to users, e.g. for password resets!
You might consider setting MAIL_USERNAME, MAIL_PASSWORD.
[FLEXMEASURES][2025-06-03 18:29:01,098] INFO: [FLEXMEASURES] No SENTRY_DSN setting found, so initialising Sentry cannot happen ...
[FLEXMEASURES][2025-06-03 18:29:02,730] INFO: Importing plugin flexmeasures-entsoe ...
[FLEXMEASURES][2025-06-03 18:29:02,825] INFO: Importing plugin flexmeasures_openweathermap ...
[FLEXMEASURES][2025-06-03 18:29:02,898] INFO: Loaded plugins: {'flexmeasures-entsoe': '0.8', 'flexmeasures_openweathermap': '0.1'}
Traceback (most recent call last):
  File "/usr/local/bin/flexmeasures", line 8, in <module>
    sys.exit(flexmeasures_cli())
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1161, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1082, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1697, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1697, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 1443, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 788, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/decorators.py", line 33, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/flask/cli.py", line 400, in decorator
    return ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 788, in invoke
    return __callback(*args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/decorators.py", line 33, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/flask/cli.py", line 400, in decorator
    return ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/click/core.py", line 788, in invoke
    return __callback(*args, **kwargs)
  File "/app/instance/flexmeasures-openweathermap/flexmeasures_openweathermap/cli/commands.py", line 80, in add_weather_sensor
    weather_station = get_or_create_weather_station(
  File "/app/instance/flexmeasures-openweathermap/flexmeasures_openweathermap/utils/modeling.py", line 63, in get_or_create_weather_station
    ).one_or_none()
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/orm/query.py", line 2785, in one_or_none
    return self._iter().one_or_none()  # type: ignore
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 1796, in one_or_none
    return self._only_one_row(
  File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 805, in _only_one_row
    raise exc.MultipleResultsFound(
sqlalchemy.exc.MultipleResultsFound: Multiple rows were found when one or none was required

How to reproduce

• Use the tutorial toy-account
• Install the weather plugin
• Create new weather sensor with latitude=52.374 and longitude=4.88969

Desired behaviour

There should be no problem with creating this asset.
Even better and easier would be an option to the argument like --location-from-asset-id <n> where one can create the sensor for an existing asset with a location (like a building).
Or, alternatively, have the weather station potentially as a child asset.

[nhoening]

A quick fix might be to extend this line, such that we do not only search by location, but also make sure we filter by asset type (GenericAssetType.name == WEATHER_STATION_TYPE_NAME) - the work probably is to figure out the SQLAlchemy syntax.

PR welcome. Also one for your idea with --location-from-asset-id <n>.

Workaround for now is to use a slightly different latitude and one can move on, also.

[beralt85]

I don't really feel comfortable with creating a PR at this stage, as I am still very much learning about most parts of the system.
But I can always help with testing/bug reporting.