Export data from Oracle to MongoDB in Python
Introduction
I had to export some data from an Oracle database to a MongoDB. For this reason I created a python function called export_data_from_oracle_to_mongodb that can be found in my Github.
To make the function more generic, I've there's an optional parameter called transform,where a function can be specified to make some transformations (like combining columns, casts, etc...) without having to modify the function.
Tested with:
- pymongo version: 3.7.0
- cx_Oracle version: 6.0.2
Useful URLs:
- PyMongo: Fast tutorial how pyMongo works.
- cx_Oracle: cx_Oracle is a module that enables access to Oracle Database and conforms to the Python database API specification.
- cx_Oracle querying best practices: Oracle's best practices how to use cx_Oracle.
Linux environment variables
Before running the script, the following environtment variables must be set (to the correct folder, here it's just an example). If it's executed in Jupyter Notebook, run the exports before the jupyter notebook command:
"""
export PATH=$PATH:$HOME/bin:/usr/lib/oracle/12.1/client64/bin
export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin
"""
Define a function to make a transformation
An example of a transformation function, could be a function to create a coord field, joining longitude and latitude, to use in geospatial queries:
def add_coord(mongo_rows: List[Dict]) -> List[Dict]:
for row in mongo_rows:
row["coord"] = [row["long"], row["lat"]]
return mongo_rows
Execute the exportation
# Configuration parameters
ip = 'oracle_ip'
port = PORT_NUMBER
SID = 'ORACLE_SID'
ora_user = 'username'
ora_password = 'password'
my_mongodb = 'mongodb://localhost:27017/'
my_mongodb_db = 'mydb'
my_mongodb_col = 'my_collection'
# The SQL query MUST NOT have semi-colon (";") at the end!
consulta_sql = u"""SELECT * FROM DUAL"""
export_data_from_oracle_to_mongodb(oracle_server=ip,
oracle_port=port,
oracle_sid=SID,
oracle_user=ora_user,
oracle_password=ora_password,
mongodb_connection_string=my_mongodb,
mongodb_database=my_mongodb_db,
mongodb_collection=my_mongodb_col,
create_mongodb_collection_if_not_exist=True,
sql_query=consulta_sql,
transform=add_coord)
Here is the code of the function.
