Создание собственных программ экспорта

Задача
Вам не хватает встроенных возможностей экспортирования MySQL.

Решение
Напишите собственные утилиты.

Обсуждение
Если существующее программное обеспечение не делает того, чего бы хотелось вам, напишите собственные программы, экспортирующие данные. Этотраздел посвящен написанию на Perl сценария mysql_to_text.pl, который выполняет произвольный запрос и экспортирует его в указанном формате. Он записывает вывод на клиентский хост и может содержать строку заголовков столбцов (ничего из этого SELECT ... INTO OUTFILE не умеет). Он формирует различные форматы вывода, причем проще, чем при использовании mysql
с пост-процессором, и записывает вывод на клиентский хост, в отличие от mysqldump, которая может записывать на клиентскую машину только вывод в формате SQL. Сценарий mysql_to_text.pl овнован на модуле Text::CSV_XS, который должен быть установлен в вашей системе. После установки модуля вы можете прочитать соответствующую документацию так:

% perldoc Text::CSV_XS

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


Поэтому преобразование вывода запроса в формат CSV становится тривиальной задачей. Но главное достоинство модуля Text::CSV_XS в том, что его можно конфигурировать – вы можете указать, какие символы следует использовать для разделителей и заключения в кавычки. То есть по умолчанию модуль выдает формат CSV, но вы можете настроить его так, чтобы выводилось множество форматов. Например, если задать в качестве разделителя символ табуляции, а кавычкой назначить undef, Text::CSV_XS сформирует вывод, элементы которого разделяются знаками табуляции. Мы воспользуемся этой гибкостью при написании mysql_to_text.pl, а также впоследствии при создании утилиты обработки файлов, преобразующей файлы из одного формата в другой.

Сценарий mysql_to_text.pl принимает ряд опций командной строки. Некоторые из них используются для определения параметров соединения MySQL (такие, как --user, --password и --host). С ними вы уже знакомы, поскольку они применяются стандартными клиентскими приложениями MySQL, такими как mysql. Сценарий также может получать параметры соединения из файла опций, если задать в файле группу [client]. Кроме того, mysql_to_text.pl принимает следующие опции:

--execute=запрос, -e запрос

Выполнить запрос и экспортировать его вывод.

--table=имя_таблицы, -t имя_таблицы

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


Эквивалентно использованию --execute со значением запроса SELECT * FROM имя_таблицы.

--labels

Вывести строку заголовков столбцов.

--delim=строка

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

--quote=c

Установить символ кавычки в значение c. По умолчанию – ничего не заключать в кавычки.

--eol=строка

Установить последовательность конца строки в значение строка. Значение опции может состоять из одного или нескольких символов. По умолчанию используются символы перевода строки.

Значения по умолчанию для опций --delim, --quote и --eol соответствуют применяемым для предложений LOAD DATA и SELECT ... INTO OUTFILE.

Последним аргументом командной строки должно быть имя базы данных, если только оно не указывается неявно (в запросе). Например, две приведенные ниже команды эквиваленты, они экспортируют таблицу passwd в формате значений, разделенных символом двоеточия, в файл tmp:

% mysql_to_text.pl --delim=":" --table=passwd cookbook > tmp
% mysql_to_text.pl --delim=":" --table=cookbook.passwd > tmp

Чтобы сформировать вывод CSV с парой CRLF в качестве признака конца строки, выполните такую команду:

% mysql_to_text.pl --delim="," --quote="\"" --eol="\r\n" \
--table=cookbook.passwd > tmp

Это было общее описание использования mysql_to_text.pl.


Теперь давайте поговорим о том, как работает сценарий. В первой части сценария mysql_ to_text.pl объявляется несколько переменных, затем аргументы командной строки обрабатываются при помощи приемов.

(Как это часто бывает, большая часть кода сценария посвящена обработке
аргументов командной строки и настройке работы сценария!)

#! /usr/bin/perl -w
# mysql_to_text.pl - экспорт вывода запроса MySQL
# в указанном пользователем текстовом формате
# Использование: mysql_to_text.pl [ опции ] [имя базы данных] > текстовый файл
use strict;
use DBI;
use Text::CSV_XS;
use Getopt::Long;
$Getopt::Long::ignorecase = 0; # опции чувствительны к регистру
$Getopt::Long::bundling = 1; # позволяет объединить короткие опции
my $prog = "mysql_to_text.pl";
# ... создание переменной с сообщением об использовании $usage (не приводится) ...
# Переменные для опций командной строки - все изначально не определены,
# кроме структуры вывода, которая установлена в формат значений,
# разделенных табуляциями, признак конца строки - LF.
my $help;
my ($host_name, $password, $port_num, $socket_name, $user_name, $db_name);my ($query, $tbl_name);
my $labels;
my $delim = "\t";
my $quote;
my $eol = "\n";
GetOptions (
# =i означает, что после опции требуется целый аргумент
# =s означает, что после опции требуется строковый аргумент
# :s означает, что после опции возможен строковый аргумент
"help" => \$help, # вывести справочное сообщение
"host|h=s" => \$host_name, # хост сервера
"password|p:s" => \$password, # пароль
"port|P=i" => \$port_num, # номер порта
"socket|S=s" => \$socket_name, # имя сокета
"user|u=s" => \$user_name, # имя пользователя
"execute|e=s" => \$query, # выполняемый запрос
"table|t=s" => \$tbl_name, # экспортируемая таблица
"labels|l" => \$labels, # формирование строки заголовков столбцов
"delim=s" => \$delim, # разделитель столбцов
"quote=s" => \$quote, # символ заключения столбца в кавычки
"eol=s" => \$eol # признак конца строки (записи)
) or die "$usage\n";
die "$usage\n" if defined $help;
$db_name = shift (@ARGV) if @ARGV;
# Должна быть указана только одна из опций --execute и --table, но не обе
die "You must specify a query or a table name\n\n$usage\n"
if !defined ($query) && !defined ($tbl_name);
die "You cannot specify both a query and a table name\n\n$usage\n"
if defined ($query) && defined ($tbl_name);
# Если было указано имя таблицы, преобразовать его в запрос, выбирающий всю таблицу
$query = "SELECT * FROM $tbl_name" if defined ($tbl_name);
# преобразовать состояние определено/не определено в истина/ложь
$labels = defined ($labels);
# интерпретировать специальные символы в опциях структуры файла
$quote = interpret_option ($quote);
$delim = interpret_option ($delim);
$eol = interpret_option ($eol);

Функция interpret_option() обрабатывает экранирующие и шестнадцатеричные последовательности для опций --delim, --quote и --eol.


Последовательности \n, \r, \t и \0 интерпретируются как перевод строки, возврат каретки, табуляция и ASCII-символ NUL. Шестнадцатеричные значения могут указываться в формате 0xnn (например, 0x0d означает возврат каретки). Функция здесь не приводится; чтобы посмотреть, как она работает, обратитесь к исходному тексту сценария.

После обработки опций командной строки сценарий формирует имя источника данных (DSN) и устанавливает соединение с сервером:my $dsn = "DBI:mysql:";

$dsn .= ";database=$db_name" if $db_name;
$dsn .= ";host=$host_name" if $host_name;
$dsn .= ";port=$port_num" if $port_num;
$dsn .= ";mysql_socket=$socket_name" if $socket_name;
# читать группу параметров [client] из стандартного файла опций
$dsn .= ";mysql_read_default_group=client";
my $dbh = DBI->connect ($dsn, $user_name, $password,
{PrintError => 0, RaiseError => 1});

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

После установки соединения с MySQL сценарий готов к выполнению запроса и формированию вывода. Тут в игру вступает модуль Text::CSV_XS.


Сначала создаем объект CSV, вызывая функцию new(), которая принимает необязательный хеш опций, управляющих обработкой строк данных. Затем сценарий подготавливает и выполняет запрос, выводит строку заголовков столбцов (если пользователь указал опцию --labels) и выводит строки результирующего множества:

my $csv = Text::CSV_XS->new ({
sep_char => $delim,
quote_char => $quote,
escape_char => $quote,
eol => $eol,
binary => 1
});
my $sth = $dbh->prepare ($query);
$sth->execute ();
if ($labels) # вывод строки заголовков столбцов
{
$csv->combine (@{$sth->{NAME}}) or die "cannot process column labels\n";
print $csv->string ();
}
my $count = 0;
while (my @val = $sth->fetchrow_array ())
{
++$count;
$csv->combine (@val) or die "cannot process column values, row $count\n";
print $csv->string ();
}

Опции sep_char и quote_char вызова функции name() устанавливают последовательность разделителя столбцов и символ кавычки. Опция escape_char устанавливается в то же значение, что и quote_char, так что вхождения символа кавычки в значения данных будут продублированы в выводе. Опция eol задает последовательность конца строки. Обычно Text::CSV_XS поручает вам печать завершающих символов для строк вывода. Передавая new() значение eol не-undef, модуль автоматически добавляет это значение в каждуюстроку вывода. Опция binary используется для обработки значений данных, содержащих двоичные символы.

Заголовки столбцов доступны в $sth->{NAME} после вызова execute(). Каждая строка вывода формируется с использованием combine() и string(). Метод combine() принимает массив значений и преобразует их в соответствующим образом отформатированную строку. Метод string() возвращает строку, и мы можем печатать ее.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Диагностическая утилита для LOAD DATA
Извлечение и перестановка столбцов файлов данных
Импорт XML в MySQL
Импорт с помощью LOAD DATA и утилиты mysqlimport
Импорт файлов в формате CSV

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных