need help fixing problem in database: can't complete a --rebuild-daily

[Eric K]

I've been running weewx 4.5.1 in a RaspberryPi for the last 2 years.

In the past, I have successfully used DB Browser for SQLite to fix erronous readings created by my system sensor behavior.

In the last 30 days, I edited some erronous wind speed values and now the "sudo bin/wee_database weewx.conf --rebuild-daily" command can't complete without errors.

The error messages suggest that some of my values are text strings rather than floating point numbers.

When I browse the weewx.sdb database with DB Broswer for SQLite, I can't see any difference between data fields with numbers and strings.

I read the page on cleaning up the database
https://github.com/weewx/weewx/wiki/Cleaning-up-old-'bad'-data
but there are no commands listed to help detect and convert text strings into floating point numbers.

How can I detect the difference between text strings and floating point numbers in the database?

Here is the feedback I get:

pi@rpi3b:/home/weewx $ sudo bin/wee_database weewx.conf --rebuild-daily
Using configuration file weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_sqlite'
All daily summaries will be rebuilt.
Proceed (y/n)? y
Rebuilding daily summaries in database 'weewx.sdb' ...
Traceback (most recent call last):023-06-02 07:45:00 CDT (1685709900)
  File "/home/weewx/bin/wee_database", line 1138, in <module>
    main()
  File "/home/weewx/bin/wee_database", line 236, in main
    rebuildDaily(config_dict, db_binding, options)
  File "/home/weewx/bin/wee_database", line 345, in rebuildDaily
    nrecs, ndays = dbmanager.backfill_day_summary(start_d=from_d,
  File "/home/weewx/bin/weewx/manager.py", line 1152, in backfill_day_summary
    day_accum.addRecord(rec, weight=weight)
  File "/home/weewx/bin/weewx/accum.py", line 436, in addRecord
    func(self, record, obs_type, add_hilo, weight)
  File "/home/weewx/bin/weewx/accum.py", line 495, in add_value
    self[obs_type].addHiLo(val, record['dateTime'])
  File "/home/weewx/bin/weewx/accum.py", line 168, in addHiLo
    val = to_float(val)
  File "/home/weewx/bin/weeutil/weeutil.py", line 1276, in to_float
    return float(x) if x is not None else None
ValueError: could not convert string to float: ''

[Eric K]

I was just looking through the documentation on wee_database.

http://weewx.com/docs/latest/utilities.htm#wee_database_utility

Can any of the wee_database options be used to search the database for text strings and convert them into floating point numbers for me?

Options: -h, --help show this help message and exit --create Create the WeeWX database and initialize it with the schema. --reconfigure Create a new database using configuration information found in the configuration file. The new database will have the same name as the old database, with a '_new' on the end. --transfer Transfer the WeeWX archive from source database to destination database. --add-column=NAME Add new column NAME to database. --type=TYPE New database column type (INTEGER|REAL) (option --add- column only). Default is 'REAL'. --rename-column=NAME Rename the column with name NAME. --to-name=NEW_NAME New name of the column (option --rename-column only). --drop-columns=NAME1,NAME2,... Drop one or more columns. Names must be separated by commas, with NO SPACES. --check Check the calculations in the daily summary tables. --update Update the daily summary tables if required and recalculate the daily summary maximum windSpeed values. --calc-missing Calculate and store any missing derived observations. --check-strings Check the archive table for null strings that may have been introduced by a SQL editing program. --fix-strings Fix any null strings in a SQLite database. --drop-daily Drop the daily summary tables from a database. --rebuild-daily Rebuild the daily summaries from data in the archive table. --reweight Recalculate the weighted sums in the daily summaries. --config=CONFIG_FILE Use configuration file CONFIG_FILE. --date=YYYY-mm-dd This date only (options --calc-missing and --rebuild- daily only). --from=YYYY-mm-dd[THH:MM] Start with this date or date-time (options --calc- missing and --rebuild-daily only). --to=YYYY-mm-dd[THH:MM] End with this date or date-time (options --calc- missing and --rebuild-daily only). --binding=BINDING_NAME The data binding to use. Default is 'wx_binding'. --dest-binding=BINDING_NAME The destination data binding (option --transfer only). --dry-run Print what would happen but do not do it. Default is False.

[Eric K]

--fix-strings did it!

The database is fixed and --rebuild-daily works without errors!

pi@rpi3b:/home/weewx $ sudo bin/wee_database weewx.conf --fix-strings

Using configuration file weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_sqlite'

Preparing Null String Fix, this may take a while...
 Checking record: 208266; Timestamp: 2023-06-23 14:00:00 CDT (1687546800)
The following null strings were found:
Timestamp = 1685826300; record['windGustDir'] = ''; ... changed to None
Timestamp = 1685832600; record['outTempBatteryStatus'] = ''; ... changed to None
Timestamp = 1685832600; record['windGustDir'] = ''; ... changed to None
Timestamp = 1685832900; record['outTempBatteryStatus'] = ''; ... changed to None
4 of 4 null strings found were fixed.
Applied Null String Fix in 43.59 seconds.