-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathupdate.py
178 lines (158 loc) · 7.89 KB
/
update.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
import os
import datetime
import calendar
import sqlite3 as sqlite
import geomag
import tkinter
from tkinter import ttk, filedialog, messagebox #separate imports needed due to tkinter idiosyncrasies
import sqlparse
# local objects
from classes import stdevs, meanw, stdevw
### begin function defintion ###
def run_sqlscript(conn, script_path, form = None, msg = None):
if script_path == None:
script_path = tkinter.filedialog.askopenfilename(title = "Choose SQL script to run on Database.", filetypes = (("SQL files", "*.sql"),("All files", "*.*")))
if os.path.isfile(script_path):
with open(script_path) as f:
script = f.read()
stmts = sqlparse.split(script)
### update messages
if msg != None:
print(msg)
if form != None:
form.lblAction['text'] = msg
form.lblAction.update_idletasks()
### initial values get the while loop to run at least once.
curlength = 1
pastlength = 2
counter = 0
while curlength < pastlength and curlength > 0:
errors = []
counter += 1
#print("Script pass",str(counter))
pastlength = len(stmts)
for stmt in stmts:
try:
#print(stmt,'\n','------------------------------------------------------','\n','------------------------------------------------------')
conn.execute(stmt)
stmts = [x for x in stmts if x != stmt] #removes SQL statement from list if completed successfully]
except sqlite.OperationalError:
errors.append(stmt)
curlength = len(stmts)
if len(stmts) == 0:
return (True, None)
else:
return (False, stmts)
else:
return (False, None)
def Update(var, form = None):
RDpath = var['RDpath']
log = ""
sqldir = var['SQLpath']
### connect to SQLite3 DB
dirpath = os.path.dirname(RDpath)
dbname = os.path.basename(RDpath)
connection = sqlite.connect(RDpath)
### creating these functions allows for custom aggreate functions within the database. See classes.py for definition.
connection.create_aggregate("stdev", 1, stdevs)
connection.create_aggregate("meanw", 2, meanw)
connection.create_aggregate("stdevw", 2, stdevw)
connection.enable_load_extension(True)
connection.row_factory = sqlite.Row
c = connection.cursor()
### converts DIMA species list semi-colon concatenated values to individual species records for ease of processing.
speciesrichness(connection)
### runs update SQL script to perform various post import updates given in the script.
run_sqlscript(connection, script_path = os.path.join(sqldir, 'update.sql'), form = form, msg = 'Running update script...')
### runs insert_tags SQL script to automatically create some species and plot tags given in the SQL script (e.g. sagebrush = woody Artemisia sp.)
run_sqlscript(connection, script_path = os.path.join(sqldir, 'insert_tags.sql'), form = form, msg = r'Inserting plot/species tags into database...')
### runs insert_custom SQL script to insert custom data defined by the user into the db.
run_sqlscript(connection, script_path = os.path.join(sqldir, 'insert_custom.sql'), form = form, msg = r'Inserting custom data into the database...')
### defines how to group plots together when looking at plot level info. Only one plot with the same plotkey is shown per season.
SeasonsCalc(connection)
### add declination information to tblPlots
msg = "Adding declination information to plots."
print(msg)
if form != None:
form.lblAction['text'] = msg
form.lblAction.update_idletasks()
if var['WMMpath'] == None:
getwmm = True
elif not os.path.isfile(var['WMMpath']):
getwmm = True
else:
getwmm = False
mmpath = var['WMMpath']
if getwmm:
getmm = tkinter.messagebox.askyesno("Calculate declination?", "Would you like to calulate the magnetic declination of imported plots (is required for some spatial QC checks)?")
if getmm:
mmpath = tkinter.filedialog.askopenfilename(title = "Choose NOAA World Magnetic Model location (i.e. WMM.COF).",
filetypes = (("Magnetic Model files", "*.COF"),("All files", "*.*")))
var['WMMpath'] = mmpath
if mmpath:
gm = geomag.geomag.GeoMag(mmpath)
i = connection.cursor()
rows = connection.execute("SELECT PlotKey, PlotID, Latitude, Longitude, Elevation, ElevationType, EstablishDate, "
"Declination FROM tblPlots WHERE PlotKey NOT IN ('888888888','999999999') AND Declination IS NULL;")
for row in rows:
if row['EstablishDate']:
dt = datetime.datetime.strptime(row['EstablishDate'],'%Y-%m-%d %H:%M:%S')
if row['ElevationType'] == 1:
elev = row['Elevation']*3.28084
elif row['ElevationType'] == 2:
elev = row['Elevation']
else:
elev = 0
mag = gm.GeoMag(row['Latitude'],row['Longitude'], elev, dt.date())
i.execute("UPDATE tblPlots SET Declination = ? WHERE PlotKey = ?;",(mag.dec, row['PlotKey']),)
else:
print("Plot", row['PlotID'], "has no EstablishDate. Skipping.")
connection.commit()
#connection.execute("VACUUM")
connection.close()
return var
### defines seasons, which the database uses to separate out plot revisits. When data are shown at the plot level,
### a season is given to it in order to view multiple visitations of the same plot. For above plot summations,
### only the most recent data in a revisit cycle is used.
def SeasonsCalc(connection):
connection.execute("DELETE FROM SeasonDefinition")
### checks if a data date range is provided and if not inserts a default range based on date values from tblPlots
rcount = connection.execute("SELECT Count(*) FROM Data_DateRange").fetchone()[0]
if rcount == 0:
sql = """INSERT INTO Data_DateRange SELECT strftime('%Y', Min(EstablishDate)) ||
'-01-01' AS StartDate, strftime('%Y', Max(EstablishDate)) || '-12-31'
AS EndDate, 12 AS SeasonLength_Months FROM tblPlots;"""
connection.execute(sql)
result = connection.execute("SELECT * FROM Data_DateRange")
row = result.fetchone()
startdate = datetime.datetime.strptime(row['StartDate'],'%Y-%m-%d')
enddate = datetime.datetime.strptime(row['EndDate'],'%Y-%m-%d')
slength = row['SeasonLength_Months']
slength_years = slength / 12
date = startdate
while date < enddate:
if calendar.isleap(date.year):
days = 366
else:
days = 365
nextdate = date + datetime.timedelta(days = (slength_years * days))
send = nextdate - datetime.timedelta(microseconds = 1)
season = date.strftime('%Y%m%d') + "-" + send.strftime('%Y%m%d')
sql = "INSERT INTO SeasonDefinition (SeasonStart, SeasonEnd, SeasonLabel) VALUES (?,?,?);"
connection.execute(sql,(date, send, season,))
date = nextdate
return
### this function is used to convert the semicolon delimitted species richness fields into individual records for ease of processing.
def speciesrichness(connection):
connection.execute("DELETE FROM SR_Raw;")
result = connection.execute("SELECT RecKey, subPlotID, SpeciesList FROM tblSpecRichDetail;")
for row in result:
speclist = []
species = row[2].split(sep=';')
for s in species:
if s and row[0] and row[1]:
speclist.append((row[0],row[1],s))
#print(speclist)
connection.executemany('INSERT OR IGNORE INTO SR_Raw VALUES (?,?,?)', speclist)
connection.commit()
### end function definition ###