Mysql and mysql.connector error

[lostintime]

Hello ,
i trying to read a text file that has 9 items to be inputted to the MySQL database.
The one issue i have (other than this error) the file can have more input data. please see end for test data
Any help i would be grateful. self taught on python

cursor = db.cursor()

# Open the text file and read its contents
with open("sam.txt") as file:
    lines = file.readlines()

# Loop through the lines of the file and insert them into the database
for line in lines:
    # Split the line into its columns, assuming they are separated by commas
    values = line.strip().split(",")
    # Define the SQL query to insert the values into the table
sql = ("INSERT INTO master1 (inf0,time1,strong,lats,lon,loc1,loc2,test1,test2,test3,test4) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)")
    # Execute the query with the values as parameters

DATAIS = ('1','2','3','4','5','6','7','8','9','10','11')


cursor.execute(sql, values)
    # Commit the changes to the database
db.commit()

# Close the database connection
db.close()
print(cursor.rowcount, "records inserted.")

Error:
Not all parameters were used in the SQL statement

test data

Information, 2021.09.17 000000 UTC , 6.6(Mwp), -33.300 -178.200, south of the Kermadec Islands An information statemenNot all parameters were used in the SQL statementt indicates that an, earthquake has occurred, but does not pose a tsunami threat, or that a tsunami warning, advisory, or watch has been issued for another section of the ocean

[deanhystad]

There are 11 things here:

(inf0,time1,strong,lats,lon,loc1,loc2,test1,test2,test3,test4)

There are 9 things here:

(%s,%s,%s,%s,%s,%s,%s,%s,%s)

]
There are nine things here:

Information, 2021.09.17 000000 UTC , 6.6(Mwp), -33.300 -178.200, south of the Kermadec Islands An information statemenNot all parameters were used in the SQL statementt indicates that an, earthquake has occurred, but does not pose a tsunami threat, or that a tsunami warning, advisory, or watch has been issued for another section of the ocean

They all have to agree. The last one is questionable. Should "but does not pose a tsunami threat" really be a column in you table?

I think these should be your table columns.
type, time, strong, lat_long, description

You need to limit the split to split the line into 5 parts. You should strip off leading and trailing whitespace.

values = [x.strip() for x in line.strip().split(",", maxsplit=5)

From your test data:

Output:
type = Information
time = 2021.09.17 000000 UTC 
strong = 6.6(Mwp)
long_lat = -33.300 -178.200
description = south of the Kermadec Islands An information statemenNot all parameters were used in the SQL statementt indicates that an, earthquake has occurred, but does not pose a tsunami threat, or that a tsunami warning, advisory, or watch has been issued for another section of the ocean

If not all lines have the same information, you will need a more intelligent parser.

[lostintime]

thanks for your help

There are 11 things here:

(inf0,time1,strong,lats,lon,loc1,loc2,test1,test2,test3,test4)

There are 9 things here:

(%s,%s,%s,%s,%s,%s,%s,%s,%s)

]
There are nine things here:

Information, 2021.09.17 000000 UTC , 6.6(Mwp), -33.300 -178.200, south of the Kermadec Islands An information statemenNot all parameters were used in the SQL statementt indicates that an, earthquake has occurred, but does not pose a tsunami threat, or that a tsunami warning, advisory, or watch has been issued for another section of the ocean

They all have to agree. The last one is questionable. Should "but does not pose a tsunami threat" really be a column in you table?

I think these should be your table columns.
type, time, strong, lat_long, description

You need to limit the split to split the line into 5 parts. You should strip off leading and trailing whitespace.

values = [x.strip() for x in line.strip().split(",", maxsplit=5)

From your test data:

Output:
type = Information
time = 2021.09.17 000000 UTC 
strong = 6.6(Mwp)
long_lat = -33.300 -178.200
description = south of the Kermadec Islands An information statemenNot all parameters were used in the SQL statementt indicates that an, earthquake has occurred, but does not pose a tsunami threat, or that a tsunami warning, advisory, or watch has been issued for another section of the ocean

If not all lines have the same information, you will need a more intelligent parser.