dbcli/litecli

CLI for SQLite Databases with auto-completion and syntax highlighting

litecli's Issues

Add support for NULLS FIRST and NULLS LAST in ORDER BY clauses

As of SQLite Release 3.30.1 ORDER BY includes the NULLS FIRST and NULLS LAST options to express where to put NULL-rows. Usage examples of this syntax can be found here.

I'm dabbling with databases and what a joy litecli is compared to the sqlite3 shell. If I'm not mistaken, the above feature is missing from the current version of litecli (1.3.2) and would be nice to have :)

Kind Regards

Auto-completion doesn't work when run without db name

When you run without passing db name (litecli (no db argument)), auto-completion doesn't work.

Affected rows is -1

Create table results displays -1 as affected rows.

sqlite3 amjith::memory:> create table foo (id integer, name varchar);
Query OK, -1 rows affected

Database file will open if provided on cli start, but not with `.open`

On cli start:

--- ~ » litecli ~/repos/engineering/rebuilds.sqlite
Version: 0.0.1
Mail: https://groups.google.com/forum/#!forum/litecli-users
Github: https://github.com/dbcli/litecli
/Users/irina/repos/engineering/rebuilds.sqlite>

When using .open:

(none)> .open ~/repos/engineering/rebuilds.sqlite
unable to open database file
(none)> .open "~/repos/engineering/rebuilds.sqlite"
unable to open database file
(none)> .open "/Users/irina/repos/engineering/rebuilds.sqlite"
unable to open database file

Exception in log:

2018-11-30 08:49:12,254 (3880/MainThread) litecli.main ERROR - traceback: 'Traceback (most recent call last):\n File "/Users/irina/.pyenv/versions/3.6.5/lib/python3.6/site-packages/litecli/main.py", line 455, in one_iteration\n for title, cur, headers, status in res:\n File "/Users/irina/.pyenv/versions/3.6.5/lib/python3.6/site-packages/litecli/sqlexecute.py", line 110, in run\n for result in special.execute(cur, sql):\n File "/Users/irina/.pyenv/versions/3.6.5/lib/python3.6/site-packages/litecli/main.py", line 197, in change_db\n self.sqlexecute.connect(database=arg)\n File "/Users/irina/.pyenv/versions/3.6.5/lib/python3.6/site-packages/litecli/sqlexecute.py", line 57, in connect\n conn = sqlite3.connect(database=db, isolation_level=None)\nsqlite3.OperationalError: unable to open database file\n'

Support CSV import

SQLite allows users to import data from CSV files:

$ cat foo.csv
one,two,three
1,2,3
4,5,6

$ sqlite3 foo.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import foo.csv foo
sqlite> .schema foo
CREATE TABLE foo(
  "one" TEXT,
  "two" TEXT,
  "three" TEXT
);
sqlite> select * from foo;
1,2,3
4,5,6
sqlite>

However, LiteCLI gives a syntax error:

$ litecli foo.db
Version: 1.2.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
GitHub: https://github.com/dbcli/litecli
foo.db> .mode csv
Changed table format to csv
Time: 0.000s
foo.db> .import foo.csv foo
near ".": syntax error
foo.db>

Aside from ., CSV paths may include other unexpected characters like : and /, e.g. in this example from the official documentation:

sqlite> .mode csv
sqlite> .import C:/work/somedata.csv tab1

This likely affects CSV export as well.

Can CSV import (and, ideally, output) support be added to LiteCLI?

Suggestion: shift+tab to start completion in the opposite direction

Right now one has to hit "tab" first and then "shift+tab" to go to opposite direction.

When not connected to database, help does not work

--- ~ » litecli
Version: 0.0.1
Mail: https://groups.google.com/forum/#!forum/litecli-users
Github: https://github.com/dbcli/litecli
(none)> \?
Not connected to database.
(none)>

Arch AUR distribution

I've added both the current master and the latest tag versions of this program to Arch Linux's user repository (AUR). You may add that to the Installation section of the readme.

The packages are:
https://aur.archlinux.org/packages/litecli/
and
https://aur.archlinux.org/packages/litecli-git/

Specifying a command to install AUR packages is kind of a taboo in Arch circles. The general wisdom is to git clone the package off of AUR and then makepkg it yourself, but there are so-called AUR-helpers that automate the process, provide easy updates and so on. One of them is yay, but adding that might enrage some purists and they may say you're pushing yay on them (when there are other helpers and the manual method).

To install the packages with yay, you'd write:

yay -S litecli

or

yay -S litecli-git

Snap [Feature Request]

I was wondering if you might have an interest in a snap version. I would help make the snap and submit a PR if there's any interest now or in the furture.
Crafting your first snap

Does not work with CSV vtable.

./litecli mycool.db

Version: 1.3.2

.load /home/ubuntu/src/sqlite-csv-vtable/csv
CREATE VIRTUAL TABLE temp.t1 USING csv(filename="sample.csv");' 'SELECT * FROM t1;

Segmentation fault

The documentation is hard to find

The project documentation is hard to find.

There's no reference in the README, there's no link in the GitHub project, and it seems that http(s)://litecli.org is not indexed in Google.

I think at least a link in the project would help.

upgrade to prompt_toolkit 2.0

prompt_toolkit 2.0 is now pulled in by pgcli and IPython which in turn results in litecli failing to start.

See dbcli/pgcli#826

When there's no rows to output, headers should still show up

When displaying an empty recordset, litecli is not consistent with other clis.

litecli behavior:

/Users/irina/repos/engineering/rebuilds.sqlite> create table abc(a text)
Query OK, 0 rows affected
Time: 0.002s
/Users/irina/repos/engineering/rebuilds.sqlite> select * from abc
0 rows in set
Time: 0.000s

pgcli behavior:

irina@/tmp:test> create table abc (a text)
CREATE TABLE
Time: 0.022s
irina@/tmp:test> select * from abc
+-----+
| a   |
|-----|
+-----+
SELECT 0
Time: 0.012s

SQLcipher support

It would be nice to be able to use this for SQLcipher databases. I think the best way for this would be if we could point you at a specific shared library that exposes the same functions with the same arguments as sqlite (or, if it's easier, an executable compatible with the sqlite3 CLI) as a CLI argument.

Unable to save favorite query

> \fs foo select
'ascii' codec can't decode byte 0xe2 in position 52: ordinal not in range(128)

[Documentation] Clarification of the completion key(s)?

• OS: Linux (Arch)
• Python: 3.7.4
• litecli: 1.1.0

This is a great tool (thanks!), but I was deterred from considering it by the references to completing with the "Right-arrow" key in the documentation (and UI). I'm used to completing with the standard GNU Readline Tab key (as in sqlite) and never use arrow keys for completion and don't use any tools which do by default (it's been a while since I last tried fish).

I then came here (after looking through the documentation on the site) to see if there's any way to override this setting but couldn't find one, so I looked elsewhere. (Actually, I tried to install it anyway to see if I could live with the weird key, but couldn't get it to install on Arch Linux.)

After getting frustrated with the default SQLite CLI again, I decided to give this another go and was delighted to find that tab-completion works as expected for keywords and identifiers, and that the arrow key is only used to complete history suggestions 👍.

Now that I know this, the documentation makes perfect sense, but it might be worth tweaking it slightly to avoid giving the impression that the right-arrow key is required for all completions.

"exit" command doesn't work when not connected to database

It raises an error (Not connected to database.) instead of exiting from the program.

history_file directive ignored in ini

The history_file directive in the ini file is ignored. Litecli always writes to the default location.

The bottom part of the screen is constantly empty

On the terminal of my machine (MATE Terminal 1.20.0), the 7 bottom lines (except the last one, which is the status bar) are constantly blank.

This looks odd, and it's also a waste of space.

I can reproduce it all the time. I use a pretty much standard configuration, with the pager disabled.

If I enable the pager, the pager output fills the entire screen, however, when I exit it, the blank space it's there again.

I have the suspicion that this is caused by the autocompletion menu "reserving" that space. At a very brief check of the source code, it seems that suggestions can't be disabled, so I can't test straight away if this is the cause.

Inconsistent behavior when path does not exist

If I open litecli providing a non-existent file name, but the directory exists, litecli will create a new database:

--- ~ » litecli ~/nonexistent-file.sqlite                                                                                                                                                                                                                1 ↵
Version: 1.0.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
Github: https://github.com/dbcli/litecli
/Users/irina/nonexistent-file.sqlite>

However, if the path does not exists, litecli will throw an error:

--- ~ » litecli ~/nonexistent/path.sqlite                                                                                                                                                                                                                1 ↵
unable to open database file

The error also does not say that "path does not exist". This is a bit misleading, because it makes one think the database is there, but it's corrupt.

I think litecli should try creating directories in path if they don't exist, and then create a new database. Then the behavior will be consistent between case 1 and case 2.

feature request: execute a set of commands during startup

Hi,
it would be great to the option to execute a set of commands during startup.

In example I often load pcre module and I would like to have it as default behavior.

Thank you

'ascii' codec can't decode byte 0xe2 in position 103: ordinal not in range(128)

When doing a SELECT somerow FROM table;, with somerow containing data that's not valid ASCII, there is no output of data at all from litecli 1.0.0, but only the cryptic python error message:

'ascii' codec can't decode byte 0xe2 in position 103: ordinal not in range(128)

I haven't found any options to change the expected file / output encoding, besides of PRAGMA encoding which is set to UTF-8.

Ideally, litecli should output whatever it can, with invalid characters replaced by replacement character �.

Cannot load extension library

I occasionally use math functions defined in the extension library extension-functions.c. The library needs to be loaded into SQLite.

The official sqlite3 executable allows me to load the extension in two ways (unless compiled with SQLITE_OMIT_LOAD_EXTENSION):

sqlite> SELECT load_extension('/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib');

sqlite> .load '/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib'

litecli however doesn't call sqlite3_enable_load_extension, so extension loading is forbidden:

db> SELECT load_extension('/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib');
not authorized

One solution is to add a .load command like the sqlite3 one. Here's a quick patch:

diff --git a/litecli/packages/special/dbcommands.py b/litecli/packages/special/dbcommands.py
index 7307ad2..2e4f622 100644
--- a/litecli/packages/special/dbcommands.py
+++ b/litecli/packages/special/dbcommands.py
@@ -149,3 +149,24 @@ def status(cur, **_):
 
     footer.append("--------------")
     return [(None, None, "", "\n".join(footer))]
+
+
+@special_command(
+    ".load",
+    ".load",
+    "Load an extension library.",
+    arg_type=PARSED_QUERY,
+    case_sensitive=True,
+)
+def load_extension(cur, arg, **_):
+    # Strip quotes around the path, if any.
+    if (arg.startswith('"') and arg.endswith('"')) or (
+        arg.startswith("'") and arg.endswith("'")
+    ):
+        path = arg[1:-1]
+    else:
+        path = arg
+    conn = cur.connection
+    conn.enable_load_extension(True)
+    conn.load_extension(path)
+    return [(None, None, None, "")]

This way we can load an extension library (with or without quotes):

db> .load /usr/local/opt/sqlite/lib/libsqlitefunctions.dylib
Time: 0.002s
db> .load "/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib"
Time: 0.000s
db> .load '/usr/local/opt/sqlite/lib/libsqlitefunctions.dylib'
Time: 0.000s
db> SELECT SQRT(2);
+--------------------+
| SQRT(2)            |
+--------------------+
| 1.4142135623730951 |
+--------------------+
1 row in set
Time: 0.012s
db> .load /usr/local/opt/sqlite/lib/libxxx.dylib
dlopen(/usr/local/opt/sqlite/lib/libxxx.dylib.dylib, 10): image not found

I can turn the patch into a PR if the new command is deemed acceptable.

(Note that the signature of .load in sqlite3 is .load FILE ?ENTRY?; however, Python's sqlite3 doesn't seem to support loading a single entry, and I've never used the optional ENTRY either. Probably okay to keep it simple. I was mistaken: ENTRY here is an entry point function, usually something like sqlite3_extension_init, not a specific entry... Still, not really supported by Python's sqlite3 API — it's hard coded as 0.)

Exception 'LiteCli' object has no attribute 'vi_state'

Pressing F4 (VI mode) gives me the following error:
Exception 'LiteCli' object has no attribute 'vi_state'

Chinese disorderly code

sqlite3.Connection object has no attribute escape

reproduce using:

1. start litecli and open a database
2. run \T sql-insert
3. run a query, like select * from pages

Returns:
'sqlite3.Connection' object has no attribute 'escape'

Expected:
sql insert queries

This should be fixed or disabled for litecli.

Install on mac OS X / anaconda fails

Hi! I'm trying to install litecli via pip on my mac OS X (10.14.3) MBP with Miniconda 4 (Python 3.7.0).
The install fails with error: can't copy 'doc/lightcli.3': doesn't exist or not a regular file

Any idea how I can fix that?

No output on launch

Installed it on gitbash using python/pip (which I know very little about but I didn't spot any errors in the output), and it has created the exe /Python27/Scripts/litecli.exe, but when I start it, it doesn't show any output at all. --help works. But litecli newdb.db or litecli existingdb.db just shows a blinking cursor underneath the prompt. Amusingly, I can move the cursor all over the screen using the cursor keys, I assume this shows something-ncurses'y has started :-)
Are there logs I can look at or a verbose mode, or ..?

PS. if I do litecli -l foo.txt somefile.db it does create foo.txt, but it remains empty.

Unusable on dark background

Some text is written with same color as background. No documentation on how to change/use themes other than "edit config file".

Feature request: support more parameter templates recognized by sqlite3_bind

Favorite queries seem to be very useful as poor man's prepared statements, which sqlite3 CLI lacks. However, right now only shell-style parameter templates $1, $2, etc. are allowed. It would be nice to support more templates recognized by sqlite3_bind(), so that prepared statements can be directly taken from code or logs. At the very least, ? would be immensely helpful.

Remove all (most) references to mycli

$ git grep -i mysql

litecli/completion_refresher.py:    # schemata - In MySQL Schema is the same as database. But for mycli
litecli/lexer.py:from pygments.lexers.sql import MySqlLexer
litecli/lexer.py:class LiteCliLexer(MySqlLexer):
litecli/liteclirc:# MySQL prompt
litecli/main.py:    help="Read MySQL config groups with the specified suffix.",
litecli/packages/special/dbcommands.py:        # Fallback in case query fail, as it does with Mysql 4
litecli/packages/special/dbcommands.py:    # prepare in case keys are bytes, as with Python 3 and Mysql 4
tests/liteclirc:# MySQL prompt
tests/liteclirc:# \t - Product type (Percona, MySQL, MariaDB)
tests/utils.py:    reason="Need a mysql instance at localhost accessible by user 'root'",

hot reload

I'm iterating on a schema design and frequently dropping all tables and then recreating, but to see my changes reflected I need to keep running .open <db_file>. Be cool if litecli could keep track of this.

Incomplete, multi-line CREATE TABLE with .schema

Under regular sqlite3 .schema command displays my database schema properly.
But under litecli a multi-line CREATE TABLE is cut weirdly only after a few column description lines.
No special character I detected at the cut point (I've checked with an hex editor).
Each indented line is using only spaces.

Example:

exampledb.sqlite3> .schema
sql
CREATE TABLE example_table ( col0_name TEXT,
                                            col1_name TEXT,
                                            col2_name TEXT,
                                            c
<null>
CREATE TABLE .... [one-liners, all displayed alright]

Tried with several \T options and with different less pager options and cat.

Queries are fine and everything else works alright though.

Prompt with D*** db names

Prompt for db DY_DeskPLRSRange03.db becomes this

C:\temp\liteFri Jul 26 17:21:37 2019Y_DeskPLRSRange03.db>

probably \D is treated as sysdate?

multi line commands?

In the regular sqlite3 shell, since commands need to end with a semi-colon one can use multiline commands (by not ending a line with a semicolon).

Unless I'm mistaken that's not the case with the litecli, e.g. this command works:

ex1> select * from tbl1 where two = 10                                                                                      
+--------+-----+
| one    | two |
+--------+-----+
| hello! | 10  |
+--------+-----+
1 row in set
Time: 0.020s

even though I forgot the ";", but this command fails

ex2> create table tbl3 (g1 varchar(30) primary key,                                                                         
incomplete input

since I pressed enter before finishing the table.

Is there an alternative way to do multiline commands?

Move user configuration/logfiles to ~/.config/litecli

Move user configuration/logfiles to ~/.config/litecli (like pgcli)

prompt-toolkit-upgrade

We use litecli as detailed in #75. python-prompt-toolkit has reached version 3, but litecli requires version 2 still. Any chance for an upgrade, please? The upgrade itself should be pretty simple as detailed here.

ERROR: litecli 1.2.0 has requirement prompt-toolkit<2.1.0,>=2.0.0, but you'll have prompt-toolkit 3.0.3 which is incompatible.

test/data/import_data.csv missing from release tarball for 1.3.2

Hi,

The release tarball is missing import_data.csv (and liteclirc) causing tests to fail.

$ diff -ur litecli-tag-v1.3.2 litecli-tarball-1.3.2
Only in litecli-tag-v1.3.2: CONTRIBUTING.md
Only in litecli-tag-v1.3.2: .coveragerc
Only in litecli-tag-v1.3.2: .github
Only in litecli-tag-v1.3.2: .gitignore
Only in litecli-tarball-1.3.2: litecli.egg-info
Only in litecli-tarball-1.3.2: PKG-INFO
Only in litecli-tag-v1.3.2: .pre-commit-config.yaml
Only in litecli-tag-v1.3.2: screenshots
diff -ur litecli-tag-v1.3.2/setup.cfg litecli-tarball-1.3.2/setup.cfg
--- litecli-tag-v1.3.2/setup.cfg	2020-03-30 17:51:52.845302447 +0200
+++ litecli-tarball-1.3.2/setup.cfg	2020-03-11 18:55:57.000000000 +0100
@@ -3,16 +3,21 @@
 
 [tool:pytest]
 addopts = --capture=sys
-          --showlocals
-          --doctest-modules
-          --doctest-ignore-import-errors
-          --ignore=setup.py
-          --ignore=litecli/magic.py
-          --ignore=litecli/packages/parseutils.py
-          --ignore=test/features
+	--showlocals
+	--doctest-modules
+	--doctest-ignore-import-errors
+	--ignore=setup.py
+	--ignore=litecli/magic.py
+	--ignore=litecli/packages/parseutils.py
+	--ignore=test/features
 
 [pep8]
 rev = master
 docformatter = True
 diff = True
 error-status = True
+
+[egg_info]
+tag_build = 
+tag_date = 0
+
Only in litecli-tag-v1.3.2/tests: data
Only in litecli-tag-v1.3.2/tests: liteclirc
Only in litecli-tag-v1.3.2: TODO
Only in litecli-tag-v1.3.2: .travis.yml

Ctrl+y in vi mode not working

Description

When configured to use vi mode, Ctrl+y doesn't yank the previous cut text.

Steps to reproduce

1. Add key_bindings = vi to your config file
2. Open litecli and type pragma table_info('albums')
3. Hit Ctrl+w
4. Hit Ctrl+y

Expected behavior

Ctrl+w should cut table_info('albums') text and Ctrl+y should paste it back

Actual behavior

Ctrl+w cut the text as intended, but Ctrl+y doesn't do anything.

Broken formatting in \d

Here's one odd looking tabulate.

Litecli version: 1.0.0

~/repos/engineering/rebuilds.sqlite> \d
+------------------------------------------------------------------------------+
| sql                                                                          |
+------------------------------------------------------------------------------+
| CREATE TABLE abc(a text)                                                     |
| CREATE TABLE rebuild_jobs_local (                                            |
|       id INTEGER NOT NULL,                                                        |
|       source VARCHAR(9),                                                          |
|       started DATETIME,                                                           |
|       submitted DATETIME,                                                         |
|       finished DATETIME,                                                          |
|       start_date DATETIME NOT NULL,                                               |
|       end_date DATETIME NOT NULL,                                                 |
|       apikeys TEXT NOT NULL,                                                      |
|       args TEXT,                                                                  |
|       rq_job_id TEXT,                                                             |
|       updated DATETIME DEFAULT CURRENT_TIMESTAMP,                                 |
|       PRIMARY KEY (id),                                                           |
|       CONSTRAINT rebuildsource CHECK (source IN ('daily', 'autobuild', 'manual')) |
| )                                                                            |

Dot-commands will not autocomplete

Completion does not work for dot-commands:

[question] Integration Instructions

First of all, thanks for this awesome project!

I have a case where I integrated litecli here. Based on how configuration parsing works in litecli and the fact that I wanted to change some defaults, I am currently monkey patching the lib here to modify default values. My primary concern is obviously the longevity of this probably unsupported and hacky method of using the library.

Is there any integration documentation that I may have missed, or alternative suggestions to better integrate. I am totally ok with "not right now" as an answer ofc.

Thanks!

No row count information

Because PyAthena's cursor doesn't support rowcount information. We can fetch all the rows and count them.

Describe table does not work

tried for table ME_13F:

.schema [ME_13F]
Result:

Time: 0.001s

\dt ME_13F
Result:

Time: 0.001s

describe table ME_13F
Result:

Time: 0.001s

desc ME_13F
Result:

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sql |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE TABLE ME_13F ("BusinessUnit" varchar,"Advisor" varchar,"Issuer_13F" varchar,"MSymbol" varchar,"MsymbolDesc" varchar,"Description13F" varchar,"FinancialType13F" varchar,"FinancialType" varchar,"SwapType" varchar,"Cusip" varchar,"UnderlyingCusip" varchar,"Is13FSec" int,"IsDefaulted" int,"PosMarketVal" float,"PosQty" float,"ConvRatio" float,"UnderlyingKey" int,"UnderlyingSymbol" varchar,"UnderlyingDesc" varchar,"UnderlyingPrice" float,"UnderlyingTf" float,"QtyCalcBasedonRatio" float,"MVCa... |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Time: 0.014s

PRAGMA table_info([ME_13F])

Result:

+-----+---------------------+-----------+---------+------------+----+
| cid | name                | type      | notnull | dflt_value | pk |
+-----+---------------------+-----------+---------+------------+----+
| 0   | BusinessUnit        | varchar   | 0       | <null>     | 0  |
| 1   | Advisor             | varchar   | 0       | <null>     | 0  |
| 2   | Issuer_13F          | varchar   | 0       | <null>     | 0  |
| 3   | MSymbol             | varchar   | 0       | <null>     | 0  |
| 4   | MsymbolDesc         | varchar   | 0       | <null>     | 0  |
| 5   | Description13F      | varchar   | 0       | <null>     | 0  |
| 6   | FinancialType13F    | varchar   | 0       | <null>     | 0  |
| 7   | FinancialType       | varchar   | 0       | <null>     | 0  |
| 8   | SwapType            | varchar   | 0       | <null>     | 0  |
| 9   | Cusip               | varchar   | 0       | <null>     | 0  |
| 10  | UnderlyingCusip     | varchar   | 0       | <null>     | 0  |
| 11  | Is13FSec            | int       | 0       | <null>     | 0  |
| 12  | IsDefaulted         | int       | 0       | <null>     | 0  |
| 13  | PosMarketVal        | float     | 0       | <null>     | 0  |
| 14  | PosQty              | float     | 0       | <null>     | 0  |
| 15  | ConvRatio           | float     | 0       | <null>     | 0  |
| 16  | UnderlyingKey       | int       | 0       | <null>     | 0  |
| 17  | UnderlyingSymbol    | varchar   | 0       | <null>     | 0  |
| 18  | UnderlyingDesc      | varchar   | 0       | <null>     | 0  |
| 19  | UnderlyingPrice     | float     | 0       | <null>     | 0  |
| 20  | UnderlyingTf        | float     | 0       | <null>     | 0  |
| 21  | QtyCalcBasedonRatio | float     | 0       | <null>     | 0  |
| 22  | MVCalcBasedonRatio  | float     | 0       | <null>     | 0  |
| 23  | PosTf               | float     | 0       | <null>     | 0  |
| 24  | FaceQty             | float     | 0       | <null>     | 0  |
| 25  | FaceCurrentQty      | float     | 0       | <null>     | 0  |
| 26  | Qty_13F             | float     | 0       | <null>     | 0  |
| 27  | MV_13F              | float     | 0       | <null>     | 0  |
| 28  | Is13FReportable     | int       | 0       | <null>     | 0  |
| 29  | LongShortInd        | varchar   | 0       | <null>     | 0  |
| 30  | InstrumentID        | int       | 0       | <null>     | 0  |
| 31  | BusinessUnitId      | int       | 0       | <null>     | 0  |
| 32  | FinancialTypeCode   | int       | 0       | <null>     | 0  |
| 33  | AccountingDate      | date      | 0       | <null>     | 0  |
| 34  | AsOfDateTime        | timestamp | 0       | <null>     | 0  |
+-----+---------------------+-----------+---------+------------+----+
35 rows in set

How to specify max column width to avoid breaking output ascii table formatting?

Disclaimer:I am a sqlite newbie.

When the contents of my query fields are long, it breaks the default formatting of the ascii tables of the output from litecli in my terminal. The separators do not line up and the output is basically unreadable.

Is there a way to specify the max column width for query output to avoid this, or some other mechanism by which this might be achieved?

Thanks.

Autocompletion and expanduser support for open command

I was somewhat expecting to have autocompletion for opening the database.
Using ~/testdb.sqlite3 wasn't recognised (I had to use /home/meeuw/testdb.sqlite3)

Not sure if I have installed properly - litecli command not found

I get the following error.

litecli: command not found

I installed using pip

python3 -m pip install -U litecli

pip3 freeze | grep litecli gives me this

litecli==1.1.0

Can you please point out where I might be going wrong?

Ubuntu 18.04

EDIT: quickly tested it using a virtual environment. It works there, but not in my base system.

Also, unrelated to this, is it possible to create a deb/ubuntu package at apt or something? Doesn't that make it easier to install? If no one has done it and if you can guide me, I can give it a try.. (?)

no color in piping with heredoc

In interactive prompt I get color.
I'm passing some commands to litecli in the following form (bash here-doc):

$ litecli database.sqlite3 << EOF

 SELECT * FROM tbl1 WHERE col1="1" …
 EOF

but the output is not colored.

Also adding '\T ...' in the here-doc doesn't seem to affect output formatting neither.

TabularOutputFormatter chokes on values that can't be converted to UTF-8 if it's a text column

I don't know how to solve this. If there is a single record with a non-convertible column it won't print anything.

Could not decode to UTF-8 column 'verifier' with text ''���U'`
sqlite3 prints

2bho15FMrSQhKAYnjBqRQ1x4LS3zcHANsRjKMJyiSwT9|GnyZktv2SaCehfNCGjoYdAgAirxpCjvBCUXH6MiEHEH7                                                                                        
`'ŜU|`'ŜU 

Seeing this was actually helpful because it notified me that I had garbage data, but I still would've thought the other rows would print.

Support sqlparse 0.3.0

Can litecli be updated to work with sqlparse 0.3.0 ?

Got the error message below. Updated setup.py to allow 0.3.0 and it seemed to work fine.

~ ❯❯❯ litecli foo
Traceback (most recent call last):
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 583, in _build_master
    ws.require(__requires__)
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 900, in require
    needed = self.resolve(parse_requirements(requirements))
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 791, in resolve
    raise VersionConflict(dist, req).with_context(dependent_req)
pkg_resources.ContextualVersionConflict: (sqlparse 0.3.0 (/usr/lib/python3.7/site-packages), Requirement.parse('sqlparse<0.3.0,>=0.2.2'), {'litecli'})

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/bin/litecli", line 6, in <module>
    from pkg_resources import load_entry_point
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 3241, in <module>
    @_call_aside
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 3225, in _call_aside
    f(*args, **kwargs)
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 3254, in _initialize_master_working_set
    working_set = WorkingSet._build_master()
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 585, in _build_master
    return cls._build_from_requirements(__requires__)
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 598, in _build_from_requirements
    dists = ws.resolve(reqs, Environment())
  File "/usr/lib/python3.7/site-packages/pkg_resources/__init__.py", line 786, in resolve
    raise DistributionNotFound(req, requirers)
pkg_resources.DistributionNotFound: The 'sqlparse<0.3.0,>=0.2.2' distribution was not found and is required by litecli