The solution Burhan pointed out reduces the memory usage for large datasets by only fetching single rows:

row = cursor.fetchone()

However, I noticed a significant slowdown in fetching rows one-by-one. I access an external database over an internet connection, that might be a reason for it.

Having a server side cursor and fetching bunches of rows proved to be the most performant solution. You can change the sql statements (as in alecxe answers) but there is also pure python approach using the feature provided by psycopg2:

cursor = conn.cursor('name_of_the_new_server_side_cursor')
cursor.execute(""" SELECT * FROM table LIMIT 1000000 """)

while True:
    rows = cursor.fetchmany(5000)
    if not rows:
        break

    for row in rows:
        # do something with row
        pass

you find more about server side cursors in the psycopg2 wiki

Look at this attribute:

cur.description

After you have executed your query, it will be set to None if no rows were returned, or will contain data otherwise - for example:

(Column(name='id', type_code=20, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None),)

Catching exceptions is not ideal because there may be a case where you're overriding a genuine exception.

I think the key is in the first paragraph of server-side-cursors docs (my boldface):

When a database query is executed, the Psycopg cursor usually fetches all the records returned by the backend, transferring them to the client process. If the query returned an huge amount of data, a proportionally large amount of memory will be allocted by the client.

With a "normal" cursor, the entire result set gets transferred to the client immediately. With a named cursor, you can "pull" data across the client/server interface as you need it.

For example, notice the difference in execution time of this program, depending on what type of cursor it uses (you may have to tweak the connection parameters):

import psycopg2, time
conn = psycopg2.connect(dbname='template1')
if False:
    c = conn.cursor('named')
else:
    c = conn.cursor()
c.execute('SELECT GENERATE_SERIES(1,100000000)')
print('Execute ok.')
rows = c.fetchmany(1)
print('Fetch ok.')