CSV and SQLite tools overview¶
Tools from csvkit¶
csvkit documentation
- in2csv - convert from other formats into CSV
- csvlook - pretty print csv output
- csvsql
- csvcut - slicing csv by columns
- csvgrep - slicing csv by rows
Tools from sqlitebiter¶
sqlitebiter documentation
Take data from various sources and input it into a sqlite database
In [ ]:
Copied!
%%bash
sqlitebiter --help
%%bash
sqlitebiter --help
Usage: sqlitebiter [OPTIONS] COMMAND [ARGS]...
Options:
--version Show the version and exit.
-o, --output-path PATH Output path of the SQLite database file.
Defaults to 'out.sqlite'.
-a, --append Append table(s) to existing database.
--add-primary-key PRIMARY_KEY_NAME
Add 'PRIMARY KEY AUTOINCREMENT' column with
the specified name.
--convert-config TEXT [experimental] Configurations for data
conversion. The option can be used only for
url subcommand.
-i, --index INDEX_ATTR Comma separated attribute names to create
indices.
--no-type-inference All of the columns assume as TEXT data type
in creating tables.
--type-hint-header Use headers suffix as type hints. If there
are type hints, converting columns by
datatype corresponding with type hints. The
following suffixes can be recognized as type
hints (case insensitive): "text": TEXT
datatype. "integer": INTEGER datatype.
"real": REAL datatype.
--replace-symbol TEXT Replace symbols in attributes.
-v, --verbose
--max-workers WORKERS Specify maximum number of workers that the
command may use. defaults to 1.
--debug For debug print.
-q, --quiet Suppress execution log messages.
-h, --help Show this message and exit.
Commands:
completion A helper command to setup command completion.
configure Configure the following application settings: (1) Default...
file Convert tabular data within CSV/Excel/HTML/JSON/Jupyter...
gs Convert a spreadsheet in Google Sheets to a SQLite database...
stdin Convert tabular data within CSV/HTML/JSON/Jupyter...
url Scrape tabular data from a URL and convert data to a SQLite...
version Show version information
Tools from sqlite-utils¶
sqlite-utils documentation
command-line tool can be used to manipulate SQLite databases in a number of different ways
- sqlite-utils
In [ ]:
Copied!
%%bash
sqlite-utils --help
%%bash
sqlite-utils --help
Usage: sqlite-utils [OPTIONS] COMMAND [ARGS]... Commands for interacting with a SQLite database Options: --version Show the version and exit. --help Show this message and exit. Commands: query* Execute SQL query and return the results as JSON add-column Add a column to the specified table add-foreign-key Add a new foreign key constraint to an existing table. add-foreign-keys Add multiple new foreign key constraints to a... analyze-tables Analyze the columns in one or more tables create-index Add an index to the specified table covering the... create-table Add an index to the specified table covering the... create-view Create a view for the provided SELECT query disable-fts Disable FTS for specific table disable-wal Disable WAL for database files drop-table Drop the specified table drop-view Drop the specified view enable-counts Configure triggers to update a _counts table with row... enable-fts Enable FTS for specific table and columns enable-wal Enable WAL for database files extract Extract one or more columns into a separate table index-foreign-keys Ensure every foreign key column has an index on it. insert Insert records from JSON file into a table, creating... insert-files Insert one or more files using BLOB columns in the... optimize Optimize all FTS tables and then run VACUUM - should... populate-fts Re-populate FTS for specific table and columns rebuild-fts Rebuild all or specific FTS tables reset-counts Reset calculated counts in the _counts table rows Output all rows in the specified table search Execute a full-text search against this table tables List the tables in the database transform Transform a table beyond the capabilities of ALTER... triggers Show triggers configured in this database upsert Upsert records based on their primary key. vacuum Run VACUUM against the database views List the views in the database
In [ ]:
Copied!
Created in