In [1]:
%load_ext sql
In [2]:
%sql sqlite:///smaller.db
Out[2]:
In [3]:
%sql select * from data limit 5
Out[3]:
In [ ]:
%sql select count(*) from data
In [3]:
%sql select * from data where state is UPPER('wa') limit 5
Out[3]:
In [19]:
import sqlite3
for state in ['or', 'ca', 'id', 'nv', 'ut', 'az', 'nm', 'co', 'wy', 'mt', 'tx']:
con = sqlite3.connect(state + ".db")
con = sqlite3.connect("smaller.db")
cur = con.cursor()
cur.execute("attach database '" + state + ".db' AS " + state + "db;")
cur.execute("create table " + state + "db.data as \
select * from data where data.state = UPPER('" + state + "');")
In [16]:
Out[16]:
In [17]:
%sql create table wash.data as select * from data where state is UPPER('wa');
Out[17]:
In [21]:
%%sql
ATTACH DATABASE '2016.db' AS other;
Out[21]:
In [22]:
%sql create table other.data as select * from data where substr(op_date, -4) is '2016';
Out[22]:
In [ ]:
import sqlite3
con = sqlite3.connect("smaller.db")
for year in range(2001, 2017):
con = sqlite3.connect(str(year) + ".db")
cur = con.cursor()
cur.execute("drop table if exists data;")
con = sqlite3.connect("smaller.db")
cur = con.cursor()
cur.execute("attach database '" + str(year) + ".db' AS db" + str(year) + ";")
cur.execute("create table db" + str(year) + ".data as \
select * from data where substr(op_date, -4) is cast(" + str(year) + " as varchar);")
In [29]:
%%sql
ATTACH DATABASE 'intermountain.db' AS jyoung;
create table jyoung.data as select *, substr(op_date, -4) as year from data where name is 'Intermountain';
Out[29]:
In [31]:
import sqlite3
import pandas
con = sqlite3.connect('intermountain.db')
table = pandas.read_sql("select unitid, op_date, op_hour, op_time, gload, SO2_MASS, NOX_MASS, CO2_MASS, heat_input, year from data", con)
table.to_csv('intermountain.csv')
In [ ]: