Oops. I forgot yet another step! TEXT types cannot use the daily summaries,
so they should be left out of the schema for the summaries:
day_summaries = [(e[0], 'scalar') for e in table
if e[0] not in ('dateTime', 'usUnits', 'interval',
'lightning_frequency', 'comfort_level')] + [('wind',
'VECTOR')]
If you don't want to start all over, you can just drop the two tables:
*sqlite3 weewx.sdb*
sqlite> *DROP TABLE archive_day_lightning_frequency;*
sqlite> *DROP TABLE archive_day_comfort_level;*
sqlite> *.quit*
This is getting complicated enough that we probably should have some
directions in either the *Customizing Guide* or in the wiki for how to do
this. I've created issue #688 <https://github.com/weewx/weewx/issues/688>
to track.
On Tue, Aug 17, 2021 at 8:19 AM [email protected] <[email protected]>
wrote:
> The accumulator stanza was configured correctly in weewx.conf. Everything
> works as expected but still have the following error:
>
> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace
> failed for database weewx.sdb: table archive_day_comfort_level has 9
> columns but 5 values were supplied
> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace
> failed for database weewx.sdb: table archive_day_lightning_frequency has 9
> columns but 5 values were supplied
> Aug 17 11:15:24 raspberrypi weewx[8482] INFO weewx.manager: Added record
> 2021-08-17 11:15:00 EDT (1629213300) to database 'weewx.sdb'
> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace
> failed for database weewx.sdb: table archive_day_comfort_level has 9
> columns but 5 values were supplied
> Aug 17 11:15:24 raspberrypi weewx[8482] ERROR weewx.manager: Replace
> failed for database weewx.sdb: table archive_day_lightning_frequency has 9
> columns but 5 values were supplied
>
> I'm summing the reason for this error is the those fields are text values
> not the expected integer or real values.
>
> Rich
>
>
> On Tuesday, August 17, 2021 at 8:30:50 AM UTC-4 [email protected] wrote:
>
>> The problem is that I forgot a step. In weewx.conf, you need to set an
>> appropriate extractor for the string. For example,
>>
>> [Accumulator]
>> [[comfort_level]]
>> accumulator = firstlast
>> extractor = last
>> [[lightning_frequency]]
>> accumulator = firstlast
>> extractor = last
>>
>>
>> This means that what will be stored in the database will be the last
>> value seen during an archive period. An alternative is "first".
>>
>> One other detail: if you want to use it in tags, you also need to specify
>> the ".raw" suffix. For example:
>>
>> <p>Current = $current.comfort_level.raw</p>
>> <p>First string of the day = $day.comfort_level.first.raw</p>
>> <p>Last string = $day.comfort_level.last.raw</p>
>>
>>
>> -tk
>>
>>
>>
>> On Tue, Aug 17, 2021 at 4:52 AM Tom Keffer <[email protected]> wrote:
>>
>>> I was able to reproduce the problem.
>>>
>>> Let me work on a solution.
>>>
>>> -tk
>>>
>>> On Mon, Aug 16, 2021 at 9:50 PM [email protected] <[email protected]>
>>> wrote:
>>>
>>>> Tom
>>>>
>>>> Added two columns via the schema: comfort_level and
>>>> lightning_frequency; in which the data types are TEXT. Again the database
>>>> gets populated with the correct data.
>>>>
>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace
>>>> failed for database weewx.sdb: table archive_day_comfort_level has 9
>>>> columns but 5 values were supplied
>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace
>>>> failed for database weewx.sdb: table archive_day_lightning_frequency has 9
>>>> columns but 5 values were supplied
>>>> Aug 17 00:35:26 raspberrypi weewx[1359] INFO weewx.manager: Added
>>>> record 2021-08-17 00:35:00 EDT (1629174900) to database 'weewx.sdb'
>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace
>>>> failed for database weewx.sdb: table archive_day_comfort_level has 9
>>>> columns but 5 values were supplied
>>>> Aug 17 00:35:26 raspberrypi weewx[1359] ERROR weewx.manager: Replace
>>>> failed for database weewx.sdb: table archive_day_lightning_frequency has 9
>>>> columns but 5 values were supplied
>>>>
>>>> This is the schema:
>>>>
>>>> table = [('dateTime', 'INTEGER NOT NULL UNIQUE PRIMARY
>>>> KEY'),
>>>> ('usUnits', 'INTEGER NOT NULL'),
>>>> ('interval', 'INTEGER NOT NULL'),
>>>> ('altimeter', 'REAL'),
>>>> ('appTemp', 'REAL'),
>>>> ('appTemp1', 'REAL'),
>>>> ('barometer', 'REAL'),
>>>> ('batteryStatus1', 'REAL'),
>>>> ('batteryStatus2', 'REAL'),
>>>> ('batteryStatus3', 'REAL'),
>>>> ('batteryStatus4', 'REAL'),
>>>> ('batteryStatus5', 'REAL'),
>>>> ('batteryStatus6', 'REAL'),
>>>> ('batteryStatus7', 'REAL'),
>>>> ('batteryStatus8', 'REAL'),
>>>> ('cloudbase', 'REAL'),
>>>> ('co', 'REAL'),
>>>> ('co2', 'REAL'),
>>>> ('consBatteryVoltage', 'REAL'),
>>>> ('dewpoint', 'REAL'),
>>>> ('dewpoint1', 'REAL'),
>>>> ('ET', 'REAL'),
>>>> ('extraHumid1', 'REAL'),
>>>> ('extraHumid2', 'REAL'),
>>>> ('extraHumid3', 'REAL'),
>>>> ('extraHumid4', 'REAL'),
>>>> ('extraHumid5', 'REAL'),
>>>> ('extraHumid6', 'REAL'),
>>>> ('extraHumid7', 'REAL'),
>>>> ('extraHumid8', 'REAL'),
>>>> ('extraTemp1', 'REAL'),
>>>> ('extraTemp2', 'REAL'),
>>>> ('extraTemp3', 'REAL'),
>>>> ('extraTemp4', 'REAL'),
>>>> ('extraTemp5', 'REAL'),
>>>> ('extraTemp6', 'REAL'),
>>>> ('extraTemp7', 'REAL'),
>>>> ('extraTemp8', 'REAL'),
>>>> ('forecast', 'REAL'),
>>>> ('hail', 'REAL'),
>>>> ('hailBatteryStatus', 'REAL'),
>>>> ('hailRate', 'REAL'),
>>>> ('heatindex', 'REAL'),
>>>> ('heatindex1', 'REAL'),
>>>> ('heatingTemp', 'REAL'),
>>>> ('heatingVoltage', 'REAL'),
>>>> ('humidex', 'REAL'),
>>>> ('humidex1', 'REAL'),
>>>> ('inDewpoint', 'REAL'),
>>>> ('inHumidity', 'REAL'),
>>>> ('inTemp', 'REAL'),
>>>> ('inTempBatteryStatus', 'REAL'),
>>>> ('leafTemp1', 'REAL'),
>>>> ('leafTemp2', 'REAL'),
>>>> ('leafWet1', 'REAL'),
>>>> ('leafWet2', 'REAL'),
>>>> ('lightning_distance', 'REAL'),
>>>> ('lightning_disturber_count', 'REAL'),
>>>> ('lightning_energy', 'REAL'),
>>>> ('lightning_noise_count', 'REAL'),
>>>> ('lightning_strike_count', 'REAL'),
>>>> ('luminosity', 'REAL'),
>>>> ('maxSolarRad', 'REAL'),
>>>> ('nh3', 'REAL'),
>>>> ('no2', 'REAL'),
>>>> ('noise', 'REAL'),
>>>> ('o3', 'REAL'),
>>>> ('outHumidity', 'REAL'),
>>>> ('outTemp', 'REAL'),
>>>> ('outTempBatteryStatus', 'REAL'),
>>>> ('pb', 'REAL'),
>>>> ('pm10_0', 'REAL'),
>>>> ('pm1_0', 'REAL'),
>>>> ('pm2_5', 'REAL'),
>>>> ('pressure', 'REAL'),
>>>> ('radiation', 'REAL'),
>>>> ('rain', 'REAL'),
>>>> ('rainBatteryStatus', 'REAL'),
>>>> ('rainRate', 'REAL'),
>>>> ('referenceVoltage', 'REAL'),
>>>> ('rxCheckPercent', 'REAL'),
>>>> ('signal1', 'REAL'),
>>>> ('signal2', 'REAL'),
>>>> ('signal3', 'REAL'),
>>>> ('signal4', 'REAL'),
>>>> ('signal5', 'REAL'),
>>>> ('signal6', 'REAL'),
>>>> ('signal7', 'REAL'),
>>>> ('signal8', 'REAL'),
>>>> ('snow', 'REAL'),
>>>> ('snowBatteryStatus', 'REAL'),
>>>> ('snowDepth', 'REAL'),
>>>> ('snowMoisture', 'REAL'),
>>>> ('snowRate', 'REAL'),
>>>> ('so2', 'REAL'),
>>>> ('soilMoist1', 'REAL'),
>>>> ('soilMoist2', 'REAL'),
>>>> ('soilMoist3', 'REAL'),
>>>> ('soilMoist4', 'REAL'),
>>>> ('soilTemp1', 'REAL'),
>>>> ('soilTemp2', 'REAL'),
>>>> ('soilTemp3', 'REAL'),
>>>> ('soilTemp4', 'REAL'),
>>>> ('supplyVoltage', 'REAL'),
>>>> ('txBatteryStatus', 'REAL'),
>>>> ('UV', 'REAL'),
>>>> ('uvBatteryStatus', 'REAL'),
>>>> ('windBatteryStatus', 'REAL'),
>>>> ('windchill', 'REAL'),
>>>> ('windDir', 'REAL'),
>>>> ('windGust', 'REAL'),
>>>> ('windGustDir', 'REAL'),
>>>> ('windrun', 'REAL'),
>>>> ('windSpeed', 'REAL'),
>>>> ('lightning_frequency', 'TEXT'),
>>>> ('comfort_level', 'TEXT'),
>>>> ]
>>>>
>>>> day_summaries = [(e[0], 'scalar') for e in table
>>>> if e[0] not in ('dateTime', 'usUnits', 'interval')] +
>>>> [('wind', 'VECTOR')]
>>>>
>>>> schema = {
>>>> 'table': table,
>>>> 'day_summaries' : day_summaries
>>>> }
>>>>
>>>> Rich
>>>> On Monday, August 16, 2021 at 10:32:02 PM UTC-4 [email protected]
>>>> wrote:
>>>>
>>>>> Tom
>>>>>
>>>>> I tried adding the column using the following command:
>>>>>
>>>>> wee_database --add-column=lightning_frequency --type=TEXT
>>>>>
>>>>> It does add the column and populates the data but generates an error
>>>>> during the archival cycle. I guess it looking for integers and real type
>>>>> values.
>>>>>
>>>>> Aug 16 22:00:22 raspberrypi weewx[24285] ERROR weewx.manager: Replace
>>>>> failed for database weewx.sdb: table archive_day_lightning_frequency has 9
>>>>> columns but 5 values were supplied
>>>>> Aug 16 22:00:22 raspberrypi weewx[24285] INFO weewx.manager: Added
>>>>> record 2021-08-16 22:00:00 EDT (1629165600) to database 'weewx.sdb'
>>>>> Aug 16 22:00:22 raspberrypi weewx[24285] ERROR weewx.manager: Replace
>>>>> failed for database weewx.sdb: table archive_day_lightning_frequency has 9
>>>>> columns but 5 values were supplied
>>>>>
>>>>> Next I will try adding using the schema.
>>>>>
>>>>> Rich
>>>>> On Monday, August 16, 2021 at 6:55:55 PM UTC-4 [email protected] wrote:
>>>>>
>>>>>> Can't say that I've tried that, but I don't see why it wouldn't work.
>>>>>> It's certainly simpler.
>>>>>>
>>>>>> Let me know how it goes.
>>>>>>
>>>>>> On Mon, Aug 16, 2021 at 9:11 AM [email protected] <
>>>>>> [email protected]> wrote:
>>>>>>
>>>>>>> Thanks Tom.
>>>>>>>
>>>>>>> I have another question before proceeding. Do I need to physically
>>>>>>> update the schema to include column and type or could I just use the
>>>>>>> following command:
>>>>>>>
>>>>>>> wee_database --add-column=comments --type=TEXT
>>>>>>>
>>>>>>> Rich
>>>>>>>
>>>>>>> On Monday, August 16, 2021 at 8:29:16 AM UTC-4 [email protected]
>>>>>>> wrote:
>>>>>>>
>>>>>>>> Good question. Yes, it is possible to store text.
>>>>>>>>
>>>>>>>> 1. Assuming you're using sqlite, make sure you read their
>>>>>>>> documentation on data types: https://sqlite.org/datatype3.html
>>>>>>>>
>>>>>>>> 2. Create a new schema accordingly. For example,
>>>>>>>>
>>>>>>>> table = [('dateTime', 'INTEGER NOT NULL UNIQUE PRIMARY KEY'),
>>>>>>>> ('usUnits', 'INTEGER NOT NULL'),
>>>>>>>> ('interval', 'INTEGER NOT NULL'),
>>>>>>>> ('outTemp', 'REAL'),
>>>>>>>> ('comments', 'TEXT')
>>>>>>>> ]
>>>>>>>>
>>>>>>>>
>>>>>>>> day_summaries = [('outTemp', 'scalar'), ('comments', 'scalar')]
>>>>>>>>
>>>>>>>> schema = {
>>>>>>>> 'table': table,
>>>>>>>> 'day_summaries' : day_summaries
>>>>>>>> }
>>>>>>>>
>>>>>>>>
>>>>>>>> This is just an example. Your schema is likely to have many more
>>>>>>>> types than this.
>>>>>>>>
>>>>>>>> 3. See the directions in the Customizing Guide for how to specify
>>>>>>>> that your new schema be used. *Picking a starting schema
>>>>>>>> <http://www.weewx.com/docs/customizing.htm#Picking_a_starting_schema>*
>>>>>>>> .
>>>>>>>>
>>>>>>>> 4. Read the developer's notes on Accumulators
>>>>>>>> <https://github.com/weewx/weewx/wiki/Accumulators>. In your
>>>>>>>> weewx.conf file, specify that your text type should be accumulated
>>>>>>>> using
>>>>>>>> the "firstlast" accumulator. This will prevent it from trying to
>>>>>>>> find the average, etc, of a string.
>>>>>>>>
>>>>>>>> [Accumulator]
>>>>>>>> [[comments]]
>>>>>>>> accumulator = firstlast
>>>>>>>>
>>>>>>>>
>>>>>>>> That's it. If your new type appears in the archive record, then it
>>>>>>>> will be included in the database.
>>>>>>>>
>>>>>>>> Tags like
>>>>>>>>
>>>>>>>> <p>Details: $current.comments, last recorded at
>>>>>>>> $day.comments.last</p>
>>>>>>>>
>>>>>>>>
>>>>>>>> should also work.
>>>>>>>>
>>>>>>>>
>>>>>>>> On Sun, Aug 15, 2021 at 8:43 PM [email protected] <
>>>>>>>> [email protected]> wrote:
>>>>>>>>
>>>>>>>>> In the Weewx DB, the column data types are primary Real or
>>>>>>>>> Integer but is it possible to store data as Text?
>>>>>>>>>
>>>>>>>>> --
>>>>>>>>> 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/faf66dd7-7e4a-45fa-9737-6f0094024c88n%40googlegroups.com
>>>>>>>>> <https://groups.google.com/d/msgid/weewx-user/faf66dd7-7e4a-45fa-9737-6f0094024c88n%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/c4c792b5-c214-446e-9e4b-6d76dbc14aabn%40googlegroups.com
>>>>>>> <https://groups.google.com/d/msgid/weewx-user/c4c792b5-c214-446e-9e4b-6d76dbc14aabn%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/be7ea2ed-51c5-4f1b-bf21-d37f8d2c0898n%40googlegroups.com
>>>> <https://groups.google.com/d/msgid/weewx-user/be7ea2ed-51c5-4f1b-bf21-d37f8d2c0898n%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/9e0b1921-f0bc-4c1e-8dd4-09a32bd63610n%40googlegroups.com
> <https://groups.google.com/d/msgid/weewx-user/9e0b1921-f0bc-4c1e-8dd4-09a32bd63610n%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/CAPq0zEAMb5jxh_d8o_ZCd4fsFrkcutt7EiQmrGoYf7Raqy1Xuw%40mail.gmail.com.
