How to Create a Database Using SQLite

This database we’re creating will be a blank canvas to create table structures, which we’ll fill with information.

Using DB Browser to create a database

One can go about using SQLite via a GUI, such as DB Browser, or by executing a series of statements in the command line.

Using a downloaded GUI, such as DB4S, will make setting up and creating a database for the first time easier. However, we will show you both ways in which to do this.

To create a new database using DB Browser, simply click New Database to create a database for your data, give the database an appropriate name, and put it in the folder that you’re using for your work on the project. You are then able to import data, create tables or indices as required.

Using the command line to create a database

The normal procedure to create a database in SQL is to use use the statement:

CREATE DATABASE MyFirstDatabase;

Generally, each SQL statement starts with an SQL function that determines the request, such as CREATE DATABASE, followed by an argument or value – in this case, the name we give our database. The semicolon at the end of a statement or query is required to execute it.

However, if you run this command in SQLite you’ll get an error. To create a new database in SQLite you need to specify databases when opening SQLite, or open an existing file using .open. You can use the .database command to see a list of existing databases.

To create a database in SQLite, open a terminal and run:

sqlite3 MyFirstDatabase.db

You should see the prompt, this time without a warning about being connected to an in-memory database.

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> 

Then run the .database command to see a list of databases available. (Note, the path to your database might be different.)

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .databases
main: C:sqliteMyFirstDatabase.db
sqlite> 

If you have multiple databases open, or you forget to specify the database to open on the command line, you can also use the .open command in SQLite.

For example:

sqlite> .open MyFirstDatabase.db
sqlite> .databases
main: C:sqliteMyFirstDatabase.db
sqlite> .open MySecondDatabase.db
sqlite> .databases
main: C:sqliteMySecondDatabase.db

Importing and exporting data

Importing CSV files

In this section we’ll show you how to import and export information with SQLite. You may have already tried this when installing and exploring DB4S. If importing a CSV file:

1. 1. Choose File > Import > Table from the CSV file menu option, which will open a dialog box. Use it to find your CSV file and click Open.

1. 1. Change the default name listed for the table if necessary. This is the name that you will use in the FROM clause of your SQL commands.

1. 1. Click the checkbox next to the words Column names in the first line, which will cause SQLite Manager to use the names in the first line of the CSV file as the names of the attributes in the table.

1. 1. Click OK at the bottom of the tab to create the table.

1. 1. By default, all of the columns will be given the type Text. To change this, use the drop-down menus that will display when you click Modify Table.

In SQLite, and using the command line, you can use the .import command to import CSV data into a table. Before you run the .import command, set the mode to ‘csv’ (this might seem odd, given we’ve used ‘.mode’ to control output, but this prevents SQLite from trying to interpret the CSV file as commands).

The .import command takes two arguments: the location of the CSV file, and the name of the table where the data should be inserted. In the example below we’ll import the CSV of the songs you were wrangling previously.

sqlite> .mode csv
sqlite> .import C:/sqlite/3.1.6-songs.csv songs

When you import from a CSV file, there are two cases to consider:

1. 1. The table doesn’t already exist.

1. 1. The table already exists.

If the table (‘songs’) doesn’t exist, the first row of the CSV file is interpreted as column names, and the actual data starts on the second row of the CSV file.

sqlite> SELECT * FROM songs;
ID Reached number 1 Artist Single Label Weeks at number 1
---------- ---------------- ----------- ---------- ---------- -----------------
527 January 15, 1983 Men at Work Down Under Columbia 4 
528 February 5, 1983 Toto Africa Columbia 1 
529 February 19, 198 Patti Austi Baby, Come Qwest 2 
...

For the second case, when the table exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table.

ID Reached number 1 Artist Single Label Weeks at number 1
---------- ---------------- ---------- ---------- ---------- -----------------
ID Reached number 1 Artist Single Label Weeks at number 1
527 January 15, 1983 Men at Wor Down Under Columbia 4 
528 February 5, 1983 Toto Africa Columbia 1 

To avoid this, make sure that table does already exist, or remember to remove the first row with the header information after your import (we’ll learn how to do that later).

Reading commands

If you find yourself frequently importing the same data, or creating and populating the same table structures or data directly into SQLite, another option to import and process SQL commands from a file. To do this you can use the .read command. This processes each line of a file and executes it as a command in SQLite.

In the example below there is a text file that creates a Customer table and populates several records.

sqlite> .open Customers.db
sqlite> .tables
sqlite> .read C:/sqlite/customer-example.txt
sqlite> .tables
Customer 

We can check the contents of the table with .schema. This will show us:

sqlite> .schema Customer
CREATE TABLE Customer (
customerId INT NOT NULL PRIMARY KEY AUTOINCREMENT,
lastName TEXT,
firstName TEXT,
dob DATE,
mobile TEXT,
countryCode TEXT
);

If your file has lines with SQLite commands (eg, .schema or .help), they will be executed as the file is processed. This can be useful if you’re importing updated CSV files every day – in which case, the file might contain only dot commands.

For example:

.mode csv
.import path/to/file1.csv table1
.import path/to/file2.csv table2
.import path/to/file2.csv table3
.mode column

Exporting data

To export a table to a CSV file using DB4S, you can:

1. 1. Select File > Export > Table(s) as CSV file menu option.

1. 1. Choose the table that you want to export.

1. 1. Then select the specifications of the formatting of the CSV file.

1. 1. Click Save to export the file.

Using the command line to export data, run the following commands:

sqlite> .headers on
sqlite> .mode csv
sqlite> .once C:/sqlite/output.csv
sqlite> SELECT * FROM songs;

In this example, you already know that the .headers on command is used to print column labels as the first row of the output.

The command .once FILENAME instructs SQLite to send all query outputs into the named file rather than printing it on screen. instead of being printed on the console. In our example this causes writes the content to a file called songs.csv in our C:/sqlite directory.

Finally, the SELECT *... statement retrieves all the records from the songs table. You’ll learn more about SELECT, and how to specify particular columns and rows, or those that match certain criteria, later in the course. Being able to retrieve and export a subset of your data can be really useful when you’re conducting an exploratory analysis. Running a few short commands to query and filter information across multiple tables and exporting to a CSV file is a lot faster than trying to manually match and merge information in a program such as Excel.