0

I have a MySQLdb installation for Python 2.7.6. I have created a MySQLdb cursor once and would like to reuse the cursor for every incoming request. If 100 users are simultaneously active and doing a db query, does the cursor serve each request one by one and block others?

If that is the case, is there way to avoid that? Will having a connection pool will do the job in a threadsafe manner or should I look at Gevent/monkey patching?

Your responses are welcome.

Jyotiska
  • 255
  • 3
  • 15
  • Do you mean `cursor` or `connection`? Cursor is when you have a reference to a result set created by a query. Iterating over the cursor would iterate over the result set. – Martin Konecny Mar 22 '15 at 15:25
  • I mean the connection. Reusing the connection to accept different queries with each request. – Jyotiska Mar 22 '15 at 17:24

2 Answers2

1

You will want to use a connection pool.

The mysql driver in python is not thread-safe meaning multiple requests/threads cannot use it at the same time. See more here:

Here is a link on how to implement a connection-pool:

It essentially works by keeping a number of connections (a pool) ready, and gives one out to each thread. When the thread is done, it returns the connection to the pool and another request/thread can use it.

Community
  • 1
  • 1
Martin Konecny
  • 57,827
  • 19
  • 139
  • 159
  • Thanks for the suggestion. Do you recommend using something like SQLAlchemy for this, like this is explained [here](http://docs.sqlalchemy.org/en/latest/core/pooling.html#pooling-plain-db-api-connections)? – Jyotiska Mar 22 '15 at 18:38
  • Yes SQLAlchemy will handle this automatically for you, if you wish to go the ORM route. – Martin Konecny Mar 22 '15 at 19:11
1

For this purpose you can use Persistence Connection or Connection Pool.

Persistence Connection - very very very bad idea. Don't use use it! Just don't! Especially when you are talking about web programming.

Connection Pool - Better then Persistence Connection, but with no deep understanding of how it works, you will end with the same problems of Persistence Connection.

Don't do optimization unless you really have performance problems. In web, its common to open/close connection per page request. It works really fast. You better think about optimizing sql queries, indexes, caches.

Alexander R.
  • 1,756
  • 12
  • 19