Converting Excel Oracle DB Query to Python Pandas

Question

I have a simple program that makes a connection with an Oracle DB in Python. I need some hints as to how to write the SQL query within Python. I have an Excel query that works fine but I need this in Python and I think i have what i need within the Excel query connection properties. My python program looks like this-

import pandas as pd
import cx_Oracle
from sys import exit

conn= cx_Oracle.connect('DOMINA_CO/S#[email protected]:1540/domp_domi_bi')
try:
    query = '''
     SELECT * from dual
         '''
    df = pd.read_sql(con = conn, sql = query)
    finally:
conn.close()
df.head()

exit()

and returns -

Out[2]: 
DUMMY
0     X

From Excel, i have the connection properties of the working query that has the "Command text:" that looks like this:

SELECT TO_CHAR (DGE_DATOS_INSTALACIONES.FEC_LOCAL - (1/24), 'YYYY-MM') MONTH,
       REPLACE(DGE_NEGOCIOS.NOM_NEGOCIO, 'ESPAÑA', 'SPAIN') BUSINESS,
       REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(DGE_PAISES.NOM_PAIS, 'ESPAÑA', 'SPAIN'), 'ALEMANIA', 'GERMANY'), 'BRASIL', 'BRAZIL'), 'CHIPRE', 'CYPRUS'), 'FRANCIA', 'FRANCE'), 'GRECIA', 'GREECE'), 'HUNGRIA', 'HUNGARY'), 'ITALIA', 'ITALY'), 'POLONIA', 'POLAND'), 'RUMANIA', 'ROMANIA'), 'ESCOCIA', 'SCOTLAND'), 'GALES', 'WALES'), 'INGLATERRA', 'ENGLAND'), 'IRLANDA DEL NORTE', 'NORTHERN IRELAND'), 'IRLANDA', 'IRELAND'), 'ESTADOS UNIDOS', 'UNITED STATES') COUNTRY,
       DGE_REGIONES.NOM_REGION REGION,
       DGE_INSTALACIONES.NOM_INSTALACION PARK,
       ROUND(SUM(DGE_DATOS_INSTALACIONES.CAN_PRODUCIBLE_SUMA*DGE_DATOS_INSTALACIONES.COEF_PERDIDAS_MEDIA_TENSION*DGE_DATOS_INSTALACIONES.COEF_PERDIDAS_PF)/1000) "POTENTIAL_GEN_(MWH)"

FROM DOMINAGE.DGE_DATOS_INSTALACIONES DGE_DATOS_INSTALACIONES,
     DOMINAGE.DGE_NEGOCIOS DGE_NEGOCIOS,
     DOMINAGE.DGE_PAISES DGE_PAISES,
     DOMINAGE.DGE_REGIONES DGE_REGIONES,
     DOMINAGE.DGE_INSTALACIONES DGE_INSTALACIONES

WHERE DGE_DATOS_INSTALACIONES.FEC_LOCAL >= TO_DATE('01-JAN-13 01:00', 'DD-MON-YY HH24:MI')
      AND DGE_DATOS_INSTALACIONES.COD_INSTALACION = DGE_INSTALACIONES.COD_INSTALACION
      AND DGE_INSTALACIONES.COD_NEGOCIO = DGE_NEGOCIOS.COD_NEGOCIO
      AND DGE_INSTALACIONES.COD_PAIS = DGE_PAISES.COD_PAIS
      AND DGE_INSTALACIONES.COD_REGION = DGE_REGIONES.COD_REGION

GROUP BY TO_CHAR (DGE_DATOS_INSTALACIONES.FEC_LOCAL - (1/24), 'YYYY-MM'),
         DGE_NEGOCIOS.NOM_NEGOCIO,
         DGE_PAISES.NOM_PAIS,
         DGE_REGIONES.NOM_REGION,
         DGE_INSTALACIONES.NOM_INSTALACION

ORDER BY MONTH,
         REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BUSINESS, 'UNITED STATES', '1'), 'SPAIN', '2'), 'UNITED KINGDOM', '3'), 'INTERNATIONAL', '4'), 'OFFSHORE', '5'),
         COUNTRY,
         PARK

Any help with converting this query to python query is much appreciated since I'm very new at writing db queries in python!!

Parfait · Accepted Answer · 2020-12-18 22:11:06Z

2

No translation is needed. Simply re-run the same query. Python and Excel VBA serve as client applications that connect to the same backend database, here being Oracle. Databases are designed to connect to any client.

In fact, consider saving your long SQL query in a text .sql file and have all clients simply read the script file and avoid long strings with quote enclosures or line breaks in either code.

Python

conn= cx_Oracle.connect('...')

# READ SQL QUERY FROM FILE
with open(r'C:\path\to\my\query.sql') as f:
    query = f.read().strip()

# IMPORT INTO PANDAS DATA FRAME
try:
    df = pd.read_sql(con = conn, 
                     sql = query)     # QUERY READ FROM .sql FILE
finally:
    conn.close()

df.head()

Excel VBA

' READ SQL QUERY FROM FILE
With CreateObject("Scripting.FileSystemObject")
      sql = .OpenTextFile("C:\Path\To\my\query.sql", 1).readall
End With

' OPEN DB CONNECTION
Set conn = New ADODB.Connection
conn.Open "..."
   
' DEFINE COMMAND OBJECT
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = conn
    .CommandType = adCmdText
    .CommandText = sql               ' QUERY READ FROM .sql FILE
    
    Set rst = cmd.Execute
End With

edited Dec 18, 2020 at 22:11

answered Dec 18, 2020 at 20:43

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

2 Comments

user2100039 Over a year ago

thanks. i'm getting a "type error" on the line sql = query. It looks like "expecting string or bytes object".

Gord Thompson Over a year ago

f.readlines() returns a list with an element for each line. f.read() returns the whole file as a single str (with embedded newline characters).

Christopher Jones · Accepted Answer · 2020-12-18 20:21:34Z

1

The query will be the same. In both cases it is sent to the DB to execute and return the data. Because the text contains single quotes, you will want to wrap it with triple double-quotes (") instead of the triple single-quotes that you have around the SELECT * FROM DUAL.

answered Dec 18, 2020 at 20:21

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

2 Comments

user2100039 Over a year ago

Ok, do i need to remove the SELECT * FROM DUAL part, insert the full query above and then wrap it with 6 quotes like (' ' ' ' ' ')?

Christopher Jones Over a year ago

For quoting, look at examples like github.com/oracle/python-cx_Oracle/blob/master/samples/…

Collectives™ on Stack Overflow

Converting Excel Oracle DB Query to Python Pandas

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related