# 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)