In [1]:

    

import datetime
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import *
import pandas as pd
import os
import numpy as np
import operator
import math
import random
from pylab import *
import ast


    

    




/Users/sabina/anaconda/lib/python2.7/site-packages/pandas/io/excel.py:626: UserWarning: Installed openpyxl is not supported at this time. Use >=1.6.1 and <2.0.0.
  .format(openpyxl_compat.start_ver, openpyxl_compat.stop_ver))

In [2]:

    

import json 
import matplotlib 
import matplotlib.pyplot as pyplt
%matplotlib inline


    

In [3]:

    

##for accessing all months
#months = ['01','02','03','04','05','06','07','08','09','10','11']
month_titles={'01':'January','02':'February','03':'March','04':'April'}
#days  = {'01':31,'02':28,'03':31,'04':30,'05':31,'06':30,'07':31,'08':31,'09':30,'10':31,'11':30}


    

In [12]:

    

##engine information
user_name = 'USERNAME'
pw='PASSWORD'
host = "db.wiki-energy.org"
port = "5432"
db = "postgres"
schema = '\"PecanStreet_SharedData\"'
table = "validated_04_2014"


    

In [6]:

    

def get_eng():
    url = "postgresql"+"://"+user_name+":"+pw+"@"+host+":"+port+"/"+db
    eng = create_engine(url)
    eng.echo=True
    return eng


    

In [7]:

    

def get_dataids(eng,table):
    query = "select distinct("+ schema + "."+table + ".dataid" +") from " + schema + "." +table
    eng_object=eng.execute(query)
    l=list(eng_object.fetchall())
    return l


    

In [8]:

    

def get_table_by_id(eng,table,i):
    query = "select * from "+schema+"."+table+ " where " +schema+"."+table+".dataid="+i
    eng_object=eng.execute(query)
    df = pd.DataFrame.from_records(eng_object.fetchall())
    df.columns = eng_object.keys()
    return df


    

In [9]:

    

def get_query(kind,col,table):
    if kind=='sum':
        return "select sum("+col +") from " + schema +"."+ table
    if kind =='count':
        return "select count("+col+ ") from " + schema +"."+ table
    if kind =='distinct':
        return "select distinct(" + schema  +"."+table+"."+col+ ") from " + schema +"."+ table
    #+"."+table+"."+col+ ") from " + schema +"."+ table


    

In [211]:

    

month_ids={}


    

In [231]:

    

feb=get_dataids(eng,"validated_04_2014")
feb = [str(i[0]) for i in feb ]
month_ids['april']=feb


    

    




2014-06-19 18:27:37,076 INFO sqlalchemy.engine.base.Engine select distinct("PecanStreet_SharedData".validated_04_2014.dataid) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select distinct("PecanStreet_SharedData".validated_04_2014.dataid) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-19 18:27:37,078 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}

In [232]:

    

inter = (list(set(month_ids['jan'])&set(month_ids['feb'])&set(month_ids['mar'])&set(month_ids['april'])))


    

In [227]:

    

not_in=[]
for val in month_ids.values():
    for i in val:
        if i not in inter:
            not_in.append(i)


    

In [233]:

    

dir_path = os.path.join(os.getcwd(), 'shared_stats/' ) 
with open(os.path.join(dir_path, 'homes_in_intersection.json'), 'w') as outfile:
    outfile.writelines(json.dumps(inter))


    

In [234]:

    

len(inter)


    

    
Out[234]:





163

In [10]:

    

def execute_query(eng,query,form):
    ds =[]
    eng_object=eng.execute(query)
    if form == 'num':
        ds=(eng_object.fetchall())
    elif form=='ls':
        ds = list(eng_object.fetchall())
    elif form =='df':
        ds = pd.DataFrame.from_records(eng_object.fetchall())
        ds.columns = eng_object.keys()
    return ds


    

In [10]:

    

def get_hvac_stats(eng,month):
    hvacs = {}
    hvac_cols = ['air1','air2','air3','furnace1','furnace2','airwindowunit1','housefan1','heater1','use','car1']
    hvacs_f = {'air':0,'furnaces':0,'heater':0,'use':0}
    for c in hvac_cols:
        hvacs[c]=execute_query(eng,get_query('sum',c,"validated_"+month+"_2014"),'ls') 
    #hvacs['total_rows'] =  execute_query(eng,len_query,'ls')
    hvacs={k:float(list(hvacs[k][0])[0]) for k in hvacs.keys() if not (math.isnan(float(list(hvacs[k][0])[0])))}
    hvacs_f['air']=hvacs['air1']+hvacs['air2']+hvacs['air3']+hvacs['airwindowunit1']
    hvacs_f['furnaces'] = hvacs['furnace1']+hvacs['furnace2']
    hvacs_f['use']=hvacs['use']-hvacs['car1']
    return hvacs_f


    

In [11]:

    

eng = get_eng()


    

In [12]:

    

q=get_query('distinct',"dataid","validated_01_2014")


    

In [13]:

    

dataids = execute_query(eng,q,'ls')


    

    




2014-06-23 14:31:11,573 INFO sqlalchemy.engine.base.Engine select version()






    




INFO:sqlalchemy.engine.base.Engine:select version()






    




2014-06-23 14:31:11,575 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-23 14:31:11,694 INFO sqlalchemy.engine.base.Engine select current_schema()






    




INFO:sqlalchemy.engine.base.Engine:select current_schema()






    




2014-06-23 14:31:11,696 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-23 14:31:11,974 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    




INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    




2014-06-23 14:31:11,975 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-23 14:31:12,096 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    




INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    




2014-06-23 14:31:12,097 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-23 14:31:12,542 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings






    




INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings






    




2014-06-23 14:31:12,543 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-23 14:31:12,666 INFO sqlalchemy.engine.base.Engine select distinct("PecanStreet_SharedData".validated_01_2014.dataid) from "PecanStreet_SharedData".validated_01_2014






    




INFO:sqlalchemy.engine.base.Engine:select distinct("PecanStreet_SharedData".validated_01_2014.dataid) from "PecanStreet_SharedData".validated_01_2014






    




2014-06-23 14:31:12,667 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}

In [15]:

    

dataids = [str(d[0]) for d in dataids]


    

In [16]:

    

'545' in dataids


    

    
Out[16]:





['744',
 '2129',
 '2575',
 '4874',
 '7989',
 '410',
 '1782',
 '3394',
 '8467',
 '1167',
 '5026',
 '8142',
 '5218',
 '8736',
 '3263',
 '6673',
 '7617',
 '8342',
 '8829',
 '3544',
 '370',
 '3736',
 '4505',
 '9938',
 '9922',
 '3795',
 '9771',
 '821',
 '94',
 '2171',
 '4313',
 '2769',
 '9499',
 '1800',
 '2829',
 '4373',
 '4154',
 '6941',
 '1632',
 '3504',
 '5972',
 '585',
 '3649',
 '871',
 '5938',
 '5357',
 '1283',
 '3652',
 '9937',
 '3044',
 '5109',
 '936',
 '1994',
 '9729',
 '2606',
 '4447',
 '5785',
 '7800',
 '484',
 '3221',
 '2156',
 '5746',
 '3367',
 '2953',
 '7390',
 '1953',
 '5395',
 '545',
 '2470',
 '6730',
 '2638',
 '7940',
 '9548',
 '2449',
 '5874',
 '2818',
 '86',
 '9343',
 '7731',
 '4298',
 '9701',
 '8419',
 '2945',
 '5275',
 '9875',
 '3482',
 '9654',
 '9939',
 '1731',
 '4922',
 '2094',
 '2974',
 '3456',
 '8852',
 '3893',
 '9609',
 '4957',
 '1697',
 '4135',
 '9160',
 '8292',
 '2233',
 '4342',
 '93',
 '8741',
 '8645',
 '9578',
 '5545',
 '3967',
 '9915',
 '661',
 '1464',
 '3778',
 '6990',
 '6412',
 '8317',
 '8079',
 '9484',
 '4514',
 '3723',
 '8197',
 '6836',
 '9830',
 '4641',
 '5677',
 '4031',
 '9643',
 '7788',
 '7531',
 '1718',
 '2814',
 '3092',
 '9019',
 '1642',
 '5209',
 '6910',
 '2337',
 '9555',
 '8084',
 '7850',
 '9121',
 '7062',
 '7863',
 '1790',
 '1169',
 '2242',
 '9932',
 '9926',
 '9934',
 '1334',
 '9983',
 '1192',
 '8031',
 '5262',
 '8669',
 '2864',
 '5371',
 '3039',
 '8061',
 '7536',
 '5949',
 '2158',
 '9036',
 '7504',
 '4956',
 '7319',
 '9737',
 '4336',
 '9933',
 '3918',
 '7875',
 '9942',
 '4526',
 '6423',
 '490',
 '5814',
 '9982',
 '7982',
 '739',
 '3192',
 '4732',
 '4767',
 '6636',
 '7769',
 '8956',
 '8046',
 '4773',
 '7951',
 '7597',
 '8047',
 '4998',
 '1629',
 '624',
 '9356',
 '6139',
 '1801',
 '5568',
 '1185',
 '1714',
 '2335']

In [17]:

    

#dataids[0][0]
test = get_table_by_id(eng,"validated_01_2014",str(dataids[1]))


    

    




2014-06-23 14:31:25,288 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2129






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2129






    




2014-06-23 14:31:25,289 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}

In [18]:

    

print test.keys()
test['kitchen1']


    

    




Index([u'dataid', u'localminute', u'use', u'air1', u'air2', u'air3', u'airwindowunit1', u'aquarium1', u'bathroom1', u'bathroom2', u'bedroom1', u'bedroom2', u'bedroom3', u'bedroom4', u'bedroom5', u'car1', u'clotheswasher1', u'clotheswasher_dryg1', u'diningroom1', u'diningroom2', u'dishwasher1', u'disposal1', u'drye1', u'dryg1', u'freezer1', u'furnace1', u'furnace2', u'garage1', u'garage2', u'gen', u'grid', u'heater1', u'housefan1', u'icemaker1', u'jacuzzi1', u'kitchen1', u'kitchen2', u'kitchenapp1', u'kitchenapp2', u'lights_plugs1', u'lights_plugs2', u'lights_plugs3', u'lights_plugs4', u'lights_plugs5', u'lights_plugs6', u'livingroom1', u'livingroom2', u'microwave1', u'office1', u'outsidelights_plugs1', u'outsidelights_plugs2', u'oven1', u'oven2', u'pool1', u'pool2', u'poollight1', u'poolpump1', u'pump1', u'range1', u'refrigerator1', u'refrigerator2', u'security1', u'shed1', u'sprinkler1', u'unknown1', u'unknown2', u'unknown3', u'unknown4', u'utilityroom1', u'venthood1', u'waterheater1', u'waterheater2', u'winecooler1'], dtype='object')






    
Out[18]:





0     None
1     None
2     None
3     None
4     None
5     None
6     None
7     None
8     None
9     None
10    None
11    None
12    None
13    None
14    None
...
44625    None
44626    None
44627    None
44628    None
44629    None
44630    None
44631    None
44632    None
44633    None
44634    None
44635    None
44636    None
44637    None
44638    None
44639    None
Name: kitchen1, Length: 44640, dtype: object

In [26]:

    

def get_sums(house):
   # house=house.drop('id',axis=1)
    house=house.drop('dataid',axis=1)
    house=house.drop('localminute',axis=1)
    sums={}
   # print(house.columns)
    for col in house.columns:
       sums[str(col)]=float(house[col].sum())
    return sums


    

In [27]:

    

##for all the houses return the highest appliances
def get_highest(house):
    sums= get_sums(house)
    sums.pop('use',None)
    #sums.pop('car1',None)
    sorted_sums = sorted(sums.iteritems(), key=operator.itemgetter(1), reverse = True)
    
    return dict(sorted_sums)


    

In [28]:

    

#get_table_by_id(eng,"validated_01_2014",'9771')


    

In [ ]:

    

for homeid in dataids:
    home = get_table_by_id(eng,"validated_01_2014",homeid)
    if 'use' not in home.columns:
        print homeid


    

    




2014-06-18 14:43:00,104 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=744






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=744






    




---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-48-5ea6185e0741> in <module>()
----> 1 t = get_table_by_id("3368")

TypeError: get_table_by_id() takes exactly 3 arguments (1 given)





    




2014-06-18 14:49:20,419 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5026






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5026






    




2014-06-18 14:49:20,420 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:50:00,117 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8142






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8142






    




2014-06-18 14:50:00,117 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:50:42,773 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5218






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5218






    




2014-06-18 14:50:42,773 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:51:20,893 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8736






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8736






    




2014-06-18 14:51:20,895 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:51:58,410 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3263






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3263






    




2014-06-18 14:51:58,411 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:52:35,536 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6673






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6673






    




2014-06-18 14:52:35,536 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:53:14,838 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7617






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7617






    




2014-06-18 14:53:14,839 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:53:54,482 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8342






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8342






    




2014-06-18 14:53:54,483 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:54:28,685 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8829






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8829






    




2014-06-18 14:54:28,686 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:55:01,184 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3544






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3544






    




2014-06-18 14:55:01,185 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:55:37,464 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=370






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=370






    




2014-06-18 14:55:37,465 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:56:17,334 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3736






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3736






    




2014-06-18 14:56:17,335 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:56:54,163 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4505






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4505






    




2014-06-18 14:56:54,164 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:57:27,732 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9938






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9938






    




2014-06-18 14:57:27,733 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:58:04,924 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9922






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9922






    




2014-06-18 14:58:04,925 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:58:49,592 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3795






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3795






    




2014-06-18 14:58:49,593 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 14:59:31,242 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9771






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9771






    




2014-06-18 14:59:31,243 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:00:14,120 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=821






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=821






    




2014-06-18 15:00:14,121 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:00:52,116 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=94






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=94






    




2014-06-18 15:00:52,116 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:01:26,739 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2171






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2171






    




2014-06-18 15:01:26,740 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:02:00,086 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4313






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4313






    




2014-06-18 15:02:00,087 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:02:34,737 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2769






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2769






    




2014-06-18 15:02:34,738 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}

In [19]:

    

##check if gen is only non nan
homes_with_gen_only=[]
dir_path = os.path.join(os.getcwd(), 'homes/' ) 
    #os.makedirs(dir_path)
filename = 'genonly.txt'
for homeid in dataids:
    home = get_table_by_id(eng,"validated_01_2014",homeid)
    l = [i for i in home.dropna(axis=1).columns]
    
    if len(l)<5 and 'gen' in l:
        homes_with_gen_only.append(homeid)
with open(os.path.join(dir_path, filename), 'w') as outfile:
    outfile.writelines(homes_with_gen_only)


    

    




2014-06-18 15:29:56,742 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=744






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=744






    




2014-06-18 15:29:56,743 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:30:34,555 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2129






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2129






    




2014-06-18 15:30:34,556 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:31:10,659 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2575






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2575






    




2014-06-18 15:31:10,660 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:31:52,360 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4874






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4874






    




2014-06-18 15:31:52,361 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:32:37,753 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7989






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7989






    




2014-06-18 15:32:37,754 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:33:15,475 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=410






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=410






    




2014-06-18 15:33:15,476 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:33:53,413 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1782






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1782






    




2014-06-18 15:33:53,414 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:34:38,052 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3394






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3394






    




2014-06-18 15:34:38,053 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:35:16,939 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8467






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8467






    




2014-06-18 15:35:16,941 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:35:50,966 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1167






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1167






    




2014-06-18 15:35:50,967 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:36:29,926 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5026






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5026






    




2014-06-18 15:36:29,927 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:37:10,334 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8142






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8142






    




2014-06-18 15:37:10,335 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:37:55,618 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5218






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5218






    




2014-06-18 15:37:55,619 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:38:38,893 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8736






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8736






    




2014-06-18 15:38:38,894 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:39:19,615 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3263






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3263






    




2014-06-18 15:39:19,616 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:40:03,678 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6673






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6673






    




2014-06-18 15:40:03,679 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:40:48,482 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7617






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7617






    




2014-06-18 15:40:48,483 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:41:26,617 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8342






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8342






    




2014-06-18 15:41:26,618 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:42:14,251 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8829






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=8829






    




2014-06-18 15:42:14,252 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:42:51,647 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3544






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3544






    




2014-06-18 15:42:51,647 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:43:46,808 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=370






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=370






    




2014-06-18 15:43:46,809 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:44:47,521 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3736






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3736






    




2014-06-18 15:44:47,521 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:45:38,219 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4505






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4505






    




2014-06-18 15:45:38,220 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:46:23,904 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9938






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9938






    




2014-06-18 15:46:23,905 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:47:08,231 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9922






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9922






    




2014-06-18 15:47:08,232 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:47:56,517 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3795






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3795






    




2014-06-18 15:47:56,518 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:48:40,245 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9771






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9771






    




2014-06-18 15:48:40,246 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:49:21,165 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=821






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=821






    




2014-06-18 15:49:21,166 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:49:58,922 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=94






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=94






    




2014-06-18 15:49:58,922 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:50:43,989 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2171






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2171






    




2014-06-18 15:50:43,990 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:51:28,233 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4313






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4313






    




2014-06-18 15:51:28,234 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:52:10,622 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2769






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2769






    




2014-06-18 15:52:10,623 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:52:53,966 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9499






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9499






    




2014-06-18 15:52:53,967 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:53:57,163 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1800






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1800






    




2014-06-18 15:53:57,164 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:54:44,180 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2829






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2829






    




2014-06-18 15:54:44,181 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:55:35,613 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4373






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4373






    




2014-06-18 15:55:35,614 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:56:21,390 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4154






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4154






    




2014-06-18 15:56:21,391 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:57:04,402 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6941






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6941






    




2014-06-18 15:57:04,403 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:58:11,794 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1632






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1632






    




2014-06-18 15:58:11,795 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 15:59:53,396 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3504






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3504






    




2014-06-18 15:59:53,397 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:01:56,500 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5972






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5972






    




2014-06-18 16:01:56,501 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:03:11,895 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=585






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=585






    




2014-06-18 16:03:11,896 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:04:05,184 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3649






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3649






    




2014-06-18 16:04:05,185 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:05:33,463 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=871






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=871






    




2014-06-18 16:05:33,463 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:06:51,766 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5938






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5938






    




2014-06-18 16:06:51,767 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:08:28,900 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5357






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5357






    




2014-06-18 16:08:28,901 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:09:38,956 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1283






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1283






    




2014-06-18 16:09:38,957 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:10:35,939 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3652






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3652






    




2014-06-18 16:10:35,940 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:11:40,161 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9937






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9937






    




2014-06-18 16:11:40,164 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:12:42,088 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3044






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3044






    




2014-06-18 16:12:42,089 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:14:37,207 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5109






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5109






    




2014-06-18 16:14:37,208 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:15:25,087 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=936






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=936






    




2014-06-18 16:15:25,087 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:16:17,698 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1994






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1994






    




2014-06-18 16:16:17,699 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:17:05,036 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9729






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9729






    




2014-06-18 16:17:05,036 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:17:56,107 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2606






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2606






    




2014-06-18 16:17:56,107 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:18:44,868 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4447






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=4447






    




2014-06-18 16:18:44,869 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:19:32,206 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5785






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5785






    




2014-06-18 16:19:32,207 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:20:12,765 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7800






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7800






    




2014-06-18 16:20:12,766 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:21:02,398 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=484






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=484






    




2014-06-18 16:21:02,399 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:21:42,017 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3221






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3221






    




2014-06-18 16:21:42,017 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:22:21,496 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2156






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2156






    




2014-06-18 16:22:21,497 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:23:02,892 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5746






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5746






    




2014-06-18 16:23:02,892 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:23:45,522 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3367






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=3367






    




2014-06-18 16:23:45,523 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:24:44,167 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2953






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2953






    




2014-06-18 16:24:44,168 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:25:32,180 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7390






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7390






    




2014-06-18 16:25:32,181 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:26:18,978 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1953






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=1953






    




2014-06-18 16:26:18,978 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:27:06,560 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5395






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5395






    




2014-06-18 16:27:06,561 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:27:54,614 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=545






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=545






    




2014-06-18 16:27:54,615 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:28:43,461 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2470






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2470






    




2014-06-18 16:28:43,462 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:29:33,895 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6730






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=6730






    




2014-06-18 16:29:33,896 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:30:16,000 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2638






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2638






    




2014-06-18 16:30:16,001 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:30:50,846 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7940






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=7940






    




2014-06-18 16:30:50,847 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:31:46,817 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9548






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9548






    




2014-06-18 16:31:46,818 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:32:30,003 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2449






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2449






    




2014-06-18 16:32:30,004 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:33:08,193 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5874






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=5874






    




2014-06-18 16:33:08,194 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:33:48,636 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2818






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=2818






    




2014-06-18 16:33:48,637 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:34:35,837 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=86






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=86






    




2014-06-18 16:34:35,838 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 16:35:20,960 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9343






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_01_2014 where "PecanStreet_SharedData".validated_01_2014.dataid=9343






    




2014-06-18 16:35:20,961 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 19:17:54,296 INFO sqlalchemy.engine.base.Engine ROLLBACK






    




INFO:sqlalchemy.engine.base.Engine:ROLLBACK






    




---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
<ipython-input-19-ce9dc4427d73> in <module>()
      5 filename = 'genonly.txt'
      6 for homeid in dataids:
----> 7     home = get_table_by_id(eng,"validated_01_2014",homeid)
      8     l = [i for i in home.dropna(axis=1).columns]
      9 

<ipython-input-7-d18d8d644fe6> in get_table_by_id(eng, table, i)
      1 def get_table_by_id(eng,table,i):
      2     query = "select * from "+schema+"."+table+ " where " +schema+"."+table+".dataid="+i
----> 3     eng_object=eng.execute(query)
      4     df = pd.DataFrame.from_records(eng_object.fetchall())
      5     df.columns = eng_object.keys()

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, statement, *multiparams, **params)
   1680 
   1681         connection = self.contextual_connect(close_with_result=True)
-> 1682         return connection.execute(statement, *multiparams, **params)
   1683 
   1684     def scalar(self, statement, *multiparams, **params):

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    710         """
    711         if isinstance(object, util.string_types[0]):
--> 712             return self._execute_text(object, multiparams, params)
    713         try:
    714             meth = object._execute_on_connection

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)
    859             statement,
    860             parameters,
--> 861             statement, parameters
    862         )
    863         if self._has_events or self.engine._has_events:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    945                                 parameters,
    946                                 cursor,
--> 947                                 context)
    948 
    949         if self._has_events or self.engine._has_events:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1096                 if cursor:
   1097                     self._safe_close_cursor(cursor)
-> 1098                 self._autorollback()
   1099 
   1100             if should_wrap:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _autorollback(self)
    608     def _autorollback(self):
    609         if not self.in_transaction():
--> 610             self._rollback_impl()
    611 
    612     def close(self):

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _rollback_impl(self)
    506                 self.engine.dialect.do_rollback(self.connection)
    507             except Exception as e:
--> 508                 self._handle_dbapi_exception(e, None, None, None, None)
    509             finally:
    510                 if self.connection._reset_agent is self.__transaction:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1074                                             e,
   1075                                             self.dialect.dbapi.Error),
-> 1076                         exc_info
   1077                         )
   1078         self._reentrant_error = True

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    183             exc_info = sys.exc_info()
    184         exc_type, exc_value, exc_tb = exc_info
--> 185         reraise(type(exception), exception, tb=exc_tb)
    186 
    187 if py3k:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _rollback_impl(self)
    504                 self.engine.logger.info("ROLLBACK")
    505             try:
--> 506                 self.engine.dialect.do_rollback(self.connection)
    507             except Exception as e:
    508                 self._handle_dbapi_exception(e, None, None, None, None)

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_rollback(self, dbapi_connection)
    403 
    404     def do_rollback(self, dbapi_connection):
--> 405         dbapi_connection.rollback()
    406 
    407     def do_commit(self, dbapi_connection):

OperationalError: (OperationalError) SSL SYSCALL error: EOF detected
 None None

In [14]:

    

eng = get_eng()


    

In [15]:

    

##test for cars
query = "select dataid from {0}.{1} where ({0}.{1}.car1)> 0".format(schema,table)
d = execute_query(eng,query,'df').drop_duplicates('dataid')


    

    




2014-06-20 10:18:46,470 INFO sqlalchemy.engine.base.Engine select version()






    




INFO:sqlalchemy.engine.base.Engine:select version()






    




2014-06-20 10:18:46,471 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-20 10:18:48,208 INFO sqlalchemy.engine.base.Engine select current_schema()






    




INFO:sqlalchemy.engine.base.Engine:select current_schema()






    




2014-06-20 10:18:48,209 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-20 10:18:48,487 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    




INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1






    




2014-06-20 10:18:48,488 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-20 10:18:48,617 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    




INFO:sqlalchemy.engine.base.Engine:SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1






    




2014-06-20 10:18:48,618 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-20 10:18:48,987 INFO sqlalchemy.engine.base.Engine show standard_conforming_strings






    




INFO:sqlalchemy.engine.base.Engine:show standard_conforming_strings






    




2014-06-20 10:18:48,987 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-20 10:18:49,084 INFO sqlalchemy.engine.base.Engine select dataid from "PecanStreet_SharedData".validated_04_2014 where ("PecanStreet_SharedData".validated_04_2014.car1)> 0






    




INFO:sqlalchemy.engine.base.Engine:select dataid from "PecanStreet_SharedData".validated_04_2014 where ("PecanStreet_SharedData".validated_04_2014.car1)> 0






    




2014-06-20 10:18:49,085 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}

In [16]:

    

testl = [val for val in d.to_dict()['dataid'].values()]
dir_path = os.path.join(os.getcwd(), 'cars/' )
with open(os.path.join(dir_path, 'april_homes_with_cars.json'), 'w') as outfile:
    outfile.write(str(testl)+'\n')
    outfile.write(str(len(testl)))
outfile.close


    

    
Out[16]:





<function close>

In [77]:

    

##checking car check 
april_car_check = {}
cars = {}
for car in testl:
    car1 = get_table_by_id(eng,"validated_04_2014",str(car))
    april_car_check[car]=car1['car1'].sum()
    cars[car]=car1


    

    




2014-06-19 12:58:10,012 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_04_2014 where "PecanStreet_SharedData".validated_04_2014.dataid=2470






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_04_2014 where "PecanStreet_SharedData".validated_04_2014.dataid=2470






    




2014-06-19 12:58:10,014 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-19 12:59:50,648 INFO sqlalchemy.engine.base.Engine ROLLBACK






    




INFO:sqlalchemy.engine.base.Engine:ROLLBACK






    




---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-77-61e133dda375> in <module>()
      3 cars = {}
      4 for car in testl:
----> 5     car1 = get_table_by_id(eng,"validated_04_2014",str(car))
      6     april_car_check[car]=car1['car1'].sum()
      7     cars[car]=car1

<ipython-input-7-d18d8d644fe6> in get_table_by_id(eng, table, i)
      1 def get_table_by_id(eng,table,i):
      2     query = "select * from "+schema+"."+table+ " where " +schema+"."+table+".dataid="+i
----> 3     eng_object=eng.execute(query)
      4     df = pd.DataFrame.from_records(eng_object.fetchall())
      5     df.columns = eng_object.keys()

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, statement, *multiparams, **params)
   1680 
   1681         connection = self.contextual_connect(close_with_result=True)
-> 1682         return connection.execute(statement, *multiparams, **params)
   1683 
   1684     def scalar(self, statement, *multiparams, **params):

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in execute(self, object, *multiparams, **params)
    710         """
    711         if isinstance(object, util.string_types[0]):
--> 712             return self._execute_text(object, multiparams, params)
    713         try:
    714             meth = object._execute_on_connection

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_text(self, statement, multiparams, params)
    859             statement,
    860             parameters,
--> 861             statement, parameters
    862         )
    863         if self._has_events or self.engine._has_events:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    945                                 parameters,
    946                                 cursor,
--> 947                                 context)
    948 
    949         if self._has_events or self.engine._has_events:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   1106                                         self.dialect.dbapi.Error,
   1107                                         connection_invalidated=self._is_disconnect),
-> 1108                                     exc_info
   1109                                 )
   1110 

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/util/compat.pyc in raise_from_cause(exception, exc_info)
    183             exc_info = sys.exc_info()
    184         exc_type, exc_value, exc_tb = exc_info
--> 185         reraise(type(exception), exception, tb=exc_tb)
    186 
    187 if py3k:

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/base.pyc in _execute_context(self, dialect, constructor, statement, parameters, *args)
    938                                      statement,
    939                                      parameters,
--> 940                                      context)
    941         except Exception as e:
    942             self._handle_dbapi_exception(

/Users/sabina/anaconda/lib/python2.7/site-packages/sqlalchemy/engine/default.pyc in do_execute(self, cursor, statement, parameters, context)
    433 
    434     def do_execute(self, cursor, statement, parameters, context=None):
--> 435         cursor.execute(statement, parameters)
    436 
    437     def do_execute_no_params(self, cursor, statement, context=None):

DatabaseError: (DatabaseError) SSL SYSCALL error: Operation timed out
 'select * from "PecanStreet_SharedData".validated_04_2014 where "PecanStreet_SharedData".validated_04_2014.dataid=2470' {}

In [159]:

    

months_cars={}
dir_path = os.path.join(os.getcwd(), 'cars/' )
for mon in ['january','feburary','march','april']:
    f=open(os.path.join(dir_path, mon+'_homes_with_cars.json'), 'r')
    months_cars[mon]=f.readline()
    f.close()


    

In [165]:

    

months_cars['feburary']= ast.literal_eval(months_cars['feburary'])


    

In [182]:

    

months_cars_dict = {k:[] for k in months_cars['january']}


    

In [183]:

    

for mon in ['january','feburary','march']:
    for i in months_cars[mon]:
        if i in months_cars_dict:
            months_cars_dict[i].append(mon)
        else:
            months_cars_dict[i]=[mon]


    

In [184]:

    

months_cars_dict


    

    
Out[184]:





{370: ['january', 'feburary', 'march'],
 545: ['january', 'feburary', 'march'],
 624: ['january', 'feburary', 'march'],
 661: ['january', 'feburary', 'march'],
 1169: ['january', 'feburary', 'march'],
 1185: ['january', 'feburary', 'march'],
 1629: ['january', 'feburary', 'march'],
 1642: ['january', 'feburary'],
 1697: ['january', 'feburary', 'march'],
 1714: ['january', 'feburary', 'march'],
 1782: ['january', 'feburary', 'march'],
 1801: ['january'],
 1953: ['january', 'feburary', 'march'],
 2335: ['january'],
 2470: ['january', 'feburary', 'march'],
 2638: ['january', 'feburary', 'march'],
 2769: ['january', 'feburary', 'march'],
 2814: ['january', 'feburary', 'march'],
 3044: ['january'],
 3192: ['january', 'feburary', 'march'],
 3367: ['january', 'feburary', 'march'],
 3482: ['january', 'feburary', 'march'],
 3723: ['january', 'feburary', 'march'],
 3795: ['january', 'feburary', 'march'],
 3967: ['january', 'feburary', 'march'],
 4135: ['january', 'feburary', 'march'],
 4336: ['january', 'feburary', 'march'],
 4373: ['january'],
 4505: ['january', 'feburary', 'march'],
 4526: ['january', 'feburary', 'march'],
 4641: ['january', 'feburary', 'march'],
 4767: ['january', 'feburary', 'march'],
 4957: ['january', 'feburary', 'march'],
 4998: ['january', 'feburary', 'march'],
 5109: ['january', 'feburary', 'march'],
 5357: ['january', 'feburary', 'march'],
 6139: ['january', 'feburary', 'march'],
 6836: ['january', 'feburary', 'march'],
 6910: ['january', 'feburary', 'march'],
 6941: ['january', 'feburary', 'march'],
 6990: ['january'],
 7850: ['january', 'feburary', 'march'],
 7863: ['january', 'feburary', 'march'],
 7875: ['january', 'feburary', 'march'],
 7940: ['january', 'feburary', 'march'],
 7982: ['january', 'feburary', 'march'],
 8046: ['january', 'feburary', 'march'],
 8142: ['january', 'feburary', 'march'],
 8197: ['january', 'feburary', 'march'],
 8645: ['january', 'feburary', 'march'],
 8669: ['january', 'feburary', 'march'],
 8736: ['january', 'feburary'],
 9484: ['january', 'feburary', 'march'],
 9499: ['january'],
 9609: ['january', 'feburary', 'march'],
 9729: ['january', 'feburary', 'march'],
 9830: ['january', 'feburary', 'march'],
 9932: ['january', 'feburary', 'march'],
 9934: ['january', 'feburary', 'march'],
 9937: ['january', 'feburary', 'march']}

In [188]:

    

with open(os.path.join(dir_path, 'month_cars.json'), 'w') as outfile:
    outfile.write(json.dumps(months_cars_dict))


    

In [202]:

    

car_in_feburary = get_table_by_id(eng,"validated_02_2014",'9499')


    

    




2014-06-19 17:53:40,763 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_SharedData".validated_02_2014 where "PecanStreet_SharedData".validated_02_2014.dataid=9499






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_SharedData".validated_02_2014 where "PecanStreet_SharedData".validated_02_2014.dataid=9499






    




2014-06-19 17:53:40,764 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-202-3356778637e9> in <module>()
----> 1 car_in_feburary = get_table_by_id(eng,"validated_02_2014",'9499')

<ipython-input-7-d18d8d644fe6> in get_table_by_id(eng, table, i)
      3     eng_object=eng.execute(query)
      4     df = pd.DataFrame.from_records(eng_object.fetchall())
----> 5     df.columns = eng_object.keys()
      6     return df

/Users/sabina/anaconda/lib/python2.7/site-packages/pandas/core/generic.pyc in __setattr__(self, name, value)
   1847         This allows simpler access to columns for interactive use."""
   1848         if name in self._internal_names_set:
-> 1849             object.__setattr__(self, name, value)
   1850         elif name in self._metadata:
   1851             return object.__setattr__(self, name, value)

/Users/sabina/anaconda/lib/python2.7/site-packages/pandas/lib.so in pandas.lib.AxisProperty.__set__ (pandas/lib.c:38173)()

/Users/sabina/anaconda/lib/python2.7/site-packages/pandas/core/generic.pyc in _set_axis(self, axis, labels)
    398 
    399     def _set_axis(self, axis, labels):
--> 400         self._data.set_axis(axis, labels)
    401         self._clear_item_cache()
    402 

/Users/sabina/anaconda/lib/python2.7/site-packages/pandas/core/internals.pyc in set_axis(self, axis, new_labels)
   1953         if new_len != old_len:
   1954             raise ValueError('Length mismatch: Expected axis has %d elements, '
-> 1955                              'new values have %d elements' % (old_len, new_len))
   1956 
   1957         self.axes[axis] = new_labels

ValueError: Length mismatch: Expected axis has 0 elements, new values have 73 elements

In [48]:

    

car_in_january['garage1']


    

    
Out[48]:





nan

In [30]:

    

def get_apps(df):
    apps = {}
    print(len(df.columns))
    for col in df.columns:
        if col!='localminute':
          if df[col].sum()!='nan':  
              apps[col]=df[col].sum()
    print(len(apps))
    return apps


    

In [182]:

    

##should make this faster
def get_pie(d,drop,title):
    if drop in d:
        d.pop(drop,None)
    fracs = [float(i) for i in d.values() if not (math.isnan(float(i)))]
    labels = [k for k in d.keys()] 
    ax = pyplt.figure(figsize=(6,6))
    pyplt.pie(fracs,labels=labels)
    pyplt.title(title)
    savefig(title+'.png')
    pyplt.clf()
    pyplt.cla()
    pyplt.close()


    

In [191]:

    

##get graphs for all 4 2014 months 
def get_pie_month_hvac(eng,month):
    h = get_hvac_stats(eng,month)
    print h
  #  h={k:float(list(h[k][0])[0]) for k in h.keys()}
    get_pie(h,'total_rows',month_titles[month]+'_HVAC_and_USE_wo_ev')


    

In [195]:

    

get_pie_month_hvac(eng,'04')


    

    




2014-06-18 12:56:07,126 INFO sqlalchemy.engine.base.Engine select sum(air1) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(air1) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:07,126 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:11,750 INFO sqlalchemy.engine.base.Engine select sum(air2) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(air2) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:11,751 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:15,639 INFO sqlalchemy.engine.base.Engine select sum(air3) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(air3) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:15,641 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:18,143 INFO sqlalchemy.engine.base.Engine select sum(furnace1) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(furnace1) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:18,145 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:23,785 INFO sqlalchemy.engine.base.Engine select sum(furnace2) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(furnace2) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:23,786 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:26,911 INFO sqlalchemy.engine.base.Engine select sum(airwindowunit1) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(airwindowunit1) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:26,912 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:29,515 INFO sqlalchemy.engine.base.Engine select sum(housefan1) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(housefan1) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:29,516 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:34,201 INFO sqlalchemy.engine.base.Engine select sum(heater1) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(heater1) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:34,202 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:38,657 INFO sqlalchemy.engine.base.Engine select sum(use) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(use) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:38,658 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




2014-06-18 12:56:42,652 INFO sqlalchemy.engine.base.Engine select sum(car1) from "PecanStreet_SharedData".validated_04_2014






    




INFO:sqlalchemy.engine.base.Engine:select sum(car1) from "PecanStreet_SharedData".validated_04_2014






    




2014-06-18 12:56:42,652 INFO sqlalchemy.engine.base.Engine {}






    




INFO:sqlalchemy.engine.base.Engine:{}






    




{'heater': 0, 'use': 8038862.05, 'furnaces': 474096.491, 'air': 1296054.4649999999}

In [24]:

    

##for all the houses return the highest appliances
def get_highest(house):
    sums= get_sums(house)
    sums.pop('use',None)
    sums.pop('car1',None)
    sorted_sums = sorted(sums.iteritems(), key=operator.itemgetter(1), reverse = True)
    
    return sorted_sums


    

In [47]:

    

print t.keys()
test['furnace1']


    

    




---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
<ipython-input-47-ad829da7b779> in <module>()
----> 1 print t.keys()
      2 test['furnace1']

NameError: name 't' is not defined

In [ ]: