Weewx DB Column Data Types

[storm...@gmail.com]

In the Weewx DB, the column data types are  primary Real or Integer but is it possible to store data as Text?

[Tom Keffer]

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.

4. Read the developer's notes on 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 storm...@gmail.com <storm...@gmail.com> 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 weewx-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/faf66dd7-7e4a-45fa-9737-6f0094024c88n%40googlegroups.com.

[storm...@gmail.com]

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

[Tom Keffer]

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.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/c4c792b5-c214-446e-9e4b-6d76dbc14aabn%40googlegroups.com.

[Tom Keffer]

No rules about ordering. WeeWX reads the schema on startup, so it knows the ordering of types.

An optional "daily summary" is also included in the database. Whether it is included or not, or used or not, is dependent on the type of "database manager" you use. This is specified by option manager in weewx.conf. If it is weewx.manager.DaySummaryManager, then a daily summary is included. If it is weewx.manager.Manager, then it is not.

On Mon, Aug 16, 2021 at 5:32 PM DR <daleea...@gmail.com> wrote:

Mr. Keffer:

As far as defining additional items to put into the Sqlite database associated with WeeWx, other than the required items for WeeWx to work, is there an expected order in the data base columns, or may one insert a required column as needed, such as a new column for snow depth, or voltage gradient, right after what has already been defined, no particular order required?

Sometimes there are rules from those who write and maintain the software like Sqlite, and then sometimes there are conventions used by those who modify or write new code for a program such as WeeWx.

Again, curious, quite a way from doing any serious writing yet.

Dale

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/CAPq0zEA%3D%2Bkt_kQw0QUpCVCcu85LfsJbqsD%3Dc4bsQ0_PSeU0DtA%40mail.gmail.com.

[storm...@gmail.com]

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

[storm...@gmail.com]

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

[Tom Keffer]

I was able to reproduce the problem. 

Let me work on a solution. 

-tk

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/be7ea2ed-51c5-4f1b-bf21-d37f8d2c0898n%40googlegroups.com.

[Tom Keffer]

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

[storm...@gmail.com]

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 

[Tom Keffer]

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 to track.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/9e0b1921-f0bc-4c1e-8dd4-09a32bd63610n%40googlegroups.com.

[storm...@gmail.com]

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

[Tom Keffer]

From a strict data science point of view, you’re better off  deriving those from primary observations. Writing an xtype extension would be more effective.

To view this discussion on the web visit https://groups.google.com/d/msgid/weewx-user/3d113ef1-7ffb-4314-ba5a-be576b322538n%40googlegroups.com.

--

-tk

[storm...@gmail.com]

I'll look into writing  an xtype extension(s).