def key_list_update(key, subkey, description, format): conn = get_conn() if subkey == '': subkey_cond = 'subkey is null' else: subkey_cond = "subkey = '%s'" % subkey check = conn.execute(u"""select count(*) from keys where key = '%s' and %s""" % (key, subkey_cond)).fetchone()[0] print subkey_cond if check > 0: conn.execute(u"""update keys set description = '%s', data_format = '%s' where key = '%s' and %s""" % (description, format, key, subkey_cond)) else: conn.execute(u""" insert into keys (key, subkey, description, data_format) values ('%s', nullif('%s', ''), '%s', '%s'); """ % (key, subkey, description, format)) conn.commit() return None
def edit_table_update_column(table, column_name, data_type, data_unit, output_format, description): """ Update a column metadata in the table. :param table: name of the table to edit; :param column_name: name of the column in the table; :param data_type: column data type (pythonic); :param data_unit: value units (arbitrary string); :param output_format: python format string; :param description: arbitrary description. """ conn = get_conn() conn.execute("""update reference_tables_columns set data_type = '%s', data_unit = '%s', output_format = '%s', description = '%s' where reference_table = '%s' and column_name = '%s' """ % (data_type, data_unit, output_format, description, table, column_name)).fetchone() conn.commit() return None
def single_cluster_update_comment(uid, comment): """ Updates comment for a cluster. """ CONN = get_conn() xcomment = comment.replace("'", "''") CONN.execute("update clusters set comment = '%s' where uid = %s" % (xcomment, uid)) CONN.commit() return None
def edit_table_key_delete(table, uid): """ Delete """ conn = get_conn() conn.execute(u""" delete from reference_tables_keys where reference_table = '%s' and uid = %s""" % (table, uid)) conn.commit() return None
def edit_table_key_update(table, mode, uid, key, reference_column, error_column_low, error_column_high, comment): conn = get_conn() if ',' in key: key, subkey = key.split(',') else: subkey = None if uid != '': check = conn.execute(u"""select key from reference_tables_keys where uid = %s""" % uid).rowcount else: check = 0 if mode == 'edit' or check > 0: conn.execute(u""" update reference_tables_keys set reference_column = %s, error_column_low = %s, error_column_high = %s, comment = %s, key = %s, subkey = %s where uid = %s """ % (nullify(reference_column), nullify(error_column_low), nullify(error_column_high), nullify(comment), nullify(key), nullify(subkey), uid)) conn.commit() else: r1 = conn.execute(""" select key from reference_tables_keys where reference_table = '%s' and key = '%s' and %s""" % (table, key, null_condition('subkey', subkey))) if r1.rowcount > 0: return "Already exists!" else: conn.execute(u""" insert into reference_tables_keys(reference_table, key, subkey, reference_column, error_column_low, error_column_high, comment) values ("%s", "%s", %s, %s, %s, %s, %s) """ % (table, key, nullify(subkey), nullify(reference_column), nullify(error_column_low), nullify(error_column_high), nullify(comment))) conn.commit() return None
def search(): t = JINJA.get_template('search.template') conn = get_conn() cur = conn.cursor() keys = get_key_list(conn, any_subkey=True) tables = cur.execute('select table_name from reference_tables').fetchall() mocs = cur.execute('select moc_name, ifnull(description, moc_name) from mocs').fetchall() html_data = {'table_x': quoted_list_item(tables, 0), 'moc_names': quoted_list_item(mocs, 0), 'moc_descr': quoted_list_item(mocs, 1), 'key_x': quoted_list(keys)} return t.render(html_data)
def moc_list(): t = JINJA.get_template('moc_list.template') t1 = from_db_cursor(get_conn().execute(""" select moc_name, moc_file, description, vizier_catalog, is_full_sky from mocs""")) select_moc = [] for row in t1._rows: if row[0] not in select_moc: select_moc.append(row[0]) return t.render({'moc_list': t1.get_html_string(attributes={'border': 1, 'id': 'moc_list'}), 'select_moc': select_moc})
def key_list(): t = JINJA.get_template('key_list.template') t1 = from_db_cursor(get_conn().execute(""" select key, ifnull(subkey, '') as subkey, description, data_format from keys k""")) select_key = [] for row in t1._rows: if row[0] not in select_key: select_key.append(row[0]) return t.render({'key_list': t1.get_html_string(attributes={'border': 1, 'id': 'key_list'}), 'select_key': select_key})
def search_mocs(mocname, mocfile): conn = get_conn() cur = conn.cursor() moc = MOCFinder(mocfile) conn.execute("""insert into mocs (moc_name, moc_file) values ('%s', '%s')""" % (mocname, mocfile)) for uid, ra, dec in cur.execute(""" select uid, ra, dec from clusters order by uid""").fetchall(): if moc.is_in(ra, dec): conn.execute("""insert into cluster_in_moc (uid, moc_name) values (%s, '%s')""" % (uid, mocname)) print mocname, uid conn.commit() cur.close()
def single_cluster_key_delete(uid, items): conn = get_conn() print items for item in items: key, subkey, subkey_cond = key_subkey_cond(item) conn.execute( """delete from per_cluster_keys where uid = %s and key = '%s' and %s""" % (uid, key, subkey_cond) ) conn.commit() return None
def edit_table_update(table, description, uid_column, brief_columns): """ Committing changes done to the table. """ conn = get_conn() conn.execute("""update reference_tables set description = '%s', uid_column = '%s', brief_columns = '%s' where table_name = '%s'""" % (description, uid_column, brief_columns, table)).fetchone() conn.commit() return open('static/table_edit_ok.html', 'r').readlines()
def single_cluster_update_obs_flag(uid, obs_flag, obs_flag_source, obs_flag_comment): """ Updates detection status for a cluster. """ CONN = get_conn() CONN.execute( """update clusters set obs_flag = %s, obs_flag_source = '%s', obs_flag_comment = '%s' where uid = %s""" % (obs_flag, obs_flag_source, obs_flag_comment, uid) ) CONN.commit() return None
def single_cluster_update_xid_flag(uid, xid_flag, xid_flag_source, xid_flag_comment): """ Updates xid status for a cluster. """ CONN = get_conn() CONN.execute( """update clusters set xid_flag = %s, xid_flag_source = '%s', xid_flag_comment = '%s' where uid = %s""" % (xid_flag, xid_flag_source, xid_flag_comment, uid) ) CONN.commit() return None
def key_list_delete(itemlist): conn = get_conn() for item in itemlist: if item[1] == '': subkey_cond = 'subkey is null' else: subkey_cond = "subkey = '%s'" % item[1] conn.execute("""delete from keys where key = '%s' and %s""" % (item[0], subkey_cond)) conn.execute("""delete from reference_tables_keys where key = '%s' and %s""" % (item[0], subkey_cond)) conn.execute("""delete from per_cluster_keys where key = '%s' and %s""" % (item[0], subkey_cond)) conn.commit() return None
def edit_tables(): """ List of data tables. """ t = JINJA.get_template('tables.template') cur = get_conn().cursor() html_data = {} html_data['tables'] = [] for row in cur.execute("""select rt.table_name, rt.uid_column, rt.extra_column, rt.description from reference_tables rt"""): html_data['tables'].append({ 'name': row[0], 'desc': row[3], 'uid': row[1]}) return t.render(html_data)
def edit_table(table): """ Editing single table. """ t = JINJA.get_template('edit_table.template') conn = get_conn() cur = conn.cursor() row = cur.execute("""select rt.table_name, rt.uid_column, rt.description, rt.brief_columns from reference_tables rt where table_name = '%s'""" % table).fetchone() cur.close() columns = get_table_columns(table) columns[0] = 'None' html_data = {'table': table, 'description': row[2], 'uid_column': row[1], 'brief_columns': row[3], 'key_list': get_key_list(conn), 'column_names': columns } t1 = from_db_cursor(conn.execute(""" select r.uid, k.key, k.subkey, k.description, k.data_format, r.reference_column, r.error_column_low, r.error_column_high, r.comment, "Delete" from keys k join reference_tables_keys r on r.key = k.key and ifnull(r.subkey, '') = ifnull(k.subkey, '') where r.reference_table = '%s' order by k.key, k.subkey""" % (table))) html_data['keys'] = t1.get_html_string(attributes={'border': 1, 'id': 'keys_table'}) t2 = from_db_cursor(conn.execute(""" select column_name, data_type, data_unit, output_format, description, ucd from reference_tables_columns where reference_table = '%s'""" % table)) html_data['columns'] = t2.get_html_string(attributes={'border': 1, 'id': 'columns_table'}) conn.close() return t.render(html_data)
def create_key(table, key, reference, error_low, error_high, comment, comment_column): conn = get_conn() key, subkey = key.split(',') row = [key, subkey, table, reference, error_low, error_high, comment, comment_column] row = ["'%s'" % item if item is not None and item != '' else 'null' for item in row] conn.execute("""insert into reference_tables_keys (key, subkey, reference_table, reference_column, error_column_low, error_column_high, comment, comment_column) values (%s)""" % ','.join(row)) conn.commit() if key in ['xid_flag', 'obs_flag']: cursor = conn.execute("""select uid_column from reference_tables where table_name = '%s'""" % table) update_flags(conn, key, table, flag_column=reference, flag_comment=comment_column, uid_column=cursor.fetchone()[0])
def sql_to_file(sql, output_name='default', write_format='ascii', automatic_extention=True): """ Run SQL query and save output to ascii (or other formats) file. """ cursor = get_conn().execute(sql) column_names = [] column_types = [] max_len = [] data = cursor.fetchall() for column_descr in cursor.description: column_names.append(column_descr[0]) for item in data[0]: max_len.append(0) column_types.append(VO_TYPES[type(item)]) for row in data: print row for icol, coltype in enumerate(column_types): print icol, coltype if coltype == 'S' and row[icol] is not None: if max_len[icol] < len(row[icol]): max_len[icol] = len(row[icol]) for icol, coltype in enumerate(column_types): if coltype == 'S': column_types[icol] = 'S%s' % max_len[icol] if len(data) == 0: table = ATable(names=column_names, dtype=column_types) else: table = ATable(data=zip(*data), names=column_names, dtype=column_types) if output_name is not None: if automatic_extention: file_name = '%s.%s' % (output_name, write_format) else: file_name = output_name temp_file = open('./static/output_cache/%s' % (file_name), 'w') else: temp_file = NamedTemporaryFile(delete=False, dir='./static/output_cache', suffix=write_format) table.write(temp_file, format=write_format) temp_file.close() return temp_file.name
def single_cluster_key_value_update(uid, old_key, key, key_value, key_err_low, key_err_high, key_comment): """ Update per cluster key. """ old_key, _, subkey_cond = key_subkey_cond(old_key) key, subkey, new_cond = key_subkey_cond(key) if subkey == "": subkey = "null" else: subkey = '"%s"' % subkey CONN = get_conn() if old_key == "" or old_key is None: # This might be a new one or an update of an old one. old_key = key subkey_cond = new_cond result = CONN.execute( """update per_cluster_keys set key = "%s", subkey = %s, value = "%s", value_error_low = "%s", value_error_high = "%s", comment = "%s" where uid = %s and key = "%s" and %s """ % (key, subkey, key_value, key_err_low, key_err_high, key_comment, uid, old_key, subkey_cond) ) if result.rowcount == 0: # This was a new key... CONN.execute( """insert into per_cluster_keys (uid, key, subkey, value, value_error_low, value_error_high, comment) values (%s, "%s", %s, "%s", "%s", "%s", "%s")""" % (uid, key, subkey, key_value, key_err_low, key_err_high, key_comment) ) CONN.commit() return None
def list_table(table): """ List the table content. """ t = JINJA.get_template('list_table.template') conn = get_conn() row = conn.execute("""select uid_column, description from reference_tables where table_name = '%s'""" % table).fetchone() sql = """select d.cluster_uid, t.* from [{table}] t join data_references d on d.reference_table = '{table}' and d.reference_uid = t.[{uid}]""".format(table=table, uid=row[0]) t1 = from_db_cursor(conn.execute(sql)) columns = conn.execute("""select column_name, data_type, output_format from reference_tables_columns where reference_table = '%s' order by uid""" % table).fetchall() columns_param = ['I'] # That is for UID for column_name, data_type, output_format in columns: if data_type.lower() in ('int', 'integer', 'long'): t1._int_format[column_name] = output_format columns_param.append('I') elif data_type.lower() in ('float', 'double', 'real'): t1._float_format[column_name] = output_format columns_param.append('F') else: columns_param.append('S') html_data = {'name': table, 'table': t1.get_html_string(attributes={'border': 1, 'id': 'list_table', 'columns': ''.join(columns_param)}), 'uid': row[0], 'desc': row[1], 'sql': sql} return t.render(html_data)
def vo_cone_search(args): """ Performs a cluster search request. Possible args keys are: ra, decl - cone center in degrees; radius - cone radius in arcmin; fullsky - perform full-sky search. Cone constraints are ignored; in_table - name of the table or list of table names, constraint will be set on cluster presence/ absence in this table(s); has_record - list of 'exists' or 'not exists' strings, one for each value in in_table; condition \ in_key | constraint| expression/ in_moc - name of MOC or list of MOCs, constraint will be set on cluster entering/ not entering the MOC area; has_moc - list of 'exists' or 'not exists' strings, one for each value in in_moc; """ conn = get_conn() conditions = [] if 'fullsky' not in args: constraint, extra_columns = get_cone_search_constraint(args) else: extra_columns = '' if 'in_table' in args: if isinstance(args['in_table'], basestring): conditions.append(get_table_constraint(args['has_record'], args['in_table'])) else: for itable, table in enumerate(args['in_table']): conditions.append( get_table_constraint(args['has_record'][itable], table)) if 'condition' in args: extra_counter = 0 args = dict_values_to_list(args, ['condition', 'in_key', 'constraint', 'expression']) for icondition, condition in enumerate(args['condition']): key, subkey, subkey_cond = \ get_subkey_constraint(args['in_key'][icondition]) if condition != 'extra': conditions.append("""and %s (select 1 from reference_tables_keys k join data_references dr on dr.reference_table = k.reference_table where dr.cluster_uid = c.uid and key = '%s' and %s)""" % (condition, key, subkey_cond)) else: expr = '%s %s' % (args['constraint'][extra_counter], args['expression'][extra_counter]) conditions.append(build_key_constraint(conn, key, subkey_cond, expr)) extra_counter = extra_counter + 1 if 'has_moc' in args: args = dict_values_to_list(args, ['has_moc', 'in_moc']) for cond, moc in zip(args['has_moc'], args['in_moc']): conditions.append(""" and %s (select 1 from cluster_in_moc m where m.uid = c.uid and m.moc_name = '%s') """ % (cond, moc)) if 'flag_name' in args: args = dict_values_to_list(args, ['flag_name', 'flag_constraint', 'xid_values', 'obs_values']) for iflag, flag in enumerate(args['flag_name']): if flag == 'obs_flag': value = args['obs_values'][iflag] else: value = args['xid_values'][iflag] conditions.append(""" and %s %s %s""" % (flag, args['flag_constraint'][iflag], value)) t = JINJA.get_template('vo_cone_search.template') sql = """select c.uid, %s ra, dec, c.source, source_id, xid_flag, obs_flag, group_concat(distinct r.reference_table) as Tables from clusters c join data_references r on r.cluster_uid = c.uid where 1=1 %s group by c.uid, ra, dec, c.source, source_id order by c.ra""" % (extra_columns, ' '.join(conditions)) print sql t1 = from_db_cursor(conn.execute(sql)) t1.float_format['ra'] = '.5f' t1.float_format['dec'] = '.5f' html_data = {'table': t1.get_html_string(attributes={'border': 1, 'id': 'search', 'columns': 'IFFSIIIS'}, unescape=[('Tables')])} return t.render(html_data)
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)
def create_table(file_name, file_type, table_name, description, uid_column, ra_column, dec_column, brief_columns='*', gal_l=None, gal_b=None, delimiter=',', reference_table=None, reference_column=None, xid_column=None, xid_value=None, xid_comment=None, obs_column=None, obs_value=None, obs_comment=None): # Import data first: if file_type is not None and file_type in ['ascii', 'ascii.csv']: table = Table.read(file_name, format=file_type, delimiter=delimiter) elif file_type is not None: table = Table.read(file_name, format=file_type) else: table = Table.read(file_name) if table_name is not None: table.meta['table_name'] = '[%s]' % table_name colnames = [] tnames = table.colnames for t in tnames: if t.lower() in colnames: table.rename_column(t, '%s_' % t) colnames.append(t.lower()) table.write('%sAIP_clusters.sqlite' % DB_LOCATION, format='sql', dbtype='sqlite') print 'Table %s created' % table_name # Now proceed metadata: conn = get_conn() conn.execute("""create unique index idx_{0}_uid on [{0}]({1})""".format(table_name, uid_column)) conn.execute("insert into reference_tables(table_name, uid_column, " "description, ra_column, dec_column, " "brief_columns, obs_class_global, obs_class_value)" "values ('%s', '%s', '%s', '%s', '%s', " "'%s', 'true', 0)" % (table_name, uid_column, description, ra_column, dec_column, brief_columns)) for colname in table.columns.keys(): column = table.columns[colname] form = column.format[1:] if column.format is not None else 's' if 'ucd' in column.meta: ucd = column.meta['ucd'] else: ucd = '' sql = """insert into reference_tables_columns (reference_table, column_name, data_type, data_unit, output_format, ucd) values ('%s', '%s', '%s', '%s', '%s', '%s')""" % ( table_name, column.name, get_field_datatype(column), column.unit, form, ucd) conn.execute(sql) if reference_table is None: cross_match(conn, table, ra_column, dec_column, gal_l, gal_b, uid_column, table_name) else: # Add references: rt_uid = conn.execute("""select uid_column from reference_tables where table_name = '%s'""" % reference_table) rt_uid = rt_uid.fetchone()[0] sql = """ insert into data_references (cluster_uid, reference_table, reference_uid) select c.uid, '{0}', {4} from clusters c join data_references d on c.uid = d.cluster_uid and d.reference_table = '{1}' join [{1}] r on d.reference_uid = r.{2} join [{0}] x on x.{3} = r.{3}""".format(table_name, reference_table, rt_uid, reference_column, uid_column) print sql conn.execute(sql) insert_clusters(conn, table_name, uid_column, ra_column, dec_column, gal_l, gal_b) if xid_value is not None: update_flags(conn, 'xid_flag', table, flag_value=xid_value, flag_comment=xid_comment, uid_column=uid_column) if obs_value is not None: update_flags(conn, 'obs_flag', table, flag_value=obs_value, flag_comment=obs_comment, uid_column=uid_column) conn.commit() print 'Done'
# -*- coding: utf-8 -*- """ Fill reference_tables_columns for an existing table. WARNING! Data in reference_tables_columns for this table will be re-created. Created on Sat Jan 24 12:07:16 2015 @author: minz """ import sys from globals import get_conn table = sys.argv[1] conn = get_conn() data = conn.execute("pragma table_info('%s')" % table).fetchall() for coldef in data: unit = '' colname = coldef[1] format = coldef[2].split()[0] sql = """insert into reference_tables_columns (reference_table, column_name, data_type, data_unit, output_format) values ('%s', '%s', '%s', '%s', '%s')""" % ( table, colname, format, unit, '%s') conn.execute(sql) conn.commit() conn.close()