Working with Cursors

[Ian Kelly]

On Tue, Apr 17, 2012 at 12:11 PM, timlash <timlash at gmail.com> wrote:
> Searched the web and this forum without satisfaction.  Using Python 2.7 and pyODBC on Windows XP I can get the code below to run and generate two cursors from two different databases without problems.  Ideally, I'd then like to join these result cursors thusly:
>
> SELECT a.state, sum(b.Sales) FROM cust_curs a INNER JOIN fin_curs b ON a.Cust_id = b.Cust_id GROUP BY a.state
>
> Is there a way to join cursors using SQL statements in python or pyODBC?

Not using SQL.  Oracle has the ability to do cross-database queries,
but only to other Oracle databases.  If you want to join that data,
you'll need to do it in Python.


>  Would I need to store these cursors in a common DB (SQLite3?) to accomplish this?

That's an option, although it seems like overkill to me.

>  Is there a pure python data handling approach that would generate this summary from these two cursors?

Create a dict keyed on the cust_id.  Something like this should do
what you want:

dict1 = {row[0]: row for row in cursor1}
dict2 = {row[0]: row for row in cursor2}
join = {}
for key in dict1.viewkeys() & dict2.viewkeys():
    join[key] = (dict1[key], dict2[key])

Cheers,
Ian