sqlitebiter 0.15.0

A CLI tool to convert CSV/Excel/HTML/JSON/LTSV/Markdown/SQLite/TSV/Google-Sheets to a SQLite database file.

sqlitebiter

Table of Contents

• Summary

  • Features

• Usage

  • Create SQLite database from files

  • Create SQLite database from URL

• Installation

  • Install via pip (recommended)

  • Installation for Debian/Ubuntu from a deb package

  • Installing executable files in Windows

  • Installation for macOS via Homebrew

• Dependencies

  • Mandatory Python package dependencies

  • Google Sheets dependencies (Optional)

  • Test dependencies

  • Misc

• Documentation

Summary

A CLI tool to convert CSV/Excel/HTML/JSON/LTSV/Markdown/SQLite/TSV/Google-Sheets to a SQLite database file.

Features

• Create a SQLite database file from:

  • File(s):

    • CSV

    • Microsoft Excel ^TM

    • HTML

    • JSON

    • Labeled Tab-separated Values (LTSV)

    • Markdown

    • Mediawiki

    • Space separated values (SSV)

    • SQLite

    • Tab separated values (TSV)

  • Google Sheets

  • URL (scrape data from web pages)

• Multi-byte character support

• Automatic file encoding detection

Usage

Create SQLite database from files

Create SQLite database from URL

Following is an example that convert HTML table tags within a web page to SQLite tables.

Example:

$ sqlitebiter -v url "https://en.wikipedia.org/wiki/Comparison_of_firewalls"
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html1 (Firewall TEXT, License TEXT, Costandusagelimits TEXT, OS TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html2 (Firewall TEXT, License TEXT, Cost TEXT, OS TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html3 (CanTarget TEXT, Changingdefaultpolicytoacceptrejectbyissuingasinglerule TEXT, IPdestinationaddresses TEXT, IPsourceaddresses TEXT, TCPUDPdestinationports TEXT, TCPUDPsourceports TEXT, EthernetMACdestinationaddress TEXT, EthernetMACsourceaddress TEXT, Inboundfirewallingress TEXT, Outboundfirewallegress TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html4 (Can TEXT, workatOSILayer4statefulfirewall TEXT, workatOSILayer7applicationinspection TEXT, ChangeTTLTransparenttotraceroute TEXT, ConfigureREJECTwithanswer TEXT, DMZdemilitarizedzoneallowsforsingleseveralhostsnottobefirewalled TEXT, Filteraccordingtotimeofday TEXT, RedirectTCPUDPportsportforwarding TEXT, RedirectIPaddressesforwarding TEXT, FilteraccordingtoUserAuthorization TEXT, TrafficratelimitQoS TEXT, Tarpit TEXT, Log TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html5 (Features TEXT, ConfigurationGUItextorbothmodes TEXT, RemoteAccessWebHTTPTelnetSSHRDPSerialCOMRS232 TEXT, Changeruleswithoutrequiringrestart TEXT, Abilitytocentrallymanageallfirewallstogether TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html6 (Features TEXT, Modularitysupportsthirdpartymodulestoextendfunctionality TEXT, IPS : Intrusion prevention system] TEXT, OpenSourceLicense TEXT, supports IPv6 ?] TEXT, ClassHomeProfessional TEXT, OperatingSystemsonwhichitruns TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html7 (Can TEXT, NAT44staticdynamicwoportsPAT TEXT, NAT64NPTv6 TEXT, IDSIntrusionDetectionSystem TEXT, VPNVirtualPrivateNetwork TEXT, AVAntiVirus TEXT, Sniffer TEXT, Profileselection TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html8 (vteFirewallsoftware TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html9 (A TEXT, B TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html10 (A TEXT, B TEXT)' table
[INFO] sqlitebiter url: convert 'https://en.wikipedia.org/wiki/Comparison_of_firewalls' to 'Comparison_of_firewalls_Wikipedia_html11 (A TEXT, B TEXT)' table
[INFO] sqlitebiter url: number of created tables: 11
[INFO] sqlitebiter url: database path: out.sqlite

Output:

sqlite> .schema
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html1' (Firewall TEXT, License TEXT, Costandusagelimits TEXT, OS TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html2' (Firewall TEXT, License TEXT, Cost TEXT, OS TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html3' (CanTarget TEXT, Changingdefaultpolicytoacceptrejectbyissuingasinglerule TEXT, IPdestinationaddresses TEXT, IPsourceaddresses TEXT, TCPUDPdestinationports TEXT, TCPUDPsourceports TEXT, EthernetMACdestinationaddress TEXT, EthernetMACsourceaddress TEXT, Inboundfirewallingress TEXT, Outboundfirewallegress TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html4' (Can TEXT, [workatOSILayer4statefulfirewall] TEXT, [workatOSILayer7applicationinspection] TEXT, ChangeTTLTransparenttotraceroute TEXT, ConfigureREJECTwithanswer TEXT, DMZdemilitarizedzoneallowsforsingleseveralhostsnottobefirewalled TEXT, Filteraccordingtotimeofday TEXT, RedirectTCPUDPportsportforwarding TEXT, RedirectIPaddressesforwarding TEXT, FilteraccordingtoUserAuthorization TEXT, TrafficratelimitQoS TEXT, Tarpit TEXT, Log TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html5' (Features TEXT, ConfigurationGUItextorbothmodes TEXT, [RemoteAccessWebHTTPTelnetSSHRDPSerialCOMRS232] TEXT, Changeruleswithoutrequiringrestart TEXT, Abilitytocentrallymanageallfirewallstogether TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html6' (Features TEXT, Modularitysupportsthirdpartymodulestoextendfunctionality TEXT, [IPS : Intrusion prevention system] TEXT, OpenSourceLicense TEXT, [supports IPv6 ?] TEXT, ClassHomeProfessional TEXT, OperatingSystemsonwhichitruns TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html7' (Can TEXT, [NAT44staticdynamicwoportsPAT] TEXT, [NAT64NPTv6] TEXT, IDSIntrusionDetectionSystem TEXT, VPNVirtualPrivateNetwork TEXT, AVAntiVirus TEXT, Sniffer TEXT, Profileselection TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html9' (A TEXT, B TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html10' (A TEXT, B TEXT);
CREATE TABLE 'Comparison_of_firewalls_Wikipedia_html11' (A TEXT, B TEXT);

For more information

More examples are available at http://sqlitebiter.rtfd.io/en/latest/pages/usage/index.html

Installation

Install via pip (recommended)

sqlitebiter can be installed via pip (Python package manager).

Example:

pip install sqlitebiter

Installation for Debian/Ubuntu from a deb package

1. wget https://github.com/thombashi/sqlitebiter/releases/download/<version>/sqlitebiter_<version>_amd64.deb

2. dpkg -iv sqlitebiter_<version>_amd64.deb

Example:

$ wget https://github.com/thombashi/sqlitebiter/releases/download/v0.12.0/sqlitebiter_0.12.0_amd64.deb
$ sudo dpkg -i sqlitebiter_0.12.0_amd64.deb

Installing executable files in Windows

sqlitebiter can be used in Windows environments without Python installation as follows:

1. Navigate to https://github.com/thombashi/sqlitebiter/releases

2. Download the latest version of the sqlitebiter_win_x64.zip

3. Unzip the file

4. Execute sqlitebiter.exe in either Command Prompt or PowerShell

>cd sqlitebiter_win_x64
>sqlitebiter.exe -h
Usage: sqlitebiter.exe [OPTIONS] COMMAND [ARGS]...

Options:
  --version         Show the version and exit.
  -a, --append      append table(s) to existing database.
  -i, --index TEXT  comma separated attribute names to create indices.
  -v, --verbose
  --debug           for debug print.
  --quiet           suppress execution log messages.
  -h, --help        Show this message and exit.

Commands:
  configure  Configure the following application settings:...
  file       Convert tabular data within...
  gs         Convert a spreadsheet in Google Sheets to a...
  url        Scrape tabular data from a URL and convert...

Installation for macOS via Homebrew

$ brew tap thombashi/sqlitebiter
$ brew install sqlitebiter

• Homebrew Formula

Dependencies

Python 2.7+ or 3.4+

Python package dependencies are as follows.

Mandatory Python package dependencies

Following mandatory Python packages are automatically installed during sqlitebiter installation process:

• appconfigpy

• click

• logbook

• path.py

• pytablereader

• SimpleSQLite

• sqliteschema

• typepy

Google Sheets dependencies (Optional)

Following Python packages are required to manual installation when you use Google Sheets feature:

• gspread

• oauth2client

• pyOpenSSL

Test dependencies

• pytablewriter

• pytest

• pytest-runner

• responses

• tox

Misc

• lxml (Faster HTML convert if installed)

Documentation

http://sqlitebiter.rtfd.io/