I was working with the mezzanine cms (built on django) and saw it was moving towards python3 support. Since the django and mezzanine projects prefer postgresql I decided it was time to check out the python database adapter for postgresql - psycopg - in python3.
## psycopg is a python db api 2.0-compliant database adapter for the postgresql rdms; the python db api specification 2.0 is defined in pep 249
! using ubuntu 12.04, python 3.3.5, postgresql 9.1.11
! links
http://initd.org/psycopg/
http://pythonhosted.org/psycopg2/
https://pypi.python.org/pypi/psycopg2/2.5.2
http://legacy.python.org/dev/peps/pep-0249/
## installed psycopg2 package from pypi using pip
! requires python 2.5|2.6|2.7|3.1|3.2|3.3
! the python3-psycopg2 package in the ubuntu 12.04 repos is 2.4.5
! the python3.3-dev and libpq-dev packages must be installed because installation builds a python c extension
root@dev:~# pip install psycopg2
Downloading/unpacking psycopg2
Downloading psycopg2-2.5.2.tar.gz (685kB): 685kB downloaded
Running setup.py (path:/tmp/pip_build_root/psycopg2/setup.py) egg_info for package psycopg2
Installing collected packages: psycopg2
Running setup.py install for psycopg2
Skipping implicit fixer: buffer
Skipping implicit fixer: idioms
Skipping implicit fixer: set_literal
Skipping implicit fixer: ws_comma
building 'psycopg2._psycopg' extension
gcc -pthread -Wno-unused-result -DNDEBUG -g -fwrapv -O2 -Wall -Wstrict-prototypes -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DPSYCOPG_DEFAULT_PYDATETIME=1 -DPSYCOPG_VERSION="2.5.2 (dt dec pq3 ext)" -DPG_VERSION_HEX=0x09010B -DPSYCOPG_EXTENSIONS=1 -DPSYCOPG_NEW_BOOLEAN=1 -DHAVE_PQFREEMEM=1 -I/usr/include/python3.3m -I. -I/usr/include/postgresql -I/usr/include/postgresql/9.1/server -c psycopg/psycopgmodule.c -o build/temp.linux-x86_64-3.3/psycopg/psycopgmodule.o -Wdeclaration-after-statement
...
Successfully installed psycopg2
Cleaning up...
! files/directories
/usr/local/lib/python3.3/dist-packages/psycopg2/
/usr/local/lib/python3.3/dist-packages/psycopg2-2.5.2-py3.3.egg-info/
/usr/local/lib/python3.3/dist-packages/psycopg2/_psycopg.cpython-33m.so
## access to a database requires a connection object and a cursor object; by default cursors are tuple-based but dictionary-based cursors are provided via the psycopg2.extras module and may be specified during either connection or cursor creation
! postgresql has no equivalent for the use statement in mysql and so from python a database must be specified in the call to connect()
! if autocommit is not enabled then all transactions must be manually terminated i.e. either committed or rolled back using the connection object methods commit() and rollback(); even select statements start a transaction
>>> import psycopg2, psycopg2.extras
>>>
>>> conn = psycopg2.connect(host="localhost", database="test", user="<user>", password="<password>")
>>> conn.autocommit=True
>>> cur = conn.cursor()
>>> cur2 = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
>>>
>>> cur.execute("create table person (id serial not null, fname varchar default '' not null, lname varchar default '' not null, primary key (id), unique (lname, fname))")
>>> cur.execute("insert into person (fname, lname) values ('keith', 'brown'), ('jon', 'doe')")
>>>
>>> cur.execute("select * from person")
>>> cur.fetchall()
[(1, 'keith', 'brown'), (2, 'jon', 'doe')]
>>>
>>> cur2.execute("select * from person")
>>> cur2.fetchall()
[{'lname': 'brown', 'id': 1, 'fname': 'keith'}, {'lname': 'doe', 'id': 2, 'fname': 'jon'}]
>>>
>>> del cur
>>> del cur2
>>> del conn
>>>
>>> conn = psycopg2.connect(host="localhost", database="test", user="<user>", password="<password>", connection_factory=psycopg2.extras.RealDictConnection)
>>> conn.autocommit=True
>>> cur = conn.cursor()
>>> cur.execute("select * from person")
>>> cur.fetchall()
[{'lname': 'brown', 'id': 1, 'fname': 'keith'}, {'lname': 'doe', 'id': 2, 'fname': 'jon'}]
>>>
>>> cur.close()
>>> conn.close()
>>>
! if autocommit is not enabled and a mistake is made entering e.g. a query then no further queries are possible until rollback() is called
>>> cur.execute("select * from persons")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python3.3/dist-packages/psycopg2/extras.py", line 223, in execute
return super(RealDictCursor, self).execute(query, vars)
psycopg2.ProgrammingError: relation "pg_databases" does not exist
LINE 1: select * from persons
^
>>> cur.execute("select * from person")
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python3.3/dist-packages/psycopg2/extras.py", line 223, in execute
return super(RealDictCursor, self).execute(query, vars)
psycopg2.InternalError: current transaction is aborted, commands ignored until end of transaction block
>>> conn.rollback()
>>> cur.execute("select * from person")
>>>