Search code examples
mysqlcsvkeyerrorexecutemany

Getting KeyError while executing executemany command in python

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

Solution

  • 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.