Storing Text as a record, wee_import and mysql UPDATE
252 views
Skip to first unread message
Glenn McKechnie
Jan 17, 2022, 8:14:00 AM1/17/22
to weewx-user
Working with TEXT records.
I've gone through the wiki entry
https://github.com/weewx/weewx/wiki/Storing-text-in-the-database
and that all works well. I can add TEXT to the database and display it
in the weewx skin. So far so good.
Using a modified filepile.py, I can add what I want via the new_archive
record hook. What I can't seem to do is add historical data that is in
TEXT form, via weewx. Fair enough, it makes sense.
I figured wee_import may be a solution so I crafted a csv file and used
wee_import but it baulks on the text field. The csv files,
/var/tmp/weewxaddnotes.csv has the contents...
timestamp,extraTemp1,extraTemp2,ANComment
1641729300,23.0,22.0,"Test.\\nThen test again"
It appears to be wanting a weewx_unit_name and I'm missing a clue stick.
I can't find a name that it will accept without error. If the
weewx_unit_name field is left blank (only the csv_field_name is given)
it gives the following.
Obtaining raw import data for period 1 ...
**** Unable to parse source-to-WeeWX field map.
**** No units specified for source field 'ANComment' in /home/weewx/csv.conf.
**** Nothing done, exiting.
Which makes sense as I had the following...
[[FieldMap]]
dateTime = timestamp, unix_epoch
interval =
barometer =
pressure =
altimeter =
inTemp =
outTemp =
extraTemp1 = extraTemp1, degree_C
extraTemp2 = extraTemp2, degree_C
ANComment = ANComment
If I tell wee_imports to ignore the ANComment entry, as follows...
ANComment =
then everything else imports as expected so it's falling over on the
ANComment field
ignore_invalid_data in the csv.conf file is tantalizing close (a numeric
field containing non-numeric data) but I don't want it skipped or
halted. I want it accepted.
Can I write anything into that 2nd field that will allow a TEXT entry to
skip the conversion process, and allow it to be written as-is to the
database?
On reflection, even if I can use wee_import to add the data I want. I
need to get it correct and complete the first time as once a RECORD is
written (and all it's fields filled) then I can't update it later
without going the manual route as weewx appears to be missing an inbuilt
sql UPDATE archive method. That or I can't find it.
And, that's probably so we don't shoot ourselves in the foot.
But it sure would be nice to write a historical record using weewx or
preferably wee_import (with all their built in sanity checks) and then,
if needed later, be able to update any missing fields, or correct errant
values without going back to the command line.
I understand the risk of blindly updating existing records, but in this
case they are all known empty or new fields, and if they do have
something in them then it needs changing anyway!
Caveat Emptor or something along those lines! :-)
As an aside. I'm doing this as I have a whole host of manual rain
records that should be entered as they are sanity checks on the
automatic gauges, weewx seems as good a place as any to store them.
At the same time I'll be adding extraTemp values that I've recorded
from the haystack.
It'd be nice to plot them, and change them, as they use existing
fields - extraTemp1 etc.
Along with that, a TEXT comment as to why some readings appear as they
do would be useful, or where the floods were after the recent epic
rainfall, or why the rain values appear to be stuffed, or which area of
the haystack the probe is at.
The latest Tongan earthquake is probably another example given the
recent barometric burps it caused.
The possibilities expand as I write.
So I better go.
But... In summary.
wee_import appears to baulk on the new add TEXT method. Is there a quick
fix?
The ability to sql UPDATE any historical weewx record would be useful. 1
or many RECORD fields at a time.
Individual confirmations not required. Other than accepting a one-time
answer that; 'yes', I will bandage my own toes and blame no-one else if
it backfires, because we always have our backups.
Ha! Perhaps a csv.conf entry named pieces_are_all_yours with a default
of no? Changing to 'yes' will 'turn on' the UPDATE method. ;-)
--
Cheers
Glenn
rorpi - read only raspberry pi & various weewx addons
https://github.com/glennmckechnie
I've gone through the wiki entry
https://github.com/weewx/weewx/wiki/Storing-text-in-the-database
and that all works well. I can add TEXT to the database and display it
in the weewx skin. So far so good.
Using a modified filepile.py, I can add what I want via the new_archive
record hook. What I can't seem to do is add historical data that is in
TEXT form, via weewx. Fair enough, it makes sense.
I figured wee_import may be a solution so I crafted a csv file and used
wee_import but it baulks on the text field. The csv files,
/var/tmp/weewxaddnotes.csv has the contents...
timestamp,extraTemp1,extraTemp2,ANComment
1641729300,23.0,22.0,"Test.\\nThen test again"
It appears to be wanting a weewx_unit_name and I'm missing a clue stick.
I can't find a name that it will accept without error. If the
weewx_unit_name field is left blank (only the csv_field_name is given)
it gives the following.
Obtaining raw import data for period 1 ...
**** Unable to parse source-to-WeeWX field map.
**** No units specified for source field 'ANComment' in /home/weewx/csv.conf.
**** Nothing done, exiting.
Which makes sense as I had the following...
[[FieldMap]]
dateTime = timestamp, unix_epoch
interval =
barometer =
pressure =
altimeter =
inTemp =
outTemp =
extraTemp1 = extraTemp1, degree_C
extraTemp2 = extraTemp2, degree_C
ANComment = ANComment
If I tell wee_imports to ignore the ANComment entry, as follows...
ANComment =
then everything else imports as expected so it's falling over on the
ANComment field
ignore_invalid_data in the csv.conf file is tantalizing close (a numeric
field containing non-numeric data) but I don't want it skipped or
halted. I want it accepted.
Can I write anything into that 2nd field that will allow a TEXT entry to
skip the conversion process, and allow it to be written as-is to the
database?
On reflection, even if I can use wee_import to add the data I want. I
need to get it correct and complete the first time as once a RECORD is
written (and all it's fields filled) then I can't update it later
without going the manual route as weewx appears to be missing an inbuilt
sql UPDATE archive method. That or I can't find it.
And, that's probably so we don't shoot ourselves in the foot.
But it sure would be nice to write a historical record using weewx or
preferably wee_import (with all their built in sanity checks) and then,
if needed later, be able to update any missing fields, or correct errant
values without going back to the command line.
I understand the risk of blindly updating existing records, but in this
case they are all known empty or new fields, and if they do have
something in them then it needs changing anyway!
Caveat Emptor or something along those lines! :-)
As an aside. I'm doing this as I have a whole host of manual rain
records that should be entered as they are sanity checks on the
automatic gauges, weewx seems as good a place as any to store them.
At the same time I'll be adding extraTemp values that I've recorded
from the haystack.
It'd be nice to plot them, and change them, as they use existing
fields - extraTemp1 etc.
Along with that, a TEXT comment as to why some readings appear as they
do would be useful, or where the floods were after the recent epic
rainfall, or why the rain values appear to be stuffed, or which area of
the haystack the probe is at.
The latest Tongan earthquake is probably another example given the
recent barometric burps it caused.
The possibilities expand as I write.
So I better go.
But... In summary.
wee_import appears to baulk on the new add TEXT method. Is there a quick
fix?
The ability to sql UPDATE any historical weewx record would be useful. 1
or many RECORD fields at a time.
Individual confirmations not required. Other than accepting a one-time
answer that; 'yes', I will bandage my own toes and blame no-one else if
it backfires, because we always have our backups.
Ha! Perhaps a csv.conf entry named pieces_are_all_yours with a default
of no? Changing to 'yes' will 'turn on' the UPDATE method. ;-)
--
Cheers
Glenn
rorpi - read only raspberry pi & various weewx addons
https://github.com/glennmckechnie
gjr80
Jan 17, 2022, 6:09:48 PM1/17/22
to weewx-user
Correct, wee_import will not accept text only fields. When we put together wee_import we only included support for numeric fields; text fields were omitted, perhaps not intentionally, but more likely because no real consideration had been given to including them in the schema. That being said, it should be a fairly simple process to modify wee_import to support text fields, at least for CSV imports anyway. Having had a quick wee_import refresher this morning I am thinking we could support something like ANComment = ANComment, text
in the field map. Let me look at this further and see what I can come up with.
As for supporting UPDATE, that will require further thought as I suspect that will introduce a number of wider issues. I'll probably raise an issue for that one.
Gary
Glenn McKechnie
Jan 17, 2022, 7:17:56 PM1/17/22
to weewx...@googlegroups.com
Thanks Gary, I appreciate it.
Hopefully UPDATE won't raise any serious weewx issues.
I look forward to seeing it raised as an issue :)
> --
> 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/75ec1eac-bd90-402d-a450-452ab9e4a960n%40googlegroups.com.
Hopefully UPDATE won't raise any serious weewx issues.
I look forward to seeing it raised as an issue :)
> 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/75ec1eac-bd90-402d-a450-452ab9e4a960n%40googlegroups.com.
gjr80
Jan 21, 2022, 10:57:51 PM1/21/22
to weewx-user
Glenn,
I have created a new branch wee_import_text off development for this and have modified the wee_import code to import text to a WeeWX TEXT field. There are a dew conditions:
1. text import only works with CSV imports
2. import source fields that are to be imported as text must use 'text' as the WeeWX units specifier in the field map in the import config file, eg:
[[FieldMap]]
....
aComment = import_text_field, text
3. the destination text field has been added to the WeeWX database and WeeWX configured as per the Storing text in the database wiki page
I have only done basic testing of the code so there may be some corner cases I have not covered, but you should be right to give it a go if you want. As I said I have taken a branch off development, since there has been quite a few changes in development since the last release the most reliable option is for you to clone and install wee_import_text. However, the wee_import code does not change often so you could probably get away with running v4.5.1 and just downloading and installing the modified weeimport.py.
To use the new wee_import:
1. Either clone and install from the wee_import_text branch or if using v4.5.1 (or any other recent version at your own peril) replace BIN/weeimport/weeimport.py with weeimport.py from the wee_import_text branch. You can download weeimport.py from the wee_import_text branch directly using wget as follows:
2. edit your import config file and add your text field import to the field map as outlined above
3. stop WeeWX and run your import using --dry-run and then without --dry-run
Let me know if you have any issues.
Gary
Glenn McKechnie
Jan 22, 2022, 1:08:27 AM1/22/22
to weewx...@googlegroups.com
Thanks Gary,
I'll give it a test drive as soon as I get a moment or three.
On a related note (ie:- related to using TEXT fields in the database)
I went and checked out wee_database and found that it baulked at the
TEXT field in the database.
Details are in the attached file, which should keep the formatting sane.
Let me know if you want it raised as a seperate issue and I'll do that instead.
On 22/01/2022, gjr80 <gjrod...@gmail.com> wrote:
> Glenn,
>
> I have created a new branch wee_import_text off development for this and
> have modified the wee_import code to import text to a WeeWX TEXT field.
> There are a dew conditions:
>
> 1. text import only works with CSV imports
>
> 2. import source fields that are to be imported as text must use 'text' as
> the WeeWX units specifier in the field map in the import config file, eg:
> [[FieldMap]]
> ....
> aComment = import_text_field, text
>
> 3. the destination text field has been added to the WeeWX database and
> WeeWX configured as per the Storing text in the database
> <https://github.com/weewx/weewx/wiki/Storing-text-in-the-database> wiki
I'll give it a test drive as soon as I get a moment or three.
On a related note (ie:- related to using TEXT fields in the database)
I went and checked out wee_database and found that it baulked at the
TEXT field in the database.
Details are in the attached file, which should keep the formatting sane.
Let me know if you want it raised as a seperate issue and I'll do that instead.
On 22/01/2022, gjr80 <gjrod...@gmail.com> wrote:
> Glenn,
>
> I have created a new branch wee_import_text off development for this and
> have modified the wee_import code to import text to a WeeWX TEXT field.
> There are a dew conditions:
>
> 1. text import only works with CSV imports
>
> 2. import source fields that are to be imported as text must use 'text' as
> the WeeWX units specifier in the field map in the import config file, eg:
> [[FieldMap]]
> ....
> aComment = import_text_field, text
>
> 3. the destination text field has been added to the WeeWX database and
> WeeWX configured as per the Storing text in the database
> page
>
> I have only done basic testing of the code so there may be some corner
> cases I have not covered, but you should be right to give it a go if you
> want. As I said I have taken a branch off development, since there has been
>
> quite a few changes in development since the last release the most reliable
>
> option is for you to clone and install wee_import_text. However, the
> wee_import code does not change often so you could probably get away with
> running v4.5.1 and just downloading and installing the modified
> weeimport.py
> .
>
> To use the new wee_import:
>
> 1. Either clone and install from the wee_import_text branch or if using
> v4.5.1 (or any other recent version at your own peril) replace
> BIN/weeimport/weeimport.py with weeimport.py from the wee_import_text
> branch. You can download weeimport.py from the wee_import_text branch
> directly using wget as follows:
>
> $ wget
> https://raw.githubusercontent.com/weewx/weewx/wee_import_text/bin/weeimport/weeimport.py
>
> 2. edit your import config file and add your text field import to the field
>
> map as outlined above
>
> 3. stop WeeWX and run your import using --dry-run and then without
> --dry-run
>
> Let me know if you have any issues.
>
> Gary
>
>
> I have only done basic testing of the code so there may be some corner
> cases I have not covered, but you should be right to give it a go if you
> want. As I said I have taken a branch off development, since there has been
>
> quite a few changes in development since the last release the most reliable
>
> option is for you to clone and install wee_import_text. However, the
> wee_import code does not change often so you could probably get away with
> running v4.5.1 and just downloading and installing the modified
> weeimport.py
> .
>
> To use the new wee_import:
>
> 1. Either clone and install from the wee_import_text branch or if using
> v4.5.1 (or any other recent version at your own peril) replace
> BIN/weeimport/weeimport.py with weeimport.py from the wee_import_text
> branch. You can download weeimport.py from the wee_import_text branch
> directly using wget as follows:
>
> $ wget
> https://raw.githubusercontent.com/weewx/weewx/wee_import_text/bin/weeimport/weeimport.py
>
> 2. edit your import config file and add your text field import to the field
>
> map as outlined above
>
> 3. stop WeeWX and run your import using --dry-run and then without
> --dry-run
>
> Let me know if you have any issues.
>
> Gary
>
gjr80
Mar 3, 2022, 7:28:28 PM3/3/22
to weewx-user
Glenn,
Have you had a chance to try the revised wee_import? I am working on a number of other changes to the same piece of wee_import code and I would prefer to do them all at once if possible.
Gary
Glenn McKechnie
Mar 3, 2022, 10:16:57 PM3/3/22
to weewx...@googlegroups.com
On 04/03/2022, gjr80 <gjrod...@gmail.com> wrote:
> Glenn,
>
> Have you had a chance to try the revised wee_import? I am working on a
> number of other changes to the same piece of wee_import code and I would
> prefer to do them all at once if possible.
Whoops. Sorry about that. It slid down the Todo list a little too
> Glenn,
>
> Have you had a chance to try the revised wee_import? I am working on a
> number of other changes to the same piece of wee_import code and I would
> prefer to do them all at once if possible.
far. ie;- I got somewhat sidetracked.
Re-visiting this and I find I've either forgotten something critical,
or it just plain doesn't work...
I'm stll using the weewx.451 installation where I was developing this.
I've fetched and replaced weeimport/weeimport.py as per your instructions.
sudo bin/wee_import --import-config=/home/weewx/csv.conf
--config=/home/weewx/weewx.conf --verbose --dry-run
and I get the following error (with dry-run and without) ...
Using WeeWX configuration file /home/weewx/weewx.conf
Starting wee_import...
A CSV import from source file '/var/tmp/weewxaddnotes.csv' has been requested.
The following options will be used:
config=/home/weewx/weewx.conf, import-config=/home/weewx/csv.conf
source=/var/tmp/weewxaddnotes.csv, from=None, to=None
dry-run=True, calc_missing=True, ignore_invalid_data=True
tranche=250, interval=conf, date/time_string_format=%Y-%m-%d %H:%M:%S
delimiter=',', rain=cumulative, wind_direction=[-360.0, 360.0]
UV=False, radiation=False
Using database binding 'wx_binding', which is bound to database 'weewx.sdb'
Destination table 'archive' unit system is '0x10' (METRIC).
Missing derived observations will be calculated.
All WeeWX UV fields will be set to None.
All WeeWX radiation fields will be set to None.
This is a dry run, imported data will not be saved to archive.
Starting dry run import ...
Obtaining raw import data for period 1 ...
Traceback (most recent call last):
File "bin/wee_import", line 899, in <module>
main()
File "bin/wee_import", line 829, in main
source_obj.run()
File "/home/weewx451/bin/weeimport/weeimport.py", line 372, in run
_raw_data = self.getRawData(period)
File "/home/weewx451/bin/weeimport/csvimport.py", line 246, in getRawData
self.map = self.parseMap('CSV', _csv_reader, self.csv_config_dict)
File "/home/weewx451/bin/weeimport/weeimport.py", line 635, in parseMap
and _val['units'] not in weewx.units.USUnits.values() \
AttributeError: 'ListOfDicts' object has no attribute 'values'
The database field is named ANComment, the field I'm importing is
named ANComment
The attached files will show the full details. weewx.conf is unedited,
it's a throw away installation.
And, I've just cloned the wee_import_text branch - gives the same error.
gjr80
Mar 3, 2022, 11:51:33 PM3/3/22
to weewx-user
Ah, didn't notice you were on 4.5.1. There was a change to units.py under 4.6.x that forced a change in weeimport.py, consequently the weeimport.py version I provided does not get on with WeeWX 4.5.x. Let me get you a 4.5.x compatible version.
Gary
Glenn McKechnie
Mar 4, 2022, 8:26:43 PM3/4/22
to weewx...@googlegroups.com
Gary, closer but...
**** could not convert string to float: test of wee_import text updates
===== full terminal output...
12:19 PM $ sudo bin/wee_import --import-config=/home/weewx/csv.conf
--config=/home/weewx/weewx.conf --verbose
source field 'ANComment' as text --> WeeWX field 'ANComment'
source field 'timestamp' in units 'unix_epoch' --> WeeWX field 'dateTime'
source field 'extratemp2' in units 'degree_C' --> WeeWX field 'extraTemp2'
source field 'extratemp1' in units 'degree_C' --> WeeWX field 'extraTemp1'
Raw import data read successfully for period 1.
Mapping raw import data for period 1 ...
Mapped 1 records.
Raw import data mapped successfully for period 1.
Saving mapped data to archive for period 1 ...
1 records identified for import.
Proceeding will save all imported records in the WeeWX archive.
Are you sure you want to proceed (y/n)? y
**** could not convert string to float: test of wee_import text updates
**** Nothing done, exiting.
====== source file '/var/tmp/weewxaddnotes.csv' contents...
timestamp,extratemp1,extratemp2,ANComment
1645966620,11.0,11.5,test of wee_import text updates
**** could not convert string to float: test of wee_import text updates
===== full terminal output...
12:19 PM $ sudo bin/wee_import --import-config=/home/weewx/csv.conf
--config=/home/weewx/weewx.conf --verbose
Using WeeWX configuration file /home/weewx/weewx.conf
Starting wee_import...
A CSV import from source file '/var/tmp/weewxaddnotes.csv' has been requested.
The following options will be used:
config=/home/weewx/weewx.conf, import-config=/home/weewx/csv.conf
source=/var/tmp/weewxaddnotes.csv, from=None, to=None
dry-run=None, calc_missing=True, ignore_invalid_data=True
Starting wee_import...
A CSV import from source file '/var/tmp/weewxaddnotes.csv' has been requested.
The following options will be used:
config=/home/weewx/weewx.conf, import-config=/home/weewx/csv.conf
source=/var/tmp/weewxaddnotes.csv, from=None, to=None
tranche=250, interval=conf, date/time_string_format=%Y-%m-%d %H:%M:%S
delimiter=',', rain=cumulative, wind_direction=[-360.0, 360.0]
UV=False, radiation=False
Using database binding 'wx_binding', which is bound to database 'weewx.sdb'
Destination table 'archive' unit system is '0x10' (METRIC).
Missing derived observations will be calculated.
All WeeWX UV fields will be set to None.
All WeeWX radiation fields will be set to None.
Starting import ...
delimiter=',', rain=cumulative, wind_direction=[-360.0, 360.0]
UV=False, radiation=False
Using database binding 'wx_binding', which is bound to database 'weewx.sdb'
Destination table 'archive' unit system is '0x10' (METRIC).
Missing derived observations will be calculated.
All WeeWX UV fields will be set to None.
All WeeWX radiation fields will be set to None.
Obtaining raw import data for period 1 ...
The following imported field-to-WeeWX field map will be used:
source field 'ANComment' as text --> WeeWX field 'ANComment'
source field 'timestamp' in units 'unix_epoch' --> WeeWX field 'dateTime'
source field 'extratemp2' in units 'degree_C' --> WeeWX field 'extraTemp2'
source field 'extratemp1' in units 'degree_C' --> WeeWX field 'extraTemp1'
Raw import data read successfully for period 1.
Mapping raw import data for period 1 ...
Mapped 1 records.
Raw import data mapped successfully for period 1.
Saving mapped data to archive for period 1 ...
1 records identified for import.
Proceeding will save all imported records in the WeeWX archive.
Are you sure you want to proceed (y/n)? y
**** could not convert string to float: test of wee_import text updates
**** Nothing done, exiting.
====== source file '/var/tmp/weewxaddnotes.csv' contents...
timestamp,extratemp1,extratemp2,ANComment
1645966620,11.0,11.5,test of wee_import text updates
> --
> 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/030ad74a-557c-465f-a7c3-8d24a46b7773n%40googlegroups.com.
> 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
gjr80
Mar 5, 2022, 6:08:42 AM3/5/22
to weewx-user
Apologies Glenn, try this version. I'm in between moving from linux to Mac on my desktop and my test environment is not quite complete so that last version had some cursory testing only. I've fixed that now and have tested this version under 4.5.1 and it imported and saved csv data with text fields without error.
Gary
Glenn McKechnie
Mar 5, 2022, 6:41:59 AM3/5/22
to weewx...@googlegroups.com
Gary, That one works a treat.
On 05/03/2022, gjr80 wrote:
> Apologies Glenn, try this version. I'm in between moving from linux to Mac
> on my desktop and my test environment is not quite complete so that last
> version had some cursory testing only.
Not a problem.
And thanks for your time getting it sorted.
On 05/03/2022, gjr80 wrote:
> Apologies Glenn, try this version. I'm in between moving from linux to Mac
> on my desktop and my test environment is not quite complete so that last
> version had some cursory testing only.
And thanks for your time getting it sorted.
Reply all
Reply to author
Forward
0 new messages