CODEWITHSUNDEEP
pythonmysqlmysql-pythonmysql-innodb-clusterpython-sql
Riku
Riku

Reputation: 23

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

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:

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")```

Upvotes: 1

Views: 60

Answers (1)

Barmar
Barmar

Reputation: 782107

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.

Upvotes: 2

Related Questions