В индекс пакетов 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
Обратите внимание, что при использовании этого поведения, загружается весь набор данных в память, следовательно для больших наборов данных это может быть очень медленным.
.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
, но также можно использовать утилиту 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
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
.sql2csv
:--db CONNECTION_STRING
- cтрока подключения к базе данных (как в sqlalchemy
).--query QUERY
- SQL-запрос для выполнения. -e ENCODING, --encoding ENCODING
- кодировка базы данных.-H, --no-header-row
- не выводить имена столбцов.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
- После вырезания удаляет полностью пустые строки.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
- выводить подробные трассировки при возникновении ошибок.