Working with Cursors

[nn]

On Apr 17, 2:11 pm, timlash <timl... 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?  Would I need to store these cursors in a common DB (SQLite3?) to accomplish this?  Is there a pure python data handling approach that would generate this summary from these two cursors?
>
> Thanks for your consideration.
>
> Working code:
>
> import pyodbc
>
> #
> # DB2 Financial Data Cursor
> #
> cnxn = pyodbc.connect('DSN=DB2_Fin;UID=;PWD=')
> fin_curs = cnxn.cursor()
>
> fin_curs.execute("""SELECT Cust_id, sum(Sales) as Sales
>                         FROM Finance.Sales_Tbl
>                         GROUP BY Cust_id""")
>
> #
> # Oracle Customer Data Cursor
> #
> cnxn = pyodbc.connect('DSN=Ora_Cust;UID=;PWD=')
> cust_curs = cnxn.cursor()
>
> cust_curs.execute("""SELECT Distinct Cust_id, gender, address, state
>                         FROM Customers.Cust_Data""")

If any of the two cursors fits in memory you could store it in a dict
and then look the extra data up as you traverse the second one. E.g.

>sales = {}
>for fin_curs_row in fin_curs:
>    fin_cust_id,sales = fin_curs_row
>    sales[fin_cust_id] = sales
>for cust_curs_row in cust_curs:
>    cust_cust_id = cust_curs_row[0]
>    print cust_curs_row, sales[cust_cust_id]

If you can't make it fit in memory, one way would be to order both
cursors by customer_id and pair them up as they come along:

>fin_curs_row = fin_curs.next()
>cust_curs_row = cust_curs.next()
>While True:
>    fin_cust_id,sales = fin_curs_row
>    cust_cust_id = cust_curs_row[0]
>    try:
>        if fin_cust_id == cust_cust_id:
>            print cust_curs_row, sales
>            fin_curs_row = fin_curs.next()
>        elif fin_cust_id > cust_cust_id:
>            cust_curs_row = cust_curs.next()
>        else:
>            fin_curs_row = fin_curs.next()
>    except StopIteration:
>        break


In the end if speed is not an issue, just loading everthing in SQLite
and doing the join there makes it so simple that anybody should be
able to maintain the code, so that is also a good choice.