Click here to Skip to main content
15,922,574 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I have just started learning databases and started with SQL and I came to know about sqlconnector which would integrate python and sql easily.

So, I am working on a command line program which does all the major tasks done in MySQL but this time without using commands, I added a section in the code which can create a table of any degree, but now I need a section in the code which basically helps to add record in the table as per the number of columns.

Initially I tried to make separate conditions ( for eg- when degree of table is 3, I defined a function named which was explicitly for table of degree 3 and similarly for table of degree 4) , but later I made the table creator code a bit brief and more powerful by using a while loop to put as many number of columns in the table. The thing is, I want the function inputter to be also that much efficient and hence increasing the number of values I can enter in a field.

I have mentioned the code I am talking about. Thanks for your time.

What I have tried:

## to create a table
def table_creator():
    table_name = input("PLEASE ENTER THE NAME OF NEW TABLE:")

    a = input("NAME FOR COLUMN 1:")
    t = input("DATATYPE OF COLUMN 1:")
    size = input("SIZE OF COLUMN 1:")

    cursor_obj.execute("CREATE TABLE {} ({} {}({}))".format(table_name,a,t,size))
    x = int(input("PLEASE ENTER THE NUMBER OF COLUMNS YOU WANT IN TABLE:"))
    i = 1 
    while i < x:
        a1 = input("NAME FOR COLUMN {}:".format(i+1))
        t1 = input("DATATYPE FOR COLUMN {}:".format(i+1))
        size1 = input("SIZE OF COLUMN {}:".format(i+1))
        cursor_obj.execute('Alter table {} add column {} {}({})'.format(table_name,a1,t1,size1))



def inputer_count3():
    #taking records from user to enter in the table
    table_name = input("Please enter the table to insert value")
    a = input(" Do you want to see the table structure: ")

    if a == 'yes' or 'y':
            cursor_obj.execute("describe {}".format(table_name))
            for x in cursor_obj:
                print(x)

    elif a == 'no' or 'n':
        print("ok")
    else:
        print("INVALID SELECTION enter 'y' or 'yes for yes and 'n' or 'no for no")


    value1 = input("please enter the value for column 1:")
    value2 = input("please enter the value for column 2:")
    value3 = input("please enter the value for column 3:")

    #Entering values into the table

    cursor_obj.execute('insert into {} values("{}",{},{})'.format(table_name,value1,value2,value3))

def inputer_count4():
    #taking records from user to enter in the table
    table_name = input("Please enter the name of the table :")
    a = input("Do you want to see the table structure? ")

    if a == 'yes' or 'y':
        cursor_obj.execute("describe table {}".format(table_name))
        for i in cursor_obj:
            print(i)
    elif a == 'no' or 'n':
        print("ok")
    else:
        print("INVALID SELECTION enter 'y' or 'yes for yes and 'n' or 'no for no")    
    value1 = input("Please enter the value for column 1:")
    value2 = input("Please enter the value for column 2:")
    value3 = input("Please enter the value for column 3:")
    value4 = input("Please enter the value for column 4:")

    #Entering values into the table

    cursor_obj.execute('insert into {} values("{}",{},{},{})'.format(table_name,value1,value2,value3,value4))


elif choice == 'd':
    cursor_obj.execute("use practice")
    table_name = input("PLEASE ENTER THE NAME OF THE TABLE: ")
    cursor_obj.execute("set @a  = (select count(*) as count from information_schema.columns where table_name = '{}') ".format(table_name))
    cursor_obj.execute("select @a")
    for i in cursor_obj:
        x = i
    y = x[0]
    if y == 3:
        inputer_count3()
    elif y == 4:
        inputer_count4()
    else:
        print("Invalid selection, please redo the actions")
Posted
Updated 24-Aug-22 7:13am
v2
Comments
Richard MacCutchan 24-Aug-22 3:41am    
So what is the actual problem?
no name Aug2022 24-Aug-22 11:40am    
not a problem, I refer to it as a limitation. currently I only can input values into the table having 3 or 4 columns not more than that, I want to modify it in such a way that it allows user to enter record in n number of columns.
Richard MacCutchan 24-Aug-22 12:01pm    
Then you need an open ended loop:
while True:
    answer = input("Do you want to add another column? ")
    if answer == 'n' or answer == 'N':
        break
    # accumulate the columns here

Once the user enters N then you should have a collection of columns and you can use the names to create the table(s).
no name Aug2022 24-Aug-22 12:14pm    
but wont this just help in having desired number of column, I already have a section which helps in having desired number of columns. What I need is that once a table of n number of columns is created by user and later the user wishes to insert records in the table, the program automatically decides the number of columns are there in the table and hence the user is able to input record easily.

for example- I create a table with column 6 columns, then later if I wish to insert a record, I am not limited to inserting record only to a table of degree 3 or 4. The program guesses that I wish to insert record to a table with 6 columns.

I appreciate your help sir.
Richard MacCutchan 24-Aug-22 12:18pm    
You can get the number of columns from SQL via a SELECT command; Google should find the full syntax appropriate to the database you are using. You can then ask for the correct number of items to build the INSERT statement.

1 solution

just added this to mark this as solved question. Answers is in the comments.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900