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")```
Your syntax for inserting multiple rows is wrong. You're generating
but it should be
You don't need to do all the looping and joining yourself.
cur.executemany()
will automatically repeat theVALUES
lists for you.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.