CODEWITHSUNDEEP
pythonmysqldatetime
muazfaiz
muazfaiz

Reputation: 5021

Insert date into a column in SQL from csv

I have data in csv like this

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

I want to make a SQL table and I am using Python to populate the table. Following is my code:

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()

But I am getting the following error:

    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'

I searched for the above error but does not a related answer. How can I fill in the dates with other columns ?

Upvotes: 0

Views: 1242

Answers (1)

Shadow
Shadow

Reputation: 34232

I would convert your datetime object to specifically formatted string within python to be inserted into MySQL using strftime('%Y-%m-%d') method:

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

This way your are passing a string formatted as '%Y-%m-%d', which complies with MySQL's standard definition for date literals.

Upvotes: 1

Related Questions