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 [email protected]:
> 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
>
> On Fri, Sep 11, 2020 at 5:03 AM Arend <[email protected]> wrote:
>
>> 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:
>>
>>> 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
>>> <https://groups.google.com/g/weewx-user/c/hp6ZvSOPM5s/m/Uo2Ym6SCBQAJ>.
>>>
>>> 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:
>>>
>>> [image: Screenshot lowes temperature current conditions.png]
>>> 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
>>>
>> --
>> 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].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/weewx-user/f6437254-3c52-4285-8d23-65207eae72den%40googlegroups.com
>>
>> <https://groups.google.com/d/msgid/weewx-user/f6437254-3c52-4285-8d23-65207eae72den%40googlegroups.com?utm_medium=email&utm_source=footer>
>> .
>>
>
--
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].
To view this discussion on the web visit
https://groups.google.com/d/msgid/weewx-user/69167af7-6ae0-443c-9968-679a46d01011n%40googlegroups.com.
