SQLite#
SQLite — a built-in SQL machine implementation. Sqlite is often used as an embedded DBMS in applications.
Note
The word SQL server is not used here because server is not needed there - all functionality that is embedded in SQL server is implemented inside a library (and therefore within program that uses it).
SQLite CLI#
SQLite package also includes a command line utility for working with SQLite. Utility is presented as a sqlite3 executable file (sqlite3.exe for Windows) and can be used to execute SQL commands manually.
With this utility it is very convenient to check the correctness of SQL commands as well as to get acquainted with SQL language in general.
Let’s try to use this utility to figure out basic SQL commands that will be needed to work with database.
We’ll figure out how to build a database first.
Note
If you are using Linux or Mac OS, it is likely that sqlite3 is installed. If you are using Windows you can download sqlite3 here.
To create a database (or open an already created database), you simply call sqlite3:
$ sqlite3 testDB.db
SQLite version 3.8.7.1 2014-10-29 13:59:56
Enter ".help" for usage hints.
sqlite>
Inside sqlite3 you can execute SQL commands or so-called metacommands (or dot commands).
Metacommands include several special commands to work with SQLite. They refer
only to sqlite3 utility, not to SQL language. There is no need to
put ;
at the end of command.
Examples of metacommands:
.help
- a prompt with a list of all metacommands.exit
or.quit
- exit sqlite3 session.databases
- shows connected databases.tables
- shows available tables
Examples:
sqlite> .help
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail ON|OFF Stop after hitting an error. Default OFF
.databases List names and files of attached databases
...
sqlite> .databases
seq name file
--- -------- ----------------------------------
0 main /home/nata/py_for_ne/db/db_article/testDB.db
litecli#
The standard Sqlite CLI interface has several disadvantages:
no autocomplete commands
no tips
all content of a column is not always displayed
All these deficiencies are fixed in litecli. So it’s best to use it.
Installation of litecli:
$ pip install litecli
Open database in litecli:
$ litecli example.db
Version: 1.0.0
Mail: https://groups.google.com/forum/#!forum/litecli-users
Github: https://github.com/dbcli/litecli
example.db>