I'm having problems trying to insert a varying number of values in a column, python-sql-CodePudding

Im new to sql, I have been trying to make a function that will allow creation of new columns and to fill it.

Its been giving these two errors:

Failed executing the operation; Not all parameters were used in the SQL statement - for the first if
Failed executing the operation; Not enough parameters for the SQL statement - for the elif

Anyother changes I should make or adivce are all welcome

def alter_table():
    tbl_name = input("Which table whould you like to edit:")
    opr = int(input("Do you want to ADD or DROP a column, Type: \n1: ADD \n2: DROP\n"))
    if opr == 1:
        
        name = input("What will this column be called: ")
        typ = int(input("What type of information will be stored in this column, Type: \n1: ONLY Numbers \n2: Letters and Numbers\n"))
        if typ == 1:
            cur.execute("ALTER TABLE {} ADD {} float".format(tbl_name,name))

        
            input_list = (input("Enter the values you want to store seperated by spaces")).split()
            value_list = [(int(i),)for i in input_list]
            
            n = "%d"*len(input_list)
            n_param = ','.join(n[i:i   2] for i in range(0, len(n), 2))
            query = "INSERT INTO {}({}) VALUES ({}) ON DUPLICATE KEY UPDATE {} = VALUES({})".format(tbl_name,name,n_param,tbl_name,tbl_name)
            cur.executemany(query, value_list)
        elif typ == 2:
            n_char = int(input("Maximum character limit for this column: "))
            cur.execute("ALTER TABLE {} ADD {} VARCHAR({})".format(tbl_name,name,n_char))

        
            input_list = (input("Enter the values you want to store seperated by spaces")).split()
            value_list = [(i,)for i in input_list]
            
            n = "%s"*len(input_list)
            n_param = ','.join(n[i:i   2] for i in range(0, len(n), 2))
            query = "INSERT INTO {}({}) VALUES ({}) ON DUPLICATE KEY UPDATE {} = VALUES({})".format(tbl_name,name,n_param,tbl_name,tbl_name)
            cur.executemany(query, value_list)

    elif opr == 2:
        name = input("Which column would you like to drop: ")
        cur.execute("ALTER TABLE {} DROP COLUMN {}".format(tbl_name,name))
        print("Column Deleted")```

CodePudding user response：

Your syntax for inserting multiple rows is wrong. You're generating

VALUES ((row1), (row2), ...)

but it should be

VALUES (row1), (row2), ...

You don't need to do all the looping and joining yourself. cur.executemany() will automatically repeat the VALUES lists for you.

            input_list = (input("Enter the values you want to store seperated by spaces")).split()
            value_list = [(int(i),)for i in input_list]
            
            query = f"INSERT INTO {tbl_name} ({name}) VALUES (%s)"
            cur.executemany(query, value_list)

There's no point in using ON DUPLICATE KEY, since you're not inserting into the key column of the table. So there's no way to tell if a row is a duplicate.