two python files needed for %pg magic in IPython pgdb.py and pgmagic.py. This is pgdb.py

gistfile1.py

# pgdb.py

__author__ = 'nitin'
import psycopg2 as pspg2
import sys
# import pandas as pd, then use pandas.io.sql

db_data = {
    'host':'localhost',
    'dbname': 'nitin',
    'user': 'nitin',
    'password': ''
}

_conn = None
_cur = None

def connect_string(data=db_data):
    cs = "host=%s dbname=%s user=%s password=%s" % (data['host'], data['dbname'], data['user'], data['password'])
    return cs

def connect():
    global _conn
    global db_data
    status = 'success'
    if not _conn:
        try:
            cs = connect_string(db_data)
            _conn = pspg2.connect(cs)
        except Exception, err:
            status = str(err)
            sys.stderr.write('error: %s\n' % status)
            _conn = None
    return status

def cursor():
    connect()
    global _cur
    if not _cur:
        _cur = _conn.cursor()
    return

def exec_qry(qry_str):
    global _conn, _cur
    cursor()
    status = 'success. '
    results = None
    try:
        _cur.execute(qry_str)
        results = _cur.fetchall()
    except Exception, err:
        status = str(err)
        sys.stderr.write('error: %s\n' % status)
        _cur.close()
        _cur=None
        _conn.close()
        _conn = None
    print "done\n"
    return status, results

def test():
    """
    Simple smoke test for db connectivity and pg_query magic success.
    Assumes a table called ipytest exists in the db declared in dict called db_data  in this module.
    
    e.g.
    
    db_data = {
        'host':'localhost',
        'dbname': ********,
        'user': *******,
        'password': *******
    }
    
    
    table ipytest needs to exist in db defined by db_data above, table is of the form
    
     a | i 
    ---+---
     x | 1
     y | 2
     z | 3
     
     created via the following SQL:
     
     create table ipytest ( a varchar(10), i integer );
     insert into ipytest values ('x',1);                                                                                                                                                                                                   
     insert into ipytest values ('y',2);
     insert into ipytest values ('z',3);
          
    """
    status = connect()
    if status == 'success':
        print 'connected\n'
        return exec_qry('select * from ipytest')
    else:
        return 'test failed'

def close():
    if _cur:
        _cur.close()
    if _conn:
        _conn.close()
    return 'done closing'