import mysql.connector
from mysql.connector import errorcode
import re
from prettytable import PrettyTable

def new_record():
    s=""
    a=""
    b=""
    print("Please enter the table name")
    name=input(">>")
    ini_query="SELECT COLUMN_NAME as cn FROM information_schema.COLUMNS WHERE TABLE_NAME='{}';".format(name)
    cursor.execute(ini_query)
    data=cursor.fetchall()
    for i in range(len(data)):
        a+=str(data[i])
    s=re.findall(r"\w+", a)
    entered_data=s
    for i in range(len(s)):
        entered_data[i]=input("please enter {}:".format(s[i]))
        b+="%s"+","
    add_record=("INSERT INTO {} VALUES({});".format(name,b[0:-1]))

    cursor.execute(add_record,entered_data)
    cnx.commit()

def print_data():
    a=""
    c=""
    b=0
    d=0
    tab=[]
    print("Enter the table name")
    name=input(">>")
    ini_query="SELECT COLUMN_NAME as cn FROM information_schema.COLUMNS WHERE TABLE_NAME='{}';".format(name)
    cursor.execute(ini_query)
    data=cursor.fetchall()
    for i in range(len(data)):
                a+=str(data[i])
                b+=1
    s=re.findall(r"\w+", a)
    t=PrettyTable(s)
    query="SELECT * FROM {}".format(name)
    cursor.execute(query)
    data=cursor.fetchall()
    for i in range(len(data)):
            c+=str(data[i])
    n=re.findall(r"\w+", c)

    for i in range(len(n)):
        if d<b:
            d+=1
            tab.append(n[i])
        else:
            d=1
            t.add_row(tab)
            tab=[]
            tab.append(n[i])
    print(t)
def create_table():
    a=""
    d=""
    att_list={}
    print("Enter the table name:")
    name=str(input())
    print("Please enter number of attributes:")
    num=int(input())
    for i in range(num):
        print("Enter the {}.attribute name:".format(i+1))
        key=str(input())
        print("Enter the {} data type:".format(key))
        value=str(input())
        att_list[key]=value
    for i in att_list:
        d="{} {},".format(i,att_list[i])
        a+=d
        init="({})".format(a[0:-1])
    new_table="""CREATE TABLE {} {};""".format(name,init)
    try:
        print("Creating table second:")
        cursor.execute(new_table)
    except mysql.connector.Error as e:
        if e.errno==errorcode.ER_TABLE_EXISTS_ERROR:
            print("already exists")
        else:
            print(e.msg)
    else:
        print("done")
        cnx.commit()
def menu():
    exitt="no"
    while exitt=="no":
        print ("What do you want to do now?")
        print("1-create a table")
        print("2-add new record")
        print("3-Print table")
        print("4-exit")
        inp=str(input(">>"))
        if inp=="1":
            create_table()
        elif inp=="2":
            new_record()
        elif inp=="3":
            print_data()
        elif inp=="4":
            exitt="yes"
        else:
            menu()

if __name__=='__main__':

    cnx=mysql.connector.connect(user='******',password='******',host='*******',database='******')
    cursor=cnx.cursor()

    menu()
    cursor.close()
    cnx.close()