Psycopg2

Summary information about the Python/PostgreSQL interface.

# a brief example, assuming table R(a,b,c)
import psycopg2

try:
   conn = psycopg2.connect("dbname=MyDB")
except:
   print("Can't open database")
   exit()

cur = conn.cursor()
query = "select * from R where a < %s";
cur.execute(query, [5])
   # runs: select * from R where < 5
for tup in cur.fetchall():
   x,y,z = tup
   print(str(x),str(y),z)
cur.close()
conn.close()

Connections

Connecting to the database	`conn = psycopg2.connect("dbname=DatabaseName")` may require additional information for authentication, e.g. password
Making cursors	`cur = conn.cursor()` create a cursor to open and answer queries
Committing changes	`conn.commit()` commit the effects of the current transaction `conn.rollback()` unwind the effects of the current transaction

Cursors

Setting up queries

cur.exuecute(Query) cur.execute(QueryTemplate, [Values])
E.g.

cur.execute("select * from R");
... runs the query: select * from R

cur.execute("select * from R where a between %s and %s",[5,10])
... runs the query: select * from R where a between 5 and 10

qry = """
select a, count(b)
from   R
where  b between 0 and 100
group  by a
order  by a
"""
cur.execute(qry)
... runs the query as written above

Fetching results

cur.fetchall()
collect all results from current query as array of tuples

cur.fetchmany(Size)
get the next Size results as array of tuples

cur.fetchone()
get the next result from the current query as a tuple

Idioms

Typical usages of the cursor operations

# show results as list of tuples
cur.execute("select a,b from R")
print(cur.fetchall())
output: [ (1,10), (2,25), (3,100) ...]

# one tuple per line
cur.execute("select * from R")
for tuple in cur.fetchall():
   print(tuple)

# grab individual attribute values
cur.execute("select * from R")
for tuple in cur.fetchall():
   x, y = tuple
   print(x,y)

# fetch tuples one at a time
cur.execute("select * from R")
while True:
   tuple = cur.fetchone()
   if tuple = None:
      break
   print(tuple)