Looking at SQLite Databases

As we have seen, SQLite provides APIs to allow a database to be embedded into a wide variety of programming languages. However, there are times when you want to take a look inside your database without writing a program to do so. This section introduces two tools that can be used to query and amend SQLite database files.

The sqlite Tool

SQLite comes bundled with the sqlite tool, which provides a command-line interface to work with database files. It is invoked from the shell taking simply the name of a database as its argument.

 $ sqlite dbfile SQLite version 2.8.15 Enter ".help" for instructions sqlite> 

The sqlite> prompt indicates that sqlite is waiting for a new command to be entered. There are two types of commandseither an SQL statement terminated with a semicolon or a command relating to the sqlite program itself, beginning with a dot.

The .help command lists all the dot commands available along with a brief description, as shown in the following:

 sqlite> .help .databases             List names and files of attached databases .dump ?TABLE? ...      Dump the database in a text format .echo ON|OFF           Turn command echo on or off .exit                  Exit this program .explain ON|OFF        Turn output mode suitable for EXPLAIN on or off. .header(s) ON|OFF      Turn display of headers on or off .help                  Show this message .indices TABLE         Show names of all indices on TABLE .mode MODE             Set mode to one of "line(s)", "column(s)",                        "insert", "list", or "html" .mode insert TABLE     Generate SQL insert statements for TABLE .nullvalue STRING      Print STRING instead of nothing for NULL data .output FILENAME       Send output to FILENAME .output stdout         Send output to the screen .prompt MAIN CONTINUE  Replace the standard prompts .quit                  Exit this program .read FILENAME         Execute SQL in FILENAME .schema ?TABLE?        Show the CREATE statements .separator STRING      Change separator string for "list" mode .show                  Show the current values for various settings .tables ?PATTERN?      List names of tables matching a pattern .timeout MS            Try opening locked tables for MS milliseconds .width NUM NUM ...     Set column widths for "column" mode 

Let's issue a few simple SQL commands to create a new table and add some rows of data. Don't worry too much about how these commands work for now; we just need to get something into the database to show how sqlite works.

First of all, the following statement creates a new table called mytable that has two columns, col1 and col2.

 sqlite> CREATE TABLE mytable (    ...>   col1 NUMERIC,    ...>   col2 TEXT    ...> ); sqlite> 

Notice how the prompt changes from sqlite> to three dots, indicating that the entered statement is not yet complete. The semicolon character signifies the end of an SQL statement, and after the semicolon is entered, the prompt changes back.

Note

If you get into a mess entering a statement and want to abort without having SQLite process a nonsense command that could potentially do harm, press Ctrl+C to send an interrupt signal to sqlite. You will be returned to the shell prompt and can start again.

Although no output is displayed to screen, SQLite has received and processed the CREATE TABLE command. The response from database operations is always silent unless there has been an error.

If you type the same command with a deliberate mistake, the error will be detected when the semicolon terminator is enteredbut not beforeas shown in the following example:

 sqlite> CREATTE TABLE mytable (    ...>   col1 NUMERIC,    ...>   col2 TEXT    ...> ); SQL error: near "CREATTE": syntax error 

The .schema command can be used to see the schema of a table. What is displayed to screen is the CREATE TABLE command that was used to create the table. Therefore if you formatted or capitalized the statement differently than that shown, the following output will be slightly different.

 sqlite> .schema mytable CREATE TABLE mytable (   col1 NUMERIC,   col2 TEXT ); 

If .schema is used without a table argument, the schema for all tables in the database is displayed.

The following commands insert a few simple records into the database. Again, the only response after entering each one is a new sqlite> prompt, unless an error occurs.

 sqlite> INSERT INTO mytable VALUES (1, 'One'); sqlite> INSERT INTO mytable VALUES (2, 'Two'); sqlite> INSERT INTO mytable VALUES (3, 'Three'); sqlite> INSERT INTO mytable VALUES (99, 'Ninety Nine'); 

The SELECT command in SQL is used to retrieve rows from the database. Let's bring back the newly inserted rows to see how this is displayed in sqlite.

 sqlite> SELECT col1, col2 FROM mytable; 1|One 2|Two 3|Three 99|Ninety Nine 

The sqlite tool has various different output formats for the selected data. The default is list mode which, as shown, outputs one row for each database record with the columns separated by a | character.

To change the separator character, use the .separator command. The following example changes the separator to a comma. You could use this output format to create a comma-separated values file to load into another application, for instance.

 sqlite> .separator , sqlite> SELECT col1, col2 FROM mytable; 1,One 2,Two 3,Three 99,Ninety Nine 

Output modes are changed using the .mode command followed by the name of the desired mode. The column mode also outputs one row for each database record, but uses a formatted column output.

 sqlite> .mode column sqlite> SELECT col1, col2 FROM mytable; 1           One 2           Two 3           Three 99          Ninety Nin 

By default each column width is 10 characters and, as shown, any values longer than the column width are truncated to fit. The .width command can be used to adjust this. It takes a series of number arguments with the first number representing the width of the first column and so on.

Column headings in this mode can be turned on or off using the .header command followed by on or off. The following example turns headings on and sets the columns widths just wide enough to display all the data and column headings.

 sqlite> .mode column sqlite> .header on sqlite> .width 4 11 sqlite> SELECT col1, col2 FROM mytable; col1  col2 ----  ------------ 1     One 2     Two 3     Three 99    Ninety Nine 

On systems with the readline library installed, you can re-execute a command that has previously been entered using the up and down arrow keys to scroll through the command history a line at a time. In the preceding example it was possible to resubmit the SELECT statement after entering the two formatting commands by pressing the Up arrow key three times followed by pressing Enter.

Rather than one line per record, the line output mode causes one row to be output for each selected column with the column name prefixed.

 sqlite> .mode line sqlite> SELECT col1, col2 FROM mytable;  col1 = 1  col2 = One  col1 = 2  col2 = Two  col1 = 3  col2 = Three  col1 = 99  col2 = Ninety Nine 

Setting output mode to html causes a SELECT statement to generate HTML code for a table that contains the fetched data. The .header setting determines whether column headings are included in <TH> tags.

 sqlite> .mode html sqlite> .header on sqlite> SELECT col1, col2 FROM mytable; <TR><TH>col1</TH><TH>col2</TH></TR> <TR><TD>1</TD> <TD>One</TD> </TR> <TR><TD>2</TD> <TD>Two</TD> </TR> <TR><TD>3</TD> <TD>Three</TD> </TR> <TR><TD>99</TD> <TD>Ninety Nine</TD> </TR> 

The final output mode is insert, which causes a series of SQL INSERT statements to be generated. .mode insert optionally takes a database table argument, which will generate INSERT statements for the given table name rather than the one from which it was selected.

 sqlite> .mode insert newtable sqlite> SELECT col1, col2 FROM mytable; INSERT INTO newtable VALUES(1,'One'); INSERT INTO newtable VALUES(2,'Two'); INSERT INTO newtable VALUES(3,'Three'); INSERT INTO newtable VALUES(99,'Ninety Nine'); 

If you want to check what the current display settings are, use the .show command. The following output shows the current settings if you have followed all the examples in this chapter so far.

 sqlite> .show      echo: off   explain: off   headers: on      mode: insert nullvalue:    output: stdout separator: ,     width: 4 12 

The sqlite tool can process both SQL statements and dot commands from standard input. Therefore it is possible to pipe or redirect a series of commands to the program rather than key them in.

If you have a script containing a series of SQL commands, for example to create and populate a database, the following command can be used:

 $ sqlite dbfile < sqlfile 

Alternatively, sqlite uses the .read command to interactively read and process a file containing SQL commands.

 sqlite> .read sqlfile 

With those basics of sqlite under your belt, you should be all set to start working with SQLite. We will use sqlite throughout this book and some of the more advanced dot commands will be introduced later on.

SQLite Database Browser

If you prefer to manage your databases through a graphical interface, take a look at SQLite Database Browser. This handy open-source application can be built on any platform supported by Trolltech's QT library, and precompiled versions are available for Linux, Windows, and OSX. You can download SQLite Database Browser from http://sourceforge.net/projects/sqlitebrowser/.

When SQLite Database Browser starts up, you will be presented with an empty-looking application window. Let's load in the sample database that we'll use in Chapter 2, "Working with Data." Download demodb.sql from the Sams Publishing website at http://www.samspublishing.com/. Enter this book's ISBN (without the hyphens) in the Search box and click Search. When the book's title is displayed, click the title to go to a page where you can download the code and save it locally. Then select File, Import, Database from SQL File, as shown in Figure 1.1.

Figure 1.1. Using SQLite Database Browser to import an SQL file.

A file selector will appear. Select demodb.sql from the location you saved it to. SQLite Database Browser will ask if you want to create a new database to store the imported data. Answer Yes and another file selector will appear where you can choose the location of your database file. No file extension is necessary, so simply call the file demodb.

The program will then go ahead and load data from the SQL file to create a new database. Five table names will be shown under the Database Structure tab, and by clicking the + symbols to the left of each one you can expand the view to show the columns in each table.

Note

SQLite Database Browser can also load data from a CSV file into a SQLite database. Select File, Import, Table from CSV File.

Figure 1.2 shows the contents of demodb fully expanded. Notice that the Object column indicates the type of each item, and the schema is shown alongside each table object. Unfortunately where the CREATE TABLE statement was formatted across multiple lines, the schema cannot fit all the information into a single row of the display and sometimes this looks messy. Expanding the table to display its columns gives as much information as you would need anyway.

Figure 1.2. Viewing table columns through SQLite Database Browser.

The icon bar at the top of the window allows you to create and drop both tables and indexeshover over each icon in turn and the bubble help will tell you what each one does. These options are also available from the Edit menu. SQLite Database Browser also provides a Modify Table option, which can be very useful as SQLite does not implement the ALTER TABLE database command.

Note

In the current version of SQLite Database Browser, there is an intermittent problem with the import process. Sometimes after you load a database from an SQL or CSV file, the icon bar and menu options to manipulate tables are disabled. If this is the case, you need to close the program and reopen your database filethere is no need to reimport it.

To add a new table, click the appropriate icon or select Edit, Create Table. Enter a table name and use the Add button to add columns to it. The dialog box will be similar to Figure 1.3.

Figure 1.3. Adding a new table using SQLite Database Browser.

The field type selection has only three valuestext, numeric, and blob. As we'll see in Chapter 2, SQLite is typeless, so there is not the range of data types you may be used to having in other database systems.

The Browse Data tab enables you to look at the actual records held in the database. Use the Table drop-down list to select a table and its records will be shown in a grid format below.

Figure 1.4 shows the records in the employees table viewed through SQLite Database Browser.

Figure 1.4. Viewing database records using SQLite Database Browser.

If there are a large number of rows in the table, the first thousand will be displayed with a scrollbar on the right of the window. The buttons in the bottom left corner of the window allow you to flip forward and backward a thousand at a time.

SQLite Database Browser also allows you to narrow the data set displayed using a search option. Click the magnifying glass icon to bring up the Find screen. From here you can select a column to search on, pick an operator, and enter the search value.

For example, to find all male employees, select the employees table under the Browse Data tab and click the magnifying glass. In the Find window, select the sex column and the equals (=) operator and enter M as the value. Click the Search button and two rows will be displayed.

Only the record number and the selected column are displayed in this view, and as you searched on sex=M, showing that the value of the sex column is M for both of the records isn't much use. However, you can still use the Browse Data view with the Find window active, and clicking an entry in the Find window will automatically highlight the corresponding row in the main application window, as shown in Figure 1.5.

Figure 1.5. Performing a search in SQLite Database browser.

Through the Execute SQL tab you can enter any SQL command to have it processed by SQLite. The application displays any error messages, allowing you to modify the statement until it is error-free. Any rows returned by a SELECT statement are displayed in the bottom half of the screen.

Finally, to make sure any changes you have made through SQLite Database Browser are saved to the database file, select File, Save Database. Changes are not saved automatically, although if you exit the program with unsaved changes you will be asked if you want to save them.