ampli/py/dbtest.py

import psycopg2 as pg
from configparser import ConfigParser
import pandas.io.sql as psql

def config(filename='database.ini', section='postgresql'):
    """Config parser from http://www.postgresqltutorial.com/postgresql-python/connect/"""
    # create a parser
    parser = ConfigParser()
    # read config file
    parser.read(filename)
 
    # get section, default to postgresql
    db = {}
    if parser.has_section(section):
        params = parser.items(section)
        for param in params:
            db[param[0]] = param[1]
    else:
        raise Exception('Section {0} not found in the {1} file'.format(section, filename))
 
    return db

conn = False

try:
    params = config()

    print("Connecting to database")

    conn = pg.connect(**params)

    cur = conn.cursor()

    print('PostgreSQL database version')

    cur.execute('SELECT version()')

    db_version = cur.fetchone()
    print(db_version)


    # Get tables
    dataframe = psql.read_sql("SELECT * FROM pg_catalog.pg_tables", conn)

    # Too big!
    # dataframe = psql.read_sql("SELECT * FROM coup_prd.coupdata_2016_06", conn)

    print(dataframe)

    cur.close()

except (Exception, pg.DatabaseError) as error:
    print(error)
finally:
    if conn is not False:
        conn.close()
        print('Database connection closed')