KeyError and TypeError in def write_to_sql (run_sql.py) when loading PCT line type data to METviewer database

[heu3becteh]

Hello!

I have encountered problem with database loading of PCT line type data:

DEBUG:root:[--- Start write_stat_data ---]
INFO:root:New headers: 0 rows
INFO:root:PCT: 18 rows
DEBUG:root:next_line_id is 36
ERROR:root:*** <class 'TypeError'> in run_sql write_to_sql ***
ERROR:root:*** <class 'KeyError'> in run_sql write_to_sql ***
INFO:root: >>> Write time Stat: 0:00:00.108345
DEBUG:root:[--- End write_stat_data ---]

The problem with KeyError: it tries to access date/lead time columns on lines 148-157 of run_sql.py, but there is no such data for line_data_pct_thresh table. raw_data for line_data_pct_thresh looks like that:

,line_data_id,i_value,thresh_i,oy_i,on_i
0,0,1,0,0,0
1,0,2,0.1,0,0
2,0,3,0.2,0,0
3,0,4,0.3,0,0
4,0,5,0.4,0,0
5,0,6,0.5,0,0
6,0,7,0.6,0,0
7,0,8,0.7,0,0
8,0,9,0.8,0,0
9,0,10,0.9,25,0
[...]

There are no fcst_valid_beg, fcst_valid_end, fcst_init_beg, obs_valid_beg, obs_valid_end, fcst_valid, fcst_init, obs_valid.

To load this data I have added a check:

try:
  raw_data_has_dates=True
  print(raw_data['fcst_valid_beg'])
except:
  raw_data_has_dates=False

at the beginning of def write_to_sql, and then have added condition if raw_data_has_dates for lines 148-157.
I guess the condition on line 148 (if 'line_data' in sql_table) should be changed, as line_data_pct_thresh has no timestamps.

The problem with TypeError is related to sql_query being not adequate for line_data_pct.
sql_query is

INSERT INTO line_data_pct (stat_header_id,data_file_id,line_num,fcst_lead,fcst_valid_beg,fcst_valid_end,fcst_init_beg,obs_lead,obs_valid_beg,obs_valid_end,cov_thresh,total,n_thresh) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)

but the data in dfile has other format, which leads to an error on line 160 of run_sql.py.
Data in dfile looks like that:

0, 2, 13.0, 16, 120000, '2000-01-23 15:00:00', '2000-01-23 15:00:00', '2000-01-23 03:00:00', 0, '2000-01-23 13:30:00', '2000-01-23 16:30:00', '-9999', '25', 10.0

It is 14 values with the first being line_data_id. But the default sql_query has 13 values without line_data_id, so I have changed it to:
sql_query="INSERT INTO line_data_pct (line_data_id, stat_header_id,data_file_id,line_num,fcst_lead,fcst_valid_beg,fcst_valid_end,fcst_init_beg,obs_lead,obs_valid_beg,obs_valid_end,cov_thresh,total,n_thresh) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
which allowed to load the data.

There were similar issues with other lines too, but did not look into it much for now.
The fixes I have made are quick and dirty, but I assume that issue exists not only for myself and should be addressed?

Best regards,
Alexander

[jprestop]

Hi @heu3becteh. My apologies for a delayed response. I'm going to tag a couple of colleagues here with hope that one of them can help answer your questions soon. @venitahagerty @TatianaBurek @bikegeek

[TatianaBurek]

Hi @heu3becteh
I was able successfully load PCT line type data into the database using the latest METdataio v2.0.2 with required Python 3.10 and Pandas 1.5.1.
Could you please provide your version of METdataio , the Python environment and the example of the MET output file with PCT data so we can use it for debugging.

[heu3becteh]

Thank you for the reply!

I attach the log from METdbload Version: version = "2.0.2".
load_data_preview_PCT.log

We had 2.0.1 version, installation of METviewer and all related packages from 22.02.2023 when we got this problem first, but updating METdataio to v2.0.2 did not help.
We use MET-main_v11.0, METplus-main_v5.0 to produce our scores.

I also attach our python environment packages. We have Python 3.8.16 according to https://metviewer.readthedocs.io/en/latest/Users_Guide/installation.html (Python 3.8.6 or higher), pandas 1.5.1.
pip.list.txt

Example data stat-file is produced from pct-file to avoid filtering of other line types, I believe that is not a problem.
load_data_preview_fromPCT.stat.txt
load_data_preview_PCT.xml.txt

Best regards,
Alexander

[TatianaBurek]

Alexander
I loaded data from the .stat file that you provided to the database successfully and without any error messages.
I use:
METdbload Version: version = "2.0.2"
Python 3.8.6
PyMySQL 1.0.2
PyYAML 6.0
lxml 4.9.1
numpy 1.23.5
pandas 1.5.1