Question 20208 in Mysql Question posted in
The official MySQL documentation can be found here.

import csv into MySQL database with python (MySQLdb.OperationalError)

I have trouble with import of csv data into MySQL tabel

The code I use looks like this :

import MySQLdb
import csv
import sys
conn = MySQLdb.connect(host="192.168.2.20", user="xxx", password="xxxx", database="admin_mt")


cursor = conn.cursor()
cursor.execute("TRUNCATE TABLE wp_dbo_iclock_transaction")
csv_data = csv.reader(open('wp_dbo_iclock_transaction.csv'))
header = next(csv_data)

print('Importing the CSV Files')
for row in csv_data:
    print(row)
    cursor.execute(
        "INSERT INTO wp_dbo_iclock_transaction (id, emp_code, punch_time, name) VALUES (%s,%s,%s,%s)", row)

conn.commit()
cursor.close()
print('Done')

The error I get :

Importing the CSV Files
['1', '1', '11/01/2021 11:56', 'frfrgrgr r4']
Traceback (most recent call last):
  File "D:Server Backuptime_importimport - Copy - Copy.py", line 15, in <module>
    cursor.execute(
  File "C:Program FilesPython311Libsite-packagesMySQLdbcursors.py", line 206, in execute
    res = self._query(query)
  File "C:Program FilesPython311Libsite-packagesMySQLdbcursors.py", line 319, in _query
    db.query(q)
  File "C:Program FilesPython311Libsite-packagesMySQLdbconnections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1292, "Incorrect datetime value: '11/01/2021 11:56' for column 'punch_time' at row 1")

I’ve been looking for a way for days and still can’t do it. Hope today I will find a good way.

Tags:

2

Answers


  1. 0

    The error message indicates that there is an issue with the format of the date/time string in the punch_time column of the CSV file. MySQL is expecting the date/time string to be in the format YYYY-MM-DD HH:MM:SS, but the string in the CSV file is in the format MM/DD/YYYY HH:MM.

    To resolve this issue, you can convert the date/time string in the CSV file to the correct format before inserting it into the database. You can use the datetime.strptime function from the datetime module to parse the date/time string and convert it to the correct format. Here’s an example of how to do this:

    import MySQLdb
import csv
from datetime import datetime

conn = MySQLdb.connect(host="192.168.2.20", user="xxx", password="xxxx", database="admin_mt")
cursor = conn.cursor()

# Truncate the table
cursor.execute("TRUNCATE TABLE wp_dbo_iclock_transaction")

# Open the CSV file and skip the header row
csv_data = csv.reader(open('wp_dbo_iclock_transaction.csv'))
header = next(csv_data)

# Import the data from the CSV file
print('Importing the CSV Files')
for row in csv_data:
    # Convert the date/time string to the correct format
    punch_time = datetime.strptime(row[2], '%m/%d/%Y %H:%M').strftime('%Y-%m-%d %H:%M:%S')
    # Insert the row into the database
    cursor.execute("INSERT INTO wp_dbo_iclock_transaction (id, emp_code, punch_time, name) VALUES (%s,%s,%s,%s)", (row[0], row[1], punch_time, row[3]))

conn.commit()
cursor.close()
print('Done')
    Login or Signup to reply.
  2. 0

    I upvoted the answer from @Mime, but an alternative method is to convert the MM/DD/YYYY date into MySQL’s format in your INSERT statement using the STR_TO_DATE() function.

    cursor.execute("""
 INSERT INTO wp_dbo_iclock_transaction (id, emp_code, punch_time, name) 
 VALUES (%s,%s,STR_TO_DATE(%s, '%e/%c/%Y %H:%i'),%s)
""", row)

    This way you don’t have to convert the datetime value in Python, you can just pass row as the list of parameter values.

    Both the solution by Mime and this solution should work equally well, and neither has a performance advantage. So it’s up to your personal preference whether you’d rather do the conversion in Python or in SQL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search