Commits

James Morrison committed 6ff9583

first commit

Comments (0)

Files changed (1)

parse_weather_data.py

+# -*- coding: utf-8 -*-
+"""
+Created on Wed Feb 01 15:37:34 2012
+
+@author: James Morrison
+A python module that takes a path to a directory containing Cumulus monthly log 
+files and parses through them creating a sqlite3 database with one table that 
+contains all the log entries, and has converted the first field date and second
+ field time values into one time_seconds field in UTC, if a row is incomplete
+it will print a 'Bad row' message then the row and similar if two rows have
+identical time_seconds. All values are stored as real.
+
+If the database table already exists with the records already added it will 
+still attempt to add those rows and any new ones.
+
+The sql helper functions allow easy modification if fields are added or removed
+
+Cumulus ( weather station software ) - http://sandaysoft.com
+"""
+
+import os
+import sqlite3
+import re
+import csv
+from datetime import datetime
+import calendar
+
+class Weather_Data_Record():
+    def __init__(self, row):
+        for index, field in enumerate(self.fields):
+            setattr(self, field, row[index])
+        
+    fields = ['current_temp', 'current_humidity', 'current_dewpoint', 
+              'current_wind_speed', 'current_wind_gust', 'current_wind_bearing'
+              , 'current_rainfall_rate', 'total_rainfall_today', 
+              'current_sea_level_pressure', 'current_rainfall_counter', 
+              'inside_temperature', 'inside_humidity',
+              'latest_gust', 'wind_chill', 'heat_index', 'uv_index', 
+              'solar_radiation', 'evapotranspiration', 
+              'annual_evapotranspiration', 'apparent_temperature', 
+              'current_theoretical_max_solar_radiation', 
+              'hours_of_sunshine_so_far_today', 'current_wind_bearing_2',
+              'time_seconds']
+              
+    unique_index = len(fields) - 1
+              
+    def insert_string(self):
+        return 'insert into ' + self.table_name + ' ('
+        
+    def sql_insert_text(self):
+        return self.insert_string() + self.string_fields() + \
+            self.write_question_marks()
+            
+    def write_question_marks(self):
+        q_marks = ') values('
+        for field in self.fields:
+            q_marks += '?,'            
+        return q_marks[:-1] + ')'
+        
+    def string_fields(self):
+        fields_string = ''
+        for index, field in enumerate(self.fields):
+            fields_string += field + ', '
+        return fields_string[0:-2]
+    
+    table_name = 'weather_log'
+    
+    def get_record_values(self):
+        try:
+            result = []
+            for field in self.fields:
+                result.append(getattr(self, field))
+            return (result)
+        except AttributeError:
+            print "Expected Attributes not found, possibly invalid file"
+            return None
+        
+       
+class Parse_Weather_Data():
+    def __init__(self, directory_path):
+        self.directory_path = directory_path
+        self.get_database_connection()
+        self.parse_log_files()
+        
+    def create_table(self, record):
+        return 'create table ' + record.table_name + '(' + \
+            self.string_fields(record.fields, record.unique_index) + ')'        
+        
+    def string_fields(self, fields, unique_index = None):
+        """ Helper function iterates over fields to simplify writing sql """
+        fields_string = ''
+        for index, field in enumerate(fields):
+            suffix = ' real , '
+            if index == unique_index:
+                suffix = ' unique' + suffix
+            fields_string += field + suffix
+        return fields_string[0:-8]
+
+    def get_database_connection(self):
+        self.database_path = self.directory_path + os.sep + database_name
+        if not os.path.exists(self.database_path):
+            file = open(self.database_path, 'w')
+            file.write('')
+            file.close()
+        self.connection = sqlite3.connect(self.database_path)
+        self.cursor = self.connection.cursor()
+        self.try_sql(self.create_table(Weather_Data_Record),
+                "Create table failed")
+        
+    def parse_log_files(self):
+        file_listing = os.listdir(self.directory_path)
+        for file_name in file_listing:
+            if re.search("log.txt",file_name):
+                self.parse_file(file_name)
+        self.connection.commit()
+        self.connection.close()  
+            
+    def parse_file(self, file_name):
+        log_reader = csv.reader(open(self.directory_path + os.sep + file_name), 
+                   delimiter=',')
+        for row in log_reader:
+            date_time_raw = str(row[0]) + ',' + str(row[1])
+            date_time = datetime.strptime(date_time_raw, "%d/%m/%y,%H:%M")
+            date_time_seconds = calendar.timegm(date_time.timetuple())
+            del row[0:2]
+            row.append(date_time_seconds) 
+            if len(row) == len(Weather_Data_Record.fields):
+                parsed_row = Weather_Data_Record(row)
+                self.write_record(self.cursor, parsed_row)
+            else:
+                print "Bad row", row
+
+    def try_sql(self, sql_command, error_message):
+        try:
+            self.connection.cursor().execute(sql_command)
+        except sqlite3.Error:
+            print error_message  
+                
+    def write_record(self, cursor, parsed_record):
+        try:
+            sql_insert_text = parsed_record.sql_insert_text()
+            record_values = parsed_record.get_record_values()
+            if record_values != None:
+                cursor.execute(sql_insert_text, record_values)
+        except sqlite3.IntegrityError:
+            print "INSERT failed - possible time_seconds is non-unique"
+            print record_values
+
+database_name = 'weather_database'
+
+Parse_Weather_Data('D:\data')