How to Create a Database Using SQLite
Using DB Browser to create a databaseOne 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 databaseThe normal procedure to create a database in SQL is to use use the statement:
CREATE DATABASE MyFirstDatabase;
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.
Want to keep
FutureLearn online course,
Data Analytics for Business: Creating Databases
.open. You can use the
.databasecommand to see a list of existing databases. To create a database in SQLite, open a terminal and run:
Then run the
SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite>
.databasecommand to see a list of databases available. (Note, the path to your database might be different.)
If you have multiple databases open, or you forget to specify the database to open on the command line, you can also use the
SQLite version 3.31.1 2020-01-27 19:55:54 Enter ".help" for usage hints. sqlite> .databases main: C:\sqlite\MyFirstDatabase.db sqlite>
.opencommand in SQLite. For example:
sqlite> .open MyFirstDatabase.db sqlite> .databases main: C:\sqlite\MyFirstDatabase.db sqlite> .open MySecondDatabase.db sqlite> .databases main: C:\sqlite\MySecondDatabase.db
Importing and exporting data
Importing CSV filesIn 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:
- 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.
- 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.
- 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.
- Click OK at the bottom of the tab to create the table.
- 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.
.importcommand 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
.importcommand 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.
When you import from a CSV file, there are two cases to consider:
sqlite> .mode csv sqlite> .import C:/sqlite/3.1.6-songs.csv songs
- The table doesn’t already exist.
- The table already exists.
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.
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 4528 February 5, 1983 Toto Africa Columbia 1529 February 19, 198 Patti Austi Baby, Come Qwest 2...
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).
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 4528 February 5, 1983 Toto Africa Columbia 1
Reading commandsIf 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
.readcommand. 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
Customertable and populates several records.
We can check the contents of the table with
sqlite> .open Customers.db sqlite> .tables sqlite> .read C:/sqlite/customer-example.txt sqlite> .tables Customer
.schema. This will show us:
If your file has lines with SQLite commands (eg,
sqlite> .schema Customer CREATE TABLE Customer ( customerId INT NOT NULL PRIMARY KEY AUTOINCREMENT, lastName TEXT, firstName TEXT, dob DATE, mobile TEXT, countryCode TEXT );
.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 dataTo export a table to a CSV file using DB4S, you can:
- Select File > Export > Table(s) as CSV file menu option.
- Choose the table that you want to export.
- Then select the specifications of the formatting of the CSV file.
- Click Save to export the file.
In this example, you already know that the
sqlite> .headers on sqlite> .mode csv sqlite> .once C:/sqlite/output.csv sqlite> SELECT * FROM songs;
.headers oncommand is used to print column labels as the first row of the output. The command
.once FILENAMEinstructs 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
C:/sqlitedirectory. 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.
Data Analytics for Business: Creating Databases
Our purpose is to transform access to education.
We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.
We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.