Cant insert data into a table using Python

Python Help
1

Please help. Thankyou.

import mysql.connector
 
# Creating connection object
mydb = mysql.connector.connect(
    host = "localhost",
    user = "root",
    password = "mypass",
    database = "retails"
)
 
cursor = mydb.cursor()

#cursor.execute("SHOW TABLES")
#for x in cursor:
#    print(x)
#print("\n")
    
cursor.execute("DROP TABLE IF EXISTS customer")
cursor.execute("DROP TABLE IF EXISTS orders")

cursor.execute("SHOW TABLES")
for x in cursor:
    print(x)

print('\n')
cursor.execute("CREATE TABLE customer (name VARCHAR(255), address VARCHAR(255))")

cursor.execute("CREATE TABLE orders(veg VARCHAR(255), nonveg VARCHAR(255))")
cursor.execute("SHOW TABLES")
for x in cursor:
    print(x)

    
cursor.execute("ALTER TABLE orders ADD is_sale VARCHAR(100) NOT NULL")

cursor.execute("SHOW COLUMNS FROM orders FROM retails")
print('\n')
for x in cursor:
    print(x)
    
print('\n')

heads = ("INSERT INTO customer (Number, Age, Town, Sex ) VALUES (%d, %d, %s, %s)")
valus = [(1001, 34, 'Austin', 'male'),
         (1002, 37, 'Houston', 'male'),
         (1003, 25, 'Austin', 'female'),
         (1004, 28, 'Houston', 'female'),
         (1005, 22, 'Dallas', 'male')]
 
cursor.execute(heads, valus)
#db.commit()
 
#print(cursor.rowcount, "details inserted")
 
# disconnecting from server
#db.close()

The error given is also given here below:

MySQLInterfaceError                       Traceback (most recent call last)
Cell In[2], line 50
     43 heads = ("INSERT INTO customer (Number, Age, Town, Sex ) VALUES (%d, %d, %s, %s)")
     44 valus = [(1001, 34, 'Austin', 'male'),
     45          (1002, 37, 'Houston', 'male'),
     46          (1003, 25, 'Austin', 'female'),
     47          (1004, 28, 'Houston', 'female'),
     48          (1005, 22, 'Dallas', 'male')]
---> 50 cursor.execute(heads, valus)
     51 #db.commit()
     52  
     53 #print(cursor.rowcount, "details inserted")
     54  
     55 # disconnecting from server
     56 #db.close()

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\cursor_cext.py:317, in CMySQLCursor.execute(self, operation, params, multi)
    314     raise ProgrammingError(str(err)) from err
    316 if params:
--> 317     prepared = self._cnx.prepare_for_mysql(params)
    318     if isinstance(prepared, dict):
    319         for key, value in prepared.items():

File ~\AppData\Local\Programs\Python\Python310\lib\site-packages\mysql\connector\connection_cext.py:780, in CMySQLConnection.prepare_for_mysql(self, params)
    771         result = [
    772             self.converter.quote(
    773                 self.converter.escape(
   (...)
    777             for value in params
    778         ]
    779     else:
--> 780         result = self._cmysql.convert_to_mysql(*params)
    781 elif isinstance(params, dict):
    782     result = {}

MySQLInterfaceError: Python type tuple cannot be converted
2

cursor.execute(...) can insert only one row. To insert multiple rows, use cursor.executemany(...).

Also, I believe the placeholder in mysql is always %s.

1 Like