update weewx-database

Hartmut Schweidler

unread,

Mar 4, 2017, 10:35:58 AM3/4/17

to weewx-development

Hallo,

Where is my error

from weewx-wd >>> weewx-wd / bin / user / weewxwd_config



i use to update  "maxSolarRad", "cloudbase", "windRun" and so on

def clear_v2_data(config_dict, db_binding_wx):
    """ Clear any legacy humidex and apparent temperature data from the 
        Weewx (not Weewx-WD) database.
    
        Under Weewx v2.x Weewx-WD stored humidex and apparent temperature 
        data in the Weewx archive in fields extraTemp1 and extratemp2 
        respectively. Under Weewx v3 Weewx-WD now stores this data in a 
        separate database and hence this legacy data can be removed from 
        the Weewx database.

        Parameters:
            config_dict: a dictionary of the weewx.conf settings
            db_binding_wx: binding for Weewx database
            
        Returns:
            Nothing.
    """
    ...
    # we do so go ahead and clear them
    for _rec in dbmanager_wx.genBatchRecords(start_ts - 1, stop_ts):
             dbmanager_wx.updateValue(_rec['dateTime'], 'extraTemp1', None)
             dbmanager_wx.updateValue(_rec['dateTime'], 'extraTemp2', None)
             nrecs += 1
    # all done, say so and give some stats
    print "Done. 'extraTemp1' and 'extraTemp2' cleared in %d records (approx %d days)." %(nrecs, ndays)
    ...

i have the line "dbmanager_wx.updateValue(_rec['dateTime'], 'extraTemp1', None)"

replaced by "dbmanager_wx.updateValue(_rec['dateTime'], 'maxSolarRad', msr) "

my plan is:
"""

msr = weewx.wxformulas.solar_rad_Bras(53.605963, 11.341407, 53, _rec['dateTime'], 2)
dbmanager_wx.updateValue(_rec['dateTime'], 'maxSolarRad', msr)

if _rec['outTemp'] <> 0 and _rec['outHumidity'] <> 0:
       cb1 = weewx.wxformulas.cloudbase_Metric(_rec['outTemp'], _rec['outHumidity'], 53)
else:
        cb1 = 0
dbmanager_wx.updateValue(_rec['dateTime'], 'cloudbase', cb1)

if _rec['windSpeed'] > 0:
         vt = (_rec['windSpeed'], "mile_per_hour", "group_speed")
         ws_kts = weewx.units.convert(vt, "knot")[0]
         be2 = ws_kts * 5.0 / 60.0
else:
         be2 = 0.0
dbmanager_wx.updateValue(_rec['dateTime'], 'windrun', be2)

"""

The data "msr, cb1 and be2" are calculated correctly.

The calculated data is not written to the database "Weewx database" to record "dateTime"

sorry my english is not so good

Hartmut

gjr80

unread,

Mar 5, 2017, 9:04:51 AM3/5/17

to weewx-development

Hi,

If I understand you correctly, you wish to backfill your weeWX archive table with calculated maxSolarRad, cloudbase and windrun values? If this is the case that is a novel piece of code reuse. First up, it appears that you have taken the code from weewxwd_config, which only exists in the weewx-WD 1.2.0_development branch. That code was last touched in late 2015 and was never put into use, so it may have some issue. That being said, you appear to be only using a small piece of the code that looks like it should have no issues.

The code you are using was originally developed to update existing weeWX archive fields extraTemp1 and extraTemp2. These fields are part of the default archive table schema used by weeWX and the key point here is that the code updates data in existing fields. Fields maxSolarRad, cloudbase and windrun are not part of the default weeWX archive table schema. Before you can set/store any values in these fields you need to add them to you archive table schema. Have you done that? The process is described in the Customization Guide, in particular the Add a new type to the archive database section.

Also, a few comments on your code.

if _rec['outTemp'] <> 0 and _rec['outHumidity'] <> 0:
cb1 = weewx.wxformulas.cloudbase_Metric(_rec['outTemp'], _rec['outHumidity'], 53)
else:
cb1 = 0
dbmanager_wx.updateValue(_rec['dateTime'], 'cloudbase', cb1)

The cloudbase_Metric() function requires temperature in Celsius, what unit system does your database use METRIC, METRICWX? If so your outTemp field will be in Celsius but if you are using the US unit system then it will be in Fahrenheit. Your code may be returning the correct cloudbase if your database is using METRIC or METRICWX but it will not be if your database is using US units. Your code could be more robust by using the weeWX unit conversion capabilities to always get the temperature in Celsius irrespective of the database units in use. I think the check that outTemp is not 0 and outHumidity is not 0 can be removed (for example, your code will never calculate a cloudbase value when outTemp=0). The cloudbase_Metric() function has the necessary protections to handle all possible outTemp and outHumidity values. The resulting code could look something like this (not tested):

# get the outTemp units and group used in the record
(t, g) = weewx.units.getStandardUnitType(_rec['usUnits'], 'outTemp')
# get _rec outTemp as a ValueTuple 
temp_vt = weewx.units.ValueTuple(_rec['outTemp'], t, g)
# now get temperature in Celsius
temp_c = weewx.units.convert(temp_vt, 'degree_C').value
# calculate cloudbase, the result will be in meters
cb_m = weewx.wxformulas.cloudbase_Metric(temp_c, _rec['outHumidity'], 53)
# get the calculated cloudbase as a ValueTuple, it will now be an altitude in m
cb_m_vt = weewx.units.ValueTuple(cb_m, 'meter', 'group_altitude')
# convert the calculated cloudbase to the altitude units used in _rec
cb1 = weewx.units.convertStd(cb_m_vt, _rec['usUnits']).value
# update the archive cloudbase value
dbmanager_wx.updateValue(_rec['dateTime'], 'cloudbase', cb1)

if _rec['windSpeed'] > 0:
         vt = (_rec['windSpeed'], "mile_per_hour", "group_speed")
         ws_kts = weewx.units.convert(vt, "knot")[0]
         be2 = ws_kts * 5.0 / 60.0
else:
         be2 = 0.0
dbmanager_wx.updateValue(_rec['dateTime'], 'windrun', be2)

Again I think you may be be taking a risk in terms of units. You imply that windSpeed is in miles per hour (that does not align with your cloudbase code where you assumed outTemp was in Celsius - Celsius and miles per hour do not co-exist in any of the three weeWX unit systems), then covert to knots to calculate windrun which is saved back to your archive where you have already assumed your speeds are in miles per hour (which implies you distance units would be miles). Also you don't need to worry about the check for windSpeed > 0, the maths will take care of that, far better to check for windSpeed is not None as that condition will certainly cause issues for your calculations. You might want to try something like (again not tested):

if _rec['windSpeed'] is not None:
    # get the windSpeed units and group used in _rec
    (t, g) = weewx.units.getStandardUnitType(_rec['usUnits'], 'windSpeed')
    # get the _rec windSpeed as a ValueTuple 
    ws_vt = weewx.units.ValueTuple(_rec['windSpeed'], t, g)
    # now convert windSpeed to a known unit, say km per hour
    ws_kmh = weewx.units.convert(ws_vt, 'km_per_hour').value
    # calculate windrun, it will be a result in km
    be2_km = ws_kmh * 5.0/60
    # get the calculated windrun as a ValueTuple, it will now be a distance in km
    be2_km_vt = weewx.units.ValueTuple(be2_km, 'km', 'group_distance')
    # convert the calculated windrun to the distance units used in _rec
    be2 = weewx.units.convertStd(be2_km_vt, _rec['usUnits']).value
else:
    # if windSpeed is None then so is windrun, no need for any unit conversions
    be2 = None
# update the archive windrun value

dbmanager_wx.updateValue(_rec['dateTime'], 'windrun', be2)

The above code will require you to import weewx.units if you are not already doing so ie:

import weewx.units

One of the keys things to remember when manipulating your data is to be cognisant of the units of the source data you are manipulating as well as the units of your result - this is particularly so if pulling data from the archive. You may happen to get some right by chance (for example cloudbase if you are using METRIC or METRICWX) but you will eventually get some wrong too.

Gary

Hartmut Schweidler

unread,

Mar 5, 2017, 11:54:23 AM3/5/17

to weewx-development

Hello and thanks for the hints,
My data is not written to the database.

The function UPDATE

  "dbmanager_wx.updateValue(_rec['dateTime'], 'windrun', be2)"
updateValue from weewx

does not work

I've tested the function weewxwd_config --copy-v2-data successfully last night and it works

quelle example weewxwd3.py
#===============================================================================
#                           Class WdGenerateDerived
#===============================================================================

class WdGenerateDerived(object):
    """ Generator wrapper. Adds Weewx-WD derived obs to the output of the
        wrapped generator.
    """

    def __init__(self, input_generator):
        """ Initialize an instance of WdGenerateDerived

            input_generator: An iterator which will return dictionary records.
        """
        self.input_generator = input_generator

    def __iter__(self):
        return self

    def next(self):
        # get our next record
        _rec = self.input_generator.next()
        _rec_mwx = weewx.units.to_METRIC(_rec)
        _rec_mwx['dateTime'] = _rec_mwx['dateTime']
       _rec_mwx['usUnits'] = _rec_mwx['usUnits']
       _rec_mwx['interval'] = _rec_mwx['interval']

....
        _rec_mwx['barometer'] = _rec_mwx['barometer']
        _rec_mwx['pressure'] = _rec_mwx['pressure']
        _rec_mwx['altimeter'] = _rec_mwx['altimeter']
        _rec_mwx['inTemp'] = _rec_mwx['inTemp']
        _rec_mwx['outTemp'] = _rec_mwx['outTemp']
        _rec_mwx['inHumidity'] = _rec_mwx['inHumidity']
        _rec_mwx['outHumidity'] = _rec_mwx['outHumidity']
        _rec_mwx['windSpeed'] = _rec_mwx['windSpeed']
        _rec_mwx['windDir'] = _rec_mwx['windDir']
        _rec_mwx['windGust'] = _rec_mwx['windGust']
        _rec_mwx['windGustDir'] = _rec_mwx['windGustDir']
        _rec_mwx['rainRate'] = _rec_mwx['rainRate']
        _rec_mwx['rain'] = _rec_mwx['rain']
        _rec_mwx['heatindex'] = _rec_mwx['heatindex']
        _rec_mwx['ET'] = _rec_mwx['ET']
        _rec_mwx['radiation'] = _rec_mwx['radiation']
        _rec_mwx['UV'] = _rec_mwx['UV']
        _rec_mwx['extraTemp1'] = _rec_mwx['extraTemp1']
        _rec_mwx['extraTemp2'] = _rec_mwx['extraTemp2']
        _rec_mwx['extraTemp3'] = _rec_mwx['extraTemp3']
        _rec_mwx['soilTempO1'] = _rec_mwx['soilTempO1']
        _rec_mwx['soilTempO2'] = _rec_mwx['soilTempO2']
        _rec_mwx['soilTempO3'] = _rec_mwx['soilTempO3']
        _rec_mwx['soilTempO4'] = _rec_mwx['soilTempO4']
        _rec_mwx['soilTempO5'] = _rec_mwx['soilTempO5']
        _rec_mwx['leafTemp1'] = _rec_mwx['leafTemp1']
        _rec_mwx['leafTemp2'] = _rec_mwx['leafTemp2']
        _rec_mwx['extraHumid1'] = _rec_mwx['extraHumid1']
        _rec_mwx['extraHumid2'] = _rec_mwx['extraHumid2']
        _rec_mwx['soilMoist1'] = _rec_mwx['soilMoist1']
        _rec_mwx['soilMoist2'] = _rec_mwx['soilMoist2']
        _rec_mwx['soilMoist3'] = _rec_mwx['soilMoist3']
        _rec_mwx['soilMoist4'] = _rec_mwx['soilMoist4']
        .....
            #_rec_mwx['maxSolarRad'] = _rec_mwx['maxSolarRad']
        #_rec_mwx['cloudbase'] = _rec_mwx['cloudbase']

        _rec_mwx['maxSolarRad'] = weewx.wxformulas.solar_rad_Bras(53.605963, 11.341407, 53, _rec_mwx['dateTime'], 2)

        if 'outTemp' in _rec_mwx and 'outHumidity' in _rec_mwx:
            _rec_mwx['cloudbase'] = weewx.wxformulas.cloudbase_Metric(_rec_mwx['outTemp'], _rec_mwx['outHumidity'], 53)
        else:
            _rec_mwx['cloudbase'] = None

        if 'windSpeed' in _rec_mwx and 'windSpeed' > 0.0:
            _rec_mwx['windrun'] = _rec_mwx['windSpeed'] * 5.0 / 60.0
        else:
            _rec_mwx['windrun'] = 0.0

        #_rec_mwx['inDewpoint'] = _rec_mwx['inDewpoint']
        if 'inTemp' in _rec_mwx and 'inHumidity' in _rec_mwx:
            _rec_mwx['inDewpoint'] = weewx.wxformulas.dewpointC(_rec_mwx['inTemp'], _rec_mwx['inHumidity'])
        else:
            _rec_mwx['inDewpoint'] = None

        #_rec_mwx['inTempBatteryStatus'] = _rec_mwx['inTempBatteryStatus']
        #_rec_mwx['sunshineS'] = _rec_mwx['sunshineS']
        if 'radiation' in _rec_mwx:
           _rec_mwx['sunshineS'] = weewx.wxformulas.sunhes(_rec_mwx['radiation'], _rec_mwx['dateTime'])
           #_rec_mwx['sunshinehours'] = _rec_mwx['sunshineS'] / 3600.0
        else:
           _rec_mwx['sunshineS'] = None
           #_rec_mwx['sunshinehours'] = None
        _rec_mwx['snow'] = _rec_mwx['snow']
        _rec_mwx['snowRate'] = _rec_mwx['snowRate']
        ....
       if 'outTemp' in _rec_mwx:
            _rec_mwx['outTempDay'], _rec_mwx['outTempNight'] = weewx.wxformulas.daynighttempC(_rec_mwx['outTemp'], _rec_mwx['dateTime'])
        else:
            _rec_mwx['outTempDay'], _rec_mwx['outTempNight'] = (None, None)

        data_x = weewx.units.to_std_system(_rec_mwx, _rec_mwx['usUnits'])
        # return our modified record
        return data_x

basis "wx_binding" and target "wd_binding" like schema wx_binding

have created a copy of the weewx database and simultaneously copied the original data weewx and calculated with the function

weewxwd_config --copy-v2-data
root@acer:/home/weewx/bin/user# ./weewxwd_config.py --copy-v2-data
Using configuration file /home/weewx/weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_mysql'
Using database binding 'wd_binding', which is bound to database 'wd_mysql'
341033 records have been identified to backfill database 'weewxacerWD' from 2013-10-31 22:18:59 CET (1383254339) to 2017-03-04 11:04:59 CET (1488621899) (approx 1220 days). This may take some time (hours) to complete.
Are you sure you wish to proceed (y/n)? y
Processing 341033 records...
341033 record(s) over 1 period(s) covering approximately 1220 day(s) processed in 17:04:03.

But I would like to update the data to the old database

My database is only METRIC

Hartmut

gjr80

unread,

Mar 5, 2017, 4:07:52 PM3/5/17

to weewx-development

Hi,

Let's wind things back a bit. A couple of questions:
1. What are you trying to do? Are you trying to calculate maxSolarRad, cloudbase and windrun for historical data in you archive?
2. Have you added fields maxSolarRad, cloudbase and windrun to your archive schema as I mentioned in my previous post?

Gary

Hartmut Schweidler

unread,

Mar 6, 2017, 1:11:02 AM3/6/17

to weewx-development

Hallo

1. I have 89 fields in my database

Am Sonntag, 5. März 2017 22:07:52 UTC+1 schrieb gjr80:

Hi,
Let's wind things back a bit. A couple of questions:
1. What are you trying to do? Are you trying to calculate maxSolarRad, cloudbase and windrun for historical data in you archive?

YES I would like to insert values like maxSolarRad, windrun and other into the old database "weewx"

2. Have you added fields maxSolarRad, cloudbase and windrun to your archive schema as I mentioned in my previous post?

Yes, have a lock at https://github.com/hes19073/hesweewx/blob/master/bin/schemas
My structure of the database under https://github.com/hes19073/hesweewx/blob/master/bin/schemas/schemas.py

Gary

I would like to calculate and enter the additional values for the old data.
I used the code from weewx wd.
The idea to remove old values from the database like "extraTemp1" and "extraTemp2" was a first step.
The data are calculated with the call of the functions from "weewx" .
It is easier to back up a database
I hope I have made myself clear.

gjr80

unread,

Mar 6, 2017, 1:33:52 AM3/6/17

to weewx-development

On Monday, 6 March 2017 16:11:02 UTC+10, Hartmut Schweidler wrote:

YES I would like to insert values like maxSolarRad, windrun and other into the old database "weewx"

Good, we both have a common understanding of what you are trying to do.

1. I have 89 fields in my database

Ok, 89 fields, that is impressive. I understand your schema is defined in schemas.py but there is a difference between having the schema defined in schemas.py and actually having the fields included in your archive table. WeeWX uses schemas.py when first creating your archive table and then it is largely ignored. So if you created your database/archive table and then changed schemas.py your changes will not necessarily be refected in your archive table. There is an easy way to check, you appear to be using mySQL so lets get into MySQL and have a look at the archive table schema that is in use and see what is there. Use the following commands to first get into the mySQL client and then look at the archive table schema (assumes your mySQL user is root and your database is archive_mysql, change as required to suit your setup):

$ mysql -u root -p

mysql> USE archive_mysql
mysql> DESC archive;

Post the output from DESC archive;. Does it show maxSolarRad, cloudbase and windrun? If so that is fine, if not then you need to follow the steps I outlined in my first post to add these fields to your archive table.

Gary

Hartmut Schweidler

unread,

Mar 7, 2017, 12:49:10 AM3/7/17

to weewx-development

Hallo Gary,

"""
For "first Time in weewx-database" to "last Time in weewx-database" step 1:

    cursor.execute("UPDATE archive SET maxSolarRad = %s WHERE dateTime = %s",    ("123.98", "1394092770"))       
    cursor.execute("UPDATE archive SET windRun = %s WHERE dateTime = %s",   ("233.08", "1394092770"))
    cursor.execute("UPDATE archive SET cdIndex = %s WHERE dateTime = %s",    ("45.7", "1394092770"))   
    
"""
Hartmut

gjr80

unread,

Mar 7, 2017, 1:02:15 AM3/7/17

to weewx-development

Hallo Hartmut,

Good. You definitely have the 3 fields in your archive.

I have just re-read your earlier posts to see where we are at and now I am a little confused. Am I correct in believing you have two databases? Are you now trying to copy maxSolarRad, cloudbase and windrun data from one database to the other? Sorry, but is important to understand exactly what you have and what you are trying to do so that I don't give you incorrect advice.

By the way, don't worry that your English is not good, my German is not good either!

Gary

Hartmut Schweidler

unread,

Mar 7, 2017, 1:17:44 AM3/7/17

to weewx-development

Good morning from Germany

I would like to "add" and/or "update" the new values to the historical database "archive".

If calculated values are missing these should be added
If calculated values are wrong these must be corrected

Hartmut
homepage: http://wetter.hes61.de

gjr80

unread,

Mar 7, 2017, 1:33:08 AM3/7/17

to weewx-development

Ok, since the 3 fields are all calculated fields the easiest thing to do will be to recalculate and save them all (if you need to check existing field values you will need to calculate the value anyway so you might as well calculate it and save it in your archive, that will simplify the code).

So far you have posted various code extracts. Can you attach the file containing the modified python code you are running, I appreciate this may seem like overkill, but it is easier to see the code in its entirety rather than pieces at a time. Also, what command did you enter to run the code? Finally, can you post the [DataBindings] and [Databases] sections from weewx.conf, I am sure that at some stage we will need to make sure we are accessing the right database.

Gary

Message has been deleted

Hartmut Schweidler

unread,

Mar 7, 2017, 2:13:46 AM3/7/17

to weewx-development

Hallo Gary,

Not to scold

The values are calculated but they are not found in the database

from weewxwd_config.py i call weewxwd_config --clear-v2-data

weewx.conf

weewxwd_config.py

gjr80

unread,

Mar 7, 2017, 2:40:36 AM3/7/17

to weewx-development

Hallo Harmut,

One final thing, could you please confirm what weeWX version you are using. Your site says 3.7.1 and your weewx.conf say 3.7.2. But 3.7.0 has not yet been released, are you using 3.6.2 (the latest release) or one of the 3.7.0 betas?

I will sit down tonite and work my way through the code.

Gary

Hartmut Schweidler

unread,

Mar 7, 2017, 3:01:17 AM3/7/17

to weewx-development

hallo Gary,

i am using weewx development 3.7.0b2 and Own extensions in wxformulars
and

die Datei (units.py),
die Datei (wxformulas.py),
sowie die Datei (tags.py )

Ein Beispiel zur Ermittlung der Anzahl der Tage wenn 'min' größer ist als
nach:

bin/weewx/tags.py

def max_ge(self, val):

      return self._do_query('max_ge', val=val)

zur Ermittlung der Tage ergänzt

def min_ge(self, val):
return self._do_query('min_ge', val=val)

eingefügt.

bin/weewx/manager.py


      'max_ge': "SELECT SUM(max >= %(val)s) FROM %(table_name)s_day_%(obs_key)s 
WHERE dateTime >= %(start)s AND dateTime < %(stop)s",

   'min_ge': "SELECT SUM(min >= %(val)s) FROM %(table_name)s_day_%(obs_key)s 
WHERE dateTime >= %(start)s AND dateTime < %(stop)s",



            elif aggregate_type in ['mintime', 'maxmintime', 'maxtime', 'minmaxtime', 'maxsumtime',

                                    'count', 'max_ge', 'max_le', 'min_ge', 'min_le', 'sum_ge']:

bin/weewx/units.py

       'max_ge'     : "group_count",

             'max_le'     : "group_count",

             'min_ge'     : "group_count",

These are uncritical since they have been functioning correctly for several months

The only problem is the new update function and the writing of the values in the database

Hartmut

Hartmut Schweidler

unread,

Mar 7, 2017, 3:06:39 AM3/7/17

to weewx-development

Supplement
have a look at https://github.com/hes19073/hesweewx

Hartmut Schweidler

unread,

Mar 7, 2017, 3:15:47 AM3/7/17

to weewx-development

in the line
print "weewx_copy_fill: ", "record %d unter %s tag" %(nrecs, msr) """

delete the >>> """ <<<

sorry
Hartmut

gjr80

unread,

Mar 7, 2017, 3:28:17 AM3/7/17

to weewx-development

Hallo Hartmut,

So removing the """ fixed the problem? That was the first thing I noticed when I opened the file but I had not yet had a chance to look through the rest of the code. I don't know what editor you are using but an editor that has syntax highlighting is very good for picking up these sorts of errors - as soon as I opened you file in Notepad++ the extra """ was very easy to see.

At least there was a good outcome.

Gary

PS. That is a smart looking site you have.

Hartmut Schweidler

unread,

Mar 7, 2017, 4:33:25 AM3/7/17

to weewx-development

Hallo Gary,

Another error in the weewx.conf

    # MySQL
    [[archive_mysql]]
        database_type = MySQL
        #database_name = weewxacerWD it is false
        database_name = weewxacer

    [[wx3080_mysql]]
        database_type = MySQL
        database_name = weewx3080

    [[vantage_mysql]]
        database_type = MySQL
        database_name = weewx

    [[wd_mysql]]
        database_type = MySQL
        #database_name = weewxacer
        database_name = weewxacerWD

Sorry, but I have already experimented so much

I had the function weewxwd_config --copy-v2-data

"""
weewxwd_config --copy-v2-data
root@acer:/home/weewx/bin/user# ./weewxwd_config.py --copy-v2-data
Using configuration file /home/weewx/weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_mysql'
Using database binding 'wd_binding', which is bound to database 'wd_mysql'
341033 records have been identified to backfill database 'weewxacerWD' from 2013-10-31 22:18:59 CET (1383254339) to 2017-03-04 11:04:59 CET (1488621899) (approx 1220 days). This may take some time (hours) to complete.
Are you sure you wish to proceed (y/n)? y
Processing 341033 records...
341033 record(s) over 1 period(s) covering approximately 1220 day(s) processed in 17:04:03. """"

and i have use the weewxacerWD as weewxacer database for Experiments

Result should be UPDATE only single data in the database

sorry, sorry

Hartmut

my editor is NANO i have no Window only debian

Hartmut Schweidler

unread,

Mar 7, 2017, 5:07:41 AM3/7/17

to weewx-development

Hallo Gary,

summary

Hartmut

test.txt

weewx.conf

weewxwd_config.py

gjr80

unread,

Mar 7, 2017, 5:43:57 AM3/7/17

to weewx-development

Hallo Hartmut,

So I see data has appeared in the maxSolarRad daily summary(archive_day_maxSolarRad), that implies there must be maxSolarRad data in your archive. So is everything as you expect or is there still a problem?

Gary

Hartmut Schweidler

unread,

Mar 7, 2017, 6:30:57 AM3/7/17

to weewx-development

Hallo Gary,

So it should look after the complete update
But the UPDATE does not work yet.
The entry of the values in the database is missing

"in the headline of def clear_v2_data

Clear any legacy humidex and apparent temperature data from the
Weewx (not Weewx-WD) database."

I want to replace the clear by update

I have same code elements from you wd adjusted for me

Thank you for this great work and also Thank all the others

Hartmut

gjr80

unread,

Mar 7, 2017, 8:19:23 AM3/7/17

to weewx-development

Hallo Harmut,

Don't worry about clear_v2_data() mentioning 'clear' rather than 'update'. The original code 'cleared' fields extraTemp1 and extraTemp2 by writing NULL to each field. So if you like it updated each field by saving NULL in each field. You have modified the code to update different fields and instead of updating the field with NULL you are updating with a calculated value. The key part is the use of the updateValue method (in dbmanager_wx.updateValue(_rec['dateTime'], 'maxSolarRad', msr)).

I think the code is working as expected and updating the archive table.

table "archive_day_maxSolarRad" after UPDATE 
dateTime 	min 	mintime 	max 			maxtime 	sum 			count 	wsum 			sumtime
1383174000 	0 	1383254340 	0 			1383254340 	0 			21 	0 			6300
1383260400 	0 	1383260640 	295.461855977661 	1383303541 	18656.2263424185 	288 	5596867.90272548 	86400
1383346800 	0 	1383347041 	289.813642518552 	1383389941 	18163.9155725552 	288 	5449174.67176655 	86400
1383433200 	0 	1383433441 	284.239023967007 	1383476341 	17683.3831545144 	287 	5305014.94635428 	86400

The above tells me that table archive_day_maxSolarRad has data and that data can only have come from the maxSolarRad field in the archive table. Timestamp 1383174000 is from 31 October 2013 which appears to be the first day of your data. So at some stage the command has worked and put maxSolarRad data in an archive (maybe not the archive you want). Let's run the code again and logically follow/check the data. If we use the weewx.conf and weewxwd_config.py you last posted then running the command:

$ weewxwd_config --clear-v2-data

would use the binding wx_binding which would use database archive_mysql which uses MySQL database weewxacer. If this is the MySQL database you want to update continue. If not, you will need to use another binding by adding the --wx-binding=BINDING_NAME option to specify another binding name and thus use another database. For example

$ weewxwd_config --clear-v2-data --wx-binding=wx3080_binding

would result in MySQL databse weewx3080 being used. After you have run the above command what is in MySQL database weewxacer? (Assuming you stuck with wx_binding ie MySQL database weewxacer)

$ mysql -u root -

p

mysql> USE weewxacer
mysql> SELECT dateTime,maxSolarRad FROM archive ORDER BY dateTime DESC LIMIT 10;

This should show you the dateTime and maxSolarRad fields from the 10 most recent records. What does it display? maxSolarRad could be 0 if this is run at night time, change '10' to something like '150' to look at around 12 hours or so of data.

The following will show the first 10 records. What does this show, your data starts around 10PM on 31 October 2013 so again thismight show you 0 only. You can increase the '10' to somethign like '150' to see what happens over 12 hours or so.

mysql> SELECT dateTime,maxSolarRad FROM archive ORDER BY dateTime ASC LIMIT 10;


Gary

Hartmut Schweidler

unread,

Mar 7, 2017, 10:47:11 AM3/7/17

to weewx-development

Hallo Gary,

1. I have created a copy of weewxacer database

2. the function record_generator = WdGenerateDerived (dbmanager_wx.genBatchRecords (start_ts - 1, stop_ts)) changed

3. weewxwd3.py WdGenerateDerived habe a look at weewxwd3.py

4. I have run the procedere from weew wd

https://bitbucket.org/ozgreg/weewx-wd/src/110688ada0c69c43912a93725976091278c2bb60/?at=v1.2.0_development

    - create the Weewx-WD database and archive table:

        weewxwd_config --create-archive   (((weewxacerWD))))

    - check if there is any legacy data to copy, or if any historical Weewx-WD data can be reconstructed:

        weewxwd_config --status

    - copy any legacy data or reconstruct any historical Weewx-WD data:

        weewxwd_config --copy-v2-data    (((from weewxacer to weewxacerWD)))

After 17 hours I had a copy of the weewxacer as weewxacerWD database
the weewxacer database the old
the weewxacerWD one kopie of the old weewxacer-database and the newly calculated values 

all TEST TEST TEST
Only for the calculation of about 300 values I will not take my original weather database 17 hours offline.

17 hours offline  at the davis should not be a problem 

however the o-wire temperature, water-temperature-sensors does not store any data
I am looking for a simple update solution

for example

#!/usr/bin/python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import sys
try:
    conn = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');
For "first Time in weewx-database" to "last Time in weewx-database" step 1:
    cursor = conn.cursor()
    cursor.execute("UPDATE archive SET maxSolarRad = %s WHERE dateTime = %s",    ("123.98", "1394092770"))       
    cursor.execute("UPDATE archive SET windRun = %s WHERE dateTime = %s",   ("233.08", "1394092770"))
    cursor.execute("UPDATE archive SET cdIndex = %s WHERE dateTime = %s",    ("45.7", "1394092770"))

and so on..

    conn.commit()
    cursor.close()
    conn.close()
"""

Format-compliant to weewx as a updateDB.py

hartmut

weewxwd3.py

gjr80

unread,

Mar 9, 2017, 12:39:26 AM3/9/17

to weewx-development

Hallo Hartmut,

My apologies have been otherwise busy. I think you are making this much harder than it need be.If you have a database, let's call it database A, to which you want to add historical maxSolarRad data then the following should work:
1. make a backup of database A in case things go badly wrong
2. add the field maxSolarRad to the archive table in database A
3. run the modified --clear-v2-data to calculate maxSolarRad for each record in the database A archive table. Use the --wd-binding=xxxxxxx, where xxxxxxx is the binding in weewx.conf for database A.
4. drop then rebuild the daily summaries

There is no need to make copies of database A with --create-archive and/or --copy-v2-data. You should be able to run the modified --clear-v2-data whilst weeWX continues to run as it will mainly only be accessing historical archive table records and I doubt it or weeWX will run into database lock issues unless the both happen to try to modify the current (ie latest) record at the same time.

Step 1 can be done with mysqldump, its covered in the first step in this wiki entry. I believe you have already done step 2. You have already created the modified --clear-v2-data, it is just a case of using it with the correct binding for database A. The above steps could be expanded to add cloudbase and windrun by adding the extra fields at step2 (again I believe you have already done this) and uncommenting a few lines in your already modified --clear-v2-data code. Step 4 is the last thing we do once all the data is in the archive.

--clear-v2-data may take a long time to complete but since weeWX continues to run it should not result in any lost data.

Gary

Message has been deleted

Hartmut Schweidler

unread,

Mar 9, 2017, 12:27:04 PM3/9/17

to weewx-development

It is missing a call which closes the function update (dbmanager_wx.updateValue) and writes to the database

for example --> finally: _cursor.close()

Am Donnerstag, 9. März 2017 17:08:54 UTC+1 schrieb Hartmut Schweidler:

Hallo Gary,

You do not have to apologize for anything.

I have already done this several times

Right now runs the next attempt instead of outTemp1 with NULL I want to replace maxSolarRad with values.
Simply

# we do so go ahead and clear them
for _rec in dbmanager_wx.genBatchRecords(start_ts - 1, stop_ts):

msr = weewx.wxformulas.solar_rad_Bras(53.605963, 11.341407, 53, _rec['dateTime'], 2)

dbmanager_wx.updateValue(_rec['dateTime'], 'maxSolarRad', msr)

For security, I just leave the current values per print on the screen issue.
In addition, weewx runs normally
result: "cleared in 350828 records (approx 1225 days)" on screen
weewx_copy_fill: record 350825 unter 128.238500608 tag
weewx_copy_fill: record 350826 unter 117.421041055 tag
weewx_copy_fill: record 350827 unter 106.746424888 tag
an in database NULL, NULL, NULL ....
the funktion from manager:
    def updateValue(self, timestamp, obs_type, new_value):
        """Update (replace) a single value in the database."""

        self.connection.execute("UPDATE %s SET %s=? WHERE dateTime=?" %
                                (self.table_name, obs_type), (new_value, timestamp))

Is called but nothing appears in the database

Hartmut

Hartmut Schweidler

unread,

Mar 11, 2017, 1:09:05 AM3/11/17

to weewx-development

Hallo,

Supplement
The function to delete the values 'extraTemp1' and ' extraTemp2' does not work.

if ans == 'y':

# we do so go ahead and clear them
for _rec in dbmanager_wx.genBatchRecords(start_ts - 1, stop_ts):

            dbmanager_wx.updateValue(_rec['dateTime'], 'extraTemp1', None)
            dbmanager_wx.updateValue(_rec['dateTime'], 'extraTemp2', None)
            nrecs += 1
           # all done, say so and give some stats

Hartmut

It is missing a call closes the function update (dbmanager_wx.updateValue) and writes to the database

gjr80

unread,

Mar 11, 2017, 2:06:00 AM3/11/17

to weewx-development

Hallo Hartmut,

We must be doing something wrong somewhere because the code works for what it was intended.

It is missing a call which closes the function update (dbmanager_wx.updateValue) and writes to the database

for example --> finally: _cursor.close()

The connection is closed when we exit the with statement in def clear_v2_data():

with weewx.manager.open_manager_with_config(config_dict, db_binding_wx) as dbmanager_wx:

dbmanager_wx is an object of type class Manager() and when we exit the above with statement the Manager() __exit__ method is called which handles the closure (have a look in bin/weewx/manager.py)

I think there has been quite a few changes since we started on this problem. I would like to sit down with the code you are using and try it on a machine here and likely put some logging into it so we can see exactly what is going on. Can you please post:

1. the python file you are running (modified weewxwd_config ?)
2. the contents of the [DataBindings], [Databases] and [DatabaseTypes] sections of weewx.conf
3. the exact command you typed at the command line that did not work

This code is very straightforward, there will be something equally basic that is causing the issue.

Gary

Hartmut Schweidler

unread,

Mar 11, 2017, 7:20:41 AM3/11/17

to weewx-development

Hallo Gary,

i use the original weewx-wd / bin / user / weewxwd_config


Version: 1.2.0b2


#!/usr/bin/env python
#
# This program is free software; you can redistribute it and/or modify it under
# the terms of the GNU General Public License as published by the Free Software
# Foundation; either version 2 of the License, or (at your option) any later
# version.
#
# This program is distributed in the hope that it will be useful, but WITHOUT 
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
# FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
# details.
#
# Version: 1.2.0b2                                  Date: 5 September 2015
#
# Revision History
#  ?? September 2015  v1.2.0    - Initial implementation, based upon portions of
#                                 weewxwd3.py
#
"""Manage the databases used by Weewx-WD"""
from __future__ import with_statement
############################################

to clear extraTemp1 and extraTemp2 from weewxAlt (current weewx database)

#######################################
my weewx.conf
[DataBindings]
    [[wx_binding]]
        # The database must match one of the sections in [Databases].
        # This is likely to be the only option you would want to change.
        database = archive_mysql
        # The name of the table within the database
        table_name = archive
        # The manager handles aggregation of data for historical summaries
        manager = weewx.wxmanager.WXDaySummaryManager
        # The schema defines the structure of the database.
        # It is *only* used when the database is created.
        schema = schemas.schemas.schema

    [[wd_binding]]
        # The database must match one of the sections in [Databases]
        database = wd_mysql
        table_name = archive
        manager = weewx.wxmanager.WXDaySummaryManager
        schema = schemas.wdSchema.weewxwd_schema

##############################################
and for weewxwd database weewxAltWD

root@acer:/home/weewx/bin/user# weewxwd_config --clear-v2-data --wx-binding=wx_binding
-bash: weewxwd_config: Kommando nicht gefunden.
root@acer:/home/weewx/bin/user# ./weewxwd_config.py --clear-v2-data --wx-binding=wx_binding

Using configuration file /home/weewx/weewx.conf
Using database binding 'wx_binding', which is bound to database 'archive_mysql'
Using database binding 'wd_binding', which is bound to database 'wd_mysql'

'extraTemp1' and 'extraTemp2' data in database 'weewxAlt' from 2013-10-31 22:19:00 CET (1383254340) to 2017-03-11 12:50:00 CET (1489233000) (approx 1227 days) is about to be cleared.
Any data in these fields will be irretrievably lost.

Are you sure you wish to proceed (y/n)? y

Done. 'extraTemp1' and 'extraTemp2' cleared in 351357 records (approx 1227 days).
root@acer:/home/weewx/bin/user#

copy by phpmyadmin
Server: localhost »

Datenbank: weewxAlt »

Tabelle: archive

SELECT `dateTime`, `usUnits`, `interval`, `extraTemp1`, `extraTemp2` FROM `archive` WHERE `extraTemp1` >0;

dateTime