Сообщить об ошибке.

Импорт CSV-файла в MySQL таблицу, экспорт данных в CSV

В индекс пакетов Python (PyPI) есть модуль csvkit, который представляет собой набор инструментов командной строки (CLI) для различных преобразований, анализа и просто работы с CSV-файлами.

Утилиты csvsql и sql2csv модуля csvkit образуют мост, который упрощает миграцию данных из CSV-файла в базу данных и из нее. По умолчанию csvsql сгенерирует SQL-код для создания таблицы для данных CSV-файла. Можно указать желаемую базу данных (для которой генерируется код создания таблицы) после флага -i:

$ csvsql -i mysql data.csv
# CREATE TABLE data (
#        state VARCHAR(2) NOT NULL, 
#        county VARCHAR(10) NOT NULL, 
#        item_name VARCHAR(62), 
#        total_cost DECIMAL(38, 2) NOT NULL, 
#        ship_date DATE NOT NULL, 
#        federal_supply_category_name VARCHAR(35) NOT NULL, 
#        federal_supply_class_name VARCHAR(63) NOT NULL
# );

Утилита csvsql анализирует данные, содержащиеся в столбцах CSV-файла и сопоставляет из с типами MYSQL. При этом первая строка CSV-файла (имена столбцов) используется в качестве имен полей в таблице.

Также можно использовать csvsql для создания таблицы непосредственно в базе данных. Если добавить параметр --insert, то данные также будут импортированы непосредственно в базу:

csvsql --db mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> --insert data.csv

Если база данных создается с целью анализа данных и необходимо выполнить только несколько SQL-запросов, то и построение базы данных будет пустой тратой времени. В этом случае можно полностью пропустить создание базы данных, так как csvsql создаст ее в памяти:

$ csvsql --query "select county,item_name,total_cost \
> from data where total_cost > 5;" data.csv | csvlook | less

Имейте в виду, что при использовании этого поведения, загружается весь набор данных в память, поэтому для больших наборов данных это может быть очень медленным.

Как можно проверить, что данные были успешно импортированы? Можно использовать интерфейс командной строки mysql, но также можно использовать утилиту sql2csv:

$ sql2csv --db mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> --query "select * from data;"

Строка подключения к базе данных (параметр --db) указывается по правилам, описанном в модуле sqlalchemy.

Обратите внимание, что параметр --query для sql2csv принимает любой запрос SQL. Например, чтобы экспортировать данные, в которых county='LANCASTER' из базы данных sqlite, необходимо выполнить:

$ sql2csv --db mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname> \
> --query "select * from data where county='LANCASTER';" > LANCASTER.csv

Параметры CLI утилиты csvsql:

  • -i {firebird,mssql,mysql,oracle,postgresql,sqlite,sybase,crate} - Диалект SQL для генерации. Не может использоваться с --db.
  • --db CONNECTION_STRING - Если присутствует, то это строка подключения SQLAlchemy к БД, используемая для непосредственного выполнения сгенерированного SQL в базе данных.
  • --query QUERY - Выполняет один или несколько SQL-запросов, разделенных символом ';' и выводит результат последнего запроса в формате CSV. QUERY может быть именем файла.
  • --insert - Вставляет данные в таблицу. Требует строку подключения --db.
  • --prefix PREFIX - Добавляет выражение после ключевого слова INSERT, например OR IGNORE или OR REPLACE.
  • --before-insert BEFORE_INSERT - Выполняет SQL перед командой INSERT. Требует параметр --insert.
  • --after-insert AFTER_INSERT - Выполняет SQL после команды INSERT. Требует параметр --insert.
  • --tables TABLE_NAMES - Разделенный запятыми список имен создаваемых таблиц. По умолчанию таблицы будут называться по именам файлов без расширений.
  • --no-constraints - Создает схему без ограничений по длине данных или проверок на null. Полезно при выборке больших таблиц.
  • --unique-constraint UNIQUE_CONSTRAINT - Разделенный по столбцам список имен столбцов для включения в ограничение UNIQUE.
  • --no-create - Пропускает создание таблицы. Требует параметр --insert.
  • --create-if-not-exists - Создает таблицу, если она не существует, в противном случае продолжает работу. Требуется параметр --insert.
  • --overwrite - Удаляет таблицу, если она уже существует. Требуется параметр --insert. Нельзя использовать с --no-create.
  • --db-schema DB_SCHEMA - Необязательное имя схемы базы данных для создания таблиц.
  • --chunk-size CHUNK_SIZE - Размер данных для пакетной вставки в таблицу. Требует --insert.

Параметры CLI утилиты sql2csv:

  • --db CONNECTION_STRING - cтрока подключения к базе данных (как в sqlalchemy).
  • --query QUERY - SQL-запрос для выполнения.
  • -e ENCODING, --encoding ENCODING - кодировка базы данных.
  • -H, --no-header-row - не выводить имена столбцов.