Как выгрузить определенные поля таблицы из DB Oracle 11G в файл скриптом, написанным на Python 3.4?

Question

[Алексей М.]

Добрый день!
Делаю скрипт на Python 3.4, генерирующий ежедневный отчет из CI Jenkins, которая в свою очередь подключается к БД Oracle 11G. Никак не разберусь в библиотеке cx_Oracle и мануалу к ней (решил использовать ее).
Необходимо выгрузить определенные поля определенной таблицы (например, таблица USERS и поля USER_NAME и USER_DEPT).

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

А вот с выборкой таблиц беда... Помогите пожалуйста?

Answer 1

[lPolar]

Я бы решил эту задачу так:
1. Pandas+sqllite, если объем выборок <30-50 тыс. записей и <10-12 колонок:

import pandas as pd
from sqlalchemy import create_engine
oracledb = create_engine('oracle://user:password@name') #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 (это коммерческий загрузчик с 30 дневной лицензией, подойдет для заливки 3-4 млн. записей):

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:password@name') #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. Если данных еще больше, то советую посмотреть здесь docs.oracle.com/cd/B25329_01/doc/admin.102/b25107/...

Comments

[Алексей М.]

Спасибо, интересное решение, обязательно попробую. Записей не так уж и много, тысяч 5 на всю базу, но вот колонок в той таблице, которая нужна >30, так что надеюсь сработает через первый способ Pandas+sqllite.

[lPolar]

Алексей М.: Тут есть одна важная деталь - если у Вас Oracle база находится на localhost, то тогда скорость будет очень хорошей. Если она удаленная, и у Вас слабый канал, то проброс данных будет долгим, потому что sqlalchemy (не sqllite, перепутал название) очень плохо сжимает данные.

[Алексей М.]

lPolar: База на локальной машине, спасибо за информацию))

[Алексей М.]

lPolar: Человеческое Вам спасибо! Все получилось! Может кому-то пригодятся то, как это получилось у меня...
Python 3.4 не захотел устанавливать библиотеку (пробовал по всякому), немного помучившись, снес и поставил Anaconda там все либы необходимые (и даже больше) есть. Мой код:

#Импорт библиотек
import pandas as pd
from sqlalchemy import create_engine
#Подключение к БД
oracledb = create_engine('oracle://ВАШ_ПОЛЬЗОВАТЕЛЬ:ПАРОЛЬ@ИМЯ_ПОДКЛЮЧЕНИЯ')
#Запрос к БД
query = """
select ПОЛЕ1,ПОЛЕ2 from ИМЯ_ТАБЛИЦЫ
"""
#Прочитать запрос
data1 = pd.read_sql_query(query,oracledb,coerce_float=True)
print(data1)

[lPolar]

Алексей М.: если вы поставили conda, можете смело попробовать iopro (conda install iopro).
Скорость у него по сравнению с read_sql_query гораздо больше.

[Алексей М.]

lPolar: вдвойне Вам огромное спасибо за очень полезную и нужную информацию!)

[lPolar]

Алексей М.: Не забывайте добавлять хинты в селект /*+parralel(x)*/. Исправил ответ, добавил +

[Алексей М.]

lPolar: про это почитаю в документации, пожалуй, так как в запросах не силен... И снова Вам спасибо огромное, добрый человек! Очень помогли мне))

Answer 2

[Swartalf]

вот один из старых примеров:

def get_query(sql):
con = cx_Oracle.connect('scott/tiger@oradb')
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

отступы расставьте сами:)

Comments

[Алексей М.]

Спасибо, видел похожий код, только он у меня работает без исключений почему-то... С except'ом ругается на запятую, буду разбираться тогда почему и что значит каждая из строчек. Но я так понял тут нет выборки полей?

[Swartalf]

в эту функцию просто передается запрос
к примеру:
get_query('select user_name, user_dept from users')

[Swartalf]

это писалось под питон 2.7 потому возможно и проблема с синтаксисом.

[Алексей М.]

Swartalf: спасибо, буду пробовать, определенно мне такое решение нравится.

Answer 3

[Алексей М.]

Сделал как-то так пока:

connection = cx_Oracle.connect('scott/tiger@oradb')
db_cursor=connection.cursor()
db_cursor.execute("""
SELECT USER_NAME, USER_DEPT
FROM USERS
ORDER BY USER_NAME""")

Помогите скорректировать что бы "это" работало? И как это потом выгрузить в файл? Что записывать в переменную - курсор?

Answer 4

[Михаил]

Алексей М., для получения результата выборки нужно использовать метод fetchall() (или подобный) объекта Cursor. Ниже пример.

Ссылка на документ: cx-oracle.readthedocs.io/en/latest/cursor.html#Cur...

connection = cx_Oracle.connect('scott/tiger@oradb')
db_cursor=connection.cursor()
db_cursor.execute("""
SELECT USER_NAME, USER_DEPT
FROM USERS
ORDER BY USER_NAME""")

# получение результата
result = db_cursor.fetchall()
print(result)