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/CAPq0zEC0F%3DwCpfzSfM3aPc0_WsNu0fCJ0LHaMAGftTn%2BboPZug%40mail.gmail.com.
