Question on Multiple Database Connections

[Matthew Woodward]

I'm working on an application that uses two databases -- one is the main database that Django ORM talks to, where sessions are stored, etc. and the other is an external database where we need to run some storedprocs to retrieve some data.

This is all working fine for the most part but I ran into a situation today that's causing some odd behavior so I figured I'd seek clarification. (This is SQL Server using django-pyodbc in case that matters.)

I have the functions that talk to the "other" database in a Python class, so their methods are more or less like so:
def get_stuff(self, foo):
    cursor = connections['otherConnection'].cursor()
    stuff = cursor.execute('exec myStoredproc').fetchall()
    return stuff

In and of themselves, the methods in this class work fine. Also note these functions only retrieve data; there's no writing going on.

Now in the context of a view function I'm handling a posted form and this interacts with the "primary" database connection (i.e. NOT the 'otherConnection' referenced above), and I'm using the @transaction.commit_on_success decorator.

Inside that function I instantiate the class containing the methods like the one above to retrieve data, and when I call that method it seems to blow away my session because I'm logged out of the application and taken back to my login page. If I change that method to NOT communicate with the other database and instead just return dummy data I don't get kicked out of the application.

Any ideas what's going on here? If the answer is that I can't do things this way that's fine, but if that is the case what's the "right" way to handle this situation?

Hopefully I explained that well but I'm happy to clarify as needed.

Thanks!

--
Matthew Woodward
ma...@mattwoodward.com
http://blog.mattwoodward.com
identi.ca / Twitter: @mpwoodward

Please do not send me proprietary file formats such as Word, PowerPoint, etc. as attachments.
http://www.gnu.org/philosophy/no-word-attachments.html

[Bill Freeman]

On Wed, Dec 12, 2012 at 7:49 PM, Matthew Woodward <ma...@mattwoodward.com> wrote:

I'm working on an application that uses two databases -- one is the main database that Django ORM talks to, where sessions are stored, etc. and the other is an external database where we need to run some storedprocs to retrieve some data.

This is all working fine for the most part but I ran into a situation today that's causing some odd behavior so I figured I'd seek clarification. (This is SQL Server using django-pyodbc in case that matters.)

I have the functions that talk to the "other" database in a Python class, so their methods are more or less like so:
def get_stuff(self, foo):
    cursor = connections['otherConnection'].cursor()
    stuff = cursor.execute('exec myStoredproc').fetchall()
    return stuff

In and of themselves, the methods in this class work fine. Also note these functions only retrieve data; there's no writing going on.

Now in the context of a view function I'm handling a posted form and this interacts with the "primary" database connection (i.e. NOT the 'otherConnection' referenced above), and I'm using the @transaction.commit_on_success decorator.

Inside that function I instantiate the class containing the methods like the one above to retrieve data, and when I call that method it seems to blow away my session because I'm logged out of the application and taken back to my login page. If I change that method to NOT communicate with the other database and instead just return dummy data I don't get kicked out of the application.

Any ideas what's going on here? If the answer is that I can't do things this way that's fine, but if that is the case what's the "right" way to handle this situation?

Hopefully I explained that well but I'm happy to clarify as needed.

Thanks!

Just a shot in the dark: Maybe if, since you're not using the ORM on the 'other' database, you shouldn't use django-pyodbc, but rather just pyobdc.  My thought is that the django version may try to bind up both connections in the transaction control stuff, and/or wants to do the cursor management stuff itself.  You don't need transaction control on the database that you only read, I presume.  Errors on that would still occur within a request and roll back the ORM transaction, so all should be good.  As I say, a seat of the pants guess from someone who has never done two databases, and has never used pyodbc.

Bill

Bill

[Matt Woodward]

On Thursday, December 13, 2012 8:01:13 AM UTC-8, ke1g wrote:

Just a shot in the dark: Maybe if, since you're not using the ORM on the 'other' database, you shouldn't use django-pyodbc, but rather just pyobdc.

I should have said this earlier but I had the same thought and tried doing it directly with pyodbc, opening and closing the connection myself, and oddly enough the behavior is the same.

Thanks for the idea though! I'll keep digging and report back if I figure anything out. I have some alternative ways to handle this situation anyway but I'm curious to get to the bottom of the behavior for my own knowledge if nothing else.