def raw_sql():
query = request.json.get("query", None)
if not query:
return sender.BadRequest()
curr = getConnection(session["user-token"])[0].cursor()
try:
curr.execute(query)
return sender.OK(curr.fetchall())
except Exception as e:
if "no results to fetch" == str(e): # not an error
return sender.OK("executed")
else:
return sender.Error(str(e))
def get_tables():
schema = request.json.get('schema', None)
if not schema:
return sender.BadRequest("Missing parameter: schema")
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT table_name FROM information_schema.tables
WHERE table_schema = '{}'""".format(schema)
try:
curr.execute(query)
rows = [row[0] for row in curr.fetchall()]
if len(rows) == 0:
return sender.OK({"message": "Schema doesn't exists or is empty"})
return sender.OK(rows)
except Exception as e:
return sender.Error(str(e))
def get_table_data():
tablename = request.json.get('table', None)
schemaname = request.json.get('schema', None)
if not tablename:
return sender.BadRequest("missing field: table")
if not schemaname:
schemaname = "public"
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT *
FROM {}.\"{}\"
LIMIT 20""".format(schemaname,tablename)
try:
curr.execute(query)
rows = curr.fetchall()
data = {}
if len(rows) == 0:
data["message"] = "Table doesn't exists or is empty"
else:
data["headers"] = [desc[0] for desc in curr.description]
dataQuery = """SELECT data_type FROM
information_schema.columns WHERE
table_name = '{}' AND table_schema = '{}';""".format(tablename,schemaname);
curr.execute(dataQuery);
data["types"] = [type[0] for type in curr.fetchall()]
data["values"] = rows
return sender.OK(data)
except Exception as e:
return sender.Error(str(e))
def login():
if session.get("user-token", None):
return sender.Forbidden("Already logged in! Logout first")
username = request.json.get("username", None)
password = request.json.get("password", None)
database = request.json.get("database", None)
if database == "":
database = "postgres"
request.json["database"] = database
host = request.json.get("host", None)
port = request.json.get("port", None)
if not (username and password and host and port):
return sender.BadRequest("Missing parameters")
connstring = "dbname='{}' user='******' host='{}' password='******' port={}".format(
database, username, host, password, port)
try:
conn = psycopg2.connect(connstring)
conn.set_session(autocommit=True)
except Exception as e:
return sender.Forbidden("Recheck credentials")
conndict = request.json
conndict.pop("password")
# Add object to session
session["user-token"] = setConnection([conn, conndict])
return sender.OK("Logged in", session["user-token"])
def logout():
# Check if logged in
if "user-token" in session:
closeConnection(session["user-token"])
session.pop("user-token")
return sender.OK("Logged out!")
else:
return sender.Forbidden("Login first!")
def get_schemas():
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT schema_name FROM information_schema.schemata"""
try:
curr.execute(query)
rows = [row[0] for row in curr.fetchall()]
return sender.OK(rows)
except Exception as e:
return sender.Error(str(e))
def create_schema():
name = request.json.get('schema', None)
if not name:
return sender.BadRequest("missing field: schema")
curr = getConnection(session["user-token"])[0].cursor()
query = "CREATE SCHEMA \"{}\";".format(name)
try:
curr.execute(query)
return sender.OK("Schema {} successfully created!".format(name))
except Exception as e:
return sender.Error(str(e))
def drop_schema():
name = request.json.get('schema', None)
if not name:
return sender.BadRequest("missing field: schema")
not_allowed = ["pg_toast", "pg_catalog", "public", "information_schema"]
if name in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = "DROP SCHEMA \"{}\";".format(name)
try:
curr.execute(query)
return sender.OK("Schema {} successfully dropped!".format(name))
except Exception as e:
return sender.Error(str(e))
def rename_schema():
name = request.json.get('schema', None)
new_name = request.json.get('new_name', None)
if not name or not new_name:
return sender.BadRequest()
not_allowed = ["pg_toast", "pg_catalog","public","information_schema"]
if name in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = "ALTER SCHEMA \"{}\" RENAME TO \"{}\";".format(name, new_name)
try:
curr.execute(query)
return sender.OK("Schema {} successfully renamed to {}!".format(name, new_name))
except Exception as e:
return sender.Error(str(e))
def drop_table():
schema = request.json.get("schema", None)
name = request.json.get("table", None)
if not name:
return sender.BadRequest()
not_allowed = ["pg_toast", "pg_catalog", "information_schema"]
if not schema:
schema = "public"
if schema in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = "DROP TABLE \"{}\".\"{}\";".format(schema, name)
try:
curr.execute(query)
return sender.OK("Table {} successfully dropped".format(name))
except Exception as e:
return sender.Error(str(e))
def import_table():
table_name = request.json.get('table', None)
schema_name = request.json.get('schema', None)
data = request.json.get('data', None)
if not table_name or not data:
return sender.BadRequest()
if not schema_name:
schema_name = "public"
columns = data.get('columns', None)
values = data.get('values', None)
if not columns or not values:
return sender.BadRequest('Export data malformed')
try:
conn, connstring = getConnection(session["user-token"])
curr = conn.cursor()
check_query = """SELECT * FROM information_schema.tables WHERE
table_name='{}'
AND table_schema='{}'""".format(table_name, schema_name)
curr.execute(check_query)
if len(curr.fetchall()) != 0:
return sender.Error("Table already exists. Remove it to import")
# create table
create_query = """CREATE TABLE {}.\"{}\"(""".format(
schema_name, table_name)
for items in columns:
create_query = create_query + items[0] + " " + items[4]
if items[3] == "NO":
create_query = create_query + " NOT NULL"
if items[2]:
create_query = create_query + " DEFAULT " + items[2]
create_query = create_query + ","
create_query = create_query[:-1] + ");"
curr.execute(create_query)
# insert data
insert_query = """INSERT INTO {}.\"{}\" VALUES""".format(
schema_name, table_name)
insert_query = insert_query + "(%s" + ", %s" * (len(values[0]) -
1) + ")"
curr.executemany(insert_query, values)
return sender.OK("Data imported successfully!")
except Exception as e:
return sender.Error(str(e))
def rename_table():
schema = request.json.get("schema", None)
name = request.json.get("table", None)
new_name = request.json.get("new_name", None)
if not name or not new_name:
return sender.BadRequest()
not_allowed = ["pg_toast", "pg_catalog", "information_schema"]
if not schema:
schema = "public"
if schema in not_allowed:
return sender.Forbidden("Not allowed")
curr = getConnection(session["user-token"])[0].cursor()
query = """ALTER TABLE {}.{}
RENAME TO \"{}\"""".format(schema, name, new_name)
try:
curr.execute(query)
return sender.OK("Table {} successfully renamed to {}".format(
name, new_name))
except Exception as e:
return sender.Error(str(e))
def get_table_info():
tablename = request.json.get('table', None)
schemaname = request.json.get('schema', None)
if not tablename:
return sender.BadRequest("missing field: table")
if not schemaname:
schemaname = "public"
data = {}
curr = getConnection(session["user-token"])[0].cursor()
query = """SELECT
table_schema,
table_name,
column_name,
data_type,
column_default,
is_nullable
FROM information_schema.columns
WHERE table_name = '{}' AND table_schema = '{}';""".format(
tablename, schemaname)
try:
curr.execute(query)
rows = curr.fetchall()
if len(rows) == 0:
data["message"] = "Table doesn't exists or is empty"
else:
data["schema"] = rows[0][0]
data["table"] = rows[0][1]
data["columns"] = []
for row in rows:
data["columns"].append({
"name": row[2],
"type": row[3],
"default": row[4],
"nullable": row[5]
})
return sender.OK(data)
except Exception as e:
return sender.Error(str(e))