Trying to show xtype in chart causes mysql errors

[Arend]

Hello,

Just starting to become familiar with user defined types (XTypes), and tried to create a new type called lowTemperature. This type compares the temperatures outTemp and extraTemp1 and returns the lowest temperature of the two. I modified an existing example, the original can be found here: XType example.

My modified code in module lowest_temperature.py looks like this:

import weewx.units

import weewx.xtypes

from weewx.units import ValueTuple

class LowestTemperature(weewx.xtypes.XType):

  def get_scalar(self, obs_type, record, dbmanager):

    """Determine which sensor has lowest temperature."""

    if obs_type != 'lowTemperature':

      raise weewx.UnknownType

    try:

      record_us = weewx.units.to_US(record)

      if record_us['outTemp'] <= record_us['extraTemp1']:

        lowTemperature = record_us['outTemp']

      else:

        lowTemperature = record_us['extraTemp1']

      return ValueTuple(lowTemperature, "degree_F", "group_temperature")

    except KeyError:

      # Don't have everything we need. Raise an exception.

      raise weewx.CannotCalculate(obs_type)

  

  def get_series(self, obs_type, timespan, db_manager, aggregate_type=None, aggregate_interval=None):

    if obs_type != 'lowTemperature':

      raise weewx.UnknownType

    start_vec = list()

    stop_vec = list()

    data_vec = list()

    if aggregate_type:

      raise weewx.UnknownAggregation(aggregate_type)

    for record in db_manager.genSql("SELECT dateTime, interval, usUnits, outTemp, extraTemp1 FROM archive WHERE dateTime > %(start)s AND dateTime <= %(stop)s;" % {'start': timespan[0], 'stop': timespan[1]}):

      if (record[2] != 1):

        raise weewx.CannotCalculate("units are not US")

      start_vec.append(record[0] - record[1] * 60)

      stop_vec.append(record[0])

      if record[3] <= record[4]:

        data_vec.append(record[3])

      else:

        data_vec.append(record[4])

    return (ValueTuple(start_vec, 'unix_epoch', 'group_time'), ValueTuple(stop_vec, 'unix_epoch', 'group_time'), ValueTuple(data_vec, "degree_F", "group_temperature"))

The code in extension.py is like this:

import user.lowest_temperature

import weewx.xtypes

weewx.xtypes.xtypes.append(user.lowest_temperature.LowestTemperature())

Added this leaf to weewx.conf Generic Labels [Generic]:

lowTemperature = Lowest Temperature

Modified current.inc from Seasons skin:

      <tr>

        <td class="label">$obs.label.lowTemperature</td>

        <td class="data">$current.lowTemperature</td>

      </tr>

The get_scalar function seems to work allright. Screenhot seasons skin:

But when I want to show the new xtype in the chart daytempdew by adding the leaf [[[[lowTemperature]]]] to Seasons skin.info like this:

        [[[daytempdew]]]

            [[[[outTemp]]]]

            [[[[dewpoint]]]]

            [[[[lowTemperature]]]]

I get these errors when I run weewx:

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine: Caught unrecoverable exception in generator 'weewx.imagegenerator.ImageGenerator'

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', usUnits, outTemp, extraTemp1 FROM archive WHERE dateTime > 1599732000 AND date' at line 1")

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  Traceback (most recent call last):

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weedb/mysql.py", line 52, in guarded_fn

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      return fn(*args, **kwargs)

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weedb/mysql.py", line 262, in execute

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      self.cursor.execute(mysql_string, tuple(sql_tuple))

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      res = self._query(query)

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      db.query(q)

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 226, in query

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      _mysql.connection.query(self, query)

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', usUnits, outTemp, extraTemp1 FROM archive WHERE dateTime > 1599732000 AND date' at line 1")

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  During handling of the above exception, another exception occurred:

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  Traceback (most recent call last):

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 197, in run

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      obj.start()

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weewx/reportengine.py", line 280, in start

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      self.run()

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weewx/imagegenerator.py", line 41, in run

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      self.genImages(self.gen_ts)

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weewx/imagegenerator.py", line 176, in genImages

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      start_vec_t, stop_vec_t ,data_vec_t = weewx.xtypes.get_series(var_type,

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weewx/xtypes.py", line 85, in get_series

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      return xtype.get_series(obs_type, timespan, db_manager, aggregate_type,

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/user/lowest_temperature.py", line 31, in get_series

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      for record in db_manager.genSql("SELECT dateTime, interval, usUnits, outTemp, extraTemp1 FROM archive WHERE dateTime > %(start)s AND dateTime <= %(stop)s;" % {'start': timespan[0], 'stop': timespan[1]}):

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weewx/manager.py", line 437, in genSql

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      for _row in _cursor.execute(sql, sqlargs):

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****    File "/home/weewx/bin/weedb/mysql.py", line 61, in guarded_fn

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****      raise klass(e)

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  weedb.DatabaseError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', usUnits, outTemp, extraTemp1 FROM archive WHERE dateTime > 1599732000 AND date' at line 1")

Sep 11 12:35:24 NB10 weewx[86760] ERROR weewx.reportengine:         ****  Generator terminated

Sep 11 12:35:24 NB10 weewx[86760] INFO weewx.reportengine: Copied 0 files to /home/weewx/public_html

My question:

What is the error in the SQL-string? And is it just an error in the SQL-string, maybe I am also missing/overlooking something else.

I am using WeeWX version 4.1.1

[Arend]

I noticed a weird typo: skin.info should be skin.conf ofcourse.

Op vrijdag 11 september 2020 om 12:50:34 UTC+2 schreef Arend:

[Tom Keffer]

In MySQL, the word 'interval' is a reserved word, so you need to enclose it in backticks. The SQL statement should be

"SELECT dateTime, `interval`, usUnits, outTemp, extraTemp1 FROM archive WHERE dateTime > %(start)s AND dateTime <= %(stop)s;"

One thing to be aware of: values can be None. So your conditional

      if record_us['outTemp'] <= record_us['extraTemp1']:

could cause a TypeError exception if one of the values is None.

Otherwise, good job! Nice to see XTypes in action.

-tk

--
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 weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/f6437254-3c52-4285-8d23-65207eae72den%40googlegroups.com.

[Arend]

Thank you Tom.

Adding those backticks did the trick. To make the code future/fool proof I've added those backticks to all the sql names in case someone runs into the same issue working with MySQL database..

SELECT `dateTime`, `interval`, `usUnits`, `outTemp`, `extraTemp1` FROM `archive` WHERE `dateTime` > %(start)s AND `dateTime` <= %(stop)s;" % {'start': timespan[0], 'stop': timespan[1]})

Also added the recommended error checking, everything works fine now.

Op vrijdag 11 september 2020 om 14:48:06 UTC+2 schreef tke...@gmail.com: