hydroffice.ssp.ssp_db

In [1]:

    

from __future__ import absolute_import, division, print_function, unicode_literals

from hydroffice.ssp.ssp_db import SspDb


    

In [2]:

    

import logging

logger = logging.getLogger()
logger.setLevel(logging.NOTSET)
ch = logging.StreamHandler()
ch.setLevel(logging.DEBUG)  # change to WARNING to reduce verbosity, DEBUG for high verbosity
ch_formatter = logging.Formatter('%(levelname)-9s %(name)s.%(funcName)s:%(lineno)d > %(message)s')
ch.setFormatter(ch_formatter)
logger.addHandler(ch)


    

In [14]:

    

# Read a profile

import os

from hydroffice.ssp.cast_reader import CastReader
from hydroffice.ssp.helper import Helper
from hydroffice.ssp.ssp_dicts import Dicts

rdr = CastReader()
test_file = os.path.join(Helper.get_samples_folder(), "sippican", "C3_00172.EDF")
rdr.read_cast(test_file, Dicts.import_formats["SIPPICAN"])

# Create a database

db_file = os.path.abspath(os.path.join(os.getcwd(), "test.db"))
print("DB file: %s" % db_file)
test_db = SspDb(db_path=db_file)

# Add a cast
ret0 = test_db.add_casts(rdr.casts)
print("added cast: %s" % ret0)


    

    




DEBUG:hydroffice.ssp.cast_reader:Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF [5]
DEBUG     hydroffice.ssp.cast_reader._run_fmt_load:55 > Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF [5]
INFO:hydroffice.ssp.drivers.sippican.sippican:reading ...
INFO      hydroffice.ssp.drivers.sippican.sippican.__init__:36 > reading ...
INFO:hydroffice.ssp.drivers.sippican.sippican:reading Sippican > header
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:44 > reading Sippican > header
INFO:hydroffice.ssp.drivers.sippican.sippican:date: 10 / 21 / 2012
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:85 > date: 10 / 21 / 2012
INFO:hydroffice.ssp.drivers.sippican.sippican:time: 1 : 39 : 22
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:93 > time: 1 : 39 : 22
INFO:hydroffice.ssp.drivers.sippican.sippican:lat: 71.6977701667
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:105 > lat: 71.6977701667
INFO:hydroffice.ssp.drivers.sippican.sippican:long: 155.092529333
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:120 > long: 155.092529333
INFO:hydroffice.ssp.drivers.sippican.sippican:probe type: 6
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:154 > probe type: 6
INFO:hydroffice.ssp.drivers.sippican.sippican:filename: Files\Sippican\WinMK21\DATA\C3_00172.RDF
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:131 > filename: Files\Sippican\WinMK21\DATA\C3_00172.RDF
INFO:hydroffice.ssp.drivers.sippican.sippican:samples offset: 61
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:69 > samples offset: 61
INFO:hydroffice.ssp.drivers.sippican.sippican:max samples: 2931
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:173 > max samples: 2931
INFO:hydroffice.ssp.drivers.sippican.sippican:sensor type: 6
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:183 > sensor type: 6
INFO:hydroffice.ssp.drivers.sippican.sippican:var alpha: False
INFO      hydroffice.ssp.drivers.sippican.sippican._read_header:184 > var alpha: False
INFO:hydroffice.ssp.drivers.sippican.sippican:reading > body
INFO      hydroffice.ssp.drivers.sippican.sippican._read_body:187 > reading > body
INFO:hydroffice.ssp.ssp:raw: - Date Time: 2012-10-21 01:39:22
- Position: -155.092529333 71.6977701667
- Original Path: Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF
- Sensor Type: 6
- Probe Type: 17
- Driver: SPC.0.2.0
- Samples: 1465
0.00 1444.88 1.76 25.75
0.10 1447.77 1.84 27.67
0.30 1448.23 1.89 27.85
0.40 1448.98 1.91 28.35
0.50 1449.36 1.93 28.57
.... .... .... .... ....
199.00 1435.22 -1.05 25.93

INFO      hydroffice.ssp.ssp.read_input_file_by_format:91 > raw: - Date Time: 2012-10-21 01:39:22
- Position: -155.092529333 71.6977701667
- Original Path: Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF
- Sensor Type: 6
- Probe Type: 17
- Driver: SPC.0.2.0
- Samples: 1465
0.00 1444.88 1.76 25.75
0.10 1447.77 1.84 27.67
0.30 1448.23 1.89 27.85
0.40 1448.98 1.91 28.35
0.50 1449.36 1.93 28.57
.... .... .... .... ....
199.00 1435.22 -1.05 25.93

INFO:hydroffice.ssp.ssp:resulting processed data size: (6, 1465)
INFO      hydroffice.ssp.ssp.set_samples:120 > resulting processed data size: (6, 1465)
INFO:hydroffice.ssp.ssp:storing raw data
INFO      hydroffice.ssp.ssp.store_raw:133 > storing raw data
INFO:hydroffice.base.base_objects:Connected
INFO      hydroffice.base.base_objects.reconnect_or_create:121 > Connected
INFO:hydroffice.ssp.ssp_db:foreign keys active
INFO      hydroffice.ssp.ssp_db.build_tables:78 > foreign keys active
INFO:hydroffice.ssp.ssp_db:got a new SSP to store:
- Date Time: 2012-10-21 01:39:22
- Position: -155.092529333, 71.6977701667
- Survey Name: default
- Original Path: Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF
- Sensor Type: 6
- Source String: XCTD-1
- Driver: SPC.0.2.0
- Samples: (6, 1465)
- Raw data: (6, 1465)
0.00 1444.88 1.76 25.75 0 0
0.10 1447.77 1.84 27.67 0 0
0.30 1448.23 1.89 27.85 0 0
0.40 1448.98 1.91 28.35 0 0
0.50 1449.36 1.93 28.57 0 0
.... .... .... .... ....
199.00 1435.22 -1.05 25.93 0 0

INFO      hydroffice.ssp.ssp_db.add_casts:177 > got a new SSP to store:
- Date Time: 2012-10-21 01:39:22
- Position: -155.092529333, 71.6977701667
- Survey Name: default
- Original Path: Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF
- Sensor Type: 6
- Source String: XCTD-1
- Driver: SPC.0.2.0
- Samples: (6, 1465)
- Raw data: (6, 1465)
0.00 1444.88 1.76 25.75 0 0
0.10 1447.77 1.84 27.67 0 0
0.30 1448.23 1.89 27.85 0 0
0.40 1448.98 1.91 28.35 0 0
0.50 1449.36 1.93 28.57 0 0
.... .... .... .... ....
199.00 1435.22 -1.05 25.93 0 0

INFO:hydroffice.ssp.ssp_db:found 1 survey named default
INFO      hydroffice.ssp.ssp_db._add_survey_if_missing:217 > found 1 survey named default
INFO:hydroffice.ssp.ssp_db:add new spp pk for 2012-10-21 01:39:22 @ (-155.092529;71.697770)
INFO      hydroffice.ssp.ssp_db._get_ssp_pk:249 > add new spp pk for 2012-10-21 01:39:22 @ (-155.092529;71.697770)
INFO:hydroffice.ssp.ssp_db:spp pk: 1
INFO      hydroffice.ssp.ssp_db._get_ssp_pk:264 > spp pk: 1
INFO:hydroffice.ssp.ssp_db:deleted 1 pk entries from raw_samples
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:278 > deleted 1 pk entries from raw_samples
INFO:hydroffice.ssp.ssp_db:deleted 1 pk entries from mod_samples
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:286 > deleted 1 pk entries from mod_samples
INFO:hydroffice.ssp.ssp_db:deleted 1 pk entries from sis_samples
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:294 > deleted 1 pk entries from sis_samples
INFO:hydroffice.ssp.ssp_db:deleted 1 pk entry from ssp
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:302 > deleted 1 pk entry from ssp
INFO:hydroffice.ssp.ssp_db:insert new 1 pk in ssp
INFO      hydroffice.ssp.ssp_db._add_ssp:330 > insert new 1 pk in ssp
INFO:hydroffice.ssp.ssp_db:max raw samples to add: 1465
INFO      hydroffice.ssp.ssp_db._add_ssp_raw_samples:341 > max raw samples to add: 1465
INFO:hydroffice.ssp.ssp_db:added 1465 raw samples
INFO      hydroffice.ssp.ssp_db._add_ssp_raw_samples:368 > added 1465 raw samples
INFO:hydroffice.ssp.ssp_db:max processed samples to add: 1465
INFO      hydroffice.ssp.ssp_db._add_ssp_mod_samples:374 > max processed samples to add: 1465
INFO:hydroffice.ssp.ssp_db:added 1465 processed samples
INFO      hydroffice.ssp.ssp_db._add_ssp_mod_samples:402 > added 1465 processed samples






    




DB file: Z:\_py\__hyo_dev\hyo_ssp\examples\test.db
added cast: True

In [6]:

    

ret0 = test_db.list_all_ssp_pks()
print(len(ret0))

ret0 = test_db.list_ssp_pks_by_survey_name()
print(len(ret0))


    

    




INFO:hydroffice.ssp.ssp_db:get casts by survey name: default
INFO      hydroffice.ssp.ssp_db.list_ssp_pks_by_survey_name:465 > get casts by survey name: default






    




1
1

In [7]:

    

if len(ret0) > 0:
     ssp_data = test_db.get_ssp_by_pk(ret0[0][0])
     print(ssp_data)


    

    




INFO:hydroffice.ssp.ssp_db:get ssp by primary key: 1
INFO      hydroffice.ssp.ssp_db.get_ssp_by_pk:487 > get ssp by primary key: 1
INFO:hydroffice.ssp.ssp:resulting raw data size: (6, 1465)
INFO      hydroffice.ssp.ssp.set_raw_samples:115 > resulting raw data size: (6, 1465)
INFO:hydroffice.ssp.ssp:resulting processed data size: (6, 1465)
INFO      hydroffice.ssp.ssp.set_samples:120 > resulting processed data size: (6, 1465)
INFO:hydroffice.ssp.ssp:resulting sis data size: (6, 0)
INFO      hydroffice.ssp.ssp.set_sis_samples:110 > resulting sis data size: (6, 0)






    




raw samples: 1465
samples: 1465
sis samples: 0
- Date Time: 2012-10-21 01:39:22
- Position: -155.092529, 71.69777
- Survey Name: default
- Original Path: Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF
- Sensor Type: 6
- Source String: XCTD-1
- Driver: SPC.0.2.0
- Samples: (6, 1465)
- Raw data: (6, 1465)
- Sis data: (6, 0)
0.00 1444.88 1.76 25.75 0 0
0.10 1447.77 1.84 27.67 0 0
0.30 1448.23 1.89 27.85 0 0
0.40 1448.98 1.91 28.35 0 0
0.50 1449.36 1.93 28.57 0 0
.... .... .... .... ....
199.00 1435.22 -1.05 25.93 0 0

In [23]:

    

print("\ntotal ssp rows: %s" % test_db.check_table_total_rows('ssp', True))


    

    




---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-23-8d562770bcc7> in <module>()
----> 1 print("\ntotal ssp rows: %s" % test_db.check_table_total_rows('ssp', True))

Z:\_py\__hyo_dev\hyo_base\hydroffice\base\base_objects.pyc in check_table_total_rows(self, table_name, print_out)
     61         table_name = BaseDbObject.clean_name(table_name)
     62 
---> 63         for ret in self.conn.execute('SELECT COUNT(*) FROM {}'.format(table_name)):
     64             if print_out:
     65                 print('\nTotal rows: {}'.format(ret[0]))

ProgrammingError: Cannot operate on a closed database.

In [9]:

    

test_db.check_table_cols_settings('ssp', True)


    

    




Column Info:
ID, Name, Type, NotNull, DefaultVal, PrimaryKey
<sqlite3.Row object at 0x000000000C778550>
<sqlite3.Row object at 0x000000000C778470>
<sqlite3.Row object at 0x000000000C7784D0>
<sqlite3.Row object at 0x000000000C778430>
<sqlite3.Row object at 0x000000000C7784B0>
<sqlite3.Row object at 0x000000000C778570>






    
Out[9]:





[<sqlite3.Row at 0xc778550>,
 <sqlite3.Row at 0xc778470>,
 <sqlite3.Row at 0xc7784d0>,
 <sqlite3.Row at 0xc778430>,
 <sqlite3.Row at 0xc7784b0>,
 <sqlite3.Row at 0xc778570>]

In [10]:

    

test_db.check_tables_values_in_cols('ssp', True)


    

    




Number of entries per column:
source_info: 1
survey_name: 1
driver: 1
original_path: 1
sensor_type: 1
pk: 1






    
Out[10]:





{u'driver': 1,
 u'original_path': 1,
 u'pk': 1,
 u'sensor_type': 1,
 u'source_info': 1,
 u'survey_name': 1}

In [11]:

    

print("\ndelete ssp")
test_db.delete_ssp_by_pk(ret0[0][0])


    

    




INFO:hydroffice.ssp.ssp_db:deleted 1 pk entries from raw_samples
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:278 > deleted 1 pk entries from raw_samples
INFO:hydroffice.ssp.ssp_db:deleted 1 pk entries from mod_samples
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:286 > deleted 1 pk entries from mod_samples
INFO:hydroffice.ssp.ssp_db:deleted 1 pk entries from sis_samples
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:294 > deleted 1 pk entries from sis_samples
INFO:hydroffice.ssp.ssp_db:deleted 1 pk entry from ssp
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:302 > deleted 1 pk entry from ssp
INFO:hydroffice.ssp.ssp_db:deleted 1 id entry from ssp_pk
INFO      hydroffice.ssp.ssp_db._delete_old_ssp:311 > deleted 1 id entry from ssp_pk






    




delete ssp

In [15]:

    

# retrieve printable ssp view rows
ret0 = test_db.get_printable_ssp_view()
print("ssp view rows:\n%s" % ret0)


    

    




INFO:hydroffice.ssp.ssp_db:retrieved 1 rows from ssp view
INFO      hydroffice.ssp.ssp_db._get_all_ssp_view_rows:619 > retrieved 1 rows from ssp view






    




ssp view rows:
   id            time stamp   longitude    latitude      sensor    type
    1   2012-10-21 01:39:22 -155.092529   71.697770      XCTD-1    XCTD

In [18]:

    

from hydroffice.base.gdal_aux import GdalAux

test_db.convert_ssp_view_to_ogr()
test_db.convert_ssp_view_to_ogr(GdalAux.ogr_formats[b'KML'])
test_db.convert_ssp_view_to_ogr(GdalAux.ogr_formats[b'CSV'])


    

    




DEBUG:hydroffice.base.gdal_aux:gdal version: 1110200
DEBUG     hydroffice.base.gdal_aux.__init__:68 > gdal version: 1110200
INFO:hydroffice.ssp.ssp_db:retrieved 1 rows from ssp view
INFO      hydroffice.ssp.ssp_db._get_all_ssp_view_rows:619 > retrieved 1 rows from ssp view
DEBUG:hydroffice.base.gdal_aux:gdal version: 1110200
DEBUG     hydroffice.base.gdal_aux.__init__:68 > gdal version: 1110200
INFO:hydroffice.ssp.ssp_db:retrieved 1 rows from ssp view
INFO      hydroffice.ssp.ssp_db._get_all_ssp_view_rows:619 > retrieved 1 rows from ssp view
DEBUG:hydroffice.base.gdal_aux:gdal version: 1110200
DEBUG     hydroffice.base.gdal_aux.__init__:68 > gdal version: 1110200
INFO:hydroffice.ssp.ssp_db:retrieved 1 rows from ssp view
INFO      hydroffice.ssp.ssp_db._get_all_ssp_view_rows:619 > retrieved 1 rows from ssp view






    
Out[18]:





True

In [19]:

    

test_db.map_ssp_view()


    

    




INFO:hydroffice.ssp.ssp_db:retrieved 1 rows from ssp view
INFO      hydroffice.ssp.ssp_db._get_all_ssp_view_rows:619 > retrieved 1 rows from ssp view
INFO:hydroffice.ssp.ssp_db:data boundary: -155.0925, -155.0925 (0.0500) / 71.6978, 71.6978 (0.0500)
INFO      hydroffice.ssp.ssp_db.map_ssp_view:783 > data boundary: -155.0925, -155.0925 (0.0500) / 71.6978, 71.6978 (0.0500)

In [20]:

    

test_db.create_daily_plots()


    

    




INFO:hydroffice.ssp.ssp_db:retrieved 1 timestamps from ssp view
INFO      hydroffice.ssp.ssp_db._get_timestamp_list:836 > retrieved 1 timestamps from ssp view
INFO:hydroffice.ssp.ssp_db:day: 0
INFO      hydroffice.ssp.ssp_db.create_daily_plots:962 > day: 0
INFO:hydroffice.ssp.ssp_db:get cleaned raw ssp by primary key: 1
INFO      hydroffice.ssp.ssp_db.get_cleaned_raw_ssp_by_pk:849 > get cleaned raw ssp by primary key: 1
INFO:hydroffice.ssp.ssp:resulting processed data size: (6, 1465)
INFO      hydroffice.ssp.ssp.set_samples:120 > resulting processed data size: (6, 1465)






    




samples: 1465
- Date Time: 2012-10-21 01:39:22
- Position: -155.092529, 71.69777
- Survey Name: default
- Original Path: Z:\_py\__hyo_dev\hyo_ssp\hydroffice\ssp\samples\sippican\C3_00172.EDF
- Sensor Type: 6
- Source String: XCTD-1
- Driver: SPC.0.2.0
- Samples: (6, 1465)
0.00 1444.88 1.76 25.75 0 0
0.10 1447.77 1.84 27.67 0 0
0.30 1448.23 1.89 27.85 0 0
0.40 1448.98 1.91 28.35 0 0
0.50 1449.36 1.93 28.57 0 0
.... .... .... .... ....
199.00 1435.22 -1.05 25.93 0 0

In [22]:

    

print("\nclose db")
test_db.close()
os.remove(db_file)


    

    




INFO:hydroffice.base.base_objects:Disconnected
INFO      hydroffice.base.base_objects.disconnect:164 > Disconnected






    




close db