def make_csv_file(conn, owner): """ Writes the 15min data for each station with status 'on' for DfW to a single CSV file (SAMDBNRM_YYYYMMDD.CSV) Calls get_15min_data() :param conn: a live DB connection :param owner: string :return: a CSV file string """ sql = "SELECT aws_id FROM tbl_stations WHERE owner = '" + owner + "' AND status = 'on';" if conn is None: conn = generator_wdtf.db_connect() cursor = conn.cursor() cursor.connection.autocommit(True) cursor.execute(sql) rows = cursor.fetchall() single_csv_file = "DfW ID,Date,Time,Ave AirTemp (AWS) (degC),Ave AppTemp (degC),Ave DewPoint (degC),Ave Humidity (AWS) (%),Ave DeltaT (degC),Ave Soil Temperature (degC),Ave GSR (W/m^2),Min WndSpd (m/s),Ave WndSpd (m/s),Max WndSpd (m/s),Ave WndDir (deg),Total Rain (mm),Ave LeafWet (% Wet),Ave AirTemp (Canopy) (degC),Ave Humidity (Canopy) (%)\r\n" for row in rows: single_csv_file += get_minutes_data(conn, row[0]) cursor.close() conn.close() return single_csv_file
import sys import datetime import logging import settings import generator_wdtf if __name__ == "__main__": logging.basicConfig( filename=settings.LOG_FILE, level=settings.LOG_LEVEL, datefmt='%Y-%m-%d %H:%M:%S', format='%(asctime)s %(levelname)s %(message)s') conn = generator_wdtf.db_connect() ''' owner = sys.argv[2] aws_id = sys.argv[3] date_string = sys.argv[4] in_date = datetime.datetime.strptime(date_string, "%Y-%m-%d") zf = generator_wdtf.make_wdtf_zip_file_for_station_and_date(conn, owner, aws_id, in_date) logging.info("manual station " + owner + " : " + aws_id + " : " + date_string + " : file " + zf) ''' import generator_dfw_csv print generator_dfw_csv.make_csv_file(conn, 'SAMDB')
def get_minutes_data(conn, aws_id): """ Get yesterday's 15min data for a station for CSV maker :param conn: a live DB connection :param aws_id: string :return: CSV data """ """ colums: DfW ID Date Time Ave AirTemp (AWS) (degC) Ave AppTemp (degC) Ave DewPoint (degC) Ave Humidity (AWS) (%) Ave DeltaT (degC) Ave Soil Temperature (degC) Ave GSR (W/m^2) Min WndSpd (m/s) Ave WndSpd (m/s) Max WndSpd (m/s) Ave WndDir (deg) Total Rain (mm) Ave LeafWet (% Wet) Ave AirTemp (Canopy) (degC) Ave Humidity (Canopy) (%) """ sql = """ SELECT COALESCE(tbl_stations.dfw_id, tbl_stations.aws_id), tbl_data_minutes.* FROM tbl_data_minutes INNER JOIN tbl_stations ON tbl_data_minutes.aws_id = tbl_stations.aws_id WHERE tbl_data_minutes.aws_id = '""" + aws_id + """' AND DATE(stamp) = CURDATE() - INTERVAL 1 DAY GROUP BY stamp ORDER BY stamp;""" if conn is None: conn = generator_wdtf.db_connect() #cursor = conn.cursor (MySQLdb.cursors.DictCursor) -- for named columns cursor = conn.cursor() cursor.connection.autocommit(True) cursor.execute(sql) rows = cursor.fetchall() csv_string = "" for row in rows: date_time = str(row[3]) row2 = [] #turn None into 0 for col in row: if col == None: row2.append(",") else: row2.append(str(col)+",") row_string = str(row2[0])+date_time[0:10]+","+date_time[11:]+","+str(row2[4])+str(row2[5])+str(row2[6])+str(row2[7])+str(row2[8])+str(row2[9])+str(row2[10])+str(row2[11])+str(row2[12])+str(row2[13])+str(row2[14])+str(row2[15])+str(row2[16])+str(row2[17])+str(row2[18]) csv_string += row_string.strip(',') + "\r\n" cursor.close() conn.commit() conn.close() return csv_string