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

Импорт CSV и XLSX(XLS) файла в sqlite3, экспорт данных в CSV

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

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

$ csvsql -i sqlite data.csv
# CREATE TABLE data (
#         state VARCHAR NOT NULL, 
#         county VARCHAR NOT NULL, 
#         item_name VARCHAR, 
#         quantity FLOAT NOT NULL, 
#         total_cost FLOAT NOT NULL, 
#         ship_date DATE NOT NULL, 
#         federal_supply_category_name VARCHAR NOT NULL, 
# );

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

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

$ csvsql --db sqlite:///data.db --insert data.csv

Если таблица в БД SQLite3 должна содержать только несколько колонок, содержащихся в CSV-файле, то необходимо воспользоваться утилитой csvcut. Утилита csvcut это оригинальный инструмент модуля csvkit. С его помощью можно выбирать, удалять и изменять порядок отображения столбцов.

$ csvsql --db sqlite:///data.db --insert <(csvcut -c 2,5,6 data.csv)

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

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

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

Импорт таблицы Excel файла (.xls или .xlsx) в БД SQLite3.

Для создания таблицы в БД SQLite3 их Excel файлов (.xls или .xlsx) воспользуемся утилитой in2csv, которая также входит в пакет csvkit. Утилита in2csv преобразует различные форматы табличных данных в CSV-формат (dbf, fixed, geojson, json, ndjson, xls, xlsx).

Все инструменты модуля csvkit принимают входной файл через "стандартный ввод". Это означает, что с помощью символа | ("канал"), можно использовать вывод одного инструмента csvkit в качестве ввода следующего. Следовательно, создать таблицу в БД SQLite3 их Excel файла можно следующей командой:

csvsql --db sqlite:///data.db --insert <(in2csv sample.xlsx)

Экспорт данных из БД SQLite3 в CSV-формат.

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

$ sql2csv --db sqlite:///data.db --query "select * from data;"

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

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

$ sql2csv --db sqlite:///data.db --query \
> "select * from data where county='LANCASTER';" > LANCASTER.csv

Справочные сведения по параметрам CLI.

Параметры 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 - не выводить имена столбцов.

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

  • -n, --names - Выводит имена столбцов и индексы из входного CSV-файла и выходит.
  • -c COLUMNS, --columns COLUMNS - Разделенный запятыми список индексов столбцов, имен или диапазонов, подлежащих извлечению, например "1,id,3-5". По умолчанию все столбцы.
  • -C NOT_COLUMNS, --not-columns NOT_COLUMNS - Разделенный запятыми список индексов столбцов, имен или диапазонов, подлежащих исключению, например "1,id,3-5". По умолчанию нет
  • -x, --delete-empty-rows - После вырезания удаляет полностью пустые строки.

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

  • -f {csv,dbf,fixed,geojson,json,ndjson,xls,xlsx} - Формат входного файла. Если не указано, будет определяться из типа файла.
  • -s SCHEMA, --schema SCHEMA - файл схемы в формате CSV для преобразования файлов фиксированной ширины.
  • -k KEY, --key KEY - ключ верхнего уровня для поиска списка объектов, подлежащих преобразованию при обработке JSON.
  • -n, --names - отображение имен листов из входного файла Excel.
  • --sheet SHEET - имя листа Excel для преобразования.
  • --write-sheets WRITE_SHEETS - имена листов Excel для записи в файлы или "-" для записи всех листов.
  • --encoding-xls ENCODING_XLS - кодировка входного XLS-файла.
  • --blanks - не приводить пустые строки 'na', 'n/a', 'none', 'null', '.' к значениям NULL.
  • --date-format DATE_FORMAT - строка формата даты date.strptime, например '%m/%d/%Y'.
  • --datetime-format DATETIME_FORMAT - строка формата даты и времени datetime.strptime, например '%m/%d/%Y %I:%M %p'.
  • -H, --no-header-row - во входном CSV-файле нет строки заголовка. Создаст заголовки по умолчанию (a,b,c,...).
  • -K SKIP_LINES, --skip-lines SKIP_LINES - количество начальных строк, которые нужно пропустить перед строкой заголовка (например, комментарии, уведомления об авторских правах, пустые строки).
  • -v, --verbose - выводить подробные трассировки при возникновении ошибок.