In [1]:

    

import sqlite3


    

In [2]:

    

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


    

In [7]:

    

%load_ext sql


    

    




The sql extension is already loaded. To reload it, use:
  %reload_ext sql

In [8]:

    

%sql sqlite:///smaller.db


    

    
Out[8]:





u'Connected: None@smaller.db'

In [11]:

    

%sql select state, name, op_date, year from data where state is 'WA' and fuel_type is 'Coal' limit 5


    

    




Done.






    
Out[11]:





    

        
state
        
name
        
op_date
        
year
    
    

        
WA
        
Centralia
        
12-31-2016
        
2016
    
    

        
WA
        
Centralia
        
12-31-2016
        
2016
    

In [2]:

    

%%sql
sqlite:///smaller.db
drop table if exists data;
ATTACH DATABASE 'cems.db' as cems;
ATTACH DATABASE 'labels.db' as labels_db;
create table data as
select test.state as state, test.facility_name as name, test.orispl_code as orispl_code, test.unitid as unitid, op_date, year, op_hour, op_time, gload, SO2_MASS, NOX_MASS, CO2_MASS,
HEAT_INPUT as heat_input, county, latitude, longitude, fuel_type, fule_type_secondary as fuel_type_2, 
CASE WHEN (fuel_type is 'Natural Gas' or fuel_type is 'Pipeline Natural Gas' or fuel_type is 'Natural Gas, Pipeline Natural Gas')    THEN ('NG') ELSE (fuel_type) END as fuel_actual 
from cems.test as test, labels_db.labels as labels where substr(test.op_date, -4) is cast(labels.year as varchar) 
AND labels.orispl_code is test.orispl_code AND test.unitid is labels.unitid;


    

    




Done.
Done.
Done.
Done.
(sqlite3.OperationalError) no such column: facility_name [SQL: u"delete from data where state is 'TX' and (county is not 'El Paso' or facility_name is 'Montana Power Station');"]

In [4]:

    

%%sql
delete from data where state is 'TX' and (county is not 'El Paso' or name is 'Montana Power Station');
vacuum;


    

    




47604813 rows affected.
Done.






    
Out[4]:





[]

In [ ]:

    




    

In [6]:

    

% sql select * from data limit 5


    

    




Done.






    
Out[6]:





    

        
state
        
name
        
orispl_code
        
unitid
        
op_date
        
year
        
op_hour
        
op_time
        
gload
        
SO2_MASS
        
NOX_MASS
        
CO2_MASS
        
heat_input
        
county
        
latitude
        
longitude
        
fuel_type
        
fuel_type_2
        
fuel_actual
    
    

        
WA
        
Centralia
        
3845
        
BW21
        
01-01-2001
        
2001
        
0
        
1.0
        
704.0
        
11493.6
        
2506.854
        
720.5
        
7022.0
        
Lewis
        
46.7559
        
-122.8578
        
Coal
        

        
Coal
    
    

        
WA
        
Centralia
        
3845
        
BW21
        
01-01-2001
        
2001
        
1
        
1.0
        
706.0
        
11774.7
        
2577.369
        
736.6
        
7179.3
        
Lewis
        
46.7559
        
-122.8578
        
Coal
        

        
Coal
    
    

        
WA
        
Centralia
        
3845
        
BW21
        
01-01-2001
        
2001
        
2
        
1.0
        
704.0
        
11156.9
        
2606.306
        
718.8
        
7006.2
        
Lewis
        
46.7559
        
-122.8578
        
Coal
        

        
Coal
    
    

        
WA
        
Centralia
        
3845
        
BW21
        
01-01-2001
        
2001
        
3
        
1.0
        
702.0
        
10990.0
        
2561.697
        
716.2
        
6980.1
        
Lewis
        
46.7559
        
-122.8578
        
Coal
        

        
Coal
    
    

        
WA
        
Centralia
        
3845
        
BW21
        
01-01-2001
        
2001
        
4
        
1.0
        
702.0
        
11027.6
        
2631.762
        
716.2
        
6980.8
        
Lewis
        
46.7559
        
-122.8578
        
Coal
        

        
Coal
    

In [6]:

    

for state in ['wa', 'or', 'ca', 'id', 'nv', 'ut', 'az', 'nm', 'co', 'wy', 'mt', 'tx']:
    con = sqlite3.connect(state + ".db")
    con = sqlite3.connect("cems.db")
    cur = con.cursor()
    cur.execute("attach database '" + state + ".db' AS " + state + ";")
    cur.execute("create table " + state + ".data as \
    select state, facility_name as name, orispl_code, unitid, op_date, op_hour, op_time, gload, SO2_MASS, NOX_MASS, CO2_MASS, \
HEAT_INPUT as heat_input, county, latitude, longitude, fuel_type from data where data.state = " + UPPER('state') + ";")


    

    




---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-6-0541d7ad823e> in <module>()
      4     cur = con.cursor()
      5     cur.execute("attach database '" + state + ".db' AS " + state + ";")
----> 6     cur.execute("create table " + state + ".data as     select state, facility_name as name, orispl_code, unitid, op_date, op_hour, op_time, gload, SO2_MASS, NOX_MASS, CO2_MASS, HEAT_INPUT as heat_input, county, latitude, longitude, fuel_type from data where data.state = " + state + ";")

OperationalError: no such column: wa

In [ ]: