COMP3311 20T3 Psycopg2 Database Systems 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
cur.fetchall()
collect all results from current query as array of tuples
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)
results
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