In [1]:

    

import csv, sqlite3
import os.path


    

In [2]:

    

con = sqlite3.connect("cems.db")
cur = con.cursor()


    

In [4]:

    

cur.execute('drop table if exists labels;')
cur.execute(
"create table labels( \
    state varchar, \
    facility_name varchar, \
    orispl_code int, \
    unitid varchar, \
    year int, \
    county varhcar, \
    county_code varchar, \
    latitude float, \
    longitude float, \
    owner varchar, \
    operator varchar, \
    representative_primary varchar, \
    representative_second varchar, \
    unit_type varchar, \
    fuel_type varchar, \
    fule_type_secondary varchar, \
    max_hourly_rate float, \
    PRIMARY KEY(orispl_code, unitid, year) \
);")


    

    
Out[4]:





<sqlite3.Cursor at 0x3539f60>

In [12]:

    

with open('facility_06-23-2017_144111628.csv', 'rb') as fin:
    dr = csv.DictReader(fin)
    to_db = [(i['State'],  i[' Facility Name'], i[' Facility ID (ORISPL)'], i[' Unit ID'], i[' Year'], i[' County'], i[' County Code'], 
              i[' Facility Latitude'], i[' Facility Longitude'], i[' Owner'], i[' Operator'], i[' Representative (Primary)'],
              i[' Representative (Secondary)'], i[' Unit Type'], i[' Fuel Type (Primary)'], i[' Fuel Type (Secondary)'],
              i[' Max Hourly HI Rate (MMBtu/hr)']) for i in dr]
cur.executemany("insert into labels (    state, facility_name, orispl_code, unitid, year, \
    county, county_code, latitude, longitude, owner, operator, representative_primary, representative_second, \
    fuel_type, fule_type_secondary, max_hourly_rate \
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", to_db)
con.commit()


    

    




---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-12-2015d80cf953> in <module>()
      5               i[' Representative (Secondary)'], i[' Unit Type'], i[' Fuel Type (Primary)'], i[' Fuel Type (Secondary)'],
      6               i[' Max Hourly HI Rate (MMBtu/hr)']) for i in dr]
----> 7 cur.executemany("insert into labels (    state, facility_name, orispl_code, unitid, year,     county, county_code, latitude, longitude, owner, operator, representative_primary, representative_second,     fuel_type, fule_type_secondary, max_hourly_rate ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", to_db)
      8 con.commit()

OperationalError: 17 values for 16 columns

In [ ]: