Pandas Dataframe displays results inside brackets with commas

Question

I am reading data into dataframe using pd.read_sql(). The database is Oracle and the Oracle connectivity module is JayDeBeApi.

Issue: The connection is successful and I am able to retrieve data as well. But when I print the dataframe, it looks different like below

df[['APPL_ID','ACTV_IND','LST_UPDT_TS']]

(A, P, P, L, _, I, D)   (A, C, T, V, _, I, N, D)    (L, S, T, _, U, P, D, T, _, T, S)
0   101 (Y) 2012-05-29 11:04:51.110840
1   102 (Y) 2013-10-04 18:42:39.143024

Answer 1

I assume that you are using jpype with jaydebeapi, since I have seen this exact behavior in such a setting before (see also this SO answer and the comments).

You can either deal with the symptoms (see other answer) or prevent the problem from occurring in the first place, by changing your connection, e.g. in one of the following two ways:

1. Use jaydebeapi without jpype:

   When this problem happened to me, I noticed that the use of jpype was both the cause and not necessary after all. I had the driver as a .jar file locally and my connection worked fine without jpype:

   import jaydebeapi as jdba
   import pandas as pd
   import os
   
   db_host = 'db.host.com'
   db_port = 1521
   db_sid = 'YOURSID'
   
   jar=os.getcwd()+'/ojdbc6.jar'
   
   conn = jdba.connect('oracle.jdbc.driver.OracleDriver', 
                   'jdbc:oracle:thin:@' + db_host + ':' + str(db_port) + ':' + db_sid, 
                   {'user': 'USERNAME', 'password': 'PASSWORD'}, 
                   jar
                   )
   
   df_jay = pd.read_sql('SELECT * FROM YOURSID.table1', conn)
   
   conn.close()
   

   This created the dataframe correctly.

2. Use cx_Oracle instead:

   The issue also does not occur if I use cx_Oracle to connect to the Oracle db:

   import cx_Oracle
   import pandas as pd
   import os
   
   db_host = 'db.host.com'
   db_port = 1521
   db_sid = 'YOURSID'
   
   dsn_tns = cx_Oracle.makedsn(db_host, db_port, db_sid)
   cx_conn = cx_Oracle.connect('USERNAME', 'PASSWORD', dsn_tns)
   
   df_cxo = pd.read_sql('SELECT * FROM YOURSID.table1', con=cx_conn)
   
   cx_conn.close()
   

   Note: For cx_Oracle to work you have to have the Oracle Instant Client installed and properly set up (see e.g. cx_Oracle documentation for Ubuntu).