IT-инфраструктура для бизнеса и творчества
Разработка
NTA

Выгружаем из базы данных с помощью Python

Задача на выгрузку данных из одной таблицы, с одним условием для фильтра решается посредством создания простого SQL-запроса. Но она легко становится трудоемкой в исполнении, если в фильтрации применить множество условий. Давайте представим, что необходимо выгрузить данные из одной таблицы по фильтру, где первые две цифры ИНН начинаются на «66». SQL-запрос будет выглядеть так:

select * from tabl where inn like '66%'

Вроде ничего сложного, но, если изменить условие фильтрации на поиск по 2 млн уникальных ИНН, SQL-запрос будет выглядеть так:

select * from tabl where inn = '66******01' or inn = '66******02' or inn = '66******03' or inn = '66******04' or inn = '66******05' or inn = '66******06' or inn = '66******07' ... or inn = '66******nn'

Исходя из своего опыта, такой запрос запустить не получится из-за ограничения СУБД на количество условий в 10 тыс. значений (Ограничения СУБД могут быть разные). В нашем случае потребуется создать 200 запросов и запустить их по отдельности. В итоге мы получим 200 файлов с данными, которые необходимо собрать в один. Сложно представить сколько потребуется на это времени и сил, но с помощью Python и библиотеки «cx_Oracle» задача решается легко.

Используем библиотеки:

import cx_Oracle import pandas as pd import time

Проверяем версию (должна быть > 3.0):

cx_Oracle.__version__

Прописываем дескриптор соединения Oracle:

ConnectStr = """(DESCRIPTION=(ADDRESS=(PROTOCOL=...)(Host=...) (Port= ... ))(CONNECT_DATA=(SERVICE_NAME= ... )))"""

Прописываем логин и пароль (при доменной аутентификации оставляем '/'):

Login = 'IVANOV/PASSWORD'

Функция, в которой создается экземпляр класса connect, он обеспечит взаимодействие с сервером Oracle:

def getConn(Login, ConnectStr): conn=None nn=0 while conn==None: try: conn=cx_Oracle.connect(Login + '@' + ConnectStr) except cx_Oracle.DatabaseError as e: ers,=e.args nn=nn+1 print (nn,end='\r') if ers.code!=2391: print ('Ошибка Oracle ', ers.code) break time.sleep(5) return conn

Функция, в которой создается курсор и выполняется запрос:

def dfFromOracle(connection, sql): us=0 outDF=pd.DataFrame() success = 'False' with connection.cursor() as cursor1: cursor1.execute(sql) trn=10 while success == 'False' and trn>0: try: outheader=[desc[0] for desc in cursor1.description] #При вызове "cursor1.fetchall()" возвращается список записей, каждая из которых #является кортежем (неизменяемым списком) полей разного типа outDF=pd.DataFrame(cursor1.fetchall()) success = 'True' print('Результат получен из базы') us = 1 except: trn=trn-1 print('Error') time.sleep(60) return outheader, outDF, us

Подключаемся к серверу:

getConn(Login, ConnectStr)

Файл, в котором одна колонка со всеми значениями ИНН:

f = open('inn_in_.txt','r',encoding='UTF-8')

Присваиваем имя файла, в который выгрузится результат:

new_file = 'new_file.csv'

r='' - для собора строк в «with», которые подставим в SQL-запрос,
h = 0 - проверки наличия заголовков,
l = 0 — счетчик строк для запуска SQL-запроса,
ll = 0 — счетчик строк для проверки на остаток,
cnt = 10000 — количество строк для запуска SQL-запроса.

Цикл для создания и запуска SQL-запросов:

for row in f: l += 1 if r == '': #Формируем "where" - список ИНН для фильтрации r = r + 'inn = \'' + row.replace('\n','') + '\'' else: r = r + 'or inn = \'' + row.replace('\n','') + '\'' if l % cnt == 0: #Проверка на 10 000 строк ll += l sql = ( 'select * from tabl where' + r ) #Сформированный SQL-запрос

Отправляем SQL-запрос и обрабатываем полученную выгрузку:

with getConn(Login,ConnectStr) as con1: if h==0: _header,result,us = dfFromOracle(con1, sql) header = ';'.join(_header)+'\n' myfile=open(new_file, 'w',encoding='UTF-8') myfile.writelines(header) myfile.close() h=1 result.to_csv(new_file, sep=';',encoding='UTF-8',mode='a',header=None,index=False) r='' print('Выгружено: '+str(l) ) if l != ll: #Если последний список менее 10 000 строк sql = ( 'select * from tabl where' + r ) #Сформированный SQL-запрос with getConn(Login,ConnectStr) as con1: _header,result,us = dfFromOracle(con1, sql) if h==0: header = ';'.join(_header)+'\n' myfile=open(new_file, 'w',encoding='UTF-8') myfile.writelines(header) myfile.close() h=1 result.to_csv(new_file, sep=';',encoding='UTF-8',mode='a',header=None,index=False) print('Выгружено: '+str(l) ) print('Выгрузка завершена') f.close()

Таким образом, с помощью Python мы автоматизировали процесс создания, запуска SQL-запросов и сохранения результатов в один файл, избежали больших трудозатрат, при этом:

  • исключили ошибки, возможные при перечислении списка условий для фильтрации, что снизило вероятность потери данных;
  • настроили процесс создания, запуска SQL-запросов и сохранения результатов в один файл порционно, что дает возможность в случае обрыва сессии не повторять заново запросы с уже отработавшими условиями, а продолжить с условий, на которых обрыв произошел.
{ "author_name": "NTA", "author_type": "editor", "tags": ["\u044f\u0432\u043b\u044f\u0435\u0442\u0441\u044f","\u0444\u043e\u0440\u043c\u0438\u0440\u0443\u0435\u043c","\u0441\u0444\u043e\u0440\u043c\u0438\u0440\u043e\u0432\u0430\u043d\u043d\u044b\u0439","\u043f\u0440\u043e\u0432\u0435\u0440\u043a\u0430","\u043f\u0440\u0438","\u0435\u0441\u043b\u0438"], "comments": 5, "likes": 2, "favorites": 21, "is_advertisement": false, "subsite_label": "dev", "id": 219528, "is_wide": true, "is_ugc": false, "date": "Mon, 15 Mar 2021 12:45:06 +0300", "is_special": false }
(function () { let cdnUrl = `https://specialsf378ef5-a.akamaihd.net/SelectelBranding/images/` let previousArticleNumber = null let currentArticleNumber = 0 let platform = 'Desktop' let articles = [ // { // name: 'camera', // url: `${cdnUrl}CameraCat`, // text: 'умную камеру для\u00A0наблюдения за\u00A0котиками', // link: '1', // num: 3 // }, { name: 'chill', url: `${cdnUrl}ChillCat`, text: 'трекер, который подскажет, когда пора отдохнуть', link: 'https://vc.ru/promo/288561-eye-tracker', num: 1 }, { name: 'cloud', url: `${cdnUrl}CloudCat`, text: 'котика: даёшь ему «пять», а\u00A0он делает бэкап в облако', link: 'https://vc.ru/dev/294799-maneki-neko', num: 2 } ] let buttonCycle = document.querySelector('.button--cycle') let buttonChoose = document.querySelector('.button--choose') let buttonMobile = document.querySelector('.button--mobile') let textField = document.querySelector('.selectel-footer-subtitle') let imageAgent = document.querySelector('.image--agent') let banner = document.querySelector('.selectel-footer') buttonCycle.addEventListener('click', cycleClick) buttonChoose.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) buttonMobile.addEventListener('click', () => sendEvent(`Promo ${articles[currentArticleNumber].num} Left`, 'Click')) let media = window.matchMedia("(max-width: 570px)") media.addEventListener('change', matchMedia) function matchMedia() { if (media.matches) { platform = 'Mobile' } else { platform = 'Desktop' } update() } matchMedia() function cycleClick(event) { sendEvent(`Promo ${articles[currentArticleNumber].num} Right`, 'Click') if (event) { event.preventDefault() event.stopPropagation() } window.open('https://vc.ru/tag/selectelDIY', '_blank') //cycle(event) } function cycle(event) { // incrementArticleNumber() textField.innerHTML = generatedText() imageAgent.src = articles[currentArticleNumber].url + platform + '.svg?3' imageAgent.setAttribute("class", "") imageAgent.classList.add('image--agent', articles[currentArticleNumber].name) banner.href = articles[currentArticleNumber].link } function update() { banner.href = articles[currentArticleNumber].link imageAgent.src = articles[currentArticleNumber].url + platform + '.svg' textField.innerHTML = generatedText() } function incrementArticleNumber() { previousArticleNumber = currentArticleNumber if (currentArticleNumber >= articles.length - 1) { currentArticleNumber = 0 } else { currentArticleNumber++ } } const sendEvent = (label, action = 'Click') => { const value = `SelectelDIY — loc: Footer — ${label} — ${action}`; if (window.dataLayer !== undefined) { window.dataLayer.push({ event: 'data_event', data_description: value, }); } }; function generatedText() { let defaultText if (platform === 'Desktop') { defaultText = `Мы тут собрали %text%. Хотите научим?` } else { defaultText = `Мы тут собрали %text%.` } return defaultText.replace('%text%', articles[currentArticleNumber].text) } function getRandom(min, max) { min = Math.ceil(min) max = Math.floor(max) return Math.floor(Math.random() * (max - min + 1)) + min } (function create() { currentArticleNumber = getRandom(0, articles.length - 1) cycle() let page = document.querySelector('.page--entry') if (page) { function insertAfter() { let parents = page.querySelectorAll('[data-id="7"]') let referenceNode = parents[0] referenceNode.parentNode.insertBefore(banner, referenceNode.nextSibling); loaded() } setTimeout(() => insertAfter(), 0) } }()) function loaded() { banner.classList.add('loaded') } loadImages([ `${cdnUrl}CameraCatDesktop.svg`, `${cdnUrl}ChillCatDesktop.svg`, `${cdnUrl}CloudCatDesktop.svg`, `${cdnUrl}CameraCatMobile.svg`, `${cdnUrl}ChillCatMobile.svg`, `${cdnUrl}CloudCatMobile.svg?3`, ]) function loadImages(urls) { return Promise.all(urls.map(function (url) { return new Promise(function (resolve) { var img = document.createElement('img'); img.onload = resolve; img.onerror = resolve; img.src = url; }); })); } }())
0
5 комментариев
Популярные
По порядку

Мисье знает толк в изврашениях 🙂
1) почему не оператор IN? 
2) можно вставить ваши инн во временную таблицу и сделать join с ней
3) файл с инн можно подключить в оракл как таблицу и использовать п. 2

3

1) почему не оператор IN?
У оператора IN ограничение в 1 000 значений, а OR ограничен настройками СУБД (в нашем случае это 10 000 значений).
Если не обращать внимание на ограничения, то оператор IN хорошо использовать для написания компактного запроса, но он в момент исполнения все равно преобразовывается в OR.

2) можно вставить ваши инн во временную таблицу и сделать join с ней
Запустить в PL/SQL Developer запрос в котором более 2 000 000 строк кода проблематично, а если получиться, то опять возникает ограничение в СУБД.

3) файл с инн можно подключить в оракл как таблицу и использовать п.2
Если не обращать внимание на ограничения в СУБД, то использовать Python более надежней, при обрыве сессии можно начать выгрузку с момента обрыва.

P.s. С Вашими предложениями в п.2 и п.3 согласны, но применить к описанной задаче не получиться, все упирается в ограничения СУБД.

0

Месье или (канонически месьё)

0

Если это одноразовая задача, не проще было бы получить все данные из таблицы и отфильровать регуляркой? 2млн записей это не так уж и много. ред.

1

> Сложно представить сколько потребуется на это времени и сил, но с помощью Python и библиотеки «cx_Oracle» задача решается легко.

Так поди не с помощью библиотеки, а с помощью вендорного ПО?
Начало и заголовок статьи как-то не дает понять, что будет речь про оракл.

0
Читать все 5 комментариев
Шпаргалка для инвестора: как инвестировать в облигации иностранных эмитентов

Рассказываем, что такое еврооблигации или евробонды, каких видов они бывают, и что нужно знать про них инвестору.

«Тинькофф» запустит новое направление для выхода клиентов на маркетплейсы Статьи редакции

Направление возглавит бывший гендиректор eBay на глобальных развивающихся рынках Илья Кретов.

Делай бэкап, или как мы чуть не потеряли самое ценное, что у нас было

ИТ-специалисты поделились своими историями, а эксперт по облачному резервному копированию их прокомментировал.

Лучшие стартапы в «Трибуне» с 11 по 17 октября 2021 года

Подборка лучших проектов по рейтингу подготовлена каналом @vchunt, где каждую неделю появляется топ-3 стартапа из «Трибуны». Подписывайтесь, чтобы поддержать русскоязычных фаундеров и не пропустить отличные продукты!

Вице-премьер Татьяна Голикова предложила ввести нерабочие дни с 30 октября по 7 ноября Статьи редакции

В некоторых регионах — с 23 октября.

Статья дополняется
Неочевидные преимущества коллективного инвестирования

Почему инвестирование в паи открытых паевых инвестиционных фондов может быть интересно не только начинающим, но и опытным инвесторам.

«Авито» для юр. лиц просит загрузить паспортные данные физ. лица

По порядку: работаю маркетологом в компании, занимающейся монтажом видеонаблюдения, СКУД и т.д. Компания с опытом, но по местному рынку малоизвестная. Логичная мысль: разместить объявление с нашими услугами на Авито (площадка большая, известная и т.д.) и попробовать задействовать все их услуги по продвижению (посмотреть, как это все будет…

«Битрикс24» заявил о двукратном росте количества платных клиентов в Италии. Включая Мэрию Венеции

Период с июня 2020 года по июнь 2021 года оказался одним из самых сложных для экономики Италии. В этот отрезок количество компаний-клиентов, которые пользовались платными тарифами нашего сервиса, выросло в 2 раза. В их числе - мэрия Венеции

Как я умоляю Samsung уже два месяца взять мои 160 000 рублей

11 августа 2021 смотрю презентацию Samsung Galaxy Z Fold3 5G, после чего решаю что надо брать, ибо девайс интересный, а мне давно пора было поменять свой рабочий телефон. OMG, давно я так не ошибался…

«Что вы нам предлагаете. Это всё шарлатанство. Ничего не сработает». Как УБРиР меняет подход к технологиям изнутри
null