python - 将日期从 csv 插入 SQL 中的列

Question

我有这样的 csv 数据

RegisterDate    StoreID ProductID   ProductType Quantity
30.12.2013      1002    00000576    001         10
1.1.2014        1002    00008577    001        20
2.1.2014        1002    00002917    002        12
3.1.2014        1002    00007542    003         1
4.1.2014        1002    00000964    002         1

我想创建一个 SQL 表，并且我正在使用 Python 来填充该表。以下是我的代码:

import pymysql as MS
import pandas as pd

db = MS.connect(host="localhost",user="root",passwd="passwd",db="db")
cursor = db.cursor()
cursor.execute("create table IF NOT EXISTS sales (productID INT (25), registerDate DATE, storeID INT(5), productType INT (3), Quantity INT (100));")

df = pd.read_csv('C:/Users/data.csv', encoding='latin-1')
for i in range (len(df)):
    date = pd.to_datetime(df['RegisterDate'][i], format='%d.%m.%Y') # Converting the field value to datetime
    data = [int(df['ProductID'][i]), date, int(df['StoreID'][i]), int(df['ProductType'][i]), int(df['Quantity'][i]]
    cursor.execute('INSERT INTO sales(productID, registerDate, storeID, productType, Quantity )' 'VALUES("%s", "%s", "%s", "%s", "%s"  )', data)

db.commit()
cursor.close()

但我收到以下错误:

    Traceback (most recent call last):
  File "C:/Users/PycharmProjects/testproject/database.py", line 17, in <module>
    cursor.execute('INSERT INTO sales(productID, registerDate, storeID, productType, Quantity )' 'VALUES("%s", "%s", "%s", "%s", "%s"  )', data)
  File "C:\Python\Python35\lib\site-packages\pymysql\cursors.py", line 164, in execute
    query = self.mogrify(query, args)
  File "C:\Python\Python35\lib\site-packages\pymysql\cursors.py", line 143, in mogrify
    query = query % self._escape_args(args, conn)
  File "C:\Python\Python35\lib\site-packages\pymysql\cursors.py", line 118, in _escape_args
    return tuple(conn.literal(arg) for arg in args)
  File "C:\Python\Python35\lib\site-packages\pymysql\cursors.py", line 118, in <genexpr>
    return tuple(conn.literal(arg) for arg in args)
  File "C:\Python\Python35\lib\site-packages\pymysql\connections.py", line 821, in literal
    return self.escape(obj, self.encoders)
  File "C:\Python\Python35\lib\site-packages\pymysql\connections.py", line 814, in escape
    return escape_item(obj, self.charset, mapping=mapping)
  File "C:\Python\Python35\lib\site-packages\pymysql\converters.py", line 27, in escape_item
    val = encoder(val, mapping)
  File "C:\Python\Python35\lib\site-packages\pymysql\converters.py", line 110, in escape_unicode
    return u"'%s'" % _escape_unicode(value)
  File "C:\Python\Python35\lib\site-packages\pymysql\converters.py", line 73, in _escape_unicode
    return value.translate(_escape_table)
AttributeError: 'Timestamp' object has no attribute 'translate'

我搜索了上述错误，但没有相关答案。如何用其他列填写日期？

Answer 1

我会将您的 datetime 对象转换为 python 中特定格式的字符串，以便使用 strftime('%Y-%m-%d') 方法插入到 MySQL 中:

data = [int(df['ProductID'][i]), date.strftime('%Y-%m-%d'), int(df['StoreID'][i]), int(df['ProductType'][i]), int(df['Quantity'][i]]

这样，您将传递一个格式为 '%Y-%m-%d' 的字符串，它符合 MySQL 的日期文字标准定义。