I'm using the cx_Oracle module to create an sqlalchemy engine and retrieve data from an Oracle database using Python and Pandas, but I have an issue with performance, and I'm getting database errors.
My code works OK (but slow) if I specify just a couple of columns, and after a long time I can get all the 3.5 million rows. Here's the code I'm using:
import cx_Oracle
import pandas as pd
import config
from sqlalchemy import create_engine
engine = create_engine('oracle+cx_oracle://config.user:config.password@config.host:1521/?service_name=config.service')
sql = "select ITEM_NO, COUNTRY_CODE, LANGUAGE_CODE from table_ben_l_t where (LANGUAGE_CODE = 'es' and COUNTRY_CODE = 'ES')"
df = pd.read_sql(sql, engine)
As soon as I add more columns, I can't run this anymore as I'm getting a database error after a long, long delay (I'm talking hours).
I know that using this method to retrieve the data and create Pandas is not the only one available, however it is definitely the most convenient... Is there a better/safer way to get this data from the Oracle DB? I was thinking about downloading the rows chunk by chunk, dumping them into a list of dict that could be passed to Pandas, but this doesn't seem very "elegant"... I'm sure there must be a better way to do this... :-)
Thanks in advance to anyone who can help me! :-)
JF
EDIT 1: In response to @OldProgrammer and @crocarneiro:
There are no indexes to the columns according to the result of the query select * from all_ind_columns where table_name = 'TABLE_BEN_L_T';
EDIT 2: This is the error message that I received:
DatabaseError: (cx_Oracle.DatabaseError) ORA-01555: snapshot too old: rollback segment number 509 with name "_SYSSMU509_3146905099$" too small
(Background on this error at: http://sqlalche.me/e/14/4xp6)
Thanks a lot too @Christopher Jones! This looks very interesting!