Database:Getting started with SQLite

From Juneday education
Jump to: navigation, search

Meta

Expand using link to the right to the meta section with instructor's notes.

Description

This chapter gets the student started with using the SQLite DBMS. We recommend that you, the teacher, watch the videos and review the exercises. This chapter (and all following chapters) assumes that the student has a working environment with a bash shell and SQLite3 installed.

The lecture shows how you can access the SQLite interactive shell from the command line, as well as gives some short examples on how to also run SQL statements from the command line, e.g. using echo and pipes. Another topic of the lecture, is to show some SQLite specific instructions (those starting with a dot, like .schema, .tables etc) for investigating tables or opening a database from within the SQLite shell (.open).

The online exercises train the student in using the SQLite software both from the command line and interactively, dumping tables as a backup, restoring from backup, etc. They touch upon a simple CREATE TABLE statement, and discuss the difference between a text file with SQL statements and the binary file with a complete database.

Topics addressed in the online exercises

There are 15 tasks with instructions and suggested solutions for this topic. If you would print out the exercises on paper, that would be 10 A4 pages, to give you an idea of the multitude of the exercises. The things trained (and explained when needed) in the exercises include:

  • How to create a database, giving the name as an argument to sqlite3
  • How to create a simple table inside a database
  • How to log out of the interactive sqlite3 shell
    • Using Ctrl-D
    • Using .exit
  • How to enter the interactive shell
    • Without argument to sqlite3 - will not select any database as the main database
    • Choosing a named database as the main database using .open
    • With an argument of the name of the database - will select it as the main database right away
  • How to investigate what database is selected as the main database using .databases
  • How to investigate what tables exist in the currently selected database using .tables
  • How to investigate how a table was created using .schema
  • How to create a text file with SQL commands to optionally create a table and insert a lot of rows into it
  • How to run sqlite3 from the command line and have it read all the SQL statements from such a file, using redirection of standard in
  • How to select all rows and columns from a table using SELECT
  • How to list the SQLite3 specific commands using .help
  • How to redirect output from the interactive session
    • Using .output
    • Using .once
  • How to create a backup (as a text file with create table and all necessary insert statements) using .dump (in combination with redirecting the output to a file)
  • That SQL statements like SELECT end with a semicolon
  • That SQLite3-specific commands start with a dot

Prepare

Expect this lecture to take around 25 minutes. As usual, we recommend watching the video lecture and looking at the online exercises (and their suggested solutions).

Topics in this lecture are:

  • Reminding students to install SQLite3 (and possibly also bash)
  • Starting SQLite3 from the shell with an argument of the name of a new database to be created
  • Simple example of a CREATE TABLE statement
  • In the interactive SQLite shell, with no database active, you can open an existing database using the .open command
  • You can execute SQL from the shell using redirection or a pipe
  • .schema .tables .open commands (SQLite3 specific, not part of SQL)

After the lecture, you may encourage the students to do the exercises online from the SQL SELECT chapter again, this time using the SQLite database and getting their hands dirty. After that, there are exercises also for this particular topic (see above).

Heads-up

Think about:

  • Most examples in lectures and in particular in exercises in this course material assume that the student is using the command line and the bash shell
  • Point out that SQL is a standard (albeit with small dialectic differences between DBMSes) but the commands starting with a dot (.open, .schema etc) are not part of SQL but rather comes with SQLite and only works there

Full frontal - Code examples up-front!

Here's some code, so you know what to expect from this chapter!

$ sqlite3 my_books < my_books.sql
$ sqlite3 my_books
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite> .tables
book
sqlite>

Introduction

This chapter gets you started with using the SQLite DBMS. See the videos and presentation, read the pages linked to below (under External links) and move on to the exercises in the next chapter.

Starting SQLite3

There are various ways to start the SQLite3 client. First of all, you need to understand that there are two modes in which the SQLite3 client can operate in. You can run it interactively, which means that you enter the program sqlite3 and start a conversation with it, pretty much as when you are running Bash in a terminal.

Interactive mode

Interactive programs are typically line-based, which means that you are presented with a prompt (in this case sqlite> ) which means "OK, talk to me", that is, that the program is accepting your input.

SQLite3-specific commands

There are two types of commands you can send to the sqlite3 client. The first types is SQLite-specific directives, all starting with a dot, sometimes accepting also arguments. Those commands don't end with a semicolon and they are specific to the SQLite database management system. With those commands you can affect various aspects of the database engine, like how results are presented to you. You can also use such commands to query the database mangement system for what databases exist, what the schemas (table definitions) exists in a database etc. Some commands also let you load commands and databases from the file system, or save a backup etc.

SQL statements

The other type of commands you can issue to the SQLite database, are in the form of SQL statements. SQL (structured query language) is a standardized language for querying databases for data, manipulating data in databases and even creating new databases with new tables of data. We will learn the basics of the SQL language throughout this course material.

Both sqlite-specific commands (those starting with a dot) and SQL statements work in both interactive and non-interactive mode (see below).

Non-interactive mode

The other way in which to engage with the sqlite3 program, is non-interactive. That means that you can send lines of text to it from Bash in the terminal, and get the results printed directly in the terminal. We are going to look at both modes.

In order to communicate with sqlite3 non-interactively, you must first remember that the program is line-based. That means that the program - pretty much like Bash - operates on lines of input. If you want to issue a command or an SQL statement to the database engine, you need to send it as a line of text ending with a newline.

Reading commands and statements from a file

One way to communicate with the sqlite3 program non-interactively is to redirect input from a file, using the shell's redirection operator < (a less-than character). That instructs the shell to start the program non-interactively by connecting the standard input channel of the sqlite3 program to the lines of a file:

$ sqlite3 < file-with-commands

Giving commands as arguments

Another way to communicate non-interactively with the sqlite3 program, is to provide the commands and SQL statements as arguments to the program:

$ sqlite3 testdb "SELECT DATE('now');"
2019-01-13

In the example above, we gave two arguments to the sqlite3 command, first the name of a database, testdb and second, the SQL statement SELECT DATE('now'); which calls a function for dates in a way that makes the database print out the current date.

Sending commands via a Bash pipe

Yet another way to communicate with sqlite3 non-interactively, is to use the shell's pipe operator, |. A pipe works like this: You issue a command which prints lines of text to its standard out stream. The pipe catches the output from the command and sends it as the standard input stream to the next command. Let's look at an example:

$ sqlite3 testdb "select date('now')"
2019-01-13

Note that we end SQL statements with a semicolon.

If you need to read up on redirection, pipes or even arguments to programs, please review our course materials on Bash (see the left-hand navigation on the wiki).

Selecting a database

You can start SQLite3 without an active database. You will then get an in-memory execution of SQLite3. That means that you can do everything you normally can do with SQLite3 like creating tables, adding data, manipulating data etc, but it will be done in the RAM memory which means that everything will disappear when you exit the program. Normally, you would want the data to be persistently stored in a file instead.

When you start sqlite3 without any arguments, you start it in this in-memory mode.

You probably want to start sqlite3 with a persistent database, stored in a file instead. In order to do that, you give one argument to sqlite3, the name of the database file (actually the path to the file). If such a database file exists, you will use that database in your communication with SQLite3. If such a database file doesn't exist, SQLite3 will create it for you, once you create a table in the database.

A database file is a binary file whose contents only sqlite3 can understand. It is of great importance that you understand the difference between such a database file and e.g. a file with commands or SQL statements.

A file with SQL statements must be a regular plain-text file whose contents is lines of semicolon-separated SQL statements. Such a file can be redirected to sqlite3 or sent via a pipe to the program (see above).

A database file, on the other hand, is a file created by sqlite3 which we cannot print or look at in an editor. The sqlite3 program saves all databases and data in a database file, using a file format only sqlite3 can understand. It is convenient, but not necessary, to name a database file with a file suffix of e.g. .db like my_first_database.db. It doesn't matter what you call your databse file, but giving it the .db file suffix might help you remember that it is a database file, and not a text file with commands and/or SQL statements.

Some SQLite3-specific commands

.open

If you have opened sqlite3 in the in-memory mode, you can actually open a database from a database file, using the command .open with an argument of the (path to) database file. If the file my_first_database.db is in current directory, you could open it like this:

$ sqlite3
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .open my_first_database.db 
sqlite> 

.databases

A useful command is the .databases command. You can use it to see what databases are connected:

sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /home/rikard/my_first_database.db                         
sqlite>

The above tells you that you have connected to the my_first_database.db database as the main database (the active database).

.tables

When you have opened the database you want to work with, you might have forgotten what tables exist in the database. You can then use the .tables command:

sqlite> .tables
colors  fruits
sqlite>

.schema

Once you have connected to a database, either by giving the database file as the argument to sqlite3, or by using the .open command, you can investigate how a table (or all tables) was defined:

sqlite> .schema colors
CREATE TABLE colors(id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY, name TEXT NOT NULL);
sqlite>

The above queries the database engine for the definition of the table colors of the active database. The argument to .schema is the name of the table to investigate. If no argument is provided to .schema the database engine shows all table definitions of the active database:

sqlite> .schema 
CREATE TABLE colors(id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE fruits(id INTEGER DEFAULT 0 NOT NULL PRIMARY KEY, name TEXT NOT NULL, is_poisonous BOOLEAN);

More commands

You can use the .help command to get a list of sqlite3-specific commands. You should also read the documentation at https://www.sqlite.org/cli.html. And of course, you should watch our video lectures linked below, as well as do our exercises on the next page!

Links

Videos

Swedish videos

Note that the videos are in Swedish but the presentation is in English for now. We aim to add English videos in the future ™

English videos

Files

  • No files for this chapter

Further reading

We expect you to read the following pages:

Where to go next

« PreviousBook TOCNext »