Postgres fails fetching data in Python

问题:

I am using Python with psycopg2 module to get data from Postgres database.

The database is quite large (tens of GB).

Everything appears to be working, I am creating objects from the fetched data. However, after ~160000 of created objects I get the following error:

I suppose the reason is the amount of data, but I could not get anywhere searching for a solution online. I am not aware of using any proxy and have never used any on this machine before, the database is on localhost.

回答1:

It's interesting how often the "It's a local server so I'm not open to SQL injection" stance leads to people thinking that string interpolation is somehow easier than a parameterized query. In your case it's ended up with:

'... cookie_id = \'{}\''.format(cookie)

So you've ended up with something that's less legible and also fails (though from the specific error I don't know exactly how). Use parameterization:

cursor.execute("SELECT user_id, created_at FROM cookies WHERE cookie_id = %s ORDER BY created_at DESC;", (cookie,))

Bottom line, do it the correct way all the time. Note, there are cases where you must use string interpolation, e.g. for table names:

cursor.execute("SELECT * FROM %s", (table_name,)) # Not valid
cursor.execute("SELECT * FROM {}".format(table_name)) # Valid

And in those cases, you need to take other precautions if someone else can interact with the code.