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 proindiarun@gmail.com

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' — jose_bacoy, Apr 15 '18 at 15:45
@âńōŋŷXmoůŜ I edited my question and added the fields in csv and one sample row. plz see. — Deba, Apr 15 '18 at 15:50
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. — jose_bacoy, Apr 15 '18 at 16:55
I would say python is not php, you cannot just simply include your variables in a string. You need to use placeholders. — Shadow, Apr 15 '18 at 18:56
@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. — Ilja Everilä, Apr 17 '18 at 12:13
Related: https://stackoverflow.com/questions/5687718/how-can-i-insert-data-into-a-mysql-database. This too, though the top answer is wrong in that it quotes around placeholders (the correct solution is in the comments): https://stackoverflow.com/questions/10154633/load-csv-data-into-mysql-in-python — Ilja Everilä, Apr 17 '18 at 16:37

Cavaz · Answer 1 · 2018-04-17T14:00:23.590

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, ('webmaster@python.org', '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)

MySQL insert statement does not work in Python

1 Answers1