MySQL insert statement does not work in Python

Question

I am trying to fetch values from a csv calles 'items.csv' and then trying to store the values in a database table named 'articles2'. The insert statement is triggering following error:

pymysql.err.InternalError: (1292, "Incorrect datetime value: 'row[3]' for column 'date_added' at row 1")

This is my code:

 import csv
 import re
 import pymysql
 import sys
 import os
 import requests
 from PIL import Image
 def insert_articles2(rows):
 rowcount = 0
 for row in rows:
 if rowcount !=0:
 sql = "INSERT INTO articles2 (country, event_name, md5, date_added, profile_image, banner, sDate, eDate, address_line1, address_line2, pincode, state, city, locality, full_address, latitude, longitude, start_time, end_time, description, website, fb_page, fb_event_page, event_hashtag, source_name, source_url, email_id_organizer, ticket_url) VALUES ('row[0]', 'row[1]', 'row[2]', 'row[3]', 'row[4]', 'row[5]', 'row[6]', 'row[7]', 'row[8]', 'row[9]', 'row[10]', 'row[11]', 'row[12]', 'row[13]', 'row[14]', 'row[15]', 'row[16]', 'row[17]', 'row[18]', 'row[19]', 'row[20]', 'row[21]', 'row[22]', 'row[23]', 'row[24]', 'row[25]', 'row[26]', 'row[27]')"
 cursor.execute(sql)
 connection.commit() 
 rowcount+=1
rows = csv.reader(open("items.csv", "r"))
insert_articles2(rows)

Here's the structure of the table 'articles2'. Please see all the datatypes of the fields. What change should I made in my Python script to make this work? :

CREATE TABLE IF NOT EXISTS `articles2` (
 `id` int(6) NOT NULL AUTO_INCREMENT,
 `country` varchar(45) NOT NULL,
 `event_name` varchar(200) NOT NULL,
 `md5` varchar(35) NOT NULL,
 `date_added` timestamp NULL DEFAULT NULL,
 `profile_image` varchar(350) NOT NULL,
 `banner` varchar(350) NOT NULL,
 `sDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `eDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
 `address_line1` mediumtext,
 `address_line2` mediumtext,
 `pincode` int(7) NOT NULL,
 `state` varchar(30) NOT NULL,
 `city` text NOT NULL,
 `locality` varchar(50) NOT NULL,
 `full_address` varchar(350) NOT NULL,
 `latitude` varchar(15) NOT NULL,
 `longitude` varchar(15) NOT NULL,
 `start_time` time NOT NULL,
 `end_time` time NOT NULL,
 `description` longtext CHARACTER SET utf16 NOT NULL,
 `website` varchar(50) DEFAULT NULL,
 `fb_page` varchar(200) DEFAULT NULL,
 `fb_event_page` varchar(200) DEFAULT NULL,
 `event_hashtag` varchar(30) DEFAULT NULL,
 `source_name` varchar(30) NOT NULL,
 `source_url` varchar(350) NOT NULL,
 `email_id_organizer` varchar(100) NOT NULL,
 `ticket_url` mediumtext NOT NULL,
 PRIMARY KEY (`id`),
 KEY `full_address` (`full_address`),
 KEY `full_address_2` (`full_address`),
 KEY `id` (`id`),
 KEY `event_name` (`event_name`),
 KEY `sDate` (`sDate`),
 KEY `eDate` (`eDate`),
 KEY `id_2` (`id`),
 KEY `country` (`country`),
 KEY `event_name_2` (`event_name`),
 KEY `sDate_2` (`sDate`),
 KEY `eDate_2` (`eDate`),
 KEY `state` (`state`),
 KEY `locality` (`locality`),
 KEY `start_time` (`start_time`),
 KEY `start_time_2` (`start_time`),
 KEY `end_time` (`end_time`),
 KEY `id_3` (`id`),
 KEY `id_4` (`id`),
 KEY `event_name_3` (`event_name`),
 KEY `md5` (`md5`),
 KEY `sDate_3` (`sDate`),
 KEY `eDate_3` (`eDate`),
 KEY `latitude` (`latitude`),
 KEY `longitude` (`longitude`),
 KEY `start_time_3` (`start_time`),
 KEY `end_time_2` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4182 ;

A sample row of the csv:

country event_name md5 date_added profile_image banner sDate eDate address_line1 address_line2 pincode state city locality full_address latitude longitude start_time end_time description website fb_page fb_event_page event_hashtag source_name source_url email_id_organizer ticket_url
India India's largest 10K challenge, ProIndiaRun, Hyderabad on April 29th 6fa7ab214c279b765748b28362e9020b 2018年04月10日 04:10:45 ../images/events/India-s-largest-10K-challenge-ProIndiaRun-Hyderabad-on-April-29th-Hyderabad-4-banner.png 2018年04月29日 00:00:00 2018年04月29日 00:00:00 500041 Telangana Hyderabad TBA, Hyderabad, Hyderabad, Telangana, 500041 05:00:00 10:00:00 Event Description,,ProIndiaRun, Hyderabad,,Welcome to Pro Run India, India's largest 10K challenge happening at Pan India Level in different cities. Come along with them to make India better, to raise the child in their choice of sports supporting them financially.,,,,Pro- Run India is coming to Hyderabad on 29th April 2018. The Run lets you choose from 5k and 10K Run. Hurry, Register today!,,,,5KM RUN : INR 650,,AGE: 10 to 50 Years(Male/Female),,AGE: 51 to 70 Years(Male/Female) VETERUN CATEGORY,,,Finisher Medals,,BIB with Timing Chip,,Electronic Timing Certificate,,Refreshment,,,10KM CHALLENGE : INR 1000,,AGE: 10 to 70 Years(Male/Female),,,Finisher Medals,,BIB with Timing Chip,,Electronic Timing Certificate,,Refreshment,,,PRIZES:-,,5KM (TROPHIES FOR 1ST THREE RUNNER UP'S MALE & FEMALE),,10KM CHALLENGE,,FEMALE,,1ST PRIZE INR 5000/- 2ND PRIZE INR 3000/- 3RD PRIZE INR 2000/-,,MALE,,1ST PRIZE INR 5000/- 2ND PRIZE INR 3000/- 3RD PRIZE INR 2000/-,,, https://www.eventsnow.com/events/9232-proindiarun-hyderabad [email protected]

• jose_bacoy: show us a sample data of thw csv file. the column date_added is defined as timestamp which will accept data in this format. '1970年01月01日 00:00:01'
• Deba: @âńōŋŷXmoůŜ I edited my question and added the fields in csv and one sample row. plz see.
• jose_bacoy: 2018年04月10日 04:10:45 looks ok so please print row[3] in your python code to confirm it is the value assign to date column.
• Shadow: I would say python is not php, you cannot just simply include your variables in a string. You need to use placeholders.
• Ilja Everilä: @shadow F-strings allow doing just that, but string formatting values to SQL is a terrible practice and so the dupe target is outright dangerous. Python is not indeed PHP and they should use the DB-API's placeholders and pass the arguments as a sequence to execute() as the second positional arg.

Answer 1

With that statement you are trying to insert into the columns the strings 'row[0]', 'row[1]', 'row[2]',... and so on.

From the documentation, the an example of correct usage is:

sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
cursor.execute(sql, ('[email protected]', 'very-secret'))

So in you case should be:

sql = """
 INSERT INTO articles2 (country, event_name, md5, ..., ticket_url)
 VALUES (%s, %s, %s, ..., %s)
"""
cursor.execute(sql, row)

Btw, if you are inserting all of the columns and the order in the table matches the csv you can avoid specifying (country, event_name, md5, ..., ticket_url).

Using executemany will instead allow you to avoid the for loop, inserting in a more efficient way the whole batch of rows in one call.

cursor.executemany(sql, rows)