Using pygresql python module with Greenplum

Python is my favourite progamming langauge and Greenplum Database is my key skill. So, Using both to perform some task is no surprise. At first I tried to use psycopg2 but When I started looking in Greenplum source code I found that Pivotal is using pygresql. So, I too thought using the same.

So, What is pygresql?

Pygresql is Python interface for PostgreSQL. It allows PostgreSQL queries to run through Python script.

Importing pygresql module in Python program.

from pygresql.pg import DB

Creating connection to database

con = DB(dbname='gpadmin', host='localhost', port=5432, user='gpadmin', passwd='changeme')

Running Queries

>>>a = con.query("select * from gp_segment_configuration where hostname=mdw")
>>>print(a) #This will print the result as same as psql utility
dbid|content|role|preferred_role|mode|status|port|hostname       |address|replication_port|san_mounts
----+-------+----+--------------+----+------+----+---------------+-------+----------------+----------
1   |-1     |p   |p             |s   |u     |5432|mdw            |mdw    |                |


>>> a.getresult() #To get result as Python tuples
[(1, -1, 'p', 'p', 's', 'u', 5432, 'mdw', 'mdw', None, None)]

>>> a.dictresult() #To get result as Python Dictionary
[{'status': 'u', 'replication_port': None, 'dbid': 1, 'hostname': 'mdw', 'preferred_role': 'p', 'content': -1, 'role': 'p', 'mode': 's', 'address': 'mdw', 'san_mounts': None, 'port': 5432}]

Closing the connection

con.close()

Here is complete tutorial for pygresql.