Opening up multiple database connections per request?

[jeff do]

Is it possible to open up multiple database connections inside of one request in Django? I am using Django 1.6 and Python3.3. 

In my use case, I have a web application that executes raw SQL against a data warehouse we have. For example, one page may require five different SQL queries to be executed to pull down all the datasets necessary to display as charts, tables, etc.

Using only one database connection and executing the SQL queries synchronously is too slow, so I am trying to use threads (specifically the concurrent.futures package in stdlib) to run my queries asynchronously. In particular, I have code that looks like:

 

from django.db import connection

from concurrent.futures import ThreadPoolExecutor, as_completed 

def execute_query(sql):

   cursor = connection.cursor()

   …

   rows = cursor.fetchall() 

    return rows

def execute_queries_asynchronously(list_of_sql_queries):

    datasets = []

    with ThreadPoolExecutor(max_workers=3) as executor:

        futures = [executor.submit(execute_query, query) for query in list_of_sql_queries]

        for future in as_completed(futures):

            datasets.append(future.result())

    return datasets

 

When I run the above code in shell, total execution time improves noticeably which is expected (about 2x-3x faster). However, when I put this code in my web application, I get an almost negligible performance gain, if any. It doesn’t seem like Django is opening up more DB connections. I’d like Django to open up a new connection or pull from the connection pool for every worker thread. Does the Django connection handler limit the number of available DB connections to a request to only one?

[Russell Keith-Magee]

Hi Jeff,

Correct - Django has one database connection per request. 

If you want to open multiple connections, you're going to look into Django's support for multiple databases. You're using django.db.connection; this is really just shorthand for django.db.connections['default']. If you define an 'other' database in your settings file, you can connect to django.db.connections['other'] as well, using a separate connection.

Although this was developed with the intention of accessing multiple *different* database sources (e.g., a database master and a slave, or a sharded database), there's no reason you can't have multiple connections to the same database (in fact, this can be a handy way to test logic around master/slave configurations without needing to set up a master/slave database configuration).

The simple approach to this requires pre-declaration of the number of connections you want to have; if you're particularly ingenious, you might be able to mess with the internals of the ConnectionManager object to dynamically create extra connections at runtime based upon the definition of your base database. However, this is undocumented API, so you're on your own if you take this path.

Yours,

Russ Magee %-)

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/cb7ebe95-1565-4f4b-84cd-e25a36a7497a%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[jeff do]

Thank you Russ. Your solution to define multiple database sources to a single database was very straightforward