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
--
You received this message because you are subscribed to the Google Groups
"weewx-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
