read csv file python and MySQL not all arguments converted during string formatting

Question

Thanks in advance for answering my question.

I'm trying to create a table, read csv file, and then save the data from the file to the created-table. I keep getting this error which I don't know how to solve. I've looked into other posts but none of them countered my issue.

The csv file has two columns and over 3000 rows. The first column name is #timestamp and the second column is Vehicle1. Here's my code

import csv
import MySQLdb


mydb = MySQLdb.connect(
        host = "localhost",
        user = "root",
        password = "1111",
        database = "LOAD_PROFILES"

)


mycursor = mydb.cursor()

mycursor.execute("DROP TABLE IF EXISTS Household1")



mycursor.execute("SHOW DATABASES;")



for db in mycursor:
    print(db)



sql = " CREATE TABLE Household1(sx TIMESTAMP NOT NULL, Vehicle1 INT NOT NULL, id INT NOT NULL  AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 1"
mycursor.execute("SHOW TABLES")
mycursor.execute(sql)

fname=input('file name: ')
if len(fname) < 1 : fname="converted0.csv"
with open(fname) as csv_file:
    csv_reader = csv.reader(csv_file, delimiter=',')
    for row in csv_reader:
        print(row)
        res = row[0].split()
        TIMESTAMP = row[0]
        Vehicle1 =row[1]
        data = mycursor.execute('''INSERT INTO Household1 VALUES (%s, %s)''',row)
        mycursor.execute("select * from Household1")
        row = mycursor.fetchall()
mycursor.execute("select * from Household1")
f = mycursor.fetchall()
print(f)
mydb.commit()

Here's the error I'm getting:

Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 238, in execute
    query = query % args
TypeError: not all arguments converted during string formatting
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "MySQL_python_Tables.py", line 49, in <module>
    data = mycursor.execute('''INSERT INTO Household1 VALUES (%%s, %%s)''',row)
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 240, in execute
    self.errorhandler(self, ProgrammingError, str(m))
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 52, in defaulterrorhandler
    raise errorclass(errorvalue)
_mysql_exceptions.ProgrammingError: not all arguments converted during string formatting

Any help would be appreciated. Thanks

EDIT:

It turns out that my table has three columns (last one is id column) but my csv file has only two columns. Once I deleted the id column, it worked just fine.

Now I have a another issue, python iterates through my file until it gets to "2010-03-14 02:00:00" and throws this error:

1292, "Incorrect datetime value: '2010-03-14 02:00:00' for column 'sx' at row 1"

I went over many posts and most of them say that it is DST related. How can I overcome this?

Thanks

Answer 1

In this line:

mycursor.execute('''INSERT INTO Household1 VALUES (%s, %s)''',row)

you have two placeholders %s, %s, but only one argument row. The number of placeholders should be equal to the number of arguments which should be concluded in a tuple.