# TOOL to assist creating javascript for web services #read in config file cdir = 'C:/Users/deepuser/Documents/cnf/user.cnf.txt' with open(cdir, 'r') as f: s = f.read() f.close() config = [line.rsplit(',') for line in s.rsplit('\n') ] # chop up the text by the newline='\n and the delim config_uid = config[0][1] config_pw = config[1][1] sql_table = 'desc awqx.activitybio;' with msc.MYSQL('localhost', 'awqx', 3306, 'pyuser', 'test') as dbo: t = dbo.query(sql_table) fields = [] for i in range(len(t)): fields += [[t[i]['Field'], t[i]['Type'][0:7]]] # create json field_str = '' for i in range(len(fields)): if fields[i][1] == 'date' or fields[i][1] == 'datetim': field_str = field_str + 'response[i].' + fields[i][ 0] + '.toISOString().substring(0,10)' + ',' else: field_str = field_str + 'response[i].' + fields[i][
'`type`, `name`, `description`, `last_change_date`, `lastUpdateDate`) ' \ 'VALUES (?,?,?,?,?,?,?);' SQLerrLog = 'INSERT INTO ' + db_scm + '.errlog VALUES (?,?,?,?,?,?,?);' file_name = 'frequencyclassdescriptor' db_err = [] uploadDate = datetime.today().strftime('%m%d%Y_%H%M%S_') fpath_base = ftp fpath_err = fpath_base + '\\ErrRpts\\' + uploadDate + file_name + 'QcRpt.txt' delim = '\t' raw = d header = raw[0] # use to check header names in the excel file raw = raw[1:] if raw is not None and header == headerList: with msc.MYSQL('localhost', db_scm, 3306, config_uid, config_pw) as dbo: insDate = datetime.today().strftime('%Y-%m-%d %H:%M:%S') user_name = 'BeckerM' # Insert into the database line by line. Append DB error if not caught by qc checks. for i in range(len(raw)): V_insert = raw[i] ins = dbo.query(SQLinsert, V_insert) if ins != {}: print('error with file %s on row %s, err=%s' % (file_name, i, ins[sorted(ins)[0]])) err = [folder[0:-1], insert_type[0:-1], file_name, insDate, i, ins[sorted(ins)[0]], user_name] dbErr = dbo.query(SQLerrLog, err) table_row = delim.join([str(e) for e in raw[i]]) db_err += [[file_name, str(i + 2), ins[sorted(ins)[0]], table_row]] else: print('success with file %s on row %s' % (file_name, i))
from awqx_app import mysql_connector as msc import pandas as pd #read in config file cdir = '' with open(cf_dir, 'r') as f: s = f.read() f.close() config = [line.rsplit(',') for line in s.rsplit('\n')] # chop up the text by the newline='\n and the delim config_uid = config[0][1] config_pw = config[1][1] SQLselect = 'SELECT staSeq, locationName, ylat, xlong,sourceMapScale, horizCollectMethod,horizRefDatum,' \ 'locationType, stateCd FROM awqx.stations;' with msc.MYSQL('localhost', 'test', 3306, config_uid, config_pw) as dbo: select = dbo.query(SQLselect) upload_date = datetime.datetime.today().strftime('%Y%m%d_%H%M%S') stations = pd.DataFrame.from_dict(select) file_loc = 'wqx/data_upload/stations_' + upload_date + '.csv' stations.to_csv(file_loc, index=False)