Here is the process I use to convert my wview to weewx. The word None and
possibly some special characters are in the wview archive table. The
following process will clean up those columns.
1. copy wview-archive.sdb to weewx.sdb
2. run the attached script weewx-wviewCleanup.sql. It will change those
values to NULL. This change the command below to the appropriate paths.
sqlite3 -init PATH_TO_FILE/weewx_wviewCleanup.sql PATH_TO_DATABSE/weewx.sdb.
3. run wee_database again.
If it fails, run wee_database --string-check PATH_TO_CONFIG/weewx.conf
if that fails, note the error. If the error identifies a column has
None in it, modify the weewx_wviewCleanup.sql script to null out that
column and run it again.
That process resolved my issues.
After the database has been converted by wee_database, you may want to run
the script zkwx_wviewHiLow.py. It reads the view-hilow.sdb data base.
It reads most of the tables and identifies the max(high) and min(low) for
each day. It updates the appropriate archive_day_XXX table in weewx.sdb.
It doesn't touch rain and rainRate. I wasn't sure how I should total them,
so I'm using the weewx calculation. The script requires python3. See
disclaimer 2.
to run it
zkwx_wviewHiLow.py -f PATH_TO/wview-hilow.sdb -t PATH_TO/weewx.sdb
Disclaimers:
1. Use at your own risk
2. I don't know python. If you (or anyone) looks at the code and asks
themselves why did he do that, the answer is I found an example and made it
work.
Good Luck,
Oscar
On Tuesday, December 27, 2016 at 7:07:02 PM UTC-7, Punch Card wrote:
> So I think I have gotten the configuration file correct where it is seeing
> the weather station.
>
> But it seems that there is some errant data in my wview sdb. Also I tried
> the *" wee_database --string-check"* there seem to be only one row in the
> data that had an issue, but it does not look like it corrected the row.
> Is there a way to start weewx in a debug mode so I can find the errant
> data and either correct the data, or just delete the row.
> Or do you have another suggestion?
>
> [root@weewx]# wee_database --string-check
>> Using configuration file /etc/weewx/weewx.conf
>> Using database binding 'wx_binding', which is bound to database
>> 'archive_sqlite'
>> Checking archive database for strings...
>> Timestamp = 1347032100; record['rain']= u'0\n0'; ... ignored.
>> [root@weewx]#
>>
>
> Last /var/log/messages for weewx attempt
>
> Dec 27 08:43:10 md-centos systemd: Stopping SYSV: start and stop the weewx
> weather system...
> Dec 27 08:43:10 md-centos weewx: Shutting down weewx: [FAILED]
> Dec 27 08:43:10 md-centos systemd: Stopped SYSV: start and stop the weewx
> weather system.
> Dec 27 09:32:16 md-centos systemd: Starting SYSV: start and stop the weewx
> weather system...
> Dec 27 09:32:16 md-centos weewx[31695]: engine: Initializing weewx version
> 3.6.2
> Dec 27 09:32:16 md-centos weewx[31695]: engine: Using Python 2.7.5
> (default, Nov 6 2016, 00:28:07) #012[GCC 4.8.5 20150623 (Red Hat 4.8.5-11)]
> Dec 27 09:32:16 md-centos weewx[31695]: engine: Platform
> Linux-3.10.0-514.2.2.el7.x86_64-x86_64-with-centos-7.3.1611-Core
> Dec 27 09:32:16 md-centos weewx[31695]: engine: pid file is
> /var/run/weewx.pid
> Dec 27 09:32:16 md-centos weewx[31699]: engine: Using configuration file
> /etc/weewx/weewx.conf
> Dec 27 09:32:16 md-centos weewx[31699]: engine: Loading station type
> WMR200 (weewx.drivers.wmr200)
> Dec 27 09:32:16 md-centos weewx: Starting weewx: [ OK ]
> Dec 27 09:32:16 md-centos systemd: Started SYSV: start and stop the weewx
> weather system.
> Dec 27 09:32:16 md-centos weewx[31699]: wmr200: MainThread: I Created
> watchdog thread to poke for live data every 30 seconds
> Dec 27 09:32:16 md-centos weewx[31699]: wmr200: MainThread: I Created USB
> polling thread to read block on device
> Dec 27 09:32:16 md-centos weewx[31699]: wmr200: Thread-2: I USB polling
> device thread for live data launched
> Dec 27 09:32:16 md-centos weewx[31699]: wmr200: MainThread: I Reset
> console device
> Dec 27 09:32:17 md-centos weewx[31699]: wmr200: Thread-2: I USB polling
> device thread signaled to start
> Dec 27 09:32:17 md-centos weewx[31699]: engine: StdConvert target unit is
> 0x1
> Dec 27 09:32:17 md-centos weewx[31699]: wxcalculate: The following values
> will be calculated: barometer=prefer_hardware, windchill=prefer_hardware,
> dewpoint=prefer_hardware, appTemp=prefer_hardware,
> rainRate=prefer_hardware, windrun=prefer_hardware,
> heatindex=prefer_hardware, maxSolarRad=prefer_hardware,
> humidex=prefer_hardware, pressure=prefer_hardware,
> inDewpoint=prefer_hardware, ET=prefer_hardware, altimeter=prefer_hardware,
> cloudbase=prefer_hardware
> Dec 27 09:32:17 md-centos weewx[31699]: wxcalculate: The following
> algorithms will be used for calculations: altimeter=aaNOAA, maxSolarRad=RS
> Dec 27 09:32:17 md-centos weewx[31699]: engine: Archive will use data
> binding wx_binding
> Dec 27 09:32:17 md-centos weewx[31699]: engine: Record generation will be
> attempted in 'hardware'
> Dec 27 09:32:17 md-centos weewx[31699]: engine: Using archive interval of
> 300 seconds (specified by hardware)
> Dec 27 09:32:17 md-centos weewx[31699]: wmr200: Thread-1: I Started
> watchdog thread live data
> Dec 27 09:32:17 md-centos weewx[31699]: engine: Using binding 'wx_binding'
> to database 'weewx.sdb'
> Dec 27 09:32:17 md-centos weewx[31699]: manager: Starting backfill of
> daily summaries
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: Thread-2: I USB polling
> device thread exiting
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: MainThread: I USB polling
> thread expired
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: Thread-1: I Watchdog
> received shutdown
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: Thread-1: I Watchdog
> thread exiting
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: MainThread: I Watchdog
> thread expired
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: MainThread: I Received
> packet count live:0 archive:0 control:0
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: MainThread: I Received
> bytes:8 sent bytes:16
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: MainThread: I Packet
> archive queue len:0 live queue len:0
> Dec 27 09:32:18 md-centos weewx[31699]: wmr200: MainThread: I Driver
> gracefully exiting
> Dec 27 09:32:18 md-centos weewx[31699]: engine: Caught unrecoverable
> exception in engine:
> Dec 27 09:32:18 md-centos weewx[31699]: **** unsupported operand
> type(s) for +=: 'float' and 'unicode'
> Dec 27 09:32:18 md-centos weewx[31699]: **** Traceback (most recent
> call last):
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/engine.py", line 841, in main
> Dec 27 09:32:18 md-centos weewx[31699]: **** engine =
> engine_class(config_dict)
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/engine.py", line 76, in __init__
> Dec 27 09:32:18 md-centos weewx[31699]: ****
> self.loadServices(config_dict)
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/engine.py", line 140, in loadServices
> Dec 27 09:32:18 md-centos weewx[31699]: ****
> self.service_obj.append(weeutil.weeutil._get_object(svc)(self, config_dict))
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/engine.py", line 486, in __init__
> Dec 27 09:32:18 md-centos weewx[31699]: ****
> self.setup_database(config_dict)
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/engine.py", line 588, in setup_database
> Dec 27 09:32:18 md-centos weewx[31699]: **** _nrecs, _ndays =
> dbmanager.backfill_day_summary() # @UnusedVariable
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/manager.py", line 1374, in backfill_day_summary
> Dec 27 09:32:18 md-centos weewx[31699]: ****
> _day_accum.addRecord(_rec)
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/accum.py", line 240, in addRecord
> Dec 27 09:32:18 md-centos weewx[31699]: **** func(self, record,
> obs_type, add_hilo)
> Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/accum.py", line 310, in add_value
> Dec 27 09:32:18 md-centos weewx[31699]: ****
> self[obs_type].addSum(val)
>
>
>
> *Dec 27 09:32:18 md-centos weewx[31699]: **** File
> "/usr/share/weewx/weewx/accum.py", line 87, in addSumDec 27 09:32:18
> md-centos weewx[31699]: **** self.sum += valDec 27 09:32:18
> md-centos weewx[31699]: **** TypeError: unsupported operand type(s) for
> +=: 'float' and 'unicode'Dec 27 09:32:18 md-centos weewx[31699]: ****
> Exiting.*
> Dec 27 09:32:18 md-centos python: detected unhandled Python exception in
> '/usr/bin/weewxd'
> Dec 27 09:32:55 md-centos systemd: Stopping SYSV: start and stop the weewx
> weather system...
> Dec 27 09:32:55 md-centos weewx: Shutting down weewx: [FAILED]
> Dec 27 09:32:55 md-centos systemd: Stopped SYSV: start and stop the weewx
> weather system.
>
>
>
> Thanks for your assistance in advance.
> Regards,
> Mike
>
>
--
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].
For more options, visit https://groups.google.com/d/optout.
--******************************************************************************
-- File Name: weewx_wviewCleanup.sql
-- Description: This script will correct any '%None%' type of bad data in wview
-- tables.
--
-- *** Revision History ***
-- DATE AUTHOR COMMENT
-- 2016-12-02 obarlow Create initial script
--******************************************************************************
.echo on
pragma count_changes=1;
update archive
set extraTemp1 = NULL
where extraTemp1 like '%None%';
update archive
set extraTemp2 = NULL
where extraTemp2 like '%None%';
update archive
set extraTemp3 = NULL
where extraTemp3 like '%None%';
update archive
set soilTemp1 = NULL
where soilTemp1 like '%None%';
update archive
set soilTemp2 = NULL
where soilTemp2 like '%None%';
update archive
set soilTemp3 = NULL
where soilTemp3 like '%None%';
update archive
set soilTemp4 = NULL
where soilTemp4 like '%None%';
update archive
set leafTemp1 = NULL
where leafTemp1 like '%None%';
update archive
set leafTemp2 = NULL
where leafTemp2 like '%None%';
update archive
set extraHumid1 = NULL
where extraHumid1 like '%None%';
update archive
set extraHumid2 = NULL
where extraHumid2 like '%None%';
update archive
set soilMoist1 = NULL
where soilMoist1 like '%None%';
update archive
set soilMoist2 = NULL
where soilMoist2 like '%None%';
update archive
set soilMoist3 = NULL
where soilMoist3 like '%None%';
update archive
set soilMoist4 = NULL
where soilMoist4 like '%None%';
update archive
set leafWet1 = NULL
where leafWet1 like '%None%';
update archive
set leafWet2 = NULL
where leafWet2 like '%None%';
update archive
set hail = NULL
where hail like '%None%';
update archive
set hailRate = NULL
where hailRate like '%None%';
update archive
set heatingTemp = NULL
where heatingTemp like '%None%';
update archive
set heatingVoltage = NULL
where heatingVoltage like '%None%';
update archive
set supplyVoltage = NULL
where supplyVoltage like '%None%';
update archive
set referenceVoltage = NULL
where referenceVoltage like '%None%';
update archive
set windBatteryStatus = NULL
where windBatteryStatus like '%None%';
update archive
set rainBatteryStatus = NULL
where rainBatteryStatus like '%None%';
update archive
set outTempBatteryStatus = NULL
where outTempBatteryStatus like '%None%';
update archive
set inTempBatteryStatus = NULL
where inTempBatteryStatus like '%None%';
update archive
set windGustDir = NULL
where windGustDir like '%None%';
update archive
set windDir = NULL
where windDir like '%None%';
update archive
set outTemp = NULL
where outTemp like '%None%';
update archive
set dewpoint = NULL
where dewpoint like '%None%';
update archive
set windchill = NULL
where windchill like '%None%';
update archive
set heatindex = NULL
where heatindex like '%None%';
-- zero's from here
--update archive
-- set barometer = NULL
-- where barometer like '%None%';
--
--update archive
-- set pressure = NULL
-- where pressure like '%None%';
--
--update archive
-- set altimeter = NULL
-- where altimeter like '%None%';
--
--update archive
-- set inTemp = NULL
-- where inTemp like '%None%';
--
--update archive
-- set inHumidity = NULL
-- where inHumidity like '%None%';
--
--update archive
-- set outHumidity = NULL
-- where outHumidity like '%None%';
--
--update archive
-- set windSpeed = NULL
-- where windSpeed like '%None%';
--
--update archive
-- set dewpoint = NULL
-- where dewpoint like '%None%';
--
--update archive
-- set radiation = NULL
-- where radiation like '%None%';
--
--update archive
-- set UV = NULL
-- where UV like '%None%';
.echo off
.quit
#!/usr/bin/python3
################################################################################
# Filename: zkwx_wviewHiLow.py
# Description: This script reads most of the tables in wview-hiLow.sdb and
# gets the max(high) and min(low) for each day. It updates the MAX and LOW
# columns in the appropriate archive_day_XXX table.
#
### Revision Histiory ###
# Date Author Comment
# 2016-12-06 obarlow Create initial script
################################################################################
# IMPORT libraries
import datetime
import getopt
import os.path
import sqlite3
import sys
import time
################################################################################
# define Variables
################################################################################
debugLevel = 0
sourceDB = ''
targetDB = ''
################################################################################
# load Table List
################################################################################
def load_table_list( wviewList, weewxList ):
wviewList.append('ET')
weewxList.append('archive_day_ET')
wviewList.append('UV')
weewxList.append('archive_day_UV')
wviewList.append('baromPressure')
weewxList.append('archive_day_barometer')
wviewList.append('heatIndex')
weewxList.append('archive_day_heatindex')
wviewList.append('inHumidity')
weewxList.append('archive_day_inHumidity')
wviewList.append('inTemp')
weewxList.append('archive_day_inTemp')
wviewList.append('outHumidity')
weewxList.append('archive_day_outHumidity')
wviewList.append('outTemp')
weewxList.append('archive_day_outTemp')
# wviewList.append('rain')
# weewxList.append('archive_day_rain')
# wviewList.append('rainRate')
# weewxList.append('archive_day_rainRate')
wviewList.append('solarRadiation')
weewxList.append('archive_day_radiation')
wviewList.append('windChill')
weewxList.append('archive_day_windChill')
wviewList.append('windGust')
weewxList.append('archive_day_windGust')
wviewList.append('windSpeed')
weewxList.append('archive_day_windSpeed')
################################################################################
# print help
################################################################################
def print_help():
print ("Format is:")
print ("zkwx_wviewHiLow.py -f wviewHiLowDataBase -t weewxDataBase")
print ("")
################################################################################
# Connect to a data base
################################################################################
def connect_db( sqliteDB, readOnly ):
connection = None
try:
if readOnly == True:
# connection = sqlite3.connect(sqliteDB + '?mode=ro')
connection = sqlite3.connect(sqliteDB)
else:
connection = sqlite3.connect(sqliteDB)
return connection
except sqlite3.IntegrityError:
print ("Error opening DB %s:" % sqliteDB)
print ("Error %s:" % args[0])
sys.exit(1)
################################################################################
# Execute sql
################################################################################
def execute_sql( connection, sqliteSQL ):
try:
cursor = connection.cursor()
cursor.execute(sqliteSQL)
return cursor
except sqlite3.IntegrityError:
print ("Error SQL %s:" % sqliteSQL)
print ("Error %s:" % args[0])
sys.exit(1)
################################################################################
# get parameters
################################################################################
try:
opts, args = getopt.getopt(sys.argv[1:],"f:t:rh")
except getopt.GetoptError:
print_help()
sys.exit(2)
if opts:
for opt, arg in opts:
if opt == '-f':
sourceDB = arg
elif opt == '-h':
print_help()
sys.exit(0)
elif opt == '-t':
targetDB = arg
else:
print ("Unknown Parm:" + opt)
sys.exit(2)
fromError = False
toError = False
if os.path.isfile(sourceDB):
a = 1
else:
fromError = True
if os.path.isfile(targetDB):
a = 1
else:
toError = True
if fromError or toError:
if fromError:
print ("FROM file not found: " + sourceDB)
if toError:
print ("TO file not found: " + targetDB)
sys.exit(2)
else:
print_help()
sys.exit(1)
################################################################################
# Main Process starts here
################################################################################
try:
commitCounter = 0;
commitMax = 1000;
sourceCon = connect_db( sourceDB, True )
targetCon = connect_db( targetDB, False )
wv_tableList = []
wx_tableList = []
load_table_list ( wv_tableList, wx_tableList)
i = 0;
while i < len(wv_tableList):
commitCounter = 0
rowCounter = 0
print(wv_tableList[i] + " MaxRows=" + str(rowCounter),end='\r')
if ( wv_tableList[i] == 'ET'
or wv_tableList[i] == 'UV'
):
highSQL = "select myDay, TimeHigh, maxHigh from "
highSQL = highSQL + "(select strftime('%Y%m%d',dateTime,'unixepoch','localtime') "
highSQL = highSQL + "myDay, timeHigh, max(high) maxHigh "
highSQL = highSQL + "from " + wv_tableList[i] + " group by 1 "
highSQL = highSQL + ") where maxHigh > 0"
lowSQL = "None"
else:
highSQL = "select strftime('%Y%m%d',dateTime,'unixepoch','localtime') myDay "
highSQL = highSQL + ", timeHigh, max(high) maxHigh "
highSQL = highSQL + "from " + wv_tableList[i] + " group by 1"
lowSQL = "select strftime('%Y%m%d',dateTime,'unixepoch','localtime') myDay "
lowSQL = lowSQL + ", timeLow, min(low) minLow "
lowSQL = lowSQL + "from " + wv_tableList[i] + " group by 1"
tableCur = execute_sql ( sourceCon, highSQL )
summList = tableCur.fetchall()
if debugLevel > 1:
print ("highSQL=" + highSQL)
print ("lowSQL=" + lowSQL)
for myRow in summList:
myDay = myRow[0]
myTime = myRow[1]
myValue = myRow[2]
if debugLevel > 0:
print ("Day=" + str(myDay))
print ("Time=" + str(myTime))
print ("Value=" + str(myValue))
epoch = int(time.mktime(time.strptime(myDay, '%Y%m%d')))
updSQL = "UPDATE " + wx_tableList[i]
updSQL = updSQL + " set maxtime = '" + str(myTime) + "'"
updSQL = updSQL + " , max = '" + str(myValue)+ "'"
updSQL = updSQL + " where dateTime = '" + str(epoch) + "'"
if debugLevel > 4:
print ("updSQL=" + updSQL)
targetCon.execute ( updSQL )
rowsUpdated = 1
if rowsUpdated > 1:
print("Too Many Rows Updated: " + str(rowsUpdated))
sys.exit(1)
if rowsUpdated <= 0:
print("No Rows Updated: " + str(rowsUpdated))
sys.exit(1)
commitCounter = commitCounter + rowsUpdated
rowCounter = rowCounter + rowsUpdated
if commitCounter >= commitMax:
commitCounter = 0
print(wv_tableList[i] + " MaxRows=" + str(rowCounter),end='\r')
targetCon.commit;
# end for myRow in summList (HIGH)
print(wv_tableList[i] + " MaxRows=" + str(rowCounter))
targetCon.commit()
rowCounter = 0
commitCounter = 0
if lowSQL != "None":
print(wv_tableList[i] + " MinRows=" + str(rowCounter),end='\r')
tableCur = execute_sql ( sourceCon, lowSQL )
summList = tableCur.fetchall()
for myRow in summList:
myDay = myRow[0]
myTime = myRow[1]
myValue = myRow[2]
epoch = int(time.mktime(time.strptime(myDay, '%Y%m%d')))
updSQL = "UPDATE " + wx_tableList[i]
updSQL = updSQL + " set mintime = '" + str(myTime) + "'"
updSQL = updSQL + " , min = '" + str(myValue) + "'"
updSQL = updSQL + " where dateTime = '" + str(epoch) + "'"
if debugLevel > 4:
print("updSQL=" + updSQL)
targetCon.execute ( updSQL )
rowsUpdated = 1
if rowsUpdated > 1:
print("Too Many Rows Updated: " + str(rowsUpdated))
sys.exit(1)
if rowsUpdated <= 0:
print("No Rows Updated: " + str(rowsUpdated))
sys.exit(1)
commitCounter = commitCounter + rowsUpdated
rowCounter = rowCounter + rowsUpdated
if commitCounter >= commitMax:
targetCon.commit()
commitCounter = 0
print(wv_tableList[i] + " MinRows=" + str(rowCounter),end='\r')
targetCon.commit()
print(wv_tableList[i] + " MinRows=" + str(rowCounter))
i = i + 1
sourceCon.close()
targetCon.close()
# end of wviewDB in dbList
except sqlite3.IntegrityError:
print ("Error %s:" % args[0])
print ("dateTime=" + myDateTime)
sys.exit(1)
