Access Postgres DB data from Python

Johann Mitlöhner, 2012

Put passwd in file with permission go-r

echo dbname=mydbname user=myuserid password=mypasswd > db.txt
chmod go-r db.txt

Use psycopg2 module:

#!/usr/bin/python

import psycopg2
import os

def testpg():
  conn = psycopg2.connect(dbstr())
  cur = conn.cursor()

  cur.execute("select color, count(*) from PROD group by color limit 5;")
  for rec in cur:
    (color, cnt) = rec
    print color, cnt

  cur.close()
  conn.close()

def main():
  testpg()

def dbstr():
  return open("db.txt").read()

if __name__ == '__main__': main()