Working with Cursors

[timlash]

Searched the web and this forum without satisfaction. Using Python 2.7 andpyODBC 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""")

 

[nn]

Searched the web and this forum without satisfaction.  Using Python 2.7and 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 thenlike 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.

 

[nn]

Searched the web and this forum without satisfaction.  Using Python 2.7and 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 thenlike 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.