So I want to upload a temporary table, populate it using a csv file and then run a bunch of other queries with the same connection. Currently I'm uploading a normal table, doing my queries and then dropping it. But I want to make it temporary to avoid confusion and to avoid large amounts of data being left in the db if the code stops for some reason (exception/debugging etc.) before it gets a chance to drop the table. I'm doing all this in python using psycopg2.
Firstly, I've assumed the temporary table will hang around as long as the connection is alive. Is this true? But more importantly, does a psycopg2 db connection ever automatically handle a momentary connection drop out by reestablishing a connection? The queries I'm running are very time consuming so I worry that this could happen. In which case is there some way of knowing when the connection refreshes so I can reupload the temporary table?
does a psycopg2 db connection ever automatically handle a momentary connection drop out by reestablishing a connection?
Do you mean does it get impatient, kill a live but "stalled" (e.g. network congestion) connection, and replace it with a new one? No. You could probably write code to do that if you wanted (but why would you?) but psycopg2 itself won't do that.