Using pyodbc with SQL join statement in Python

Question

I am trying to join 2 tables in Python. (Using Windows, jupyter notebook.)

Table 1 is an excel file read in using pandas.

TABLE_1= pd.read_excel('my_file.xlsx')

Table 2 is a large table in oracle database that I can connect to using pyodbc. I can read in the entire table successfully using pyodbc like this, but it takes a very long time to run.

sql = "SELECT * FROM ORACLE.table_2"
cnxn = odbc.connect(##########)
TABLE_2 = pd.read_sql(sql, cnxn)

So I would like to do an inner join as part of the pyodbc import, so that it runs faster and I only pull in the needed records. Table 1 and Table 2 share the same unique identifier/primary key.

sql = "SELECT * FROM ORACLE.TABLE_1 INNER JOIN TABLE_2 ON ORACLE.TABLE1.ID=TABLE_2.ID"
cnxn = odbc.connect(##########)
TABLE_1_2_JOINED = pd.read_sql(sql, cnxn)

But this doesn't work. I get this error:

DatabaseError: Execution failed on sql 'SELECT * FROM ORACLE.TABLE_1
INNER JOIN TABLE_2 ON ORACLE.TABLE1.ID=TABLE_2.ID': ('42S02', '[42S02]
[Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n (942)
(SQLExecDirectW)')

Is there another way I can do this? It seems very inefficient to have to import entire table w/millions of records when I only need to join a few hundred. Thank you.

No, TABLE_1 only exists in excel and I have imported it using pandas pd.read_excel() method. — user2895991
– user2895991, Commented Jun 13, 2017 at 0:18

cardamom · Accepted Answer · 2017-06-12 22:24:32Z

2

Something like this might work. First do:

MyIds = set(table_1['id'])

Then:

SQL1 = "CREATE TEMPORARY TABLE MyIds ( ID int );"

Now insert your ids:

SQL2 = "INSERT INTO MyIds.ID %d VALUES %s"
for element in list(MyIds):
    cursor.execute(SQL2, element)

And lastly

SQL3 = "SELECT * FROM ORACLE.TABLE_1 WHERE ORACLE.TABLE1.ID IN (SELECT ID FROM MyIds)"

I have used MySQL not oracle and a different connector to you but the principles are probably the same. Of course there's a bit more code with the python-sql connections etc. Hope it works, otherwise try to make a regular table rather than a temporary one.

answered Jun 12, 2017 at 22:24

cardamom

7,51112 gold badges62 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using pyodbc with SQL join statement in Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related