How to dump certain fields of a table from Oracle 11G DB to a file with a script written in Python 3.4?

Question

Oracle

Good afternoon!
I'm making a Python 3.4 script that generates a daily report from CI Jenkins, which in turn connects to an Oracle 11G database. I can’t figure out the cx_Oracle library and the manual for it (I decided to use it).
It is necessary to unload certain fields of a certain table (for example, the USERS table and the USER_NAME and USER_DEPT fields).

#Подключаюсь так:
connection = cx_Oracle.connect('scott/[email protected]')
#Все подключается нормально, версия выводится...
connection.version

And here with selection of tables a trouble... Help please?

Answer 1

I would solve this problem like this:
1. Pandas + sqllite, if the sample size is < 30-50 thousand records and < 10-12 columns:

import pandas as pd
from sqlalchemy import create_engine
oracledb = create_engine('oracle://user:[email protected]') #name - название подключения в TNS файле
query = """
select  /*+parallel(4)*/ 2 from dual
""" 
data = pd.read_sql_query(query,oracledb,coerce_float=True)
print(data)

2. Pandas + conda + iopro (this is a commercial downloader with a 30 day license, suitable for uploading 3-4 million records):

import pandas as pd
import iopro.pyodbc as pdbc
conn_string = "DSN=OracleDSN;UID=user;PWD=password"
conn = pdbc.connect(conn_string)
query = """
select /*+parallel(4)*/ 2 from dual
"""
data = pd.DataFrame.from_dict(conn.cursor(query).fetchasarray(),orient='columns')
print(data)
3. Если данных больше 3-4 миллионов, то запрос вы в RAM не пробросите, но можно сделать так:
import pandas as pd
from sqlalchemy import create_engine
oracledb = create_engine('oracle://user:[email protected]') #name - название подключения в TNS файле
query = """
select /*+parallel(4)*/ 2 from dual
""" 
data = pd.read_sql_query(query,oracledb,coerce_float=True,chunksize=10000)
for idx,chunk in enumerate(data):
    print('Loading chunk %i'%idx)
    chunk.to_csv('data_%i_chunk.csv'%idx,header=True,index=False)

4. If there is even more data, then I advise you to look here docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/...

Answer 2

here is one of the old examples:
def get_query(sql):
con = cx_Oracle.connect('scott/[email protected]')
try:
cur = con.cursor()
cur.execute(sql)
result=cur.fetchone()
cur. close()
con.close()
if result is None:
#if query result in None return 0
return 0
return result[0]
except Exception, e:
return e
indent yourself :)

Answer 3

Did something like this so far:

connection = cx_Oracle.connect('scott/[email protected]')
db_cursor=connection.cursor()
db_cursor.execute("""
SELECT USER_NAME, USER_DEPT
FROM USERS
ORDER BY USER_NAME""")

Can you help me fix this to work? And how can I upload it to a file? What to write in a variable - the cursor?

Answer 4

Alexey M. , to get the result of the selection, you need to use the fetchall () method (or similar) of the Cursor object. Below is an example.
Document Link: cx-oracle.readthedocs.io/en/latest/cursor.html#Cur...
connection = cx_Oracle.connect('scott/[email protected]')
db_cursor=connection.cursor()
db_cursor.execute(" ""
SELECT USER_NAME, USER_DEPT
FROM USERS
ORDER BY USER_NAME""")
# get result
result = db_cursor.fetchall()
print(result)