In this part of the SQLite tutorial, we will cover the sqlite3 command line tool.
The manual describes the sqlite3 as follows: sqlite3 is a terminal-based front-end to the SQLite library that can evaluate queries interactively and display the results in multiple formats. sqlite3 can also be used within shell scripts and other applications to provide batch processing features.
sqlite3 tool
sqlite3 is a terminal based frontend to the SQLite library that can evaluate queries interactively and display the results in multiple formats. It can also be used within scripts.
On the terminal screen, we see the following prompt of the sqlite3 tool.
$ sqlite3 test.db SQLite version 3.7.7 2011-06-23 19:49:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite>
The .help is one of the meta commnads of the sqlite3 tool. It shows a list of them. The .exit and the.quit commands exit the sqlite3 session. We can also use the Ctrl+Q key combination. The.databases command shows the attached databases. The .tables command lists the available tables.
Creating a database
The complete SQLite database is stored in a single cross-platform disk file. We use the sqlite3command line tool to create a new database file.
$ sqlite3 movies.db
Here we create a new movies.db database. If the exists, it is opened.
Basic sqlite3 meta commnads
Next we describe some of the meta commands of the sqlite3 tool.
sqlite> .tables Books Customers Log Orders Testing Cars Friends Names Reservations
The .tables commnad shows the available tables.
sqlite> SELECT * FROM Friends; 1|Jane|F 2|Thomas|M 3|Frank|M 4|Elisabeth|F 5|Mary|F 6|Lucy|F 7|Jack|M
Here we get the output of a simple SELECT statement. By default, the output mode is line and the separator is |.
sqlite> .separator : sqlite> SELECT * FROM Friends; 1:Jane:F 2:Thomas:M 3:Frank:M 4:Elisabeth:F 5:Mary:F 6:Lucy:F 7:Jack:M
Here we have used a new colon separator.
There are several other output modes available. The following example will show the columnoutput mode.
sqlite> .mode column sqlite> .headers on sqlite> SELECT * FROM Friends; Id Name Sex ---------- ---------- ---------- 1 Jane F 2 Thomas M 3 Frank M 4 Elisabeth F 5 Mary F 6 Lucy F 7 Jack M
In this example, we have our data in the column mode. Plus we show the column headers with the.headers command. By default, the headers are hidden.
The .width command adjusts the size of the columns.
sqlite> SELECT Title, Author FROM Books; Title Author ------------- ----------- War and Peace Leo Tolstoy The Brothers Fyodor Dost Crime and Pun Fyodor Dost
Here, the column widths are not wide enough to display all data correctly.
sqlite> .width 22, 18 sqlite> SELECT Title, Author FROM Books; Title Author ---------------------- ------------------ War and Peace Leo Tolstoy The Brothers Karamazov Fyodor Dostoyevsky Crime and Punishment Fyodor Dostoyevsky
Here we change the column widths. The first column will be 22 characters wide, the second 18.
sqlite> .show
echo: off
explain: off
headers: on
mode: column
nullvalue: ""
output: stdout
separator: "|"
stats: off
width: 22 18
The .show command lists various settings. We can see the output mode, the separator used in the list mode or whether the headers are on.
sqlite> .schema Cars CREATE TABLE Cars(Id integer primary key, Name text, Cost integer);
The .schema command shows the structure of the table. It gives the DDL SQL to create the table.
Executing SQL from the shell
We can execute SQL commands from the shell.
$ sqlite3 test.db "SELECT * FROM Cars;" 1|Audi|52642 2|Mercedes|57127 3|Skoda|9000 4|Volvo|29000 5|Bentley|350000 6|Citroen|21000 7|Hummer|41400 8|Volkswagen|21600
Here we have non-interactively executed a SELECT SQL command. We selected all cars from the Cars table.
Dumping tables
It is possible to dump tables in SQL format to the disk. This way we can easily save the structure and the data of a database table.
We have the Cars table.
sqlite> SELECT * FROM Cars; Id Name Cost ---------- ---------- ---------- 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 6 Citroen 21000 7 Hummer 41400 8 Volkswagen 21600
Now, we are going to use the .dump command to dump the table.
sqlite> .dump Cars PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE Cars(Id integer primary key, Name text, Cost integer); INSERT INTO "Cars" VALUES(1,'Audi',52642); INSERT INTO "Cars" VALUES(2,'Mercedes',57127); INSERT INTO "Cars" VALUES(3,'Skoda',9000); INSERT INTO "Cars" VALUES(4,'Volvo',29000); INSERT INTO "Cars" VALUES(5,'Bentley',350000); INSERT INTO "Cars" VALUES(6,'Citroen',21000); INSERT INTO "Cars" VALUES(7,'Hummer',41400); INSERT INTO "Cars" VALUES(8,'Volkswagen',21600); COMMIT;
The .dump command shows us the SQL necessary to recreate the table.
sqlite> .output cars2.sql sqlite> .dump Cars
We can also redirect the output to a file. The .output command will redirect the output to the cars2.sql file.
$ cat cars2.sql PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE Cars(Id integer primary key, Name text, Cost integer); INSERT INTO "Cars" VALUES(1,'Audi',52642); ...
We verify it.
Reading SQL
We can read SQL from a file name with the .read commnad.
sqlite> .tables Cars Cars sqlite> DROP TABLE CARS; sqlite> .tables Cars sqlite> .read cars.sql sqlite> .tables Cars Cars sqlite> SELECT * FROM Cars WHERE id=1; Id Name Cost ---------- ---------- ---------- 1 Audi 52642
Here we have executed a series of commands. We drop the table and read it from the cars.sql.
$ tail -5 ~/.sqlite_history SELECT * FROM Cars LIMIT 2; .help .tables .echo ON SELECT * FROM Cars;
The commands and statements are archived in the .sqlite_history file, located in the home directory. Using the
tail command, we show the last five entries.Resource file
The sqlite3 tool has a resource file called .sqliterc. It is located in the home directory. If there is no such file, we can simply create it. We can place the meta commnads there or the regular SQL statements. However, we should avoid using SQL in the resource file.
$ cat .sqliterc .mode column .header on .nullvalue NULL
Here is a simple example of a resource file. It has three meta commands. With resource file, we don't have to execute meta commnads all over again, when we start the sqlite3 tool. They will be executed automatically at the start of the tool.
$ sqlite3 test.db -- Loading resources from /home/janbodnar/.sqliterc SQLite version 3.7.7 2011-06-23 19:49:22 Enter ".help" for instructions Enter SQL statements terminated with a ";"
We have a message saying, that the tool loaded resources upon the beginning.
Command line options
The tool has several command line options. They mostly duplicate the meta commands. Note, that commnad line options overwrite the resource file meta commands.
$ sqlite3 -help Usage: sqlite3 [OPTIONS] FILENAME [SQL] FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist. OPTIONS include: -help show this message -init filename read/process named file -echo print commands before execution -[no]header turn headers on or off -bail stop after hitting an error -interactive force interactive I/O -batch force batch I/O -column set output mode to 'column' -csv set output mode to 'csv' -html set output mode to HTML -line set output mode to 'line' -list set output mode to 'list' -separator 'x' set output field separator (|) -stats print memory stats before each finalize -nullvalue 'text' set text string for NULL values -version show SQLite version -vfs NAME use NAME as the default VFS
The -help options gives us the list of all available options with a brief description.
$ sqlite3 -echo -line -noheader test.db SQLite version 3.7.7 2011-06-23 19:49:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT * FROM Cars LIMIT 2; SELECT * FROM Cars LIMIT 2; Id = 1 Name = Audi Cost = 52642 Id = 2 Name = Mercedes Cost = 57127
We start the sqlite3 tool with -echo, -line and -noheader options. The SELECT command is repeated/echoed after being launched. The ouput is in the line mode and we have no headers.
$ sqlite3 -version -- Loading resources from /home/janbodnar/.sqliterc 3.7.7 2011-06-23 19:49:22 4374b7e83ea0a3fbc3691f9c0c936272862f32f
We get the sqlite3 version.
$ sqlite3 -html test.db -- Loading resources from /home/janbodnar/.sqliterc SQLite version 3.7.7 2011-06-23 19:49:22 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> SELECT * FROM Cars LIMIT 2; <TR><TH>Id</TH><TH>Name</TH><TH>Cost</TH></TR> <TR><TD>1</TD> <TD>Audi</TD> <TD>52642</TD> </TR> <TR><TD>2</TD> <TD>Mercedes</TD> <TD>57127</TD> </TR>
The -html option causes the results to be output as simple HTML tables.
In this part of the SQLite tutorial, we worked with the sqlite3 command line tool. We have described various meta commnads. We have shown, how to dump tables, read SQL from files; we described the sqlite's resource file.
No comments:
Post a Comment