>

csv 호출에서 'items.csv'를 호출 한 다음 'articles2'라는 데이터베이스 테이블에 값을 저장하려고합니다. insert 문이 다음 오류를 발생시킵니다 :

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

이것은 내 코드입니다 :

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)

다음은 'articles2'테이블의 구조입니다. 필드의 모든 데이터 유형을 참조하십시오. 이 작업을하려면 파이썬 스크립트에서 어떤 변경을해야합니까? :

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 ;

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


  • 답변 # 1

    이 문장을 사용하면 문자열 'row[0]' 를 열에 삽입하려고합니다. 'row[1]' 'row[2]' ... 등.

    문서에서 올바른 사용법의 예는 다음과 같습니다.

    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('webmaster@python.org', 'very-secret'))
    
    

    따라서 귀하의 경우 :

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

    Btw, 열의all을 삽입하고 테이블의 순서가 csv와 일치하면 (country, event_name, md5, ..., ticket_url) 를 지정하지 않아도됩니다. .

    executmany를 사용하면 for 루프를 피할 수있어 한 번의 호출로 행의 전체 배치를보다 효율적으로 삽입 할 수 있습니다.

    cursor.executemany(sql, rows)
    
    

관련 자료

  • 이전 views-viewhtmltwig 파일에서 컨텐츠 유형을 얻는 방법 | 드루팔 8
  • 다음 javascript - 버튼이있는 DataTable, 목록에 요소 추가