DatabaseSchemaRetrival-Flask/app.py at main · HemantKMehta/DatabaseSchemaRetrival-Flask · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
from flask import Flask, request
import mysql.connector

app = Flask(__name__)

@app.route('/users',methods=['POST'])
def storeuser_DB():
    username = request.form['username']
    password = request.form['password']
    with open("users.txt","w") as userfile:
        userfile.write(username+":"+password)
    return "Successfully Received Database credentials, feel free to visit the http://localhost:5000/schema/dbname or http://localhost:5000/table/dbname/tablename"

@app.route('/schema/<dbname>')
def getschema_DB(dbname):
    str_err = "Username is empty, please use /users endpoint to pass the database username and password first"
    result = "<pre> database: "+dbname+"\n"
    username=""
    password=""
    with open("users.txt","r") as userfile:
        for line in userfile:
            words = line.split(":")
            username = words[0]
            password = words[1]
    if not bool(username) or not bool(password):
       return str_err
    mydb = mysql.connector.connect(host="localhost", user=username, password=password, database=dbname)
    cursor = mydb.cursor()
    cursor.execute("SHOW TABLES")
    tablenames=cursor.fetchall()
    result += "<pre>Table Details"+"\n"
    cursor.close()
    for table_name in tablenames:
        result += "<pre> Table: "+str(table_name).strip("'),(")+"\n"
        cursor1 = mydb.cursor()
        sql = "describe "+str(table_name).strip("'),(")
        cursor1.execute(sql)
        for row in cursor1.fetchall():
            result += "<pre>"+str(row)+"\n"
    return result

@app.route('/table/<dbname>/<tablename>')
def search_table(dbname, tablename):
    str_err = "Username is empty, please use /users endpoint to pass the database username and password first"
    str_notfounferr = "There is no table in the database with name "+str(tablename)
    result = ""
    username=""
    password=""
    with open("users.txt","r") as userfile:
        for line in userfile:
            words = line.split(":")
            username = words[0]
            password = words[1]
    if not bool(username) or not bool(password):
       return str_err
    mydb = mysql.connector.connect(host="localhost", user=username, password=password, database=dbname)
    cursor = mydb.cursor()
    cursor.execute("SHOW TABLES")
    tablenames = cursor.fetchall()
    result += "<pre>Table Details" + "\n"
    cursor.close()
    flag=0
    for table_name in tablenames:
        if (str(table_name).strip("'),(") == tablename):
            flag=1
            result += "<pre> Table: " + str(table_name).strip("'),(") + " found with schema\n"
            cursor1 = mydb.cursor()
            sql = "describe " + str(table_name).strip("'),(")
            cursor1.execute(sql)
            for row in cursor1.fetchall():
                result += "<pre>" + str(row) + "\n"
    if ( flag == 0 ):
        return str_notfounferr
    return result

@app.route('/')
def index():
   return ('<pre> Welcome to database Schema Retrival API '
           '<pre>it has three other endpoints '
           '<pre>1. To provide database credential available at http://localhost:5000 call this from login.html from project folder'
           '<pre>2. To retrive database schema details available at http://localhost:5000/schema/dbname'
           '<pre>3. To search a table in database available at httl://localhost:5000/table/dbname/tablename')

if __name__ == '__main__':
    app.run(debug = True)