remote ssh tunnel python

connect.py

from sshtunnel import SSHTunnelForwarder
import psycopg2

#http://stackoverflow.com/questions/22046708/
#https://github.com/pahaz/sshtunnel

#at least wrap in a try except block
server = SSHTunnelForwarder(
    ('remote.server.ip', remote.port),
    ssh_username="jeff",
    ssh_private_key="path\to\your\openssh\key",
    remote_bind_address=('127.0.0.1', 5432)# this needs to be the port on the remote server,
    local_bind_address=('localhost', 1234), #this can be whatever you want
)

server.start()

print(server.local_bind_port)
# work with `SECRET SERVICE` through `server.local_bind_port`.

conn = psycopg2.connect(database="dbname",port=server.local_bind_port, user="scott")
cur = conn.cursor()
cur.execute("""
  Longwinded Select Statement Goes Here;
  """)
#http://initd.org/psycopg/docs/cursor.html#cursor.fetchall
print cur.fetchall() #will return a list of tuples 
conn.close()

server.stop()

"""

If you want to save the fetchall to a variable called data and then convert to JSON, 
the psycopg2 returned data types may not be compatible, so you can create a 
class that extends json.JSONEncoder to convert dates or decimals or whatever to 
a string or a flat, etc....the below worked for those 2 cases

class GenEncoder(json.JSONEncoder):

    def default(self, obj):
        if isinstance(obj, date):
            return str(obj)
        
        elif isinstance(obj, Decimal):
            return float(obj)
        
                
        return json.JSONEncoder.default(self, obj)


"""