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()
