Как прочитать Excel с помощью OleDB (C#)

В этой статье опишу как прочитать Excel с помощью OleDB.

Иногда бывает нужно вытянуть таблицу из Excel документа, записать в DataTable для последующей обработки.

Не всегда это удобно делать с помощью циклов, поэтому будем считывать таблицы, содержащиеся в документе и запрашивать из них данные с помощью SQL запросов.

oledb C#

Определение строки подключения

Для разных версий Excel будут свои строки подключения.

Строка подключения для Excel 2007 и более новых версий

//Можно использовать, если количество строк менее 65536
Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'

//Если строк больше 65536
Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties="Excel 12.0 Xml;HDR=YES";

Для работы с данными версиями необходимо установить Microsoft Access Database Engine 2010 Redistributable.

Так же C# может выбрасывать исключения по поводу недостающих драйверов. В этом случае необходимо скачать соответствующие драйверы с сайта Microsoft.

Строка подключения для более ранних версий

Строка подключения для Excel версии 2003 может иметь такой вид:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'

Если C# выбросит исключение, скачайте недостающий драйвер, Visual Studio подскажет, какой.

Как сделать SQL запрос из таблицы Excel

Для выполнения SQL запроса нужно найти таблицу в документе и выполнить к ней запрос:

//Объявляем OleDB соединение
using(OleDbConnection conn = new OleDbConnection(conStr))
{
  //Открываем подключение
  conn.Open();
  //Запрашиваем таблицы
  DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
  DataRow schemaRow = schemaTable.Rows[0];
  //Получаеи имя таблицы
  string sheet = schemaRow["TABLE_NAME"].ToString();
  //Объявляем команду
  OleDbCommand com = conn.CreateCommand();
  //Создаем SQL запрос
  com.CommandText = "SELECT * FROM [" + sheet + "]";
  //Выполняем SQL запрос
  OleDbDataReader reader = com.ExecuteReader();
  //Записываем результат в DataTable
  DataTable table = new DataTable();
  table.Load(reader);
  //Выводим DataTable в таблицу на форму (если нужно)
  gridControl1.DataSource = table;
}

На этом шаге мы имеем считанные данные из Excel документа в DataTable.

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

Как работать с Excel с помощью C# обсуждалось ранее.

Так же можете посмотреть, как работать со сводными таблицами в Excel с помощью C# и редактора VBA, встроенного в Excel.

Создаем сводные таблицы на C# в Excel

Сегодня программируем сводные таблицы на C#. Для этого надо будет иметь заготовленную таблицу с данными (обычную).

Заготовленная таблица будет иметь данные о телефонных звонках.

Пример сводной таблицы:

Сводные таблицы на C#

Открываем документ и создаем диапазоны для сводной таблицы

Откроем Excel документ с таблицей:

ex.Workbooks.Open("Excel.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

Далее создаем диапазон из одной ячейки, в которой указываем, куда будет помещаться сводная таблица:

Excel.Range pivotDestination = sheetPivot.get_Range("A1", "A1");

Объявляем диапазон сводной таблицы:

Excel.Range all = sheetPivot.get_Range("A1", "D1000");

Пишем код создания сводной таблицы

Код для создания сводной таблицы из обычной таблицы с данными имеет такой вид:

workBook.PivotTableWizard(
  Excel.XlPivotTableSourceType.xlDatabase,
all,                        //Таблица, на основе которой строим сводную
pivotDestination,              //Где сохранять
"NumberA",                        //Название таблицы
true,
true,
true,
true,
Type.Missing,
Type.Missing,
false,
false,
Excel.XlOrder.xlDownThenOver,
0,
Type.Missing,
Type.Missing
);

Распределяем поля сводной таблицы

Далее надо определить, какие поля будут отвечать за значения, за столбцы и строки.

Для этого создаем экземпляры полей на основе заголовков столбцов таблицы:

//Создаем поля таблицы
Excel.PivotTable pivotTable = (Excel.PivotTable)sheetPivot.PivotTables("Number");
Excel.PivotField Y = ((Excel.PivotField)pivotTable.PivotFields("Y"));
Excel.PivotField M = ((Excel.PivotField)pivotTable.PivotFields("M"));
Excel.PivotField Src_country = ((Excel.PivotField)pivotTable.PivotFields("Country"));
Excel.PivotField Operator_Name = ((Excel.PivotField)pivotTable.PivotFields("Operator"));
Excel.PivotField Minutes = ((Excel.PivotField)pivotTable.PivotFields("Minutes"));

Распределяем поля:

//Расставляем строки, столбцы и данные
Y.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
M.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
Src_country.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Operator_Name.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Minutes.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
//Функция, применяемая к данным - есть еще количество, MAX, MIN и т.д.
Minutes.Function = Excel.XlConsolidationFunction.xlSum;

Создаем сводную таблицу без исходной таблицы с данными

Описываю, как создать сводную таблицу на основе SQL-запроса к БД.

Для этого понадобится библиотека ADODB (adodb.dll можно скачать в интернете).

Подключим adodb.dll к проекту:

using ADODB;

Объявим строку подключения (драйвер SQLOLEDB.1):

Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sqlserver;Initial Catalog=DBName;Data Source=serverNameOrIp

Далее пишем код для работы со сводной таблицей:

//Создаем подключение и открываем его
ADODB.Connection sql = new Connection();
sql.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
sql.ConnectionString = conString;
sql.Open();

//Объявляем команду. Здесь функция getQuery() возвращает SQL-запрос
ADODB.Command cmd = new Command();
cmd.CommandType = CommandTypeEnum.adCmdText;
cmd.CommandText = getQuery();
cmd.CommandTimeout = 5000;
cmd.ActiveConnection = sql;

//Объявляем Recordset и выполняем запрос, сохраняя в него данные
ADODB.Recordset rs = new Recordset();
rs.Open(cmd, Type.Missing, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
//тут хранится результат запроса

Excel.PivotCache pc = wb.PivotCaches().Add(
Excel.XlPivotTableSourceType.xlExternal
, Type.Missing);

pc.Recordset = rs;
//Ссылка на все сводные таблицы на листе
Excel.PivotTables pts = (Excel.PivotTables)sheet.GetType().InvokeMember("PivotTables", System.Reflection.BindingFlags.GetProperty, null, sheet, null);
//Создаем новую сводную таблицу
Excel.PivotTable pt = pts.Add(pc, sheet.get_Range("A3", Type.Missing), "NumA", Type.Missing, Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent);

//Распределяю поля и настраиваю форматы полей
((Excel.PivotField)pt.PivotFields("Year")).Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
((Excel.PivotField)pt.PivotFields("Month")).Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
((Excel.PivotField)pt.PivotFields("Country")).Orientation = Excel.XlPivotFieldOrientation.xlRowField;
((Excel.PivotField)pt.PivotFields("Operator")).Orientation = Excel.XlPivotFieldOrientation.xlRowField;
((Excel.PivotField)pt.PivotFields("Minutes")).Orientation = Excel.XlPivotFieldOrientation.xlDataField;
((Excel.PivotField)pt.PivotFields("Сумма по полю Minutes")).Function = Excel.XlConsolidationFunction.xlSum;
((Excel.PivotField)pt.PivotFields("Сумма по полю Minutes")).NumberFormat = "# ##0";
((Excel.PivotField)pt.PivotFields("Operator")).ShowDetail=false;
((Excel.PivotField)pt.PivotFields("Country")).ShowDetail = false;

pt.TableStyle2 = "PivotStyleMedium13";     //Стиль сводной таблицы. Взял через VBA

При работе со сводными таблицами так же окажет помощь встроенный в Excel редактор VBA, о котором речь шла в теме Работа с Excel с помощью C# (Microsoft.Office.Interop.Excel).

Работа с Excel с помощью C# (Microsoft.Office.Interop.Excel)

Оставляю заметку по работе с Excel с помощью C#.

Привожу фрагменты кода, которые искал когда-то сам для работы с Excel документами.

Наработки очень пригодились в работе для формирования отчетности.

Прежде всего нужно подключить библиотеку Microsoft.Office.Interop.Excel.

Подключение Microsoft.Office.Interop.Excel
Visual Studio здесь довольно старой версии. Если у вас версия новая, отличаться будет только вид окна.

Далее создаем псевдоним для работы с Excel:

using Excel = Microsoft.Office.Interop.Excel;

//Объявляем приложение
Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();

//Отобразить Excel
ex.Visible = true;

//Количество листов в рабочей книге
ex.SheetsInNewWorkbook = 2;

//Добавить рабочую книгу
Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);

//Отключить отображение окон с сообщениями
ex.DisplayAlerts = false;                                       

//Получаем первый лист документа (счет начинается с 1)
Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(1);

//Название листа (вкладки снизу)
sheet.Name = "Отчет за 13.12.2017";

//Пример заполнения ячеек
for (int i = 1; i <= 9; i++)
{
  for (int j = 1; j < 9; j++)
  sheet.Cells[i, j] = String.Format("Boom {0} {1}", i, j);
}

//Захватываем диапазон ячеек
Excel.Range range1 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[9, 9]);

//Шрифт для диапазона
range1.Cells.Font.Name = "Tahoma";
//Размер шрифта для диапазона
range1.Cells.Font.Size = 10;

//Захватываем другой диапазон ячеек
Excel.Range range2 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[9, 2]);
range2.Cells.Font.Name = "Times New Roman";

//Задаем цвет этого диапазона. Необходимо подключить System.Drawing
range2.Cells.Font.Color = ColorTranslator.ToOle(Color.Green);
//Фоновый цвет
range2.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0xFF, 0xFF, 0xCC));

Расстановка рамок.

Расставляем рамки со всех сторон:

range2.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;

Цвет рамки можно установить так:

range2.Borders.Color = ColorTranslator.ToOle(Color.Red);

Выравнивания в диапазоне задаются так:

rangeDate.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
rangeDate.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

Формулы

Определим задачу: получить сумму диапазона ячеек A4:A10.

Для начала снова получим диапазон ячеек:

Excel.Range formulaRange = sheet.get_Range(sheet.Cells[4, 1], sheet.Cells[9, 1]);

Далее получим диапазон вида A4:A10 по адресу ячейки ( [4,1]; [9;1] ) описанному выше:

string adder = formulaRange.get_Address(1, 1, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

Теперь в переменной adder у нас хранится строковое значение диапазона ( [4,1]; [9;1] ), то есть A4:A10.

Вычисляем формулу:

//Одна ячейка как диапазон
Excel.Range r = sheet.Cells[10, 1] as Excel.Range;
//Оформления
r.Font.Name = "Times New Roman";
r.Font.Bold = true;
r.Font.Color = ColorTranslator.ToOle(Color.Blue);
//Задаем формулу суммы
r.Formula = String.Format("=СУММ({0}", adder);

Выделение ячейки или диапазона ячеек

Так же можно выделить ячейку или диапазон, как если бы мы выделили их мышкой:

sheet.get_Range("J3", "J8").Activate();
//или
sheet.get_Range("J3", "J8").Select();
//Можно вписать одну и ту же ячейку, тогда будет выделена одна ячейка.
sheet.get_Range("J3", "J3").Activate();
sheet.get_Range("J3", "J3").Select();

Авто ширина и авто высота

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

range.EntireColumn.AutoFit(); 
range.EntireRow.AutoFit();

Получаем значения из ячеек

Чтобы получить значение из ячейки, используем такой код:

//Получение одной ячейки как ранга
Excel.Range forYach = sheet.Cells[ob + 1, 1] as Excel.Range;
//Получаем значение из ячейки и преобразуем в строку
string yach = forYach.Value2.ToString();

Добавляем лист в рабочую книгу

Чтобы добавить лист и дать ему заголовок, используем следующее:

var sh = workBook.Sheets;
Excel.Worksheet sheetPivot = (Excel.Worksheet)sh.Add(Type.Missing, sh[1], Type.Missing, Type.Missing);
sheetPivot.Name = "Сводная таблица";

Добавление разрыва страницы

//Ячейка, с которой будет разрыв
Excel.Range razr = sheet.Cells[n, m] as Excel.Range;
//Добавить горизонтальный разрыв (sheet - текущий лист)
sheet.HPageBreaks.Add(razr); 
//VPageBreaks - Добавить вертикальный разрыв

Сохраняем документ

ex.Application.ActiveWorkbook.SaveAs("doc.xlsx", Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Как открыть существующий документ Excel

ex.Workbooks.Open(@"C:\Users\Myuser\Documents\Excel.xlsx",
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing,
  Type.Missing, Type.Missing);

Комментарии

При работе с Excel с помощью C# большую помощь может оказать редактор Visual Basic, встроенный в Excel.

Для этого в настройках ленты надо добавить пункт «Разработчик». Далее начинаем запись макроса, производим действия и останавливаем запись.

Далее заходим в редактор Visual Basic и смотрим код, который туда записался:

Vusial Basic (VBA)

Например:

Sub Макрос1()
'
' Макрос1 Макрос
'

'
    Range("E88").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$118"), , xlYes).Name = _
        "Таблица1"
    Range("A1:F118").Select
    ActiveSheet.ListObjects("Таблица1").TableStyle = "TableStyleLight9"
    Range("E18").Select
    ActiveWindow.SmallScroll Down:=84
End Sub

В данном макросе записаны все действия, которые мы выполнили во время его записи. Эти методы и свойства можно использовать в C# коде.

Данный метод так же может оказать помощь в формировании относительных формул, например, выполнить сложение чисел, находящиеся слева от текущей ячейки на 4 столбца, и т.п. Пример:

//Складываем значения предыдущих 12 ячеек слева
rang.Formula = "=СУММ(RC[-12]:RC[-1])";

Так же во время работы может возникнуть ошибка: метод завершен неверно. Это может означать, что не выбран лист, с которым идет работа.

Чтобы выбрать лист, выполните sheetData.Select(Type.Missing); где sheetData это нужный лист.

Настройка майнера Minerd на Linux Ubuntu 16.04 (Пул minergate.com)

Оставляю заметку о том, как запустить майнинг XMR на Linux Ubuntu.

Git должен быть уже установлен.

sudo apt-get update
sudo apt-get -y install git make automake gcc libcurl4-openssl-dev libmysqlclient-dev
git clone https://github.com/wolf9466/cpuminer-multi
cd cpuminer-multi
./autogen.sh
CFLAGS="-march=native" ./configure --disable-aes-ni
make

Список команд для запуска майнинга других валют можно найти здесь.

Команда для запуска майнига

./minerd -a cryptonight -o stratum+tcp://xmr.pool.minergate.com:45560 -u user@myemail.ru -p x

Так же на сайте есть статья о запуске майнинга на Linux Fedora.

Ручная настройка сети и MySQL сервера на Ubuntu Server 16.04

Заметка по установке Linux Ubuntu Server 16.04 на сервер HP  (думаю, что подобным образом его можно поставить на любой другой сервер) и о том, как настроить MySQL сервер.

Во время установки выбираем установить LAMP, SSH server, Samba сервер.

Система спросит, надо будет выбрать перечисленное пробелами.

DHCP сервера у меня не было, поэтому отметил «Настроить сеть вручную». Настройкой занялся после установки ОС.

Установленный сервер Ubuntu выглядит таким образом:

Installed ubuntu16.04

Настройка сети

Логинимся в систему и выполняем следующую команду:

sudo lshw -C network

Получаем примерно следующее:

lshw

Здесь нас интересует имя сетевого адаптера logical name: enp0s3.

Далее выполняем следующую команду:

sudo nano /etc/network/interfaces

В данном файле прописываем настройки сети для адаптера enp0s3:

interfaces

(Настройки у вас должны быть, конечно, свои)

Сохраняем файл комбинацией клавиш Ctrl+O и выходим из программы с помощью Ctrl+X.

Перезапускаем сеть:

sudo /etc/init.d/networking restart

На этом этапе сеть должна уже работать и соседние компьютеры пинговаться.

Настройка MySQL

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

Так же необходимо изменить движок таблиц по умолчанию с InnoDB на MyISAM (в моем случае), так как транзакций не будет, а на движке MyISAM все будет работать быстрее. Для этого необходимо присвоить переменной default_storage_engine значение MyISAM в файле mysqld.cnf.

Пишем в консоли:

sudo nano /etc/mysql/mysql.conf.d/mysql.cnf

Прописываем переменную в конфиге и комментируем строку bind-address = 127.0.0.1 чтобы мы могли подключаться к MySQL с других компьютеров:

mysqld conf

По умолчанию MySQL сервер не позволит пользователю root подключиться с других компьютеров.

Создадим нового пользователя и дадим ему полные права. Для этого запустим mysql client на сервере либо по SSH:

mysql -h 127.0.0.1 -u root -p

Введем пароль, установленный для root и получим следующее:

mysql

На этом этапе и можем писать SQL запросы. В конце каждого запроса ставим символ ‘;‘ и нажимаем Enter.

Создаем нового пользователя MySQL со всеми правами

Создаем нового пользователя (здесь myuser11 это пароль):

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'myuser11';
CREATE USER 'myuser'@'%' IDENTIFIED BY 'myuser11';

Даем все права:

GRANT ALL ON *.* TO 'myuser'@'localhost';
GRANT ALL ON *.* TO 'myuser'@'%';

Обновляем все права доступа командой:

FLUSH PRIVILEGES;

На этом этапе мы имеем настроенный MySQL сервер с админом myuser, который может подключаться к MySQL с других компьютеров.

Подключаемся к удаленному рабочему столу Linux Ubuntu 16.04 из Windows (VNC)

В данной записи рассматриваем возможность подключения к удаленному рабочему столу на Linux Ubuntu.

В Ubuntu за доступ к удаленным рабочим столам отвечает программа Vino.

Ищем ее в поиске и запускаем. Получаем такое окно:

ubuntu vino

Можно настроить программу под ваши нужды и подключаться к данной машине по протоколу VNC.

Но есть важное замечание. Из систем Windows подключение выполнить нельзя.

Чтобы подключиться из Windows необходимо выполнить следующие действия:

  1. Установить dconf-editor;
  2. Отключить принудительное шифрование;
  3. Подготовить программу для подключения к удаленным рабочим столам (я использовал mRemoteNG).

Шаг 1 (Открываем терминал и пишем команду)

sudo apt install dconf-editor

Шаг 2 (Запускаем dconf и убираем галочку с require-encryption)

Шаг 3 (Подключаемся к удаленному рабочему столу)

Подключаемся к удаленному рабочему с помощью программы mRemoteNG.

mRemoteNG

 

Установка шрифтов Microsoft на Linux Ubuntu 16.04

Недавно понадобилось установить Microsoft шрифты в Ubuntu для редакторов LibreOffice и прочих,

чтобы документы выглядели как в продуктах Microsoft Office.

Устанавливаем шрифты Microsoft

Чтобы установить шрифты используем команды:

wget http://ftp.de.debian.org/debian/pool/contrib/m/msttcorefonts/ttf-mscorefonts-installer_3.6_all.deb

Далее:

sudo dpkg -i ttf-mscorefonts-installer_3.6_all.deb

Обновим кэш шрифтов:

sudo fc-cache -f -v

Всё готово. Чтобы увидеть установленные шрифты, выполните поиск по запросу «шрифты» и увидите следующее окошко:

Установка шрифтов Microsoft на Linux

После данных манипуляций в редакторах будут доступны шрифты Microsoft.

Пишем класс, описывающий рациональные дроби (C#)

Рациональные дроби

Рациональные дроби — это числа, представляемые обыкновенной дробью с числителем и знаменателем.

Рациональные дроби

В данной статье выкладываю код, который я написал для реализации класса рациональных дробей. Это было одно из заданий для получения сертификата курса проектирования на C#.

В представленном листинге ниже реализован данный класс:

Показать текст ->

В классе описаны методы выполнения арифметический операций с рациональными дробями:

  • Сложение и вычитание дробей через НОЗ (наименьший общий знаменатель);
  • Умножение и деление дробей;
  • Умножение дроби на число;
  • Преобразование числа к рациональной дроби (любое число это рациональная дробь, где взятое число делится на 1);
  • Преобразование рациональной дроби к десятичной;
  • Описаны функции нахождения НОЗ и сокращения рациональной дроби.

Настройка майнинга XMR на Linux Fedora (Пул minergate.com)

Майнинг XMR на Fedora.

В данной заметке запускаем майнинг XMR на Fedora, используя сервис Minergate.com через CPU майнер minerd.

Майнер будет браться с GitHub и компилироваться. Git должен быть уже установлен, если у вас нету Git, установить можно так:

yum install git

Далее устанавливаем необходимые пакеты:

yum install -y cpp make automake gcc libcurl-devel openssl-devel

Клонируем репозиторий с GitHub:

git clone https://github.com/wolf9466/cpuminer-multi

Выполняем команды в терминале:

cd cpuminer-multi
./autogen.sh
CFLAGS="-march=native"
./configure
make
make install

Запускаем майнинг

Лично мне пригодилась следующая команда для запуска:

minerd -a cryptonight -o stratum+tcp://xmr.pool.minergate.com:45560 -u MY_EMAIL -p 4

Пример выполнения команды:

Майнинг XMR на Fedora

Список команд для запуска можно взять здесь.

На данном сайте есть статья о запуске майнинга на Linux Ubuntu.

Выгрузка больших объемов данных из Oracle БД и заливка в MySQL (Python)

Недавно понадобилось перелить большие объемы данных из БД Oracle в MySQL для последующего анализа. Задача решалась с помощью Python.

Строился sql запрос к таблицам Oracle для выгрузки данных в курсор, и затем, построчно пробегая по курсору, данные заливались в MySQL.

Подготовка модулей pymysql, sshtunnel и cx_Oracle

Для работы с MySQL понадобилось установить модуль pymysql, и модуль sshtunnel, так как подключение находилось за SSH. Для работы с Oracle был установлен модуль cx_Oracle и скачан Oracle Instant Client с сайта Oracle (может понадобиться указать путь к распакованному архиву в переменной среды PATH и перезагрузить компьютер). Так же необходимо иметь актуальные версии Microsoft Visual C++ Redistributable (в моем случае версии 2013).

Устанавливаем модули (через cmd от имени администратора):

pip3 install pymysql

pip3 install sshtunnel

pip3 install cx_Oracle

Переливаем данные из Oracle в MySQL

Работаем со следующим кодом:

import cx_Oracle
import pymysql
import datetime
import os
import sshtunnel

#Если работаем с ОС Windows, то задаем переменную NLS_LANG = .AL32UTF8
#чтобы избежать проблем с кириллицей
if(os.name == 'nt'):
    os.environ['NLS_LANG'] = '.AL32UTF8'

#Объявляем переменные типа даты для последующего цикла (строка 51)
startdate = datetime.datetime.now().date() - datetime.timedelta(days=2)
enddate = datetime.datetime.now().date() - datetime.timedelta(days=1)

insert_query = ("replace into resultTable "
             "(name, date, mainData, count, sumvalues) "
             "values (%s, %s, %s, %s, %s)")

#Создаем подключение к MySQL и инициализируем объект курсора             
db = pymysql.connect(passwd="pass11",db="data",host="serv3", user="root")
cur = db.cursor()

#Создаем подключение к Oracle и инициализируем объект курсора
oracleDb = cx_Oracle.connect("username/password@10.250.100.100/servicename")
oraclesCursor = oracleDb.cursor()

ora_query='''
select * from oracleData
where date between to_date('{0} 00:00:00','dd.mm.yyyy hh24:mi:ss') and to_date('{0} 23:59:59','dd.mm.yyyy hh24:mi:ss')
'''

#Строим SSH туннель
#Здесь пробрасываем порт 12140
#Можно пробросить любой свободный
with sshtunnel.SSHTunnelForwarder(
        ('ssh_address', 22),
        ssh_username='ssh_username',
        ssh_password='ssh_password',
        remote_bind_address=('mysql_address', 3306),
        local_bind_address=('127.0.0.1', 12140)
) as tunnel:
    conn = MySQLdb.connect(
        user='root',
        password='password123',
        host='127.0.0.1',
        database='Db_Name',
        port=12140)
    curss = conn.cursor()
    
    startdate_oracle_temp = startdate

    #Формируем цикл по дате. Один оборот цикла обрабатывает один день
    while startdate_oracle_temp <= enddate:
        ora_query = all_oracle.format(startdate_oracle_temp.strftime('%d.%m.%Y'))
        oraclesCursor.execute(ora_query)
        for raw in oraclesCursor:
            #В качестве параметров %s, %s, %s, %s, %s переменной insert_query передаем raw
            cur.execute(insert_query, raw)
        db.commit()
        startdate_oracle_temp = startdate_oracle_temp + datetime.timedelta(days=1)
        
cur.close()
db.close()

В моем случае в таблице содержалось более 10 млн записей за каждый день.

Можно в запросе к Oracle указать сразу весь диапазон дат, но в случае сбоя этот запрос надо выполнять заново. Если нужна хотя бы часть данных, с которыми необходимо работать, используйте цикл. Преимущества цикла в том, что если Вы выполняете запрос данных за, например, 10-30 дней или более, уже после первого оборота цикла данные за один день будут обработаны и с ними можно работать.

Если Вы зададите весь диапазон дат в сам запрос, придется ждать обработки всех заданных дат и только потом начинать с ними работать.