On Tue, Nov 27, 2012 at 4:16 AM, Hynek Schlawack <hs@ox.cx> wrote:

Hi,

I have a really bad time with the combination of a low-volume service and adbapi.ConnectionPool, pyodbc, FreeTDS and Sybase.

Basically my connections just time out and fail in weird, generic ways like:

Error: ('01000', '[01000] [FreeTDS][SQL Server]Unexpected EOF from the server (20017) (SQLEndTran)')

(but in many others too, there is no real pattern)

Have you tried enabling reconnects (cp_reconnect=True)? If "select 1" doesn't work with your database you may also have to pass in custom cp_good_sql.

In my desperation, I’m employing for-loops for the SQL queries now. :(

Since there isn’t much traffic (yet) I would like to just make ConnectionPool close the connections and re-open fresh ones, as soon as they are necessary.

Is there some straight-forward way to do that? Or any better approach I’ve overlooked?

Don't use ConnectionPool at all. Just have a function that does the SQL connect etc usually normal DB-API, and call it with twisted.internet.threads.deferToThread:

def dbTxn(x):
    conn = db.connect(...)
    cursor = conn.cursor()
    cursor.execute()
    result = cursor.fetchall()
    conn.close()
    return result

deferredResult = deferToThread(dbtxn, argForX)