SQLitely

Index
Downloads
Installation
Screenshots
Help Texts
Search syntax
Command-line help
Changelog
View the Project on GitHub suurjaak/SQLitely

Help Texts

Search Syntax

SQLitely supports a simple query syntax for searching the database:

Search for all words `this andthis alsothis`	Row is matched if each word finds a match in at least one column.
Search for exact word or phrase `"do re mi"`	Use quotes (`"`) to search for an exact phrase or word. Quoted text is searched exactly as entered, leaving empty space as-is and ignoring any wildcard characters.
Search for either word `this OR that`	To find results containing at least one of several words, include `OR` between the words. `OR` works also for phrases and grouped words (but not keywords).
Group words together `(these two) OR this -(none of these)`	Surround words with round brackets to group them for `OR` queries, or for excluding from results.
Search for partially matching text `bas*ball`	Use an asterisk (`*`) to make a wildcard query: the wildcard will match any text between its front and rear characters (including empty space and other words).
Exclude words or keywords `-notthisword -"not this phrase" -(none of these) -table:notthistable -date:2013`	To exclude certain results, add a dash (`-`) in front of words, phrases, grouped words or keywords.
Search specific tables `table:fromthistable view:fromthisview -table:notfromthistable -view:notfromthisview`	Use the keyword `table:name` or `view:name` to constrain results to specific tables and views only. Search from more than one source by adding more `table:` or `view:` keywords, or exclude certain sources by adding a `-table:` or `-view:` keyword.
Search specific columns `column:fromthiscolumn -column:notfromthiscolumn`	Use the keyword `column:name` to constrain results to specific columns only. Search from more than one column by adding more `column:` keywords, or exclude certain columns by adding a `-column:` keyword.
Search from specific time periods `date:2008 date:2009-01 date:2005-12-24..2007`	To find rows from specific time periods (where source has DATE/DATETIME/TIMESTAMP columns), use the keyword `date:period` or `date:periodstart..periodend`. For the latter, either start or end can be omitted. A date period can be year, year-month, or year-month-day. Additionally, `date:period` can use a wildcard in place of any part, so `date:*-12-24` would search for all rows having a timestamp from the 24th of December. Search from a more narrowly defined period by adding more `date:` keywords.

Examples

search for "domain.com" in columns where name contains "url":
domain.com column:url
search for "foo bar" from rows where date column values predate 2012:
"foo bar" date:..2011
search for either "John" and "my side" or "Stark" and "your side":
(john "my side") OR (stark "your side")
search for either "birthday" or "cake" from columns where column name contains "message", except where table name contains "log":
birthday OR cake column:message -table:log
search for "TPS report" but not "my TPS report" from rows where date column values are the first day of the month in 2012:
"tps report" -"my tps report" date:2012-*-1

All search texts and keywords are case-insensitive by default.
Keywords are global, even when in bracketed (grouped words).
Metadata search supports only table: and view: keywords, and does not support OR queries.

Note: for excluding words or keywords in the command-line interface,
use the tilde character (~) instead of the dash (-).

Command-Line Help

Command-line interface does not require having wx installed, if running from source code.
It is likewise available with compiled binaries.

$ sqlitely -h

usage: sqlitely [-h] [--verbose] {export,search,sync,merge,diff,gui} ...

SQLitely - SQLite database tool.

positional arguments:
  {execute,export,import,parse,search,stats,gui}
    execute             run SQL statements in SQLite database
    export              export SQLite database in various output formats
    gui                 launch SQLitely graphical program (default option)
    import              import data from file to database
    parse               search in SQLite database schema
    search              search in SQLite database data
    stats               print or save database statistics

optional arguments:
  -h, --help            show this help message and exit
  -v, --version         show program's version number and exit
  --verbose             print detailed logging messages to stderr
  --config-file FILE    path of program configuration file to use

$ sqlitely -h execute

usage: sqlitely execute [-h] [-p PARAM [PARAM ...]] [--create] [-o [FILE]]
                        [-f {csv,db,html,json,sql,txt,xlsx,yaml}] [--overwrite]
                        [--allow-empty] [--progress] [--verbose] [--config-file FILE]
                        DATABASE SQL

Run given SQL in an SQLite database,
query results printed to console or written to file.

positional arguments:
  DATABASE              SQLite database file to run SQL in
  SQL                   SQL text to execute, with one or more statements,
                        or a path to file with SQL statements;
                        may contain parameter placeholders like ? or :name

optional arguments:
  -h, --help            show this help message and exit
  -p PARAM [PARAM ...], --param PARAM [PARAM ...]
                        positional or keyword parameters for SQL,
                        keywords as name=value
  --create              create DATABASE if file does not exist yet
  -o [FILE], --output [FILE]
                        write query output to file instead of printing to console;
                        filename will be auto-generated if not given;
                        automatic for non-printable formats (db,html,xlsx)
  -f {csv,db,html,json,sql,txt,xlsx,yaml}, --format {csv,db,html,json,sql,txt,xlsx,yaml}
                        output format for query results:
                          csv:   CSV spreadsheet
                          db:    SQLite database
                          html:  HTML document
                          json:  JSON data
                          sql:   SQL statements
                          txt:   text document (default)
                          xlsx:  Excel workbook
                          yaml:  YAML data
                        (auto-detected from output filename if not specified)
  --overwrite           overwrite output file if already exists
                        (by default appends unique counter to filename)
  --allow-empty         write output file even if query has no results
  --progress            display progress bar
  --verbose             print detailed logging messages to stderr
  --config-file FILE    path of program configuration file to use

$ sqlitely -h export

usage: sqlitely export [-h] [-o [FILE]] [-f {csv,db,html,json,sql,txt,xlsx,yaml}]
                       [-p DIR] [--combine] [--overwrite] [--no-empty]
                       [--include-related] [--select SELECT [SELECT ...]] [--limit NUM]
                       [--offset NUM] [--reverse] [--max-count NUM] [--progress]
                       [--verbose] [--config-file FILE]
                       DATABASE

Export data or schema from an SQLite database,
printed to console or written to file.

positional arguments:
  DATABASE              SQLite database to export

optional arguments:
  -h, --help            show this help message and exit
  -o [FILE], --output [FILE]
                        write output to file instead of printing to console;
                        filename will be auto-generated if not given;
                        used as prefix if not --combine;
                        automatic for non-printable formats (db,html,xlsx)
  -f {csv,db,html,json,sql,txt,xlsx,yaml},
  --format {csv,db,html,json,sql,txt,xlsx,yaml}
                        export format:
                          csv:   CSV spreadsheet
                          db:    SQLite database
                          html:  HTML document
                          json:  JSON data
                          sql:   SQL statements (default)
                          txt:   text document
                          xlsx:  Excel workbook
                          yaml:  YAML data
                        (auto-detected from output filename if not specified)
  -p DIR, --path DIR    output file directory if not current directory
  --combine             combine all outputs into a single file,
                        instead of each table or view to a separate file;
                        automatic if exporting to another database.
  --overwrite           overwrite output file if already exists
                        (by default appends unique counter to filename)
  --no-empty            skip empty tables and views from output altogether
                        (affected by offset and limit)
  --include-related     include related entities:
                        foreign tables and view dependencies if using --select,
                        and indexes and triggers if exporting to database or SQL
  --select SELECT [SELECT ...]
                        names of specific entities to export or skip
                        (supports * wildcards; initial ~ skips)
  --limit NUM           maximum number of rows to export per table or view
  --offset NUM          number of initial rows to skip from each table or view
  --reverse             query table rows in reverse ROWID/PK order,
                        view rows in reverse row_number() order
  --max-count NUM       maximum total number of rows to export over all tables and views
  --progress            display progress bar
  --verbose             print detailed logging messages to stderr
  --config-file FILE    path of program configuration file to use

$ sqlitely -h gui

usage: sqlitely gui [-h] [--config-file FILE] [DATABASE [DATABASE ...]]

Launch SQLitely graphical program (default option)

positional arguments:
  DATABASE            SQLite database(s) to open on startup, if any
                      (supports * wildcards)

optional arguments:
  -h, --help          show this help message and exit
  --config-file FILE  path of program configuration file to use

$ sqlitely -h import

usage: sqlitely import [-h] [--select SELECT [SELECT ...]] [--row-header] [--no-empty]
                       [--columns COLUMNS] [--table-name NAME] [--create-always]
                       [--add-pk [NAME]] [--assume-yes] [--limit NUM] [--offset NUM]
                       [--max-count NUM] [--progress] [--verbose] [--config-file FILE]
                       INFILE DB

Import data from files to a new or existing SQLite database.
Prompts for confirmation.

positional arguments:
  INFILE                file to import from.
                        Supported extensions: {csv,json,xls,xlsx,yaml,yml}.
  DB                    SQLite database to import to, will be created if not present

optional arguments:
  -h, --help            show this help message and exit
  --select SELECT [SELECT ...]
                        names of specific Excel worksheets to import or skip
                        (supports * wildcards; initial ~ skips)
  --row-header          use first row of input spreadsheet for column names
  --no-empty            skip spreadsheets with no content rows
                        (affected by offset and limit)
  --columns COLUMNS     columns to import if not all,
                        as a comma-separated list of columns or column ranges,
                        range as START..END or START.. or ..END
                        (no START: from first, no END: until last).
                        Columns can be indexes starting from 1
                        or spreadsheet column labels like AB,
                        must be column names if import from JSON/YAML.
  --table-name NAME     name of table to import into, defaults to file base name
                        or worksheet name if Excel spreadsheet
  --create-always       create new table even if a matching table already exists
  --add-pk [NAME]       add auto-increment primary key column to created tables;
                        defaults to "id" if name not specified
  --assume-yes          skip confirmation prompt for starting import
  --limit NUM           maximum number of rows to import, per table
  --offset NUM          number of initial rows to skip from each table
  --max-count NUM       maximum total number of rows to import over all tables
  --progress            display progress bar
  --verbose             print detailed logging messages to stderr
  --config-file FILE    path of program configuration file to use

$ sqlitely -h parse

usage: sqlitely parse [-h] [--case] [-o [FILE]] [--overwrite] [--limit NUM] [--offset NUM]
                      [--reverse] [--verbose] [--config-file FILE]
                      DATABASE [FILTER [FILTER ...]]

Parse and search database schema CREATE SQL.

positional arguments:
  DATABASE              SQLite database file to parse
  FILTER                search text if any, with simple query syntax, for example:
                        "each word present" or "fk_* trigger:on_insert_*".
                        More at search syntax.

optional arguments:
  -h, --help            show this help message and exit
  --case                case-sensitive search
  -o [FILE], --output [FILE]
                        write output to SQL file instead of printing to console;
                        filename will be auto-generated if not given
  --overwrite           overwrite output file if already exists
                        (by default appends unique counter to filename)
  --limit NUM           maximum number of matches to find
  --offset NUM          number of initial matches to skip
  --reverse             find matches in reverse order
  --verbose             print detailed logging messages to stderr
  --config-file FILE    path of program configuration file to use

$ sqlitely -h search

usage: sqlitely search [-h] [--case] [-o [FILE]] [-f {csv,db,html,json,sql,txt,xlsx,yaml}]
                       [-p DIR] [--combine] [--overwrite] [--limit NUM] [--offset NUM]
                       [--reverse] [--max-count NUM] [--no-empty] [--progress] [--verbose]
                       [--config-file FILE]
                       DATABASE FILTER [FILTER ...]

Search over all columns of all rows in an SQLite database,
matches printed to console or written to file.

positional arguments:
  DATABASE              SQLite database file to search
  FILTER                search text, with simple query syntax, for example:
                        "each word in some col" or "this OR that column:foo*".
                        More at search syntax.

optional arguments:
  -h, --help            show this help message and exit
  --case                case-sensitive search
  -o [FILE], --output [FILE]
                        write output to file instead of printing to console;
                        filename will be auto-generated if not given;
                        used as prefix if not --combine;
                        automatic for non-printable formats (db,html,xlsx)
  -f {csv,db,html,json,sql,txt,xlsx,yaml}, --format {csv,db,html,json,sql,txt,xlsx,yaml}
                        output format:
                          csv:   CSV spreadsheet
                          db:    SQLite database
                          html:  HTML document
                          json:  JSON data
                          sql:   SQL statements
                          txt:   text document (default)
                          xlsx:  Excel workbook
                          yaml:  YAML data
                        (auto-detected from output filename if not specified)
  -p DIR, --path DIR    output file directory if not current directory
  --combine             combine all outputs into a single file,
                        instead of each table or view to a separate file;
                        automatic if exporting to another database.
  --overwrite           overwrite output file if already exists
                        (by default appends unique counter to filename)
  --limit NUM           maximum number of matches to find from each table or view
  --offset NUM          number of initial matches to skip from each table or view
  --reverse             query table matches in reverse ROWID/PK order,
                        view matches in reverse row_number() order
  --max-count NUM       maximum total number of rows to export over all tables and views
  --no-empty            skip empty tables and views from output altogether
                        (affected by offset and limit and search text)
  --progress            display progress bar
  --verbose             print detailed logging messages to stderr
  --config-file FILE    path of program configuration file to use

$ sqlitely -h stats

usage: sqlitely stats [-h] [-o [FILE]] [-f {html,sql,txt}] [--disk-usage] [--overwrite]
                      [--start-file] [--progress] [--verbose] [--config-file FILE]
                      DATABASE

Analyze an SQLite database and produce statistics as HTML, SQL or text.

positional arguments:
  DATABASE              SQLite database to analyze

optional arguments:
  -h, --help            show this help message and exit
  -o [FILE], --output [FILE]
                        write output to file instead of printing to console,
                        filename will be auto-generated if not given;
                        auto-populated for non-printable formats (html)
  -f {html,sql,txt}, --format {html,sql,txt}
                        output format:
                          html:  HTML document
                          sql:   SQL statements
                          txt:   text document (default)
                        (auto-detected from output filename if not specified)
  --disk-usage          count bytes of disk usage per table and index
                        (enabled by default in SQL output)
  --overwrite           overwrite output file if already exists
                        (by default appends unique counter to filename)
  --start-file          open output file with registered program
  --progress            display progress bar
  --verbose             print detailed logging messages to stderr
  --config-file FILE    path of program configuration file to use