Python script connecting to oracle DB with env variables

Question

I'm practicing with Python, environment variables and oracle database.

I want to create a python script who's :

• connecting to an oracle db, credentials not showing up (located in an env variable)
• execute a query on the database
• transform this query result into an excel file (xlsx)
• create the excel file on a specific harddrive

Here's my current script :

import os
from datetime import datetime
import pandas as pd
import cx_Oracle

conn = cx_Oracle.connect( -> ***should I insert here the env variable name file ?***)

df_ora = pd.read_sql('''select * from example_db''', conn)

df_ora = pd.DataFrame(sql_query)

df_ora.to_csv(os.environ["user"] + "\\Desktop" + "file_name" +".csv")

To be honest i'm lost with cx_Oracle and pandas, can't figure what to set for this kind of script. Tried to learn on multiple videos, libraries informations and from here but everytime I find a documentation it seems not accurate for my needs. A bit of help and explaination would help me grow on about scripting for sure. Best regards,

Answer 1

You're doing pretty good so far, although some parts aren't clear from your question.

I'm assuming that you're setting environment variables like this? (on windows using cmd.exe)

set username=romsnp
set password=SecretPassword123!

Then you call the python script:

import os
from datetime import datetime
import pandas as pd
import cx_Oracle

username = os.environ.get('username')
password = os.environ.get('password')

conn = cx_Oracle.connect(user=username, password=password, dsn="yourserver.com/ServiceName")

df_ora = pd.read_sql('''select * from example_db''', conn)

# the previous line already creates a dataframe, so you don't need to do it again

df_ora.to_csv("file_name.csv")

For testing, I would try starting out just exporting the file to the same directory you're running the script in. Depending on your OS, figuring out the correct filesystem path can be a confusing issue.

Another thing that might help - when trying to figure out Python scripting issues, it can help a lot to run it interactively. The easiest way is to use the python interpreter by just calling python from the command line, then enter your script one line at a time to make sure each command works as expected.

For example, you could try running pd.read_sql('''select * from example_db''', conn) by itself to make sure your query and connection is working. Or you could run print(os.environ["user"] + "\\Desktop" + "file_name" +".csv") to verify what your filepath will look like before trying to write to it.

Using interactive notebooks like Jupyter is also a popular way to do this kind of experimentation without the frustration of trying to debug a shell script.

Edit: if the environment variables are confusing or not working, another common way to hide your credentials is using dotenv

Create a file in the same directory as the script named .env:

username=romsnp
password=SecretPassword123!
host=yourserver.com/ServiceName

Then you can load it into your Python environment:

import os
from datetime import datetime
import pandas as pd
import cx_Oracle
from dotenv import load_dotenv

# loads the credentials from the file into environment
load_dotenv() 

# loads the credentials from environment into variables
username = os.environ.get('username')
password = os.environ.get('password')
host = os.environ.get('host')

# use the variable names; the secrets from the .env file are never displayed
conn = cx_Oracle.connect(user=username, password=password, dsn=host)

...continued...