2

Trying to serve database query results to adhoc client requests, but do not want to open a connection for each individual query. I'm not sure if i'm doing it right.

Current solution is something like this on the "server" side (heavily cut down for clarity):

import rpyc
from rpyc.utils.server import ThreadedServer
import cx_Oracle

conn = cx_Oracle.conect('whatever connect string')
cursor = conn.cursor()

def get_some_data(barcode):
    # do something
    return cursor.execute("whatever query",{'barcode':barcode})

class data_service(rpyc.Service):
   def exposed_get_some_data(self, brcd):       
       return get_some_data(brcd)


if __name__ == '__main__':
   s = ThreadedServer(data_service, port=12345, auto_register=False)
   s.start()

This runs okay for a while. However from time to time the program crashes and so far i haven't been able to track when it does that.

What i wish to confirm, is see how the database connection is created outside of the data_service class. Is this in itself likely to cause problems?

Many thanks any thoughts appreciated.

user811463
  • 31
  • 6
  • 1
    Just noticed http://cx-oracle.sourceforge.net/html/module.html?highlight=thread#cx_Oracle.threadsafety mentions something about threads and cursors. – user811463 Nov 07 '11 at 00:25
  • 1
    Might have finally found the culprit after all this time. The oracle connection needs to be created with an extra argument threaded=True. This was in the documentation but i didn't read it thoroughly enough. – user811463 Sep 20 '12 at 06:14

1 Answers1

1

I don't think the problem is that you're creating the connection outside of the class, that should be fine.

I think the problem is that you are creating just one cursor and using it for a long time, which as far as I understand is not how cursors are meant to be used.

You can use conn.execute without manually creating a cursor, which should be fine for how you're using the database. If I remember correctly, behind the scenes this creates a new cursor for each SQL command. You could also do this yourself in get_some_data(): create a new cursor, use it once, and then close it before returning the data.

In the long run, if you wish your server to be more robust, you'll need to add some error-handling for when database operations fail or the connection is lost.

A final note: Essentially you've written a very basic database proxy server. There are probably various existing solutions for this already, which already handle many issues you are likely to run in to. I recommend at least considering using an existing solution.

taleinat
  • 8,441
  • 1
  • 30
  • 44
  • Thanks heaps for that. It's a great feeling of being heard. Will definitely look into getting a simple database proxy server (didn't know of its existence before). – user811463 Nov 06 '11 at 23:30
  • I'm glad I could help :) BTW do you know that you can directly connect to a database on a remote server and use a single connection for multiple request? I assumed that you did but on second reading it isn't obvious. In any case, that is what I would normally do unless I had an actual need for a proxy. – taleinat Nov 07 '11 at 15:03
  • Thanks again. I googled a bit but could find something obvious regarding a proxy, so at the moment i moved the cursor creation part to the function. It's running well so far, no perceivable impact on performance, so tonight should be able to conclude that it's stable enough. – user811463 Nov 08 '11 at 01:35
  • oh i see, you meant without using rpyc. The trouble is the data is being requested via a web client(s) and i don't know how to keep the connection open between requests. – user811463 Nov 08 '11 at 01:45