In [17]:

    

import psycopg2
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import *


    

In [18]:

    

user_name = 'USERNAME'
pw='PASSWORD'
host = "db.wiki-energy.org"
port = "5432"
db = "postgres"
connect_string = "dbname = 'postgres' host='db.wiki-energy.org'"+ " user="+user_name+" password="+pw


    

In [3]:

    

conn = psycopg2.connect(connect_string)


    

In [4]:

    

curr=conn.cursor()


    

In [10]:

    




    

In [12]:

    

curr.execute("""SELECT distinct"PecanStreet_RawData".audits_2011) from "PecanStreet_RawData".audits_2011""")


    

    




---------------------------------------------------------------------------
InternalError                             Traceback (most recent call last)
<ipython-input-12-12cb390991e1> in <module>()
----> 1 curr.execute("""SELECT "PecanStreet_RawData".audits_2011 from "PecanStreet_RawData".audits_2011""")

InternalError: current transaction is aborted, commands ignored until end of transaction block

In [6]:

    

audit_2011 = pd.DataFrame(curr.fetchall())


    

    




---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-6-65e464f3f07b> in <module>()
----> 1 audit_2011 = pd.DataFrame(curr.fetchall())

ProgrammingError: no results to fetch

In [16]:

    

len(audit_2011.index)


    

    
Out[16]:





188

In [48]:

    

df = pd.io.sql.read_frame("""SELECT distinct("PecanStreet_RawData".audits_2011.dataid) from "PecanStreet_RawData".audits_2011""",conn)


    

In [23]:

    

df.head()


    

    
Out[23]:






  

    

      

      
dataid
    
  
  

    

      
0
      
  744
    
    

      
1
      
 8890
    
    

      
2
      
 2129
    
    

      
3
      
 2575
    
    

      
4
      
 3834
    
  

In [5]:

    

rows = curr.fetchall()


    

In [8]:

    

curr.execute("""select distinct("PecanStreet_RawData".audits_2011.dataid) from "PecanStreet_RawData".audits_2011, "PecanStreet_CuratedSets".group2_disaggregated_2013_05 where  "PecanStreet_CuratedSets".group2_disaggregated_2013_05.dataid="PecanStreet_RawData".audits_2011.dataid""")


    

In [10]:

    

rows=curr.fetchall()


    

In [11]:

    

rows


    

    
Out[11]:





[(9922,),
 (94,),
 (5972,),
 (2818,),
 (9609,),
 (93,),
 (7531,),
 (9019,),
 (2864,),
 (7536,),
 (9933,),
 (739,),
 (7769,)]

In [32]:

    

#trying sqlalchemy engine
#url format:
#dialect+driver://username:password@host:port/database
url = "postgresql"+"://"+user_name+":"+pw+"@"+host+":"+port+"/"+db


    

In [33]:

    

engine = create_engine(url)
engine.echo=True


    

In [43]:

    

#create query 
query = """select * from "PecanStreet_RawData".audits_2011"""


    

In [45]:

    

df_eng = pd.DataFrame.from_records(engine.execute(query).fetchall())


    

    




2014-06-07 19:18:28,965 INFO sqlalchemy.engine.base.Engine select * from "PecanStreet_RawData".audits_2011






    




INFO:sqlalchemy.engine.base.Engine:select * from "PecanStreet_RawData".audits_2011






    




2014-06-07 19:18:28,966 INFO sqlalchemy.engine.base.Engine {}






    




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

In [46]:

    

df_eng.head()


    

    
Out[46]:






  

    

      

      
0
      
1
      
2
      
3
      
4
      
5
      
6
      
7
      
8
      
9
      
...
      
301
      
302
      
303
      
304
      
305
      
306
      
307
      
308
      
309
      
310
    
  
  

    

      
0
      
  585
      
 3/30/2011
      
 1
      
 3
      
 2008
      
   not assessed
      
 1720
      
 15480
      
     North
      
 None
      
...
      
  997
      
 1540
      
 None
      
    6
      
                                               N/A
      
 None
      
 None
      
 None
      
 None
      
  49
    
    

      
1
      
 9933
      
 11/3/2011
      
 1
      
 3
      
 1952
      
        unknown
      
 1471
      
 11768
      
 Southeast
      
 None
      
...
      
 1728
      
 2474
      
 None
      
 12.6
      
 House is primarily pier and beam which account...
      
    0
      
    0
      
    0
      
    1
      
  93
    
    

      
2
      
 9134
      
 3/15/2011
      
 2
      
 3
      
 2007
      
   not assessed
      
 2122
      
  None
      
         W
      
 None
      
...
      
 1396
      
 2017
      
 None
      
 None
      
                                              None
      
 None
      
 None
      
 None
      
 None
      
 175
    
    

      
3
      
 3456
      
  4/4/2011
      
 1
      
 3
      
 2008
      
   not assessed
      
 1720
      
  None
      
     south
      
 None
      
...
      
 1255
      
 1274
      
 None
      
 None
      
 CFM50 data listed. Strong/gusty winds during t...
      
 None
      
 None
      
 None
      
 None
      
  55
    
    

      
4
      
 1589
      
 6/24/2011
      
 1
      
 3
      
 1994
      
  DAVID WEEKLEY
      
 2426
      
 24260
      
      WEST
      
 None
      
...
      
 2503
      
 2413
      
 2496
      
 6.17
      
                        (2496 csm50 * 60) / 24260V
      
    0
      
    0
      
    0
      
    1
      
 115
    
  

5 rows × 311 columns

In [ ]: