def tables(db='main', matching_text=None, print_list=False):
"""Get the database tables as a python list"""
list_ = []
try:
conn = psycopg2.connect(conn_str(db))
cur = conn.cursor()
allTablesSql = """
SELECT table_name
FROM information_schema.tables
WHERE table_type='BASE TABLE'
AND table_schema='public'
ORDER BY table_name ASC;
"""
# Execute the query
cur.execute(allTablesSql)
for row in cur:
list_.append(row[0])
if matching_text is not None:
value = config.autoselect(matching_text, list_)
if value is not None:
list_.remove(value)
list_.insert(0, value)
if print_list is True:
print(f"-Tables in database {db}:-")
for t_ in list_:
print(t_)
else:
return list_
except Exception:
return []
def get_value(dict_keys, var_name=None):
"""Get value for tables.
Example:
database.get_value(['database', 'table'], variable_table_name)
Arguments:
dict_keys, list of keys to get value from.
var_name, the name ofthe variable
"""
if dict_keys[0][-1] == '2':
config_value = config.get_value(dict_keys)
value = config.autoselect(config_value, tables(2), True)
else:
config_value = config.get_value(dict_keys)
value = config.autoselect(config_value, tables(1), True)
if var_name is not None:
if value == None:
print(f"!WARNING! The value for table '{var_name}' is: '{value}'.")
else:
print(f"The value for table '{var_name}' is: '{value}'.")
return value
def table_columns(table, db='main', matching_text=None):
"""Get a list of the columns from a table."""
conn = psycopg2.connect(conn_str(db))
try:
getTableColumns = f"""
SELECT column_name
FROM information_schema.columns
WHERE table_name = '{table}';
"""
df_columns = pd.read_sql_query(getTableColumns, conn)
columns_list = df_columns['column_name'].tolist()
if matching_text is not None:
value = config.autoselect(matching_text, columns_list)
if value is not None:
columns_list.remove(value)
columns_list.insert(0, value)
except Exception:
print("Did not find any columns...")
df_columns = pd.DataFrame(columns=['column_name'])
return columns_list
def dsc_config(dsc_value):
values = config.read()
ds_db = Dropdown(options=["1"],
value="1",
description='Database:',
disabled=False,
layout=Layout(width='140px'))
try:
with open(f"{config.get_value(['paths','temp'])}tb_prefix",
'r') as f:
code_value = f.read()
except Exception:
code_value = dsc_value
ds_code = Combobox(
value=code_value,
placeholder='abc',
options=[m for m in data_options.eu_ms()] + [''],
description='AOI code:',
ensure_option=False,
disabled=False,
layout=Layout(width='200px'),
tooltip='Lowercase AOI code name for the dataset (5chr max).')
ds_year = BoundedIntText(value=int(dsy.value),
min=1980,
max=2100,
step=1,
description='Dataset year:',
disabled=False,
layout=Layout(width='180px'))
ds_desc = Text(value=values['ds_conf'][dsc_value]['desc'],
description='Description:',
disabled=False)
info_map_text = [
"Set default map view options. ",
"You can get automatically the dataset ", "center coordinates."
]
lat, lon = values['ds_conf'][dsc_value]['center'].split(",")
map_cent_lat = FloatText(value=float(lat),
description='Lat:',
disabled=False,
layout=Layout(width='160px'))
map_cent_lon = FloatText(value=float(lon),
description='Lon:',
disabled=False,
layout=Layout(width='160px'))
map_zoom = BoundedIntText(value=values['ds_conf'][dsc_value]['zoom'],
min=0,
max=20,
step=1,
description='Zoom:',
disabled=False,
layout=Layout(width='140px'))
bt_get_center = Button(layout=Layout(width='40px'),
icon='bullseye',
tooltip='Get center point from database.')
ds_box = HBox([ds_code, ds_year, ds_desc])
map_box = HBox([
Label("Map center: "), map_cent_lat, map_cent_lon, bt_get_center,
map_zoom
])
info_config = Label(
"""Change 'AOI code' value to create a new configuration set or
leave the same 'AOI code' value to configure the selected one.""")
db = int(values['ds_conf'][dsc_value]['db'])
def get_tb_list():
tbls = database.tables(db, None, False)
if tbls is None:
return []
else:
return tbls
tb_dc = Dropdown(options=get_tb_list(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['tables']['dias_catalog'],
get_tb_list(), False),
description='DIAS catalog:',
disabled=False)
tb_pr = Dropdown(options=get_tb_list(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['tables']['parcels'],
get_tb_list(), False),
description='Parcels:',
disabled=False)
def get_pr_columns():
try:
colms = database.table_columns(tb_pr.value, 1, None)
if colms is None:
return []
else:
return colms
except Exception:
return []
tc_id = Dropdown(options=get_pr_columns(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['columns']['parcels_id'],
get_pr_columns(), False),
description='Parcels ID:',
disabled=False,
layout=Layout(width='180px'))
tc_cn = Dropdown(options=get_pr_columns(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['columns']['crop_names'],
get_pr_columns(), False),
description='Crop names:',
disabled=False,
layout=Layout(width='180px'))
tc_cc = Dropdown(options=get_pr_columns(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['columns']['crop_codes'],
get_pr_columns(), False),
description='Crop codes:',
disabled=False,
layout=Layout(width='180px'))
def on_tb_pr_change(change):
tc_id.options = get_pr_columns()
tc_cn.options = get_pr_columns()
tc_cc.options = get_pr_columns()
tb_pr.observe(on_tb_pr_change, 'value')
parcel_box = HBox([tb_pr, tc_id, tc_cn, tc_cc])
tb_s2 = Dropdown(options=get_tb_list(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['tables']['s2'],
get_tb_list(), False),
description='S2 signatures:',
disabled=False)
tb_bs = Dropdown(options=get_tb_list(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['tables']['bs'],
get_tb_list(), False),
description='Backscattering:',
disabled=False)
tb_6c = Dropdown(options=get_tb_list(),
value=config.autoselect(
values['ds_conf'][dsc_value]['years'][str(
ds_year.value)]['tables']['c6'],
get_tb_list(), False),
description='6 day coherence:',
disabled=False)
wb_save = Button(description='Save', disabled=False, icon='save')
@bt_get_center.on_click
def bt_get_center_on_click(b):
import json
center_json = json.loads(
database.getTableCentroid(tb_pr.value)['center'][0])
map_cent_lat.value = round(center_json['coordinates'][1], 2)
map_cent_lon.value = round(center_json['coordinates'][0], 2)
map_zoom.value = 10
@wb_save.on_click
def wb_save_on_click(b):
progress.clear_output()
dscode = ds_code.value
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'tables', 'dias_catalog'
], str(tb_dc.value))
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'tables', 'parcels'
], str(tb_pr.value))
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'columns', 'parcels_id'
], str(tc_id.value))
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'columns', 'crop_names'
], str(tc_cn.value))
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'columns', 'crop_codes'
], str(tc_cc.value))
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'tables', 's2'
], str(tb_s2.value))
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'tables', 'bs'
], str(tb_bs.value))
config.update([
'ds_conf', dscode, 'years',
str(ds_year.value), 'tables', 'c6'
], str(tb_6c.value))
config.update(['ds_conf', dscode, 'db'], str(ds_db.value))
config.update(['ds_conf', dscode, 'desc'], str(ds_desc.value))
config.update(['ds_conf', dscode, 'center'],
f"{map_cent_lat.value},{map_cent_lon.value}")
config.update(['ds_conf', dscode, 'zoom'], str(map_zoom.value))
config.update(['set', 'ds_conf'], str(dscode))
config.update(['set', 'ds_year'], str(ds_year.value))
values = config.read()
ds_c = values['set']['ds_conf']
ds_y = values['set']['ds_year']
dsc.options = [d for d in values['ds_conf']]
dsy.options = [int(y) for y in values['ds_conf'][ds_c]['years']]
dsc.value = ds_c
dsy.value = int(ds_y)
outlog("The configurations are saved.")
return VBox([
info_config, ds_box, parcel_box, tb_dc, tb_s2, tb_bs, tb_6c,
Label(''.join(info_map_text)), map_box, wb_save
])
