Thanks Tom, that did the trick. Some general information on the two text fields:
Lightning Frequency is based on number of Lightning Strikes over 5 minute period '----' --- 0 Occasional --- 1 to 5 Frequent --- 6 to 30 Continuous --- over 31 Comfort level is based off of the dewpoint. Pleasant dp <= 55.9 Comfortable dp between 56.0 --- 60.9 Getting Sticky dp between 61.0 --- 65.9 Uncomfortable dp between 66.0 --- 70.9 Oppressive dp between 71.0 --- 75.9 Miserable dp > 76.0 Rich On Tuesday, August 17, 2021 at 11:54:34 AM UTC-4 [email protected] wrote: > 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/3d113ef1-7ffb-4314-ba5a-be576b322538n%40googlegroups.com.
