In [90]:
import pandas as pd
import sqlalchemy as sa
from sqlalchemy import *
import json
In [91]:
##engine information
user_name = 'ssuffian'
pw='W&;R4P9@j'
host = "db.wiki-energy.org"
port = "5432"
db = "postgres"
schema = '\"PecanStreet_SharedData\"'
In [92]:
#Sabina's functions:
def get_eng():
url = "postgresql"+"://"+user_name+":"+pw+"@"+host+":"+port+"/"+db
eng = create_engine(url)
eng.echo=True
return eng
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
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
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
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 [93]:
eng = get_eng()
In [94]:
#query data by id
query = get_table_by_id(eng,'validated_01_2014','2638')
In [95]:
#create dataframe
df = pd.DataFrame(query)
In [96]:
#view head of df
df.head()
Out[96]:
In [84]:
#create a json object from our dataframe
df_to_json = df.to_json()
In [85]:
#read the df_to_json back to df..... this will create 'None's into NaN
#and rearrange the df in abc order
read_json = pd.read_json(df_to_json)
In [86]:
read_json.head()
Out[86]:
In [75]:
#save the json
with open('energy.json', 'w') as outfile:
json.dump(df_to_json, outfile)
In [ ]: