def move_to_db(ddf, table_name, ftype="fitacf", coords="mlt", db_name=None, config_filename="../../mysql_dbconfig_files/config.ini", section="midlat"): """Writes POES Aur Bnd fitted coeffs to MySQL DB""" from mysql.connector import MySQLConnection from sqlalchemy import create_engine import sys sys.path.append("../../") from mysql_dbutils.db_config import db_config # construct a db name if db_name is None: db_name = "master_" + coords + "_" +ftype # read db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection to master db try: engine = create_engine("mysql://" + config_info["user"] + ":" +\ config_info["password"] + "@" +\ config_info["host"] + "/" + db_name) conn = engine.connect() except Exception, e: logging.error(e, exc_info=True)
def add_aur_bnd(sd_input_table, sd_output_table, poes_table, coords="mlt", ftype="fitacf", nbatches=20, db_name=None, config_filename="../mysql_dbconfig_files/config.ini", section="midlat"): """ Calculates Equatorward Bnd of Aur MLAT & rel_MLAT and writes them to new columns in sd_input_table """ from mysql.connector import MySQLConnection from sqlalchemy import create_engine import sys sys.path.append("../") from mysql_dbutils.db_config import db_config if db_name is None: db_name = "master_" + coords + "_" + ftype # read db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection to ten-min median iscat db try: conn = MySQLConnection(database=db_name, **config_info) cur = conn.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True)
def _create_dbconn(self, config_filename="../mysql_dbconfig_files/config.ini", section="midlat", db_name=None): """ creates a db connection Parameters ---------- config_filename: str name and path of the configuration file section: str, default to "midlat" section of database configuration db_name : str, default to None Name of the MySQL db to which iscat data has been written """ from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # read the db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make db connection if db_name is None: db_name = self.rad + "_iscat_" + self.ftype try: conn = MySQLConnection(database=db_name, **config_info) except Exception, e: logging.error(e, exc_info=True)
def master_summary_by_radar_season( input_table, output_table, coords="mlt", db_name=None, radar_pair=["cvw", "cve"], config_filename="../mysql_dbconfig_files/config.ini", section="midlat"): """ stores the summay statistics of the data in master table into a different table in the same database. Time and rad informatin are all lost at this point. NOTE: this function is only used to selecet for pairs of radars from the six U.S. radars. Parameters ---------- input_table : str Name of a master table in master db output_table : str Name of a master_summary table in master db coords : str Coordinates in which the binning process took place. Default to "mlt, can be "geo" as well. radar_pair = list a pair of three-character radar names config_filename: str name and path of the configuration file section: str, default to "midlat" section of database configuration db_name : str, default to None Name of the master db Returns ------- Nothing """ import datetime as dt import numpy as np from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # construct a db name if db_name is None: db_name = "master_" + coords + "_" + ftype # read db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection to master db try: conn = MySQLConnection(database=db_name, **config_info) cur = conn.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True)
def combine_ten_min_median( rads, ftype="fitacf", coords="mlt", config_filename="../mysql_dbconfig_files/config.ini", section="midlat", db_name=None): """ combines ten-minute median filtered gridded data from radars specified by rads argument into a single table. Parameters ---------- rads : list A list of three-letter radar codes ftype : str SuperDARN file type coords : str The Coordinate system. Default to "mlt. Can be "geo" as well. config_filename: str name and path of the configuration file section: str, default to "midlat" section of database configuration db_name : str, default to None Name of the MySQL db where ten-min median data is stored. Returns ------- Nothing """ import numpy as np import datetime as dt from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # consruct a db name and a table name if db_name is None: db_name = "ten_min_median_" + coords + "_" + ftype output_table = "all_radars_" + ftype # read the db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection to ten-min median db try: conn = MySQLConnection(database=db_name, **config_info) cur = conn.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True)
def gmi_based_filter(rad, output_table, stm=None, etm=None, ftype="fitacf", coords="mlt", isKp_based=True, kp_lim=[0.0, 2.3], isSymH_based=False, symh_min=-50, config_filename="../mysql_dbconfig_files/config.ini", section="midlat", ten_min_median_dbname=None, gmi_dbname=None, gmi_db_location="../../data/sqlite3/"): """ Selects the data based on geomagnetic indicies. Parameters ---------- rads : str Three-letter name of a radar whose data will be filtered. output_table : str Name of the table where filtered iscat data will be stored. stm : datetime.datetime The start time. Default to None, in which case takes the earliest in db. etm : datetime.datetime The end time. Default to None, in which case takes the latest time in db. NOTE: if stm is None then etm should also be None, and vice versa. ftype : str SuperDARN LOS data file type coords : str Coordinate systems. valid inputs are "geo" or "mlt". isKp_based : bool If set to True, data will be filtered based on Kp. kp_lim : list The range of Kp. The range bondaries are inclusive. isSymH_based : bool If set to True, data will be filtered based on SymH. symh_min : float The lower limit for SYMH. config_filename: str name and path of the configuration file section: str, default to "midlat" section of database configuration ten_min_median_dbname : str Name of the db where ten min median iscat data is stored. Default to None. gmi_dbname : str Name of the sqlite3 db where geomagnetic indices are stored. Default to None. gmi_db_location : str The path to gmi_dbname sqlite db. Default to None. Returns ------- Nothing Note ---- SymH base filter has not been implemented yet """ import datetime as dt import sqlite3 import os import sys sys.path.append("../../") from mysql.connector import MySQLConnection from mysql_dbutils.db_config import db_config import logging # construct db names and table names if ten_min_median_dbname is None: ten_min_median_dbname = "ten_min_median_" + coords + "_" + ftype if gmi_dbname is None: gmi_dbname = "gmi_imf.sqlite" input_table = rad + "_" + ftype # make db connection to geomag indicies data conn_gmi = sqlite3.connect(database=gmi_db_location + gmi_dbname, detect_types=sqlite3.PARSE_DECLTYPES) cur_gmi = conn_gmi.cursor() # make db connection for ten-min median filtered iscat data # read the db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection try: conn_iscat = MySQLConnection(database=ten_min_median_dbname, **config_info) cur_iscat = conn_iscat.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True)
# create db name if input_dbname is None: input_dbname = "ten_min_median_" + coords + "_" + ftype if output_dbname is None: output_dbname = "master_" + coords + "_" + ftype # create a db (if not exist) that combines all the data try: # create a db db_tools.create_db(output_dbname) except Exception, e: logging.error(e, exc_info=True) # read db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection to ten-min median iscat db try: conn_ten = MySQLConnection(database=input_dbname, **config_info) cur_ten = conn_ten.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True) # make a connection to master db try: conn = MySQLConnection(database=output_dbname, **config_info) cur = conn.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True)
def read_from_db(rad, stm, etm, ftype="fitacf", config_filename="../mysql_dbconfig_files/config.ini", section="midlat", dbName=None, plotrti=False): """ reads the boxcar-filtered data from db instead of files. NOTE: plotrti option does not work. working on it... Parameters ---------- rad : str Three-letter radar code stm : datetime.datatime Start time for reading data etm : datetime.datatime End time for reading data ftype : str file type config_filename : str name of the configuration file section: str section of database configuration dbName : str, default to None db name plotrti : bool NOTE: plotrti option does not work. working on it... Returns ------- dict A list of dicts of the data. Each dict element stores data for a certain beam. Data in each dict is stored in lists and separated in to tbands. """ import json import datetime as dt from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # construct a db name if dbName is None: dbName = rad + "_boxcar_" + ftype # read the db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make db connection conn = MySQLConnection(database=dbName, **config_info) cur = conn.cursor() # get all the table names command = "SELECT table_name FROM information_schema.tables " command = command + "where TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='{db}'".\ format(db=dbName) try: cur.execute(command) tbl_names = cur.fetchall() tbl_names = [x[0] for x in tbl_names] except Exception, e: logging.error(e, exc_info=True)
def bin_to_grid(rad, bmnum, stm=None, etm=None, ftype="fitacf", coords="mlt", hemi="north", config_filename="../mysql_dbconfig_files/config.ini", section="midlat", db_name=None): """ bins the data into mlat-mlt-azm. Parameters ---------- rad : str Three-letter radar code bmnum : int Radar beam stm : datetime.datetime The start time. Default to None, in which case takes the earliest in db. etm : datetime.datetime The end time. Default to None, in which case takes the latest time in db. NOTE: if stm is None then etm should also be None, and vice versa. ftype : str SuperDARN file type coords : str Coordinates in which the binning process takes place. Default to "mlt. Can be "geo" as well. hemi : str Hemisphere. e.g., "north", "south" NOTE: hemi not have been implemented yet. config_filename: str name and path of the configuration file section: str, default to "midlat" section of database configuration db_name : str, default to None Name of the MySQL db to which iscat data has been written Note ---- 0 xxx_gazmc directs towards magnetic (or geo) north. 180 gazmc directs towards south. gazmc (gridded azimuthal center) spans from 5 - 355 degrees. """ import numpy as np import datetime as dt from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # create grid points if hemi == "north": grds = grids(lat_min=20, lat_max=90, dlat=1, half_dlat_offset=False) # read the db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make db connection if db_name is None: db_name = rad + "_iscat_" + ftype try: conn = MySQLConnection(database=db_name, **config_info) except Exception, e: logging.error(e, exc_info=True)
# create tmpdirs to store dmap files temporarily for rad in rad_list: tmpdir = "../data/" + rad + "_tmp" os.system("mkdir -p " + tmpdir) # create dbs (if not exist) for radars for rad in rad_list: db_name = rad + "_boxcar_" + ftype try: # create a db db_tools.create_db(db_name) except Exception, e: logging.error(e, exc_info=True) # read the db config info config = db_config.db_config(config_filename="../mysql_dbconfig_files/config.ini", section="midlat") config_info = config.read_db_config() # make db connections and save them into a dict conn_dict = {} for rad in rad_list: db_name = rad + "_boxcar_" + ftype try: conn_tmp = MySQLConnection(database=db_name, **config_info) conn_dict[rad] = conn_tmp except Exception, e: logging.error(e, exc_info=True) # create dates, does not include the edate all_dates = [sdate + dt.timedelta(days=i) for i in range((edate-sdate).days)]
def cos_fit(input_table, output_table, db_name=None, config_filename="../mysql_dbconfig_files/config.ini", section="midlat", ftype="fitacf", coords="mlt", azbin_nvel_min=10, naz_min=3, az_span_min=30, sqrt_weighting=True): """ Does cosine fitting to the LOS data in each MLAT/MLT grid, and stores the results in a different table named "master_cosfit_xxx". This table has only the qualified latc-lonc grid points. Parameters ---------- input_table : str A table name in db_name db output_table : str A table name in db_name db config_filename: str name and path of the configuration file section: str, default to "midlat" section of database configuration db_name : str, default to None Name of the master db ftype : str SuperDARN file type coords : str Coordinates in which the binning process took place. Default to "mlt, can be "geo" as well. azbin_nvel_min : int The minimum number of measurements an azimuthal bin should have to be qualified for cosfitting. naz_min : int The minimum number of azimuthal bins within a grid cell. cosine fitting is done if a grid cell has at least naz_min number of qualified azimuthal bins az_span_min : int The minimum azimuhtal span a grid cell should have to be qualified for cosfitting. sqrt_weighting : bool if set to True, the fitting is weighted by the number of points within each azimuthal bin. if set to False, all azimuthal bins are considered equal regardless of the nubmer of points each of them contains. Returns ------- Nothing """ import numpy as np import datetime as dt from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # construct a db name if db_name is None: db_name = "master_" + coords + "_" + ftype # read db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection to master db try: conn = MySQLConnection(database=db_name, **config_info) cur = conn.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True)
def move_to_db(self, config_filename="../mysql_dbconfig_files/config.ini", section="midlat", db_name=None): """ writes the data into a MySQL db given by conn argument. Parameters ---------- config_filename: str name and path of the configuration file section: str, default to mysql section of database configuration db_name : str, default to None Name of the MySQL db to which iscat data will be written Returns ------- Nothing """ from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # read the db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make db connection if db_name is None: db_name = self.rad + "_iscat_" + self.ftype conn = MySQLConnection(database=db_name, **config_info) cur = conn.cursor() # loop through each radar beam for bmnum in self.events.keys(): data_dict = self.events[bmnum] # create a table table_name = self.rad + "_bm" + str(bmnum) command = "CREATE TABLE IF NOT EXISTS {tb} (\ datetime DATETIME,\ vel TEXT DEFAULT NULL,\ slist TEXT DEFAULT NULL,\ rsep TINYINT(4) DEFAULT NULL,\ frang SMALLINT(4) DEFAULT NULL,\ bmazm FLOAT(7,2) DEFAULT NULL,\ PRIMARY KEY (datetime))".format(tb=table_name) try: cur.execute(command) except Exception, e: logging.error(e, exc_info=True) # loop through each scan time, usually 2 minutes, # and write the data into table_name in db for i in xrange(len(data_dict['datetime'])): command = "INSERT IGNORE INTO {tb} (datetime, vel, slist, rsep, " +\ "frang, bmazm) VALUES (%s, %s, %s, %s, %s, %s)" command = command.format(tb=table_name) try: cur.execute(command, (data_dict["datetime"][i], data_dict["vel"][i], data_dict["slist"][i], data_dict["rsep"][i], data_dict["frang"][i], data_dict["bmazm"][i])) except Exception, e: logging.error(e, exc_info=True)
def fetch_data(input_table, lat_range=[52, 59], nvel_min=250, season="winter", config_filename="../mysql_dbconfig_files/config.ini", section="midlat", db_name=None, ftype="fitacf", coords="mlt", sqrt_weighting=True, limit_to_night=True): """ fetch fitted data from the master db into a dict Parameters ---------- input_table : str A table name in db_name db lat_ragne : list The range of latitudes of interest nvel_min : int minimum requirement for the number of velocity measurements in a lat-lon grid cell season : str season of interest config_filename: str name and path of the configuration file section: str, default to "midlat" section of database configuration db_name : str, default to None Name of the master db ftype : str SuperDARN file type coords : str Coordinates in which the binning process took place. Default to "mlt, can be "geo" as well. sqrt_weighting : bool if set to True, the fitted vectors that are produced through weighting the number of points within each azimuthal bin will be retrieved. if set to False, the fitted vectors that are produced by equality weighting the number of points within each azimuthal bin will be retrieved. Return ------ data_dict : dict """ import sqlite3 import datetime as dt import numpy as np from mysql.connector import MySQLConnection import sys sys.path.append("../") from mysql_dbutils.db_config import db_config import logging # construct a db name if db_name is None: db_name = "master_" + coords + "_" + ftype # read db config info config = db_config(config_filename=config_filename, section=section) config_info = config.read_db_config() # make a connection to master db try: conn = MySQLConnection(database=db_name, **config_info) cur = conn.cursor(buffered=True) except Exception, e: logging.error(e, exc_info=True)