def query(dbname):
"Perform a query of the database; return rows."
try:
db = dbshare.db.get_check_read(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
timer = utils.Timer()
try:
query = flask.request.get_json()
sql = dbshare.query.get_sql_statement(query)
dbcnx = dbshare.db.get_cnx(dbname)
cursor = utils.execute_timeout(dbcnx, sql)
except (jsonschema.ValidationError, sqlite3.Error) as error:
utils.abort_json(http.client.BAD_REQUEST, error)
except SystemError:
flask.abort(http.client.REQUEST_TIMEOUT)
columns = [d[0] for d in cursor.description]
rows = cursor.fetchall()
result = {
"query": query,
"sql": sql,
"nrows": len(rows),
"columns": columns,
"cpu_time": timer(),
"data": [dict(zip(columns, row)) for row in rows],
}
return utils.jsonify(utils.get_json(**result), "/query/output")
def rows_csv(dbname, viewname):
"Return the rows in CSV format."
try:
db = dbshare.db.get_check_read(dbname, nrows=[viewname])
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
schema = db["views"][viewname]
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
dbcnx = dbshare.db.get_cnx(dbname)
columns = [c["name"] for c in schema["columns"]]
colnames = ",".join([f'"{c}"' for c in columns])
sql = f'SELECT {colnames} FROM "{viewname}"'
try:
cursor = utils.execute_timeout(dbcnx, sql)
except SystemError:
flask.abort(http.client.REQUEST_TIMEOUT)
except sqlite3.Error:
flask.abort(http.client.INTERNAL_SERVER_ERROR)
writer = utils.CsvWriter(header=columns)
writer.write_rows(cursor)
return flask.Response(writer.getvalue(), mimetype=constants.CSV_MIMETYPE)
def rows_json(dbname, tablename):
"Return the rows in JSON format."
try:
db = dbshare.db.get_check_read(dbname)
except ValueError:
flask.abort(http.client.UNAUTHORIZED)
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
schema = db["tables"][tablename]
except KeyError:
flask.abort(http.client.NOT_FOUND)
try:
dbcnx = dbshare.db.get_cnx(dbname)
columns = [c["name"] for c in schema["columns"]]
colnames = ",".join([f'"{c}"' for c in columns])
sql = f'SELECT {colnames} FROM "{tablename}"'
try:
cursor = utils.execute_timeout(dbcnx, sql)
except SystemError:
flask.abort(http.client.REQUEST_TIMEOUT)
except sqlite3.Error:
flask.abort(http.client.INTERNAL_SERVER_ERROR)
result = {
"name": tablename,
"title": schema.get("title") or "Table {}".format(tablename),
"source": {
"type":
"table",
"href":
utils.url_for("api_table.table",
dbname=db["name"],
tablename=tablename),
},
"nrows": schema["nrows"],
"data": [dict(zip(columns, row)) for row in cursor],
}
return utils.jsonify(utils.get_json(**result), "/rows")