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 ?
Python Knowledge Base: Make coding great again.
- Updated:
2024-11-20 by Andrey BRATUS, Senior Data Analyst.
Simple example - getting database sysdate.
Next example - saving select results to file with cycle.
There is a way to do it using cx_Oracle Python library.
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)
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)