CODEWITHSUNDEEP
pythonpyodbc
timlash
timlash

Reputation: 89

Working with Cursors in Python

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""")

Upvotes: 2

Views: 3408

Answers (1)

stoebelj
stoebelj

Reputation: 1598

Cursors are simply objects used for executing SQL commands and retrieving the results. The data aren't migrated in a new database and thus joins aren't possible. If you would like to join the data you'll need to have the two tables in the same database. Whether that means brining both tables and their data into a SQLite database or doing it some other way depends on the specifics of your use case, but that would theoretically work.

Upvotes: 2

Related Questions