As you may have experienced, cancelling a long running query from
Python is not easy: ctrl-c doesn't work and it usually takes a
pg_cancel_backend from another connection.
After a request in the bug tracking, here is a recipe to allow queries
to be stopped by a ctrl-c in interactive sessions.
http://initd.org/psycopg/articles/2014/07/20/cancelling-postgresql-statements-python/
Hope it helps.
-- Daniele