def get_selected_obstypes(self):
sql = "select obsid, type from obs_points where obsid in ({})".format(
utils.sql_unicode_list(self.observations))
ConnOK, types = db_utils.sql_load_fr_db(sql)
self.typedict = dict(types) #make it a dictionary
sql = "select distinct type from obs_points where obsid in ({})".format(
utils.sql_unicode_list(self.observations))
ConnOK, self.distincttypes = db_utils.sql_load_fr_db(sql)
def get_table_data(self, tname, obsids, dbconnection, file_data_srid):
dbconnection.execute("SELECT * FROM %s LIMIT 1"%tname)
columns = [x[0] for x in dbconnection.cursor.description]
if file_data_srid:
astext = 'ST_AsBinary(ST_Transform({}, %s))'%str(file_data_srid)
else:
astext = 'ST_AsBinary({})'
geom_columns = list(db_utils.get_geometry_types(dbconnection, tname).keys())
#Transform to 4326 just to be sure that both the source and dest database has support for the srid.
select_columns = [astext.format(col)
if (col.lower() in geom_columns and dbconnection.get_srid(tname, col))
else col
for col in columns]
sql = '''SELECT {} FROM {}'''.format(u', '.join(select_columns), tname)
if obsids:
sql += " WHERE obsid IN ({})".format(sql_unicode_list(obsids))
dbconnection.execute(sql)
table_data = [[x.lower() for x in columns]]
table_data.extend([row for row in dbconnection.execute_and_fetchall(sql)])
if len(table_data) < 2:
return None
else:
return table_data
def to_csv(self, tname, obsids=None, replace=False):
"""
Write to csv
:param tname: The destination database
:param obsids:
:return:
"""
sql = "SELECT * FROM %s"%tname
if obsids:
sql += " WHERE obsid IN ({})".format(sql_unicode_list(obsids))
data = self.source_dbconnection.execute_and_fetchall(sql)
printlist = [[col[0] for col in self.source_dbconnection.cursor.description]]
printlist.extend(data)
filename = os.path.join(self.exportfolder, tname + ".csv")
utils.write_printlist_to_file(filename, printlist)
def big_sql(self):
# Data must be stored as mg/l in the database since it is converted to meq/l in code here...
sql = """select a.obsid as obsid, date_time, obs_points.type as type, Cl_meqPl, HCO3_meqPl, SO4_meqPl, Na_meqPl + K_meqPl as NaK_meqPl, Ca_meqPl, Mg_meqPl
from (select u.obsid, u.date_time, u.Cl_meqPl, u.HCO3_meqPl, u.SO4_meqPl, u.Na_meqPl, u.K_meqPl, u.Ca_meqPl, u.Mg_meqPl
from (
select obsid, date_time,
(max (case when %s then reading_num end))/35.453 as Cl_meqPl,
(max (case when %s then reading_num end))/61.0168 as HCO3_meqPl,
2*(max (case when %s then reading_num end))/96.063 as SO4_meqPl,
(max (case when %s then reading_num end))/22.9898 as Na_meqPl,
(max (case when %s then reading_num end))/39.0983 as K_meqPl,
2*(max (case when %s then reading_num end))/40.078 as Ca_meqPl,
2*(max (case when %s then reading_num end))/24.305 as Mg_meqPl
from w_qual_lab where obsid in (%s)
group by obsid, date_time
) AS u
where u.Ca_meqPl is not null and u.Mg_meqPl is not null and u.Na_meqPl is not null and u.K_meqPl is not null and u.HCO3_meqPl is not null and u.Cl_meqPl is not null and u.SO4_meqPl is not null
) as a, obs_points WHERE a.obsid = obs_points.obsid""" % (
ru(self.ParameterList[0]), ru(self.ParameterList[1]),
ru(self.ParameterList[2]), ru(self.ParameterList[3]),
ru(self.ParameterList[4]), ru(self.ParameterList[5]),
ru(self.ParameterList[6]), utils.sql_unicode_list(
self.observations))
return sql
def get_number_of_rows(self, obsids, tname):
sql = "select count(obsid) from %s"%tname
if obsids:
sql += " WHERE obsid IN ({})".format(sql_unicode_list(obsids))
nr_of_rows = self.source_dbconnection.execute_and_fetchall(sql)[0][0]
return nr_of_rows
def big_sql(self):
# Data must be stored as mg/l in the database since it is converted to meq/l in code here...
sql = """select a.obsid as obsid, date_time, obs_points.type as type, Cl_meqPl, HCO3_meqPl, SO4_meqPl, Na_meqPl + K_meqPl as NaK_meqPl, Ca_meqPl, Mg_meqPl
from (select u.obsid, u.date_time, u.Cl_meqPl, u.HCO3_meqPl, u.SO4_meqPl, u.Na_meqPl, u.K_meqPl, u.Ca_meqPl, u.Mg_meqPl
from (
select obsid, date_time,
(max (case when %s then reading_num end))/35.453 as Cl_meqPl,
(max (case when %s then reading_num end))/61.0168 as HCO3_meqPl,
2*(max (case when %s then reading_num end))/96.063 as SO4_meqPl,
(max (case when %s then reading_num end))/22.9898 as Na_meqPl,
(max (case when %s then reading_num end))/39.0983 as K_meqPl,
2*(max (case when %s then reading_num end))/40.078 as Ca_meqPl,
2*(max (case when %s then reading_num end))/24.305 as Mg_meqPl
from w_qual_lab where obsid in (%s)
group by obsid, date_time
) AS u
where u.Ca_meqPl is not null and u.Mg_meqPl is not null and u.Na_meqPl is not null and u.K_meqPl is not null and u.HCO3_meqPl is not null and u.Cl_meqPl is not null and u.SO4_meqPl is not null
) as a, obs_points WHERE a.obsid = obs_points.obsid""" %(ru(self.ParameterList[0]), ru(self.ParameterList[1]), ru(self.ParameterList[2]), ru(self.ParameterList[3]), ru(self.ParameterList[4]), ru(self.ParameterList[5]), ru(self.ParameterList[6]), utils.sql_unicode_list(self.observations))
return sql
def get_selected_obstypes(self):
sql = "select obsid, type from obs_points where obsid in ({})".format(utils.sql_unicode_list(self.observations))
ConnOK, types = db_utils.sql_load_fr_db(sql)
self.typedict = dict(types)#make it a dictionary
sql = "select distinct type from obs_points where obsid in ({})".format(utils.sql_unicode_list(self.observations))
ConnOK, self.distincttypes = db_utils.sql_load_fr_db(sql)