def main():
"""Go Main!"""
pgconn = get_dbconn("sustainablecorn")
cursor = pgconn.cursor()
drive = util.get_driveclient(util.get_config(), "cscap")
spr_client = util.get_spreadsheet_client(util.get_config())
res = drive.files().list(q="title contains 'Plot Identifiers'").execute()
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
site = item["title"].split()[0]
print(site)
cursor.execute(
"""SELECT distinct plotid from agronomic_data
WHERE site = %s""",
(site,),
)
agr_plotids = [row[0] for row in cursor]
cursor.execute(
"""SELECT distinct plotid from soil_data
WHERE site = %s""",
(site,),
)
soil_plotids = [row[0] for row in cursor]
spreadsheet = util.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets["Sheet 1"]
for entry in sheet.get_list_feed().entry:
dirty = False
data = entry.to_dict()
res = "yes"
if data["plotid"] not in agr_plotids:
res = "no"
# print("%s %s" % (data['plotid'], agr_plotids))
if data["agro"] != res:
print(
" AGR plotid: %s :: %s -> %s"
% (data["plotid"], data["agro"], res)
)
entry.set_value("agro", res)
dirty = True
res = "yes"
if data["plotid"] not in soil_plotids:
res = "no"
# print("%s %s" % (data['plotid'], soil_plotids))
if data["soil"] != res:
print(
" SOIL plotid: %s :: %s -> %s"
% (data["plotid"], data["soil"], res)
)
entry.set_value("soil", res)
dirty = True
if dirty:
spr_client.update(entry)
def main():
"""Go Main!"""
pgconn = psycopg2.connect(database='sustainablecorn', host='iemdb',
user='******')
cursor = pgconn.cursor()
drive = util.get_driveclient(util.get_config(), 'cscap')
spr_client = util.get_spreadsheet_client(util.get_config())
res = drive.files().list(q="title contains 'Plot Identifiers'").execute()
for item in res['items']:
if item['mimeType'] != 'application/vnd.google-apps.spreadsheet':
continue
site = item['title'].split()[0]
print(site)
cursor.execute("""SELECT distinct plotid from agronomic_data
WHERE site = %s""", (site, ))
agr_plotids = [row[0] for row in cursor]
cursor.execute("""SELECT distinct plotid from soil_data
WHERE site = %s""", (site, ))
soil_plotids = [row[0] for row in cursor]
spreadsheet = util.Spreadsheet(spr_client, item['id'])
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets['Sheet 1']
for entry in sheet.get_list_feed().entry:
dirty = False
data = entry.to_dict()
res = 'yes'
if data['plotid'] not in agr_plotids:
res = 'no'
# print("%s %s" % (data['plotid'], agr_plotids))
if data['agro'] != res:
print(" AGR plotid: %s :: %s -> %s" % (data['plotid'],
data['agro'], res))
entry.set_value('agro', res)
dirty = True
res = 'yes'
if data['plotid'] not in soil_plotids:
res = 'no'
# print("%s %s" % (data['plotid'], soil_plotids))
if data['soil'] != res:
print(" SOIL plotid: %s :: %s -> %s" % (data['plotid'],
data['soil'], res))
entry.set_value('soil', res)
dirty = True
if dirty:
spr_client.update(entry)
def process6(spreadkey):
sprclient = util.get_spreadsheet_client(util.get_config())
spreadsheet = util.Spreadsheet(sprclient, spreadkey)
rows = []
for yr in spreadsheet.worksheets:
lf = spreadsheet.worksheets[yr].get_list_feed()
for i, entry in enumerate(lf.entry):
if i == 0:
continue
rows.append(entry.to_dict())
df = pd.DataFrame(rows)
df['valid'] = pd.to_datetime(df['date'], format='%m/%d/%Y %H:%M:%S')
res = {}
print(df.columns)
for col in df.columns:
if col.find('wat4') == -1:
print('skipping column %s' % (repr(col), ))
continue
plotid = col.replace('wat4watertabledepth', '').upper()
df['depth'] = pd.to_numeric(
df['%swat4watertabledepth' % (plotid.lower(), )],
errors='coerce') * 1. # TODO: watch the units here!
res[plotid] = df[['valid', 'depth']].copy()
return res
def main():
"""Go Main Go."""
config = get_config()
drive = get_driveclient(config, "td")
res = (
drive.files()
.list(
q=(
"'1MA6spcXyu_TeyZYkUSizks9fuQTSLC7m' in parents and "
"mimeType='application/vnd.google-apps.folder'"
)
)
.execute()
)
rows = []
for item in res["items"]:
siteid, typename = item["title"].rsplit("_", 1)
rows.append({"siteid": siteid, "res": typename, "id": item["id"]})
df = pd.DataFrame(rows)
df = df.pivot("siteid", "res", "id")
pgconn = get_dbconn("td")
cursor = pgconn.cursor()
for siteid, row in df.iterrows():
cursor.execute(
"UPDATE meta_site_history SET drive_maps_folder = %s, "
"drive_photos_folder = %s where siteid = %s",
(row["maps"], row["photos"], siteid),
)
if cursor.rowcount != 1:
LOG.info("failed update for |%s|", siteid)
cursor.close()
pgconn.commit()
def main():
"""Go Main"""
config = util.get_config()
pgconn = psycopg2.connect(database='sustainablecorn', user='******',
host=config['database']['host'])
pcursor = pgconn.cursor()
# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
spread = util.Spreadsheet(spr_client, config['cscap']['manstore'])
translate = {'date': 'valid'}
tabs = ['Field Operations', 'Management', 'Pesticides', 'DWM', 'Notes']
tablenames = ['operations', 'management', 'pesticides', 'dwm', 'notes']
for sheetkey, table in zip(tabs, tablenames):
pcursor.execute("""DELETE from """ + table)
deleted = pcursor.rowcount
sheet = spread.worksheets[sheetkey]
added = 0
for rownum, entry in enumerate(sheet.get_list_feed().entry):
# Skip the first row of units
if rownum == 0:
continue
d = entry.to_dict()
cols = []
vals = []
for key in d.keys():
if key.startswith('gio'):
continue
val = d[key]
if key in ['date', 'biomassdate1', 'biomassdate2',
'outletdate']:
val = (val if val not in ['unknown', 'N/A', 'n/a']
else None)
vals.append(val)
cols.append(translate.get(key, key))
sql = """
INSERT into %s(%s) VALUES (%s)
""" % (table, ",".join(cols), ','.join(["%s"]*len(cols)))
try:
pcursor.execute(sql, vals)
except Exception as exp:
print("CSCAP harvest_management traceback")
print(exp)
for a, b in zip(cols, vals):
print(" |%s| -> |%s|" % (a, b))
return
added += 1
print(("harvest_management %16s added:%4s deleted:%4s"
) % (sheetkey, added, deleted))
pcursor.close()
pgconn.commit()
pgconn.close()
def process6(spreadkey):
sprclient = util.get_spreadsheet_client(util.get_config())
spreadsheet = util.Spreadsheet(sprclient, spreadkey)
rows = []
for yr in spreadsheet.worksheets:
lf = spreadsheet.worksheets[yr].get_list_feed()
for i, entry in enumerate(lf.entry):
if i == 0:
continue
rows.append(entry.to_dict())
df = pd.DataFrame(rows)
df['valid'] = pd.to_datetime(df['date'], format='%m/%d/%Y %H:%M:%S')
res = {}
print(df.columns)
for col in df.columns:
if col.find('wat4') == -1:
print('skipping column %s' % (repr(col), ))
continue
plotid = col.replace('wat4watertabledepth', '').upper()
df['depth'] = pd.to_numeric(
df['%swat4watertabledepth' % (plotid.lower(),)],
errors='coerse') * 1. # TODO: watch the units here!
res[plotid] = df[['valid', 'depth']].copy()
return res
def main():
"""Go Main Go"""
config = util.get_config()
spr_client = util.get_spreadsheet_client(config)
drive = util.get_driveclient(config)
# Fake last conditional to make it easy to reprocess one site...
res = (drive.files().list(q=("title contains 'Soil Texture Data'"),
maxResults=999).execute())
HEADERS = [
"uniqueid",
"plotid",
"depth",
"tillage",
"rotation",
"soil6",
"nitrogen",
"drainage",
"rep",
"subsample",
"landscape",
"notes",
"herbicide",
"sampledate",
]
sz = len(res["items"])
for i, item in enumerate(res["items"]):
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
spreadsheet = util.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
for year in spreadsheet.worksheets:
print('%3i/%3i sheet "%s" for "%s"' %
(i + 1, sz, year, item["title"]))
lf = spreadsheet.worksheets[year].get_list_feed()
for rownum, entry in enumerate(lf.entry):
dirty = False
data = entry.to_dict()
for key in ["soil13", "soil14"]:
if key not in data:
continue
value = data[key]
if rownum == 1 and value == "%":
print("updating % to g/kg")
entry.set_value(key, "g/kg")
dirty = True
continue
if rownum >= 2:
try:
newvalue = float(value) * 10.0
except Exception:
continue
print("%s updating %s to %s" % (key, value, newvalue))
entry.set_value(key, "%.4f" % (newvalue, ))
dirty = True
if dirty:
util.exponential_backoff(spr_client.update, entry)
def test_missing_config():
"""Test that we can deal with a missing file."""
with tempfile.NamedTemporaryFile() as tmp:
tmpfn = tmp.name
csu.CONFIG_FN = tmpfn
cfg = csu.get_config()
assert cfg is None
csu.save_config({})
def main():
"""Go Main"""
pgconn = get_dbconn('td')
pcursor = pgconn.cursor()
config = util.get_config()
sheets = util.get_sheetsclient(config, "cscap")
f = sheets.spreadsheets().get(
spreadsheetId=config['td']['manstore'], includeGridData=True
)
j = util.exponential_backoff(f.execute)
translate = {'date': 'valid'}
for sheet in j['sheets']:
table = TABLENAMES[TABS.index(sheet['properties']['title'])]
pcursor.execute("""DELETE from """ + table)
deleted = pcursor.rowcount
added = 0
for grid in sheet['data']:
cols = [a['formattedValue'] for a in grid['rowData'][0]['values']]
for row in grid['rowData'][2:]:
vals = [a.get('formattedValue') for a in row['values']]
data = dict(zip(cols, vals))
dbvals = []
dbcols = []
for key in data.keys():
if key.startswith('gio'):
continue
val = data[key]
if key in ['date', 'biomassdate1', 'biomassdate2',
'outletdate']:
val = (
val
if val not in ['unknown', 'N/A', 'n/a', 'TBD']
else None
)
dbvals.append(val)
dbcols.append(translate.get(key, key.replace("_", "")))
sql = """
INSERT into %s(%s) VALUES (%s)
""" % (table, ",".join(dbcols), ','.join(["%s"]*len(dbcols)))
try:
pcursor.execute(sql, dbvals)
except Exception as exp:
print("[TD] harvest_management traceback")
print(exp)
for col, val in zip(cols, vals):
print(" |%s| -> |%s|" % (col, val))
return
added += 1
print(("[TD] harvest_management %16s added:%4s deleted:%4s"
) % (table, added, deleted))
pcursor.close()
pgconn.commit()
pgconn.close()
def test_config():
"""Make sure we exercise the config logic as things get hairy"""
(_, tmpfn) = tempfile.mkstemp()
# create bogus config file
cfg = dict(a="a", b="b")
# Write config to bogus file
csu.save_config(cfg, tmpfn)
# Attempt to load it back now
cfg = csu.get_config(tmpfn)
assert cfg is not None
os.unlink(tmpfn)
def test_config():
"""Make sure we exercise the config logic as things get hairy"""
(_, tmpfn) = tempfile.mkstemp()
# create bogus config file
cfg = dict(a='a', b='b')
# Write config to bogus file
save_config(cfg, tmpfn)
# Attempt to load it back now
cfg = get_config(tmpfn)
assert cfg is not None
os.unlink(tmpfn)
def test_config(self):
"""Make sure we exercise the config logic as things get hairy"""
(_, tmpfn) = tempfile.mkstemp()
# create bogus config file
cfg = dict(a='a', b='b')
# Write config to bogus file
save_config(cfg, tmpfn)
# Attempt to load it back now
cfg = get_config(tmpfn)
self.assertTrue(cfg is not None)
os.unlink(tmpfn)
def main():
"""Go Main Go"""
mydb = psycopg2.connect('dbname=nwa')
mcursor = mydb.cursor()
mcursor.execute("""
DELETE from lsrs where date(valid) = '2019-03-28'
""")
print('Deleted %s rows' % (mcursor.rowcount,))
# Get me a client, stat
config = util.get_config()
sheets = util.get_sheetsclient(config, "cscap")
f = sheets.spreadsheets().get(
spreadsheetId=SHEET, includeGridData=True
)
j = util.exponential_backoff(f.execute)
inserts = 0
grid = j['sheets'][0]['data'][0]
cols = [a['formattedValue'] for a in grid['rowData'][0]['values']]
print(cols)
for row in grid['rowData'][1:]:
vals = [a.get('formattedValue') for a in row['values']]
data = dict(zip(cols, vals))
if data['Workshop UTC'] is None:
continue
ts = convtime(data['Workshop UTC'])
ts = ts.replace(tzinfo=pytz.UTC)
if data['Workshop Reveal UTC'] is None:
revealts = ts
else:
revealts = convtime(data['Workshop Reveal UTC'])
revealts = revealts.replace(tzinfo=pytz.UTC)
if ts != revealts:
print((" Entry has reveal delta of %s minutes"
) % ((revealts - ts).total_seconds() / 60.,))
geo = 'SRID=4326;POINT(%s %s)' % (data['LON'], data['LAT'])
sql = """
INSERT into lsrs (valid, display_valid, type, magnitude, city, source,
remark, typetext, geom, wfo) values (%s, %s, %s, %s, %s, %s,
%s, %s, %s, 'DMX')"""
args = (ts, revealts,
LKP[data['Type']],
0 if data['Magnitude'] == 'None' else data['Magnitude'],
data['Workshop City'], data['source'], data['Remark'],
data['Type'], geo)
mcursor.execute(sql, args)
inserts += 1
mcursor.close()
mydb.commit()
print("Inserted %s new entries!" % (inserts,))
print("ALERT: Consider running assign_lsr_wfo.py to get WFO right in DB")
def build_vars(mode):
''' build vars '''
config = util.get_config(CFG)
spr_client = util.get_spreadsheet_client(config)
feed = spr_client.get_list_feed(config['cscap']['sdckey'], 'od6')
places = 3 if mode != 'soil' else 4
prefix = 'AGR' if mode != 'soil' else 'SOIL'
for entry in feed.entry:
data = entry.to_dict()
if data['key'] is None or data['key'][:places] != prefix:
continue
varorder.append(data['key'].strip())
varlookup[data['key'].strip()] = data['name'].strip()
def build_vars(mode):
''' build vars '''
config = util.get_config(CFG)
spr_client = util.get_spreadsheet_client(config)
feed = spr_client.get_list_feed(config['cscap']['sdckey'], 'od6')
places = 3 if mode != 'soil' else 4
prefix = 'AGR' if mode != 'soil' else 'SOIL'
for entry in feed.entry:
data = entry.to_dict()
if data['key'] is None or data['key'][:places] != prefix:
continue
varorder.append(data['key'].strip())
varlookup[data['key'].strip()] = data['name'].strip()
def main():
"""Go!"""
config = util.get_config()
sheets = util.get_sheetsclient(config, "cscap")
drive = util.get_driveclient(config)
res = (drive.files().list(
q=("title contains 'Soil Bulk Density' or "
"title contains 'Soil Nitrate Data' or "
"title contains 'Soil Texture Data' or "
"title contains 'Agronomic Data'"),
maxResults=999,
).execute())
results = []
for item in tqdm(res["items"]):
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
title = item["title"]
f = sheets.spreadsheets().get(spreadsheetId=item["id"],
includeGridData=True)
j = util.exponential_backoff(f.execute)
for sheet in j["sheets"]:
sheet_title = sheet["properties"]["title"]
for griddata in sheet["data"]:
startcol = griddata.get("startColumn", 1)
startrow = griddata.get("startRow", 1)
header = []
for row, rowdata in enumerate(griddata["rowData"]):
if "values" not in rowdata: # empty sheet
continue
for col, celldata in enumerate(rowdata["values"]):
if row == 0:
header.append(celldata.get("formattedValue",
"n/a"))
if celldata.get("note") is not None:
results.append({
"title": title,
"header": header[col],
"sheet_title": sheet_title,
"row": row + startrow + 1,
"col": col + startcol + 1,
"note": celldata["note"],
})
df = pd.DataFrame(results)
df.to_csv("notes.csv", sep="|")
def main():
"""Go Main Go."""
config = util.get_config()
# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
drive_client = util.get_driveclient()
res = (drive_client.files().list(
q="title contains 'Agronomic Data'").execute())
for item in res["items"]:
spreadsheet = util.Spreadsheet(spr_client, item["id"])
for yr in ["2011", "2012", "2013", "2014", "2015"]:
spreadsheet.worksheets[yr].del_column("AGR392")
def main():
"""Go Main Go."""
config = util.get_config()
drive = util.get_driveclient(config)
res = drive.files().list(q="title contains 'Agronomic Data'").execute()
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
LOG.debug(item["title"])
spread = Spread(item["id"], config=config["cscap"]["service_account"])
for sheet in spread.sheets:
df = spread.sheet_to_df(index=None, sheet=sheet)
LOG.debug("%s %s", sheet.title, len(df.index))
def main():
"""Go Main Go"""
config = util.get_config()
spr_client = util.get_spreadsheet_client(config)
drive = util.get_driveclient(config)
# Fake last conditional to make it easy to reprocess one site...
res = drive.files().list(q=("title contains 'Soil Texture Data'"),
maxResults=999).execute()
HEADERS = ['uniqueid', 'plotid', 'depth', 'tillage', 'rotation', 'soil6',
'nitrogen', 'drainage', 'rep', 'subsample', 'landscape',
'notes', 'herbicide', 'sampledate']
sz = len(res['items'])
for i, item in enumerate(res['items']):
if item['mimeType'] != 'application/vnd.google-apps.spreadsheet':
continue
spreadsheet = util.Spreadsheet(spr_client, item['id'])
spreadsheet.get_worksheets()
for year in spreadsheet.worksheets:
print('%3i/%3i sheet "%s" for "%s"' % (i + 1, sz, year,
item['title']))
lf = spreadsheet.worksheets[year].get_list_feed()
for rownum, entry in enumerate(lf.entry):
dirty = False
data = entry.to_dict()
for key in ['soil13', 'soil14']:
if key not in data:
continue
value = data[key]
if rownum == 1 and value == '%':
print("updating % to g/kg")
entry.set_value(key, 'g/kg')
dirty = True
continue
if rownum >= 2:
try:
newvalue = float(value) * 10.
except Exception as _exp:
continue
print("%s updating %s to %s" % (key, value, newvalue))
entry.set_value(key, "%.4f" % (newvalue, ))
dirty = True
if dirty:
util.exponential_backoff(spr_client.update, entry)
def process5(spreadkey):
""" SERF, round 2"""
sprclient = util.get_spreadsheet_client(util.get_config())
spreadsheet = util.Spreadsheet(sprclient, spreadkey)
lf = spreadsheet.worksheets['2011-2015'].get_list_feed()
rows = []
for entry in lf.entry:
rows.append(entry.to_dict())
df = pd.DataFrame(rows)
df['valid'] = pd.to_datetime(df['valid'])
res = {}
for plotid in [str(s) for s in range(1, 9)]:
df['plot%swatertablemm' % (plotid, )] = pd.to_numeric(
df['plot%swatertablemm' % (plotid, )], errors='coerce')
res[plotid] = df[['valid', 'plot%swatertablemm' % (plotid, )]].copy()
res[plotid].columns = ['valid', 'depth']
return res
def process5(spreadkey):
""" SERF, round 2"""
sprclient = util.get_spreadsheet_client(util.get_config())
spreadsheet = util.Spreadsheet(sprclient, spreadkey)
lf = spreadsheet.worksheets['2011-2015'].get_list_feed()
rows = []
for entry in lf.entry:
rows.append(entry.to_dict())
df = pd.DataFrame(rows)
df['valid'] = pd.to_datetime(df['valid'])
res = {}
for plotid in [str(s) for s in range(1, 9)]:
df['plot%swatertablemm' % (plotid,)] = pd.to_numeric(
df['plot%swatertablemm' % (plotid,)], errors='coerse')
res[plotid] = df[['valid', 'plot%swatertablemm' % (plotid,)]].copy()
res[plotid].columns = ['valid', 'depth']
return res
def main():
"""Go Main!"""
config = util.get_config()
spr_client = util.get_spreadsheet_client(config)
drive_client = util.get_driveclient(config)
treat_feed = spr_client.get_list_feed(config["cscap"]["treatkey"], "od6")
treatments, treatment_names = util.build_treatments(treat_feed)
res = (
drive_client.files()
.list(q="title contains 'Plot Identifiers'")
.execute()
)
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
spreadsheet = util.Spreadsheet(spr_client, item["id"])
print("Processing '%s'..." % (item["title"],))
spreadsheet.get_worksheets()
worksheet = spreadsheet.worksheets["Sheet 1"]
for entry in worksheet.get_list_feed().entry:
data = entry.to_dict()
sitekey = data.get("uniqueid").lower()
if sitekey is None:
continue
trt = treatments[sitekey]
if data[COLKEY] is not None and data[COLKEY] != "":
continue
if len(trt[TRTKEY]) != 2:
print("can't deal with this: %s" % (trt[TRTKEY],))
break
newval = treatment_names.get(trt[TRTKEY][1], "")
entry.set_value(COLKEY, newval)
print(
("Setting plotid: %s uniqueid: %s column:%s to %s")
% (data.get("plotid"), sitekey, COLKEY, newval)
)
spr_client.update(entry)
def main():
"""Go Main Go."""
config = utils.get_config()
drive = utils.get_driveclient(config, 'cscap')
perms = drive.permissions().list(
fileId=config['cscap']['basefolder']).execute()
for item in perms.get('items', []):
# Unclear what type of permission this is that does not have this
# set, maybe a file with an allow for anybody that has the link to it
if 'emailAddress' not in item:
continue
email = item['emailAddress'].lower()
res = input("%s id: %s role: %s revoke?[y]" %
(email, item['id'], item['role']))
if res == "":
drive.permissions().update(fileId=config['cscap']['basefolder'],
permissionId=item['id'],
body={
'role': 'reader'
}).execute()
print("Del")
def googlesheet(siteid, sheetkey):
"""Harvest a google sheet, please"""
rows = []
config = util.get_config()
sheets = util.get_sheetsclient(config, "td")
f = sheets.spreadsheets().get(spreadsheetId=sheetkey, includeGridData=True)
j = util.exponential_backoff(f.execute)
for sheet in j['sheets']:
# sheet_title = sheet['properties']['title']
for griddata in sheet['data']:
for row, rowdata in enumerate(griddata['rowData']):
if 'values' not in rowdata: # empty sheet
continue
if row == 1: # skip units
continue
if row == 0:
header = []
for col, celldata in enumerate(rowdata['values']):
header.append(celldata['formattedValue'])
continue
data = {}
for col, celldata in enumerate(rowdata['values']):
data[header[col]] = fmt(celldata.get('formattedValue'))
rows.append(data)
df = pd.DataFrame(rows)
print("googlesheet has columns: %s" % (repr(df.columns.values),))
newcols = {}
for k in df.columns:
newcols[k] = XREF.get(k, k)
df.rename(columns=newcols, inplace=True)
df['valid'] = pd.to_datetime(df['valid'], errors='raise',
format='%m/%d/%y %H:%M')
df['valid'] = df['valid'] + datetime.timedelta(hours=TZREF[siteid])
# do some conversions
print("ALERT: doing windspeed unit conv")
df['windspeed_mps'] = speed(df['windspeed_mps'].values, 'KMH').value('MPS')
print("ALERT: doing windgustunit conv")
df['windgust_mps'] = speed(df['windgust_mps'].values, 'KMH').value('MPS')
return df
"""
Synchronize the ACL on the Google Drive to the local DB
"""
import pyiem.cscap_utils as utils
from pyiem.util import get_dbconn
import psycopg2
pgconn = get_dbconn('sustainablecorn')
cursor = pgconn.cursor()
removed = 0
config = utils.get_config()
for project in ['td', 'cscap']:
cursor.execute("""
SELECT access_level from website_access_levels where appid = %s
""", (project, ))
access_level = cursor.fetchone()[0]
CURRENT = []
cursor.execute("""
SELECT email from website_users WHERE access_level = %s
""", (access_level, ))
for row in cursor:
CURRENT.append(row[0])
drive = utils.get_driveclient(config, project)
perms = drive.permissions().list(
fileId=config[project]['basefolder']).execute()
for item in perms.get('items', []):
# Unclear what type of permission this is that does not have this
# set, maybe a file with an allow for anybody that has the link to it
if 'emailAddress' not in item:
My purpose in life is to send an email each day with changes found
on the Google Drive
"""
from __future__ import print_function
import sys
import datetime
import json
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from gdata.client import RequestError
import pytz
import pyiem.cscap_utils as util
CONFIG = util.get_config()
FMIME = 'application/vnd.google-apps.folder'
FORM_MTYPE = 'application/vnd.google-apps.form'
SITES_MYTPE = 'application/vnd.google-apps.site'
CFG = {'cscap': dict(emails=CONFIG['cscap']['email_daily_list'],
title="Sustainable Corn"),
'nutrinet': dict(emails=CONFIG['nutrinet']['email_daily_list'],
title='NutriNet (4R)'),
'td': dict(emails=CONFIG['td']['email_daily_list'],
title='Transforming Drainage')}
LOCALTZ = pytz.timezone("America/Chicago")
def pprint(mydict):
"""pretty print JSON"""
return json.dumps(mydict, sort_keys=True, indent=4,
"""
Synchronize the ACL on the Google Drive to the local DB
"""
import pyiem.cscap_utils as utils
import psycopg2
pgconn = psycopg2.connect(database='sustainablecorn', host='iemdb')
cursor = pgconn.cursor()
removed = 0
config = utils.get_config()
for project in ['td', 'cscap']:
cursor.execute("""
SELECT access_level from website_access_levels where appid = %s
""", (project, ))
access_level = cursor.fetchone()[0]
CURRENT = []
cursor.execute("""
SELECT email from website_users WHERE access_level = %s
""", (access_level, ))
for row in cursor:
CURRENT.append(row[0])
drive = utils.get_driveclient(config, project)
perms = drive.permissions().list(
fileId=config[project]['basefolder']).execute()
for item in perms.get('items', []):
# Unclear what type of permission this is that does not have this
# set, maybe a file with an allow for anybody that has the link to it
if 'emailAddress' not in item:
continue
#!/usr/bin/python
"""
"""
import sys
import cgi
import datetime
from psycopg2.extras import RealDictCursor
import pandas as pd
import pandas.io.sql as pdsql
import pyiem.cscap_utils as util
from pyiem.util import get_dbconn, ssw
config = util.get_config(
'/opt/datateam/config/mytokens.json')
def clean(val):
''' Clean the value we get '''
if val is None:
return ''
if val.strip().lower() == 'did not collect':
return 'DNC'
if val.strip().lower() == 'n/a':
return 'NA'
return val.decode('ascii', 'ignore')
def check_auth(form):
""" Make sure request is authorized """
def main():
"""Go Main"""
config = util.get_config()
pgconn = get_dbconn("sustainablecorn")
pcursor = pgconn.cursor()
# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
drive_client = util.get_driveclient(config)
res = (
drive_client.files()
.list(q="title contains 'Plot Identifiers'")
.execute()
)
translate = {"column": "col"}
lookup = {
"tillage": "TIL",
"rotation": "ROT",
"herbicide": "HERB",
"drainage": "DWM",
"nitrogen": "NIT",
"landscape": "LND",
}
pcursor.execute("""DELETE from plotids""")
removed = pcursor.rowcount
added = 0
sheets = 0
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
sheets += 1
spreadsheet = util.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
# A one off
worksheet = spreadsheet.worksheets.get("Sheet 1_export")
if worksheet is None:
worksheet = spreadsheet.worksheets["Sheet 1"]
for entry2 in worksheet.get_list_feed().entry:
data = entry2.to_dict()
cols = []
vals = []
for key in data.keys():
val = data[key]
if val is None:
continue
if key in lookup:
if data[key] is not None:
val = (
data[key]
.strip()
.replace("[", "")
.replace("]", "")
.split()[0]
)
if val not in ["N/A", "n/a"]:
val = "%s%s" % (lookup.get(key, ""), val)
if key == "uniqueid":
val = val.upper()
if key.startswith("no3") or key.startswith("_"):
continue
vals.append(val)
cols.append(translate.get(key, key))
if not cols:
print("No columns for '%s'?" % (item["title"],))
continue
if "uniqueid" not in cols:
print("No uniqueid column for '%s'" % (item["title"],))
sql = """
INSERT into plotids(%s) VALUES (%s)
""" % (
",".join(cols),
",".join(["%s"] * len(cols)),
)
try:
pcursor.execute(sql, vals)
except Exception as exp:
print(exp)
print(item["title"])
print(cols)
sys.exit()
added += 1
# One-time correction of missing nitrogen entries
# if data['landscape'] == 'N/A':
# print("Updating %s %s for landscape" % (data['uniqueid'],
# data['plotid']))
# entry2.set_value('landscape', 'n/a')
# spr_client.update(entry2)
print(
("harvest_plotids, removed: %s, added: %s, sheets: %s")
% (removed, added, sheets)
)
if removed > (added + 10):
print("harvest_plotids, aborting due to large difference")
sys.exit()
pcursor.close()
pgconn.commit()
pgconn.close()
import pyiem.cscap_utils as util
drive = util.get_driveclient(util.get_config(), "cscap")
spr_client = util.get_spreadsheet_client(util.get_config())
res = drive.files().list(q="title contains 'Plot Identifiers'").execute()
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
print(item["title"])
spreadsheet = util.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets["Sheet 1"]
for col in ["AGRO", "SOIL", "GHG", "IPM_CSCAP", "IPM_USB"]:
sheet.add_column(col)
def main():
"""Go Main"""
pgconn = psycopg2.connect(database='sustainablecorn')
plotdf = read_sql("""
SELECT upper(uniqueid) as uniqueid, plotid from plotids
""", pgconn, index_col=None)
plotdf['ipm_usb'] = 'no'
drive = utils.get_driveclient(utils.get_config(), 'cscap')
spr_client = utils.get_spreadsheet_client(utils.get_config())
res = drive.files().list(
q=("'0B4fyEPcRW7IscDcweEwxUFV3YkU' in parents and "
"title contains 'USB'")).execute()
U = {}
rows = []
for item in res['items']:
if item['mimeType'] != 'application/vnd.google-apps.spreadsheet':
continue
uniqueid = item['title'].strip().split()[-1]
spreadsheet = utils.Spreadsheet(spr_client, item['id'])
for worksheet in spreadsheet.worksheets:
lf = spreadsheet.worksheets[worksheet].get_list_feed()
for entry in lf.entry:
d = entry.to_dict()
rows.append(dict(uniqueid=uniqueid, plotid=d['plotid']))
plotipm = pd.DataFrame(rows)
for i, row in plotipm.iterrows():
df2 = plotdf[(plotdf['uniqueid'] == row['uniqueid']) &
(plotdf['plotid'] == row['plotid'])]
if len(df2.index) == 0:
key = "%s_%s" % (row['uniqueid'], row['plotid'])
if key in U:
continue
U[key] = True
print("Missing uniqueid: |%s| plotid: |%s|" % (row['uniqueid'],
row['plotid']))
else:
plotdf.at[df2.index, 'ipm_usb'] = 'yes'
res = drive.files().list(q="title contains 'Plot Identifiers'").execute()
for item in res['items']:
if item['mimeType'] != 'application/vnd.google-apps.spreadsheet':
continue
site = item['title'].split()[0]
print site
spreadsheet = utils.Spreadsheet(spr_client, item['id'])
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets['Sheet 1']
for entry in sheet.get_list_feed().entry:
data = entry.to_dict()
df2 = plotdf[(plotdf['uniqueid'] == site) &
(plotdf['plotid'] == data['plotid'])]
res = 'no'
if len(df2.index) == 1:
res = df2['ipm_usb'].values[0]
# print res, site, data['plotid'], df2
if data['ipmusb'] != res:
print(" IPM_USB plotid: %s :: %s -> %s" % (data['plotid'],
data['ipmusb'], res))
entry.set_value('ipmusb', res)
spr_client.update(entry)
def main():
"""Go"""
config = util.get_config()
pgconn = psycopg2.connect(database='sustainablecorn',
host=config['database']['host'])
pcursor = pgconn.cursor()
# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
drive_client = util.get_driveclient(config)
res = drive_client.files(
).list(q="title contains 'Soil Nitrate Data'").execute()
# Load up what data we have for this year
current = {}
pcursor.execute("""
SELECT uniqueid, plotid, varname, depth, subsample, sampledate
from soil_data WHERE year = %s and varname in %s
""", (YEAR, tuple(DOMAIN)))
for row in pcursor:
key = "%s|%s|%s|%s|%s|%s" % row
current[key] = True
for item in res['items']:
if item['mimeType'] != 'application/vnd.google-apps.spreadsheet':
continue
spreadsheet = util.Spreadsheet(spr_client, item['id'])
spreadsheet.get_worksheets()
worksheet = spreadsheet.worksheets.get(YEAR)
if worksheet is None:
# print("Missing Year: %s from %s" % (YEAR, spreadsheet.title))
continue
worksheet.get_cell_feed()
siteid = item['title'].split()[0]
# print 'Processing %s Soil Nitrate Year %s' % (siteid, YEAR),
if worksheet.get_cell_value(1, 1) != 'plotid':
print(('harvest_soil_nitrate: %s[%s] cell(1,1)="%s", skipping'
) % (siteid, YEAR, worksheet.get_cell_value(1, 1)))
continue
startcol = 3
if worksheet.get_cell_value(1, 2) == 'depth':
depthcol = 2
elif worksheet.get_cell_value(1, 3) == 'depth':
depthcol = 3
startcol = 4
if worksheet.get_cell_value(1, 2) == 'location':
locationcol = 2
else:
locationcol = None
for row in range(3, worksheet.rows+1):
plotid = worksheet.get_cell_value(row, 1)
depth = worksheet.get_cell_value(row, depthcol)
if depth.find(" to ") == -1:
print(("harvest_soil_nitrate found invalid depth: %s %s %s"
) % (depth, siteid, YEAR))
continue
if plotid is None or depth is None:
continue
subsample = "1"
if locationcol is not None:
subsample = worksheet.get_cell_value(row, locationcol)
for col in range(startcol, worksheet.cols+1):
if worksheet.get_cell_value(1, col) is None:
print(("h_soil_nitrate site: %s year: %s col: %s is null"
) % (siteid, YEAR, col))
continue
colheading = worksheet.get_cell_value(1, col).strip()
if not colheading.startswith('SOIL'):
print(('Invalid colheading: %s site: %s year: %s'
) % (colheading, siteid, YEAR))
continue
# Attempt to tease out the sampledate
tokens = colheading.split()
varname = tokens[0]
datetest = tokens[1]
if len(datetest.split("/")) == 3:
date = datetime.datetime.strptime(datetest, '%m/%d/%Y')
else:
if row == 3:
print(("h_soil_nitrate %s[%s] unknown sample date %s"
) % (siteid, YEAR, repr(colheading)))
date = DUMMY_DATES.get(datetest, None)
if date is None and row == 3:
print(("FIXME h_soil_nitrate %s[%s] "
"double unknown date %s"
) % (siteid, YEAR, repr(colheading)))
inval = worksheet.get_cell_value(row, col)
val = util.cleanvalue(inval)
if inval is not None and val is None:
print(("harvest_soil_nitrate found None. "
"site: %s year: %s "
" row: %s col: %s varname: %s"
) % (siteid, YEAR, row, col, varname))
if varname not in DOMAIN:
print(("harvest_soil_nitrate %s[%s] "
"found additional var: %s"
) % (siteid, YEAR, varname))
DOMAIN.append(varname)
key = ("%s|%s|%s|%s|%s|%s"
) % (siteid, plotid, varname, depth, subsample,
date
if date is None
else date.strftime("%Y-%m-%d"))
if key in current:
del current[key]
continue
try:
pcursor.execute("""
INSERT into soil_data(uniqueid, plotid, varname, year,
depth, value, subsample, sampledate)
values (%s, %s, %s, %s, %s, %s, %s, %s)
""", (siteid, plotid, varname, YEAR, depth, val,
subsample, date))
except Exception as exp:
print(('site: %s year: %s HARVEST_SOIL_NITRATE TRACEBACK'
) % (siteid, YEAR))
print(exp)
print(('%s %s %s %s %s %s'
) % (siteid, plotid, varname, depth,
date, val))
sys.exit()
for key in current:
(siteid, plotid, varname, depth, subsample, date) = key.split("|")
if date != 'None':
datesql = " and sampledate = '%s' " % (date, )
else:
datesql = " and sampledate is null "
print(('h_soil_nitrate rm %s %s %s %s %s %s %s'
) % (YEAR, siteid, plotid, varname, depth, subsample,
date))
pcursor.execute("""
DELETE from soil_data where uniqueid = %s and
plotid = %s and varname = %s and year = %s and depth = %s and
subsample = %s """ + datesql + """
""", (siteid, plotid, varname, YEAR, depth, subsample))
# print "...done"
pcursor.close()
pgconn.commit()
pgconn.close()
"""One off."""
import datetime
import pyiem.cscap_utils as util
sprclient = util.get_spreadsheet_client(util.get_config())
spread = util.Spreadsheet(sprclient,
"1FxKx0GDJxv_8fIjKe2xRJ58FGILLlUSXcb6EuSLQSrI")
spread.get_worksheets()
sheet = spread.worksheets["BRADFORD.A"]
for entry in sheet.get_list_feed().entry:
d = entry.to_dict()
if d["ghg2"] in [None, "Sampling Date", "-"]:
continue
ts = datetime.datetime.strptime(d["ghg2"][:15], "%a %b %d %Y")
entry.set_value("ghg200", ts.strftime("%m/%d/%Y"))
sprclient.update(entry)
def main():
"""Do Main"""
pgconn = psycopg2.connect(database="sustainablecorn")
plotdf = read_sql(
"""SELECT upper(uniqueid) as uniqueid,
upper(plotid) as plotid from plotids""",
pgconn,
index_col=None,
)
plotdf["ghg"] = "no"
drive = utils.get_driveclient(utils.get_config(), "cscap")
spr_client = utils.get_spreadsheet_client(utils.get_config())
X = {
"2011": "1DSHcfeBNJArVowk0CG_YvzI0YQnHIZXhcxOjBi2fPM4",
"2012": "1ax_N80tIBBKEnWDnrGxsK4KUa1ssokwMxQZNVHEWWM8",
"2013": "1UY5JYKlBHDElwljnEC-1tF7CozplAfyGpbkbd0OFqsA",
"2014": "12NqffqVMQ0M4PMT_CP5hYfmydC-vzXQ0lFHbKwwfqzg",
"2015": "1FxKx0GDJxv_8fIjKe2xRJ58FGILLlUSXcb6EuSLQSrI",
}
years = X.keys()
years.sort()
unknown = ["UniqueID_PlotID", "-_-"]
rows = []
for year in years:
spreadsheet = utils.Spreadsheet(spr_client, X.get(year))
for worksheet in spreadsheet.worksheets:
lf = spreadsheet.worksheets[worksheet].get_list_feed()
for entry in lf.entry:
d = entry.to_dict()
if d.get("uniqueid") is None or d.get("plotid") is None:
continue
rows.append(d)
df2 = plotdf[(plotdf["uniqueid"] == d["uniqueid"].upper())
& (plotdf["plotid"] == d["plotid"].upper())]
if len(df2.index) == 0:
key = "%s_%s" % (d["uniqueid"], d["plotid"])
if key in unknown:
continue
unknown.append(key)
print(("%s[%s] Unknown uniqueid: |%s| plotid: |%s|") %
(year, worksheet, d["uniqueid"], d["plotid"]))
else:
idx = plotdf[
(plotdf["uniqueid"] == d["uniqueid"].upper())
& (plotdf["plotid"] == d["plotid"].upper())].index
plotdf.at[idx, "ghg"] = "yes"
df = pd.DataFrame(rows)
writer = pd.ExcelWriter("output.xlsx")
df.to_excel(writer, "Sheet1")
writer.save()
res = drive.files().list(q="title contains 'Plot Identifiers'").execute()
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
site = item["title"].split()[0]
print(site)
spreadsheet = utils.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets["Sheet 1"]
for entry in sheet.get_list_feed().entry:
data = entry.to_dict()
df2 = plotdf[(plotdf["uniqueid"] == site)
& (plotdf["plotid"] == data["plotid"])]
res = "no"
if len(df2.index) == 1:
res = df2["ghg"].values[0]
if data["ghg"] != res:
print(" GHG plotid: %s :: %s -> %s" %
(data["plotid"], data["ghg"], res))
entry.set_value("ghg", res)
spr_client.update(entry)
)
.execute
)
if len(res["items"]) == 0:
body = {
"title": title,
"mimeType": "application/vnd.google-apps.spreadsheet",
"parents": [{"id": colfolder}],
}
print("Creating Tile Flow Sheet: %s in %s" % (title, colfolder))
res = drive.files().insert(body=body).execute()
return res["id"]
return res["items"][0]["id"]
config = util.get_config()
ssclient = util.get_spreadsheet_client(config)
drive = util.get_driveclient(config)
msheet = util.Spreadsheet(ssclient, config["td"]["site_measurements"])
msheet.get_worksheets()
sheet = msheet.worksheets["Plot ID"]
sheet.get_list_feed()
sites = {}
pis = {}
for entry in sheet.list_feed.entry:
row = entry.to_dict()
if row["tileflowandtilenitrate-nyesno"] == "NO":
continue
site = row["siteid"]
d = sites.setdefault(site, [])
def main():
"""Go"""
config = util.get_config()
pgconn = psycopg2.connect(
database="sustainablecorn", host=config["database"]["host"]
)
pcursor = pgconn.cursor()
# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
drive_client = util.get_driveclient(config)
res = (
drive_client.files()
.list(q="title contains 'Soil Nitrate Data'")
.execute()
)
# Load up what data we have for this year
current = {}
pcursor.execute(
"""
SELECT uniqueid, plotid, varname, depth, subsample, sampledate
from soil_data WHERE year = %s and varname in %s
""",
(YEAR, tuple(DOMAIN)),
)
for row in pcursor:
key = "%s|%s|%s|%s|%s|%s" % row
current[key] = True
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
spreadsheet = util.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
worksheet = spreadsheet.worksheets.get(YEAR)
if worksheet is None:
continue
worksheet.get_cell_feed()
siteid = item["title"].split()[0]
if worksheet.get_cell_value(1, 1) != "plotid":
print(
('harvest_soil_nitrate: %s[%s] cell(1,1)="%s", skipping')
% (siteid, YEAR, worksheet.get_cell_value(1, 1))
)
continue
startcol = 3
if worksheet.get_cell_value(1, 2) == "depth":
depthcol = 2
elif worksheet.get_cell_value(1, 3) == "depth":
depthcol = 3
startcol = 4
if worksheet.get_cell_value(1, 2) == "location":
locationcol = 2
else:
locationcol = None
for row in range(3, worksheet.rows + 1):
plotid = worksheet.get_cell_value(row, 1)
depth = worksheet.get_cell_value(row, depthcol)
if depth.find(" to ") == -1:
print(
("harvest_soil_nitrate found invalid depth: %s %s %s")
% (depth, siteid, YEAR)
)
continue
if plotid is None or depth is None:
continue
subsample = "1"
if locationcol is not None:
subsample = worksheet.get_cell_value(row, locationcol)
for col in range(startcol, worksheet.cols + 1):
if worksheet.get_cell_value(1, col) is None:
print(
("h_soil_nitrate site: %s year: %s col: %s is null")
% (siteid, YEAR, col)
)
continue
colheading = worksheet.get_cell_value(1, col).strip()
if not colheading.startswith("SOIL"):
print(
("Invalid colheading: %s site: %s year: %s")
% (colheading, siteid, YEAR)
)
continue
# Attempt to tease out the sampledate
tokens = colheading.split()
varname = tokens[0]
datetest = tokens[1]
if len(datetest.split("/")) == 3:
date = datetime.datetime.strptime(datetest, "%m/%d/%Y")
else:
if row == 3:
print(
("h_soil_nitrate %s[%s] unknown sample date %s")
% (siteid, YEAR, repr(colheading))
)
date = DUMMY_DATES.get(datetest, None)
if date is None and row == 3:
print(
(
"FIXME h_soil_nitrate %s[%s] "
"double unknown date %s"
)
% (siteid, YEAR, repr(colheading))
)
inval = worksheet.get_cell_value(row, col)
val = util.cleanvalue(inval)
if inval is not None and val is None:
print(
(
"harvest_soil_nitrate found None. "
"site: %s year: %s "
" row: %s col: %s varname: %s"
)
% (siteid, YEAR, row, col, varname)
)
if varname not in DOMAIN:
print(
(
"harvest_soil_nitrate %s[%s] "
"found additional var: %s"
)
% (siteid, YEAR, varname)
)
DOMAIN.append(varname)
key = ("%s|%s|%s|%s|%s|%s") % (
siteid,
plotid,
varname,
depth,
subsample,
date if date is None else date.strftime("%Y-%m-%d"),
)
if key in current:
del current[key]
continue
try:
pcursor.execute(
"""
INSERT into soil_data(uniqueid, plotid, varname, year,
depth, value, subsample, sampledate)
values (%s, %s, %s, %s, %s, %s, %s, %s)
""",
(
siteid,
plotid,
varname,
YEAR,
depth,
val,
subsample,
date,
),
)
except Exception as exp:
print(
("site: %s year: %s HARVEST_SOIL_NITRATE TRACEBACK")
% (siteid, YEAR)
)
print(exp)
print(
("%s %s %s %s %s %s")
% (siteid, plotid, varname, depth, date, val)
)
sys.exit()
for key in current:
(siteid, plotid, varname, depth, subsample, date) = key.split("|")
if date != "None":
datesql = " and sampledate = '%s' " % (date,)
else:
datesql = " and sampledate is null "
print(
("h_soil_nitrate rm %s %s %s %s %s %s %s")
% (YEAR, siteid, plotid, varname, depth, subsample, date)
)
pcursor.execute(
"""
DELETE from soil_data where uniqueid = %s and
plotid = %s and varname = %s and year = %s and depth = %s and
subsample = %s """
+ datesql
+ """
""",
(siteid, plotid, varname, YEAR, depth, subsample),
)
pcursor.close()
pgconn.commit()
pgconn.close()
speedup2 = ((orig1 - origB).total_seconds() /
(workshop1 - workshopB2).total_seconds())
print 'Part2 Speedup is %.4f' % (speedup2,)
def warp(lsrtime):
""" Convert the LSR Time to our workshop time, of some sort"""
base = orig0 if lsrtime < origB else origB
newbase = workshop0 if lsrtime < origB else workshopB2
_speedup = speedup1 if lsrtime < origB else speedup2
return newbase + datetime.timedelta(
seconds=((lsrtime - base).total_seconds() / _speedup))
# ______________________________________________________________________
# Get me a client, stat
spr_client = util.get_spreadsheet_client(util.get_config())
feed = spr_client.get_list_feed("1V6-xV7Sm3ST-0tYpvtD5s-sMmNe_FdhKDCrVS5vOxoE",
"od6")
def getdir(u, v):
if v == 0:
v = 0.000000001
dd = math.atan(u / v)
ddir = (dd * 180.00) / math.pi
if (u > 0 and v > 0): # First Quad
ddir = 180 + ddir
elif (u > 0 and v < 0): # Second Quad
ddir = 360 + ddir
def main():
"""Go Main Go."""
config = util.get_config()
drive = util.get_driveclient(config)
print(drive.files().delete(fileId=sys.argv[1]).execute())
def main():
"""Do Main Things"""
sprclient = util.get_spreadsheet_client(util.get_config())
spreadsheet = util.Spreadsheet(sprclient, BACKEND)
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets["Field Operations"]
for entry in sheet.get_list_feed().entry:
row = entry.to_dict()
if row["operation"] != "fertilizer_synthetic":
continue
if (row["productrate"] is None or row["productrate"] == "-1.0"
or row["productrate"] == ""):
option = "B"
# Option B, values are in lbs per acre
phosphoruse = 0.0
potassium = 0.0
nitrogen = float2(row["nitrogen"]) * KGHA_LBA
if row["phosphorus"] is not None and float2(row["phosphorus"]) > 0:
phosphoruse = float2(row["phosphorus"]) * KGHA_LBA
if row["phosphate"] is not None and float2(row["phosphate"]) > 0:
phosphoruse = float2(row["phosphate"]) * KGHA_LBA * 0.437
if row["potassium"] is not None and float2(row["potassium"]) > 0:
potassium = float2(row["potassium"]) * KGHA_LBA
if row["potash"] is not None and float2(row["potash"]) > 0:
potassium = float(row["potash"]) * KGHA_LBA * 0.830
sulfur = float2(row["sulfur"]) * KGHA_LBA
zinc = float2(row["zinc"]) * KGHA_LBA
magnesium = float2(row["magnesium"]) * KGHA_LBA
calcium = float2(row["calcium"]) * KGHA_LBA
iron = float2(row["iron"]) * KGHA_LBA
else:
option = "A"
# Option A, straight percentages
prate = float2(row["productrate"])
nitrogen = prate * float2(row["nitrogen"]) / 100.0
phosphoruse = 0.0
potassium = 0.0
if row["phosphorus"] is not None and float2(row["phosphorus"]) > 0:
phosphoruse = prate * float2(row["phosphorus"]) / 100.0
if row["phosphate"] is not None and float2(row["phosphate"]) > 0:
phosphoruse = prate * float2(row["phosphate"]) / 100.0 * 0.437
if row["potassium"] is not None and float2(row["potassium"]) > 0:
potassium = prate * float2(row["potassium"]) / 100.0
if row["potash"] is not None and float2(row["potash"]) > 0:
potassium = prate * float(row["potash"]) / 100.0 * 0.830
sulfur = prate * float2(row["sulfur"]) / 100.0
zinc = prate * float2(row["zinc"]) / 100.0
magnesium = prate * float2(row["magnesium"]) / 100.0
calcium = prate * float2(row["calcium"]) / 100.0
iron = prate * float2(row["iron"]) / 100.0
print(("Option: %s\n nitrogen: Old: %s -> New: %s") %
(option, row["nitrogenelem"], nitrogen))
entry.set_value("nitrogenelem", "%.2f" % (nitrogen, ))
entry.set_value("phosphoruselem", "%.2f" % (phosphoruse, ))
entry.set_value("potassiumelem", "%.2f" % (potassium, ))
entry.set_value("sulfurelem", "%.2f" % (sulfur, ))
entry.set_value("zincelem", "%.2f" % (zinc, ))
entry.set_value("magnesiumelem", "%.2f" % (magnesium, ))
entry.set_value("calciumelem", "%.2f" % (calcium, ))
entry.set_value("ironelem", "%.2f" % (iron, ))
# if option == 'B':
util.exponential_backoff(sprclient.update, entry)
import sys
import datetime
import json
import smtplib
import time
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
# third party
from gdata.client import RequestError
import pytz
import pyiem.cscap_utils as util
from pyiem.util import logger
LOG = logger()
CONFIG = util.get_config()
FMIME = "application/vnd.google-apps.folder"
FORM_MTYPE = "application/vnd.google-apps.form"
SITES_MYTPE = "application/vnd.google-apps.site"
CFG = {
"cscap": dict(
emails=CONFIG["cscap"]["email_daily_list"], title="Sustainable Corn"
),
"cig": dict(emails=CONFIG["cig"]["email_daily_list"], title="CIG"),
"inrc": dict(
emails=CONFIG["inrc"]["email_daily_list"],
title="Integrating Social and Biophysical Indicators (ISBI)",
),
"ardn": dict(
emails=CONFIG["ardn"]["email_daily_list"],
title="Ag Research Data Network",
def main():
"""Go Main"""
pgconn = psycopg2.connect(database="sustainablecorn")
plotdf = read_sql(
"""
SELECT upper(uniqueid) as uniqueid, plotid from plotids
""",
pgconn,
index_col=None,
)
plotdf["ipm_usb"] = "no"
drive = utils.get_driveclient(utils.get_config(), "cscap")
spr_client = utils.get_spreadsheet_client(utils.get_config())
res = (drive.files().list(
q=("'0B4fyEPcRW7IscDcweEwxUFV3YkU' in parents and "
"title contains 'USB'")).execute())
U = {}
rows = []
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
uniqueid = item["title"].strip().split()[-1]
spreadsheet = utils.Spreadsheet(spr_client, item["id"])
for worksheet in spreadsheet.worksheets:
lf = spreadsheet.worksheets[worksheet].get_list_feed()
for entry in lf.entry:
d = entry.to_dict()
rows.append(dict(uniqueid=uniqueid, plotid=d["plotid"]))
plotipm = pd.DataFrame(rows)
for _i, row in plotipm.iterrows():
df2 = plotdf[(plotdf["uniqueid"] == row["uniqueid"])
& (plotdf["plotid"] == row["plotid"])]
if df2.empty:
key = "%s_%s" % (row["uniqueid"], row["plotid"])
if key in U:
continue
U[key] = True
print("Missing uniqueid: |%s| plotid: |%s|" %
(row["uniqueid"], row["plotid"]))
else:
plotdf.at[df2.index, "ipm_usb"] = "yes"
res = drive.files().list(q="title contains 'Plot Identifiers'").execute()
for item in res["items"]:
if item["mimeType"] != "application/vnd.google-apps.spreadsheet":
continue
site = item["title"].split()[0]
print(site)
spreadsheet = utils.Spreadsheet(spr_client, item["id"])
spreadsheet.get_worksheets()
sheet = spreadsheet.worksheets["Sheet 1"]
for entry in sheet.get_list_feed().entry:
data = entry.to_dict()
df2 = plotdf[(plotdf["uniqueid"] == site)
& (plotdf["plotid"] == data["plotid"])]
res = "no"
if len(df2.index) == 1:
res = df2["ipm_usb"].values[0]
if data["ipmusb"] != res:
print(" IPM_USB plotid: %s :: %s -> %s" %
(data["plotid"], data["ipmusb"], res))
entry.set_value("ipmusb", res)
spr_client.update(entry)
def main():
"""Go Main"""
config = util.get_config()
pgconn = psycopg2.connect(
database="sustainablecorn",
user="******",
host=config["database"]["host"],
)
pcursor = pgconn.cursor()
# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
spread = util.Spreadsheet(spr_client, config["cscap"]["manstore"])
translate = {"date": "valid"}
tabs = ["Field Operations", "Management", "Pesticides", "DWM", "Notes"]
tablenames = ["operations", "management", "pesticides", "dwm", "notes"]
for sheetkey, table in zip(tabs, tablenames):
pcursor.execute("""DELETE from """ + table)
deleted = pcursor.rowcount
sheet = spread.worksheets[sheetkey]
added = 0
for rownum, entry in enumerate(sheet.get_list_feed().entry):
# Skip the first row of units
if rownum == 0:
continue
d = entry.to_dict()
cols = []
vals = []
for key in d.keys():
if key.startswith("gio"):
continue
val = d[key]
if key in [
"date",
"biomassdate1",
"biomassdate2",
"outletdate",
]:
val = val if val not in ["unknown", "N/A", "n/a"] else None
vals.append(val)
cols.append(translate.get(key, key))
sql = """
INSERT into %s(%s) VALUES (%s)
""" % (
table,
",".join(cols),
",".join(["%s"] * len(cols)),
)
try:
pcursor.execute(sql, vals)
except Exception as exp:
print("CSCAP harvest_management traceback")
print(exp)
for a, b in zip(cols, vals):
print(" |%s| -> |%s|" % (a, b))
return
added += 1
print(
("harvest_management %16s added:%4s deleted:%4s")
% (sheetkey, added, deleted)
)
pcursor.close()
pgconn.commit()
pgconn.close()
"""
Harvest the Agronomic Data into the ISU Database
"""
from __future__ import print_function
import sys
import psycopg2
import pyiem.cscap_utils as util
config = util.get_config()
pgconn = psycopg2.connect(database='td',
host=config['database']['host'])
# Get me a client, stat
spr_client = util.get_spreadsheet_client(config)
drive_client = util.get_driveclient(config, "td")
def delete_entries(current, siteid):
for key in current:
(plotid, varname) = key.split("|")
print(('harvest_agronomic REMOVE %s %s %s'
) % (siteid, plotid, varname))
pcursor.execute("""DELETE from agronomic_data where uniqueid = %s and
plotid = %s and varname = %s and year = %s
""", (siteid, plotid, varname, YEAR))
res = drive_client.files().list(q="title contains 'Crop Yield Data'").execute()
#!/usr/bin/python
"""
"""
import sys
import cgi
import datetime
from psycopg2.extras import RealDictCursor
import pandas as pd
import pandas.io.sql as pdsql
import pyiem.cscap_utils as util
from pyiem.util import get_dbconn, ssw
config = util.get_config('/opt/datateam/config/mytokens.json')
def clean(val):
''' Clean the value we get '''
if val is None:
return ''
if val.strip().lower() == 'did not collect':
return 'DNC'
if val.strip().lower() == 'n/a':
return 'NA'
return val.decode('ascii', 'ignore')
def check_auth(form):
""" Make sure request is authorized """
if form.getfirst('hash') != config['appauth']['sharedkey']: