CX_Oracle - import data from Oracle to Pandas dataframe

Question

Hy,

I'm new in python and I want import some data from a Oracle Database to python (pandas dataframe) using this simple query

SELECT* 
                FROM TRANSACTION
                WHERE DIA_DAT >=to_date('15.02.28 00:00:00',  'YY.MM.DD HH24:MI:SS')
                AND (locations <> 'PUERTO RICO'
                OR locations <> 'JAPAN')
                AND CITY='LONDON'

What I did

import cx_Oracle
ip = 'XX.XX.X.XXX'
port = YYYY
SID = 'DW'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)

connection = cx_Oracle.connect('BA', 'PASSWORD', dsn_tns)

df_ora = pd.read_sql('SELECT* FROM TRANSACTION WHERE DIA_DAT>=to_date('15.02.28 00:00:00',  'YY.MM.DD HH24:MI:SS') AND (locations <> 'PUERTO RICO' OR locations <> 'JAPAN') AND CITY='LONDON'', con=connection)  

But I have this error

SyntaxError: invalid syntax

What did I do wrong?

Thanks

Waldo

#32869217 – Dyad

Answer 1

You need to properly quote your SQL Query. If you look at the syntax highlighting in your question (or an IDE), you'll notice that the single quotes aren't working as you expect.

Change the outer most quotes to double quotes - if you want it all on one line - or triple quotes if you want it across multiple lines:

query = """SELECT* 
           FROM TRANSACTION
           WHERE DIA_DAT >=to_date('15.02.28 00:00:00',  'YY.MM.DD HH24:MI:SS')
           AND (locations <> 'PUERTO RICO'
           OR locations <> 'JAPAN')
           AND CITY='LONDON'"""
df_ora = pd.read_sql(query, con=connection)

Whereby

How do you do bind variables with this? – Logistician

@Logistician - see #32869217 – Dyad