1

Python code

import pymysql
import xlrd
import re
import os
import csv
conn = pymysql.connect(host='',
                             user='',
                             password='',
                             db='',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)
sql = "INSERT INTO `webscrap_wunderground` (Date,MeanTemp,MaxTemp,MinTemp,HeatingDegree,DewPoint,MeanHumidity,MaxHumidity,MinHumidity,Precipitation,SeaLevel,MeanWindSpeed,MaxWind_Speed,Visibility,Event) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"

with open('C:/Users/SARIKA/Weather Data.csv') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        print(row)
        cur = conn.cursor()
        cur.executemany(sql,[(row['Date'],row['Mean Temperature'],row['Max Temperature'],row['Min Temperature'],row['Heating Degree Days'],row['Dew Point'],row['MeanHumidity'],row['MaxHumidity'],row['MinHumidity'],row['Precipitation'],row['SeaLevel'],row['MeanWindSpeed'],row['MaxWindSpeed'],row['visibility'],row['Events'])])

        conn.escape_string(sql)
        conn.commit()

here is error i get when I execute above code. Csv file is read correctly as i have print row from that as shown below. But while inserting data in database table it gives Error. I think there is problem in mapping between csv and database but i couldn't found mistake please help.

OrderedDict([('Date', '2017/1/1'), ('Mean Temperature', '24'), ('Max Temperature', '31'), ('Min Temperature', '16'), ('Heating Degree Days', 'N/A'), (' Dew Point', '16'), (' Average Humidity', '53'), (' Max Humidity', '83'), (' Minimum Humidity', '27'), (' Precipitation', '0.0'), (' Sea Level Pressure', '1012.73'), (' Average Wind Speed', '4'), (' Maximum Wind Speed', '17'), (' Visibility', '2.0'), (' Events', '')])
Traceback (most recent call last):
  File "C:\Users\SARIKA\eclipse-workspace\a1\csvtosql.py", line 20, in <module>
    cur.executemany(sql,[(row['Date'],row['Mean Temperature'],row['Max Temperature'],row['Min Temperature'],row['Heating Degree Days'],row['Dew Point'],row['MeanHumidity'],row['MaxHumidity'],row['MinHumidity'],row['Precipitation'],row['SeaLevel'],row['MeanWindSpeed'],row['MaxWindSpeed'],row['visibility'],row['Events'])])
KeyError: 'Dew Point'

CSV file

weather data csv

Database table structure

Database table

sarika
  • 35
  • 8

1 Answers1

0

Looking at your output, the CSV's column is called " Dew Point", with a space before the "D". Referencing it like that (i.e., row[' Dew Point']) should fix the error.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • Thanks a lot sir my issue resolved only because of you! thanks a ton – sarika Dec 31 '17 at 11:23
  • Again I facing another problem here, Date in csv file is like dd-mm-yy format but it wrongly written in database.e.g date in csv is 30-07-17 but same date while inserting in database as 2030-07-17. Means it yy field is considered as dd while writing in database. How can I resolved this – sarika Dec 31 '17 at 11:50
  • cur.executemany(sql,[(datetime.datetime.strptime(row['Date'],'%d-%m-%Y').strftime('%Y-%m-%d'),row['Mea....................continued . #Error: File "C:\python3\lib\_strptime.py", line 565, in _strptime_datetime tt, fraction = _strptime(data_string, format) File "C:\python3\lib\_strptime.py", line 362, in _strptime (data_string, format)) ValueError: time data '2017/1/1' does not match format '%d-%m-%Y' – sarika Dec 31 '17 at 15:39
  • cant resolve r ValueError: time data '2017/1/1' does not match format '%d-%m-%y' please help @Mureinik – sarika Dec 31 '17 at 16:56