def select_cluster_key(uid, table_data, conn):
params = []
for key in conn.cursor().execute(
"""
select kr.reference_column, kr.is_string,
kr.error_column_low, kr.error_column_high, kr.comment_column,
k.key, k.subkey, k.description,
ifnull(k.data_format, rc.output_format) as output_format
from reference_tables_keys kr
join keys k on k.key = kr.key
and ifnull(k.subkey, 'null') = ifnull(kr.subkey, 'null')
join reference_tables_columns rc on kr.reference_table = rc.reference_table
and kr.reference_column = rc.column_name
where kr.reference_table = '%s'
order by k.key, k.subkey"""
% table_data["table_name"]
):
select = "x.[%s] as reference_column" % key["reference_column"]
for extra in ["error_column_low", "error_column_high", "comment_column"]:
if key[extra] is not None:
select = "%s, x.[%s] as %s" % (select, key[extra], extra)
ext_row = table_data.copy()
ext_row["uid"] = uid
ext_row["select"] = select
values = conn.execute(
"""select {select}, r.reference_uid
from [{table_name}] x
join data_references r on r.reference_uid = [{uid_column}]
where r.cluster_uid = {uid}
and r.reference_table = '{table_name}'""".format(
**ext_row
)
).fetchall()
for value in values:
print value["reference_column"], key["output_format"]
par = {
"name": key["key"],
"desc": key["description"],
"value": format_value(value["reference_column"], key["output_format"]),
"source": "%s[%s]:%s" % (table_data["table_name"], value["reference_uid"], key["reference_column"]),
}
if key["error_column_low"] is not None:
par["err_low"] = format_value(value["error_column_low"], key["output_format"])
if key["error_column_high"] is not None:
par["err_high"] = format_value(value["error_column_high"], key["output_format"])
if key["comment_column"] is not None:
par["comment"] = key["comment_column"]
params.append(par)
return params
def single_cluster(uid):
t = JINJA.get_template("single_cluster.template")
CONN = get_conn(dict_row=True)
cur = CONN.cursor()
html_data = {}
result = CONN.execute(
"select source, source_id, comment, "
"xid_flag, xid_flag_source, xid_flag_comment,"
"obs_flag, obs_flag_source, obs_flag_comment,"
"ra, dec"
" from clusters where uid = %s" % uid
).fetchone()
html_data = dict(result)
for key in ["xid_flag_source", "xid_flag_comment", "obs_flag_source", "obs_flag_comment"]:
if html_data[key] is None:
html_data[key] = ""
html_data["uid"] = uid
html_data["ra"] = "%.6f" % html_data["ra"]
html_data["dec"] = "%.6f" % html_data["dec"]
html_data["params"] = []
html_data["tables"] = []
for key in CONN.cursor().execute(
"""
select k.key, k.subkey, k.description,
v.value, v.value_error_low, v.value_error_high,
v.comment,
ifnull(k.data_format, 's') output_format
from per_cluster_keys v
join keys k on k.key = v.key
and ifnull(k.subkey, 'null') = ifnull(v.subkey, 'null')
where v.uid = %s"""
% uid
):
if key["subkey"] is not None and key["subkey"] != "":
key_name = "%s,%s" % (key["key"], key["subkey"])
else:
key_name = key["key"]
par = {
"name": key_name,
"desc": key["description"],
"value": format_value(key["value"], key["output_format"]),
"err_low": key["value_error_low"],
"err_high": key["value_error_high"],
"comment": key["comment"],
"source": "User defined",
}
html_data["params"].append(par)
html_data["select_key"] = get_key_list(CONN)
mocs = []
for row in CONN.execute(
"""select m.moc_name,
coalesce(m.description, m.moc_name) as description,
m.vizier_catalog
from cluster_in_moc c
join mocs m on c.moc_name = m.moc_name
where uid = %s
union
select mm.moc_name, mm.description, mm.vizier_catalog
from mocs mm where mm.is_full_sky
"""
% uid
).fetchall():
moc = {"moc": row[0], "description": row[1], "link": ""}
if row[2] is not None:
url_data = {
"-source": row[2],
"-out.max": "unlimited",
"-out.form": "HTML Table",
"-out.add": "_r,_RAJ,_DEJ",
"-sort": "_r",
"-oc.form": "dec",
"-c": "%+f%+f" % (float(html_data["ra"]), float(html_data["dec"])),
"-c.eq": "J2000",
"-c.r": 2,
"-c.u": "arcmin",
"-c.geom": "r",
}
link = "http://vizier.u-strasbg.fr/viz-bin/VizieR-4?%s" % urlencode(url_data)
moc["link"] = '<a href="%s">Vizier data</a>' % link
mocs.append(moc)
html_data["mocs"] = mocs
for row in cur.execute(
"""select rt.table_name,
rt.uid_column,
rt.extra_column, rt.description,
rt.brief_columns
from reference_tables rt
where exists (select 1
from data_references d
where d.reference_table = rt.table_name
and d.cluster_uid = %s)
"""
% uid
):
row = dict(row)
column_list = get_brief_columns(row["table_name"], row["brief_columns"].split(","), negate=False)
column_list = ",".join(["x.[%s]" % x for x in column_list])
t1 = from_db_cursor(
CONN.execute(
"""
select {column_list}
from [{table_name}] x
join data_references r on r.reference_uid = [{uid_column}]
and r.reference_table = '{table_name}'
where r.cluster_uid = {uid}""".format(
column_list=column_list, uid=uid, **row
)
)
)
t1.border = True
t1.float_format = ".3e"
for column_properties in CONN.execute(
"""
select column_name, output_format, description,
data_unit, lower(data_type) as data_type
from reference_tables_columns
where reference_table = '%s'"""
% row["table_name"]
).fetchall():
if column_properties["data_type"] == "integer":
t1.int_format[column_properties["column_name"]] = column_properties["output_format"]
elif column_properties["data_type"] in ("real", "double"):
t1.float_format[column_properties["column_name"]] = column_properties["output_format"]
# Full table record (initially hidden)
cursor = CONN.execute(
"""
select x.*
from [{table_name}] x
join data_references r on r.reference_uid = [{uid_column}]
and r.reference_table = '{table_name}'
where r.cluster_uid = {uid}""".format(
uid=uid, **row
)
)
full_table = PrettiestTable()
full_table.add_column("Parameter", [item[0] for item in cursor.description])
for irow, arow in enumerate(cursor.fetchall()):
full_table.add_column(str(irow), arow)
if len(t1._rows) > 0:
html_data["tables"].append(
{
"title": row["description"],
"id": row["table_name"],
"full_table": full_table.get_html_string(attributes={"border": 1}),
"html": t1.get_html_string(attributes={"border": 1, "id": row["table_name"]}),
}
)
html_data["params"].extend(select_cluster_key(uid, row, CONN))
return t.render(html_data)