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!!

Answer 1

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 

• user2100039: thanks. i'm getting a "type error" on the line sql = query. It looks like "expecting string or bytes object".
• Gord Thompson: 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).

Answer 2

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.

• user2100039: 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: For quoting, look at examples like github.com/oracle/python-cx_Oracle/blob/master/samples/…