Check if a database connection is busy using python

Question

I want to create a Database class which can create cursors on demand. It must be possible to use the cursors in parallel (two or more cursor can coexist) and, since we can only have one cursor per connection, the Database class must handle multiple connections.

For performance reasons we want to reuse connections as much as possible and avoid creating a new connection every time a cursor is created: whenever a request is made the class will try to find, among the opened connections, the first non-busy connection and use it.

A connection is still busy as long as the cursor has not been consumed.

Here is an example of such class:

class Database:
    ...
    def get_cursos(self,query):
        selected_connection = None

        # Find usable connection
        for con in self.connections:
            if con.is_busy() == False: # <--- This is not PEP 249
                selected_connection = con
                break

        # If all connections are busy, create a new one
        if (selected_connection is None):
            selected_connection = self._new_connection()
            self.connections.append(selected_connection)


         # Return cursor on query
         cur = selected_connection.cursor()
         cur.execute(query)
         return cur

However looking at the PEP 249 standard I cannot find any way to check whether a connection is actually being used or not.

Some implementations such as MySQL Connector offer ways to check whether a connection has still unread content (see here), however as far as I know those are not part of PEP 249.

Is there a way I can achieve what described before for any PEP 249 compliant python database API ?

score 1 · Accepted Answer · 2019-03-19 18:10:22Z

1

Perhaps you could use the status of the cursor to tell you if a cursor is being used. Let's say you had the following cursor:

new_cursor = new_connection.cursor()
cursor.execute(new_query)

and you wanted to see if that connection was available for another cursor to use. You might be able to do something like:

if (new_cursor.rowcount == -1):
    another_new_cursor = new_connection.cursor()
    ...

Of course, all this really tells you is that the cursor hasn't executed anything yet since the last time it was closed. It could point to a cursor that is done (and therefore a connection that has been closed) or it could point to a cursor that has just been created or attached to a connection. Another option is to use a try/catch loop, something along the lines of:

try:
    another_new_cursor = new_connection.cursor()
except ConnectionError?: //not actually sure which error would go here but you get the idea.
    print("this connection is busy.")

Of course, you probably don't want to be spammed with printed messages but you can do whatever you want in that except block, sleep for 5 seconds, wait for some other variable to be passed, wait for user input, etc. If you are restricted to PEP 249, you are going to have to do a lot of things from scratch. Is there a reason you can't use external libraries?

EDIT: If you are willing to move outside of PEP 249, here is something that might work, but it may not be suitable for your purposes. If you make use of the mysql python library, you can take advantage of the is_connected method.

new_connection = mysql.connector.connect(host='myhost',
                         database='myDB',
                         user='me',
                         password='myPassword')

...stuff happens...

if (new_connection.is_connected()):
    pass
else:
    another_new_cursor = new_connection.cursor()
    ...

edited Mar 19, 2019 at 18:10

answered Mar 19, 2019 at 16:16

user10597469

Sign up to request clarification or add additional context in comments.

4 Comments

ttk203 Over a year ago

Thanks for your answer. With your first solution is that according to the standard rowcount will be -1 also if the rowcount of the last operation cannot be determined by the interface. So it may be a problem with cursors (furthermore I tried with PyMSQL to retrieve rowcount while iterating a cursor and I get always -1, even before the cursor has been consumed). For the second solution unfortunately there is no standard exception to catch, PyMySQL doesn't even raise one AFAIK. I want to stick to PEP 249 so that I will be able to change DB easily.

user10597469 Over a year ago

Hmm. Yeah, the only thing I know of that I can see working in this case involves using the mysql.connector module and is pretty similar to what you already have: if(new_connection.is_connected()): I'll go ahead and stick it in the answer in case its useful to someone else but I don't know if that suits your purposes. I just wrap my code to close the connection to prevent errors in it but it should work for this too.

user10597469 Over a year ago

The problem with that is that...well the mysql library is pretty clearly just for mysql...so no changing databases without also changing the method. Is this for a web-based project? Django does some voodoo with it's models to make them database agnostic and I'm sure there are other things out there with the same goal in mind but you may have to do some digging to find them.

ttk203 Over a year ago

At the end I decided to follow your advice and use some higher-level API. I am using SQLAlchemy.

Collectives™ on Stack Overflow

Check if a database connection is busy using python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related