Python & Oracle.
When it comes to work with datasets in Python e. g. via Jupyter Notebook the most common way is to import data from .csv or .xls file. But what if we need to work with big datasets and wont to import them directly from existing Oracle database using SQL ?
There is a way to do it using cx_Oracle Python library.
Simple example - getting database sysdate.
import cx_Oracle
dsn = cx_Oracle.makedsn(‘HOST_address', '1521', service_name='databasename')
conn = cx_Oracle.connect(user= login', password= password', dsn=dsn)
cursor = conn.cursor()
cursor.execute("""select sysdate from dual""")
data_from_query =cursor.fetchall()
conn.close()
print(data_from_query)
Next example - simple table select.
dsn = cx_Oracle.makedsn(‘HOST_address', '1521', service_name='databasename')
conn = cx_Oracle.connect(user= login', password= password', dsn=dsn)
cursor = conn.cursor()
cursor.execute(f"""
select distinct date, item_id
from tablename t
where 1=1
and t.date = date'2021-01-01'
and t.item_id = 1
""")
data_from_query = cursor.fetchall()
conn.close()
print(data_from_query)
Next example - saving select results to file with cycle.
dsn = cx_Oracle.makedsn(‘HOST_address', '1521', service_name='databasename')
conn = cx_Oracle.connect(user= login', password= password', dsn=dsn)
cursor = conn.cursor()
cursor.execute("""
select *
from tablename
""")
data_from_query = cursor.fetchall()
conn.close()
with open(r"C:\docs\download.csv", 'w') as my_file:
print("date;item_id", file=my_file)
for row in data_from_query:
for column in row:
print(column, end=";", file=my_file)
print("", file=my_file)