Fetchall() limited in python

Question

I’m making a code that makes a query in postgres and returns in a list, but this is giving memory error. The part of the code that does this:

c = conn.cursor()
c.execute("SELECT latitude, longitude, gid FROM pontos")  
records = c.fetchall() #pega todos os resultados do select e armazena em uma tupla
e = conn.cursor()
e.execute("SELECT precipitacaoh, gidgeo_fk FROM historico")
records2 = e.fetchall()

How do I take, for example, the 100 elements of the query and store on the disk, and then take the next 100 elements, until I store the whole query? Not to overload the system.

Answer 1

It is possible to do this yes - I wonder if it will be useful to simply store the values in a text file: in general recover the desired data in the database and process them directly is better than Pear the same data from a text file, where we have no indexing tool, ways to search, etc...

It is very strange to me that you are having a "memory error" with records as simple as these - even millions of records should not exhaust the memory of a system with RAM in the gigabyte home.

Now, turning specifically to what you ask - the Python database connectors - all - implement beyond the fetchall, the fetchmany - which returns only the requested number of records.

So, one way to transfer a query to a local file (which I don’t consider useful - unless that’s all you want to do with the data) would be:

import csv
c = conn.cursor()
c.execute("SELECT latitude, longitude, gid FROM pontos")

with open("arquivo_destino.csv", "wt") as file_:
    writer  = csv.writer(file_)
    writer.writerow(("latitude", "longitude"))
    records = True
    while records:
        records = c.fetchmany(100)
        writer.writerows(records)
...

The connection cursor itself can also be used as an iterator, returning a query result at a time if it is used in a query - this way of using is better if you are consuming your data, rather than simply writing it to a local file:

c = conn.cursor()
c.execute("SELECT latitude, longitude, gid FROM pontos")
for record in c:
      # do things with row result

(but I’ve seen bugle implementations of coenxions to the bank in which interacting the cursor that way was very slow - if that’s the case, better make a combination with fetchmany as well).