Reporting and technical writing. Wednesday - Jul 26, 2017

Log in to sqlite3 via the command line


The sky is the limit with SQLite, once you’re logged in. Here’s the first crucial step.

Open your command line utility (Terminal on the Mac).

Use a few UNIX commands to get your whereabouts:
pwd ls cd

Type pwd (print working directory) to see your directory location.
Type ls (list) to view which files are in that directory.
Type cd (change directory) followed by the desired directory’s path.

To start the SQLite program, type sqlite3 (optionally, type the name the file that holds the SQLite database), then press return.

If the database file does not exist, SQLite will automatically create a new one with the given name as a temporary file. SQLite will delete the file once you exit sqlite3).

The sqlite3 program will show a banner message and then prompt you to enter SQL statements. End each statement with a semicolon and press return to execute.

Tips

Enter .help for usage hints.
Press ctrl-D to terminate the sqlite3 program.
Press ctrl-C to stop a long-running SQL statement.

Example

Here’s how to log in to SQLite and immediately create a new database named directory in the same command, followed by the creation of a table named “employees” with fields for name and hats:

$sqlite3 directory
sqlite> create table employees(name varchar(10), hats smallint);
sqlite> insert into employees values('Dan',3);
sqlite> insert into employees values('Jan',4);

Then query your new data right away by using the SELECT command:

sqlite> SELECT * from employees;

name hats
Dan 3
Jan 4

Create structure
Import data from a csv