Belchertown Skin version: 0.9rc6 crashed when used with non-standard database name

[Luc Heijst]

Hi Pat,

I installed Belchertown Skin Version: 0.9rc6 from scratch (no update) in weewx 3.9.1.

At first the generation of the skin crashed with the following error:

Feb 17 12:12:25 pi21 vpro[9234]: cheetahgenerator: Generated 4 files for report Highcharts_Belchertown in 122.54 seconds

Feb 17 12:12:25 pi21 vpro[9234]: copygenerator: copied 0 files to /var/www/html/vpro/data/weewx/belchertown

Feb 17 12:12:25 pi21 vpro[9234]: reportengine: Running report 'Belchertown'

Feb 17 12:12:25 pi21 vpro[9234]: reportengine: Found configuration file /home/weewx/skins/Belchertown/skin.conf for report 'Belchertown'

Feb 17 12:12:25 pi21 vpro[9234]: cheetahgenerator: using search list ['weewx.cheetahgenerator.Almanac', 'weewx.cheetahgenerator.Station', 'weewx.cheetahgenerator.Current', 'weewx.cheetahgenerator.Stats', 'weewx.cheetahgenerator.UnitInfo', 'weewx.cheetahgenerator.Extras', 'user.belchertown.getData']

Feb 17 12:12:25 pi21 vpro[9234]: manager: Daily summary version is 2.0

Feb 17 12:12:25 pi21 vpro[9234]: Belchertown Extension: version 0.9rc6

Feb 17 12:12:26 pi21 vpro[9234]: reportengine: Caught unrecoverable exception in generator 'weewx.cheetahgenerator.CheetahGenerator'

Feb 17 12:12:26 pi21 vpro[9234]:         ****  local variable 'year_rainiest_month_sql' referenced before assignment

Feb 17 12:12:26 pi21 vpro[9234]:         ****  Traceback (most recent call last):

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 204, in run

Feb 17 12:12:26 pi21 vpro[9234]:         ****      obj.start()

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 300, in start

Feb 17 12:12:26 pi21 vpro[9234]:         ****      self.run()

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/weewx/cheetahgenerator.py", line 160, in run

Feb 17 12:12:26 pi21 vpro[9234]:         ****      ngen = self.generate(gen_dict[section_name], self.gen_ts)

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/weewx/cheetahgenerator.py", line 234, in generate

Feb 17 12:12:26 pi21 vpro[9234]:         ****      ngen += self.generate(section[subsection], gen_ts)

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/weewx/cheetahgenerator.py", line 234, in generate

Feb 17 12:12:26 pi21 vpro[9234]:         ****      ngen += self.generate(section[subsection], gen_ts)

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/weewx/cheetahgenerator.py", line 322, in generate

Feb 17 12:12:26 pi21 vpro[9234]:         ****      default_binding)

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/weewx/cheetahgenerator.py", line 374, in _getSearchList

Feb 17 12:12:26 pi21 vpro[9234]:         ****      searchList += obj.get_extension_list(timespan, db_lookup)

Feb 17 12:12:26 pi21 vpro[9234]:         ****    File "/home/weewx/bin/user/belchertown.py", line 228, in get_extension_list

Feb 17 12:12:26 pi21 vpro[9234]:         ****      year_rainiest_month_query = wx_manager.getSql( year_rainiest_month_sql )

Feb 17 12:12:26 pi21 vpro[9234]:         ****  UnboundLocalError: local variable 'year_rainiest_month_sql' referenced before assignment

Feb 17 12:12:26 pi21 vpro[9234]:         ****  Generator terminated

This is a result of my non-standard weewx configuration.

Differences:

- startup file is weewx_vpro.conf instead of weewx.conf

- [StdArchive] data_binding = vpro_binding instead of wx_binding

    [[vpro_binding]]

        database = vpro_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = schemas.wview.schema

Because of the non-standard binding some parts of belchertown.py are not executed.

- line 212 in belchertown.py should be (in my case):

         if dbtype == "vpro_sqlite":

  instead of:

         if dbtype == "weewx_sqlite":

- line 219 in belchertown.py should be (in my case):

         if dbtype == "vpro_mysql":

  instead of:

         if dbtype == "weewx_mysql":

After I have changed lines 212 and 219 in belchertow.py the skin was generated succesfully.

Cheers, Luc

[Pat]

Luc, interesting update. Thanks for the information. I don't think non-standard database types are common? The script is looking for archive_sqlite or archive_mysql - which so far has covered everyone.

I don't know what vpro_mysql is. If it's mysql, why don't you use the archive_mysql binding? Is this how you're able to have multiple databases per weewx instance?

[Luc Heijst]

Hi Pat,

My Raspberry PI2B system is a busy one: it runs via BerryBoot four weewx instances on a Synolofy NAS and writes the data to six different external MariaDB 10 databases:

It has drivers for Vantage, Meteostick, KlimaLogg and tfrec and numerous skins.

weewx_vpro

bindings:

    [[vpro_binding]]

        database = vpro_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = schemas.wview.schema

    [[mstk_binding]]

        database = mstk_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = schemas.wview.schema

    [[klim_binding]]

        database = klim_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = user.klschema.schema

    [[forecast_binding]]

        database = forecast_mysql    #lh forecast_sqlite

    [[cmon_binding]]

        database = cmon_mysql

        table_name = archive

        manager = weewx.manager.DaySummaryManager

        schema = user.cmon.schema

weewx_mstk

    [[mstk_binding]]

        database = mstk_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = schemas.wview.schema

weewx_klim

    [[klim_binding]]

        database = klim_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = user.klschema.schema

weewx_mben

    [[mben_binding]]

        database = archive_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = user.modbusenergy.schema

To keep track of everything I try to avoid the standard wx-binding and database naming

Luc

[Luc Heijst]

Sorry,

The PI2B runs the weewx_mben (modbus energy driver) and not the tfrec driver.

Both weewx_mben and weewx_tfrec are new drivers developed by me.

Luc

[Pat]

Interesting! That is a busy Pi. I'm not sure how I'd be able to support your custom integrations with the few custom sql queries I have, so I think I'll leave it alone for now unless you have insight on that? So far I think you're the only one using the skin with such an elaborate setup :)

[Luc Heijst]

Hi Pat,

My weewx config file says:

[StdArchive]

    data_binding = vpro_binding

[DataBindings]

    [[vpro_binding]]

        database = vpro_mysql

        table_name = archive

        manager = weewx.wxmanager.WXDaySummaryManager

        schema = schemas.wview.schema

[Databases]

    [[vpro_mysql]]

        database_type = MySQL

        database_name = weewx_vpro

[DatabaseTypes]

    [[MySQL]]

        driver = weedb.mysql

        host = 192.168.0.14

        port = 3307  # 3306=mariadb 5, 3307=mariadb 10

        user = xxxx

        password = xxxx

In my oppinion with the proper commands is is doable to detect a generic database name and type.

Cheers, Luc

[Luc Heijst]

On Sunday, 17 February 2019 15:48:47 UTC-3, Pat wrote:

Interesting! That is a busy Pi. I'm not sure how I'd be able to support your custom integrations with the few custom sql queries I have, so I think I'll leave it alone for now unless you have insight on that? So far I think you're the only one using the skin with such an elaborate setup :)

It's ok to leave it for now, Pat. I can patch future releases of belchertown.py myself.

Luc

[Luc Heijst]

On Sunday, 17 February 2019 15:48:47 UTC-3, Pat wrote:

 I think I'll leave it alone for now unless you have insight on that? 

Hi Pat,

I have a working solution.

In Belchertown.py

Change:

        # Find what kind of database we're working with and specify the correctly tailored SQL Query for each type of database

        dbtype = self.generator.config_dict['DataBindings']['wx_binding']['database']

        if dbtype == "archive_sqlite":

            year_rainiest_month_sql = 'SELECT strftime("%%m", datetime(dateTime, "unixepoch")) as month, ROUND( SUM( sum ), 2 ) as total FROM archive_day_rain WHERE strftime("%%Y", datetime(dateTime, "unixepoch")) = "%s" GROUP BY month ORDER BY total DESC LIMIT 1;' % time.strftime( "%Y", time.localtime( time.time() ) )

            at_rainiest_month_sql = 'SELECT strftime("%m", datetime(dateTime, "unixepoch")) as month, strftime("%Y", datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total FROM archive_day_rain GROUP BY month, year ORDER BY total DESC LIMIT 1;'

            year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain WHERE strftime("%%Y", datetime(dateTime, "unixepoch")) = "%s";' % time.strftime( "%Y", time.localtime( time.time() ) )

            at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain;'

            # The all stats from http://www.weewx.com/docs/customizing.htm doesn't seem to calculate "Total Rainfall for" all time stat correctly. 

            at_rain_highest_year_sql = 'SELECT strftime("%Y", datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total FROM archive_day_rain GROUP BY year ORDER BY total DESC LIMIT 1;'

        elif dbtype == "archive_mysql":

            year_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%m" ) AS month, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain WHERE year( FROM_UNIXTIME( dateTime ) ) = "{0}" GROUP BY month ORDER BY total DESC LIMIT 1;'.format( time.strftime( "%Y", time.localtime( time.time() ) ) ) # Why does this one require .format() but the other's don't?

            at_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%m" ) AS month, FROM_UNIXTIME( dateTime, "%%Y" ) AS year, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain GROUP BY month, year ORDER BY total DESC LIMIT 1;'

            year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain WHERE year( FROM_UNIXTIME( dateTime ) ) = "%s";' % time.strftime( "%Y", time.localtime( time.time() ) )

            at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain;'

            # The all stats from http://www.weewx.com/docs/customizing.htm doesn't seem to calculate "Total Rainfall for" all time stat correctly. 

            at_rain_highest_year_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%Y" ) AS year, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain GROUP BY year ORDER BY total DESC LIMIT 1;'

into:

        # Find what kind of database we're working with and specify the correctly tailored SQL Query for each type of database

        dataBinding = self.generator.config_dict['StdArchive']['data_binding']

        database = self.generator.config_dict['DataBindings'][dataBinding]['database']

        databaseType = self.generator.config_dict['Databases'][database]['database_type']

        driver = self.generator.config_dict['DatabaseTypes'][databaseType]['driver']

        if driver == "weedb.sqlite":

            year_rainiest_month_sql = 'SELECT strftime("%%m", datetime(dateTime, "unixepoch")) as month, ROUND( SUM( sum ), 2 ) as total FROM archive_day_rain WHERE strftime("%%Y", datetime(dateTime, "unixepoch")) = "%s" GROUP BY month ORDER BY total DESC LIMIT 1;' % time.strftime( "%Y", time.localtime( time.time() ) )

            at_rainiest_month_sql = 'SELECT strftime("%m", datetime(dateTime, "unixepoch")) as month, strftime("%Y", datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total FROM archive_day_rain GROUP BY month, year ORDER BY total DESC LIMIT 1;'

            year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain WHERE strftime("%%Y", datetime(dateTime, "unixepoch")) = "%s";' % time.strftime( "%Y", time.localtime( time.time() ) )

            at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain;'

            # The all stats from http://www.weewx.com/docs/customizing.htm doesn't seem to calculate "Total Rainfall for" all time stat correctly. 

            at_rain_highest_year_sql = 'SELECT strftime("%Y", datetime(dateTime, "unixepoch")) as year, ROUND( SUM( sum ), 2 ) as total FROM archive_day_rain GROUP BY year ORDER BY total DESC LIMIT 1;'

        elif driver == "weedb.mysql":

            year_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%m" ) AS month, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain WHERE year( FROM_UNIXTIME( dateTime ) ) = "{0}" GROUP BY month ORDER BY total DESC LIMIT 1;'.format( time.strftime( "%Y", time.localtime( time.time() ) ) ) # Why does this one require .format() but the other's don't?

            at_rainiest_month_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%m" ) AS month, FROM_UNIXTIME( dateTime, "%%Y" ) AS year, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain GROUP BY month, year ORDER BY total DESC LIMIT 1;'

            year_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain WHERE year( FROM_UNIXTIME( dateTime ) ) = "%s";' % time.strftime( "%Y", time.localtime( time.time() ) )

            at_rain_data_sql = 'SELECT dateTime, ROUND( sum, 2 ) FROM archive_day_rain;'

            # The all stats from http://www.weewx.com/docs/customizing.htm doesn't seem to calculate "Total Rainfall for" all time stat correctly. 

            at_rain_highest_year_sql = 'SELECT FROM_UNIXTIME( dateTime, "%%Y" ) AS year, ROUND( SUM( sum ), 2 ) AS total FROM archive_day_rain GROUP BY year ORDER BY total DESC LIMIT 1;'

For my situation the log info was:

Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension: dataBinding vpro_binding

Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension: database vpro_mysql

Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension: databaseType MySQL

Feb 18 11:18:49 pi21 wee_reports[20273]: Belchertown Extension: driver weedb.mysql

With this modification the Belchertown reports finished without errors.

Cheers, Luc

[Pat O'Brien]

Luc, Gary good info, great insight. Thanks for that... It all makes sense and wasn't something I had considered previously.

I'm on holiday today, but Luc if you want to submit a pull request that would be great. Otherwise I can merge this myself after some testing tomorrow.

--
You received this message because you are subscribed to a topic in the Google Groups "weewx-user" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/weewx-user/NNvvqe5ni2E/unsubscribe.
To unsubscribe from this group and all its topics, send an email to weewx-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.