Postgres Database Access
These are wrapper objects around PyGreSQL-3.5.

PgCursorInSubthread - handling a Postgres named cursor in a subthread, which the application periodically polls until the subthread has received results from Postgres
PgQueryInSubthread - handling a single query to Postgres in a subthread, which the application periodically polls until the subthread has received results from Postgres
PgQueryInSubthreadWithQtTimer - just like the last one, except that the polling is done by a Qt timer build into the query object
PgQueryInForeground - simple wrapper of PyGreSQL that causes the application to block until it receives results from Postgres

PgCursorInSubthread

Problem: The libpq interface will load into RAM the entire result set from a select statement for any client application that is linked with libpq, so you can't use this for scrolling through very large data sets (why use a database at all if you can fit it into RAM?).

Solution: The PgCursorInSubthread creates a Postgres named cursor for your SQL string, and then issues the FETCH and MOVE SQL commands against this named cursor to pull back only the very limited rows that you are interested in. Because these FETCH and MOVE SQL commands could take a long time when running against a very large result set, they are issued in a subthread so that the main thread of program execution does not hang.

PgCursorInSubthread starts off a Python subthread from the thread module's start_new_thread() function. This subthread calls the main() method, which spins in an infinite loop, calling internal methods (like __fetch(), __move() and __close()) to talk to the database using PyGreSQL when needed, and polling several internal state variables (like __new_rows_requested, __refresh and __do_close) to determine what to do when it's not waiting on the database. It does a tiny time.sleep() during each loop so that when there is no work to do it does not spin through the loop out of control and take up tons of CPU time.

The main thread of execution in a program that uses PgCursorInSubthread will tell a PgCursorInSubthread object what to do through an API that sets and queries the internal state variables. If there are rows of data back from the database, they can be retrieved.

Specifically, the main program in the main thread will call get_or_request_rows(starting_row_number, ending_row_number). This will lock the lock, and search through the row cache, which is implemented as a dict indexed by row number. If the rows from starting_row_number to ending_row_number can be fetched from the row cache, they are immediately returned and the lock is unlocked.
If all the requested rows cannot be found in the row cache, get_or_request_rows() will set some internal variables like __new_rows_requested, and unlock the lock and return an empty list to the main program.
The main program in the main thread would need to know that it needs to keep polling get_or_request_rows() until it gets back more than an empty list. This could be done via user intervention (clicking on a refresh button), or by a timer.
- In the subthread, the main() method of PgCursorInSubthread will see that the __new_rows_requested state variable has been changed as soon as it goes through it's loop again (and as soon as the main thread unlocks the lock). It will then call the internal __move() and/or __fetch() methods, which in turn call on the PyGreSQL module. Those __move() and/or __fetch() calls could hang for a very long time as the database does its work, but that's OK because it is happening in a subthread, so the main thread of the program will not hang.
  - The __move() and/or __fetch() routines issue sql statements against the named cursor that was created in the initialization of the PgCursorInSubthread object. These sql statements tell the named cursor to move forward or backward for a certain number of rows, and to retrieve a very limited result set - just the range from starting_row_number to ending_row_number, and a little more prefetch, based on the direction of movement.
  - When the rows actually do return, the subthread lock the lock, and sprinkles the result rows into the row cache. If the cache is too full, the subthread will at this time also clean out the least recently used rows from row cache. The subthread then unlocks the lock and goes back to looping and taking tiny sleeps.
The main program in the main thread would then somehow again call get_or_request_rows() and it would then get back a list containing the rows from starting_row_number to ending_row_number.

PgQueryInSubthread

Unlike PgCursorInSubthread, this class is intended for single-use queries with results that are small enough to grab in one try. This class will take a single SQL string (supplied by the application) and wait for the database to deliver the result, and then switch it's state to "ready". The application is again responsible for polling, to check PgQueryInSubthread for readiness. When readiness occurs, the application can get from PgQueryInSubthread a PyGreSQL "query" object, which can deliver the result of the query to the application. There doesn't actually need to be any result from the SQL string (as in the case of a "CREATE TABLE ..." statement), so the application might not be interested in anything but the readiness of the PgQueryInSubthread object. The application can then refrain from obtaining the "query" object.

PgQueryInSubthread starts off a Python subthread from the thread module's start_new_thread() function. This subthread calls the main() method, which uses the internal __run_sql() method to send the application-supplied SQL string to the database using PyGreSQL. When PyGreSQL returns a result, the subthread sets it's __ready internal variable to 1.

The main thread of execution in a program that uses PgQueryInSubthread will supply the PgQueryInSubthread object with a single SQL string, and then periodically query the PgQueryInSubthread for readiness. If there are rows of data back from the database, they can be retrieved.

Specifically, the main program in the main thread will call ready(). This will lock the lock, and check the internal variable __ready, and then unlock the lock.
If ready() does not return 1, the main thread should not call query() because it would have nothing to return.
The main program in the main thread would need to know that it needs to keep polling ready() until it gets back 1. This could be done via user intervention (clicking on a refresh button), or by a timer.
- In the subthread, the main() method calls the __run_sql() method of PgQueryInSubthread, which will eventually return, and the subthread will store the PyGreSQL module's query object in the internal variable __query, and it would set the internal variable __ready to 1.
The main program in the main thread would then somehow again call ready() and it would then get back 1. It could, if it desired, then call query() to get the PyGreSQL query object which resulted from executing the SQL string. The returned rows can be obtained from the query object using it's getresult() or getdictresult() methods.
Or, if the main program had supplied a SQL string like "CREATE TABLE...", and it was only interested in the readiness of the PgQueryInSubthread object, the call to query() by the main thread could be omitted.

PgQueryInSubthreadWithQtTimer

Like PgQueryInSubthread, this class is intended for single-use queries with results that are small enough to grab in one try. But this class removes from the main thread all responsibility for polling. It does this by assuming that the main thread is a Qt application, and does it's own polling via a Qt timer. This class requires that the main thread supply it with a callback function that will be called once the results are available.

This class will take as arguments a callback function, and a single SQL string (supplied by the application) and will then wait in the subthread for the database to deliver the result, and then call the application supplied callback function in the main thread, passing itself as the one and only argument. In the callback function, the application can get from PgQueryInSubthreadWithQtTimer a PyGreSQL "query" object, which can deliver the result of the query to the application. There doesn't actually need to be any result from the SQL string (as in the case of a "CREATE TABLE ..." statement), so the application might not be interested in anything but the actual invocation of the callback function. The application can then refrain from obtaining the "query" object.

PgQueryInSubthreadWithQtTimer starts off a Python subthread from the thread module's start_new_thread() function. This subthread calls the main() method, which uses the internal __run_sql() method to send the application-supplied SQL string to the database using PyGreSQL. When PyGreSQL returns a result, the subthread sets it's __ready internal variable to 1.

The main thread of execution in a program that uses PgQueryInSubthreadWithQtTimer will supply the PgQueryInSubthreadWithQtTimer object with a callback function and a single SQL string, and then the PgQueryInSubthreadWithQtTimer will poll itself for readiness in the main thread.

Specifically, the main program in the main thread will call the PgQueryInSubthreadWithQtTimer constructor. This constructor will create a Qt timer which is stored in the PgQueryInSubthreadWithQtTimer variable my_timer, and which will periodically call the PgQueryInSubthreadWithQtTimer method my_timer_callback() in the main thread.
- The my_timer_callback() method would keep checking the internal variable __ready to see if the subthread had completed its work.
The constructor will also start a subthread.
- In the subthread, the main() method calls the __run_sql() method of PgQueryInSubthread, which will eventually return, and the subthread will store the PyGreSQL module's query object in the internal variable __query, and it would set the internal variable __ready to 1.
In the main thread, the my_timer_callback() method would eventually find the __ready internal variable set to 1, indicating that the subthread had finished it's work. The my_timer_callback() method would then stop the timer, and call the application-supplied callback function, passing the PgQueryInSubthreadWithQtTimer object as the callback function's one and only argument.
If the main thread of the application was interested in the results of the query, it could, in the callback function, call the query() method of the PgQueryInSubthreadWithQtTimer object to get the PyGreSQL query object which resulted from executing the SQL string. The returned rows can be obtained from the query object using it's getresult() or getdictresult() methods.
Or, if the main program had supplied a SQL string like "CREATE TABLE...", and it was only interested in the readiness of the PgQueryInSubthreadWithQtTimer object, the call to query() by the main thread could be omitted.