Using SQLite

  1. Introduction
  2. Creating Databases and Tables
  3. Modifying a Table's Structure
  4. Adding Data to a Table
  5. Modifying Data in a Table
  6. Deleting or Modifying a Column
  7. Executing SQL Commands From a File
  8. Backing Up a Database

Introduction

An easy way to create and maintain Structured Query Language (SQL) databases at no cost is to use the free SQLite software, which is available for Linux, OS X, and Microsoft Windows systems and also as C source code.

SQLite is provided by Apple with the current version of OS X; I don't know when it was first included with OS X, but I know it has been present from at least OS X 10.8.5 (Mountain Lion). If you open the Terminal application, which you can find in /Applications/Utilities, and type which sqlite3, you should see the program.

$ which sqlite3
/usr/bin/sqlite3

If it isn't present on your Mac OS X system, download the precompiled binaries for Mac OS X (x86) from the SQLite Download Page. Currently the download file is a 1.06 MB .zip file. From the Apple OS X Finder, double-click on the downloaded ZIP file to extract its contents. You will then see 3 executable files in the folder in which the files have been extracted.

$ ls -lo ~/Downloads/sqlite-tools-osx-x86-3110100
total 4112
-rwxr-xr-x@ 1 jasmith1  580176 Mar  3 22:44 sqldiff
-rwxr-xr-x@ 1 jasmith1  892296 Mar  3 22:47 sqlite3
-rwxr-xr-x@ 1 jasmith1  627324 Mar  3 22:45 sqlite3_analyzer
$

You can obtain help information for the command by typing sqlite3 -help at the Terminal shell prompt.

$ sqlite3 -help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
if the file does not previously exist.
OPTIONS include:
   -bail                stop after hitting an error
   -batch               force batch I/O
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -echo                print commands before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -mmap N              default mmap size set to N
   -nullvalue TEXT      set text string for NULL values. Default ''
   -separator SEP       set output field separator. Default: '|'
   -stats               print memory stats before each finalize
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
$

You can obtain an sqlite prompt where you can issue SQL commands by typing sqlite3. You can get help information by typing .help at that prompt.

Udemy
$ sqlite3
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .help
.backup ?DB? FILE      Backup DB (default "main") to FILE
.bail on|off           Stop after hitting an error.  Default OFF
.clone NEWDB           Clone data into NEWDB from the existing database
.databases             List names and files of attached databases
.dump ?TABLE? ...      Dump the database in an SQL text format
                         If TABLE specified, only dump tables matching
                         LIKE pattern TABLE.
.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.
                         With no args, it turns EXPLAIN on.
.headers on|off        Turn display of headers on or off
.help                  Show this message
.import FILE TABLE     Import data from FILE into TABLE
.indices ?TABLE?       Show names of all indices
                         If TABLE specified, only show indices for tables
                         matching LIKE pattern TABLE.
.log FILE|off          Turn logging on or off.  FILE can be stderr/stdout
.mode MODE ?TABLE?     Set output mode where MODE is one of:
                         csv      Comma-separated values
                         column   Left-aligned columns.  (See .width)
                         html     HTML <table> code
                         insert   SQL insert statements for TABLE
                         line     One value per line
                         list     Values delimited by .separator string
                         tabs     Tab-separated values
                         tcl      TCL list elements
.nullvalue STRING      Use STRING in place of NULL values
.once FILENAME         Output for the next SQL command only to FILENAME
.open ?FILENAME?       Close existing database and reopen FILENAME
.output ?FILENAME?     Send output to FILENAME or stdout
.print STRING...       Print literal STRING
.prompt MAIN CONTINUE  Replace the standard prompts
.quit                  Exit this program
.read FILENAME         Execute SQL in FILENAME
.restore ?DB? FILE     Restore content of DB (default "main") from FILE
.save FILE             Write in-memory database into FILE
.schema ?TABLE?        Show the CREATE statements
                         If TABLE specified, only show tables matching
                         LIKE pattern TABLE.
.separator STRING      Change separator used by output mode and .import
.shell CMD ARGS...     Run CMD ARGS... in a system shell
.show                  Show the current values for various settings
.stats on|off          Turn stats on or off
.system CMD ARGS...    Run CMD ARGS... in a system shell
.tables ?TABLE?        List names of tables
                         If TABLE specified, only list tables matching
                         LIKE pattern TABLE.
.timeout MS            Try opening locked tables for MS milliseconds
.timer on|off          Turn SQL timer on or off
.trace FILE|off        Output each SQL statement as it is run
.vfsname ?AUX?         Print the name of the VFS stack
.width NUM1 NUM2 ...   Set column widths for "column" mode
                         Negative values right-justify
sqlite>

You can see the current settings for SQLite by issuing the .show command.

sqlite> .show
     echo: off
      eqp: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width: 
sqlite>

You can exit from the SQLite program by typing .exit at the sqlite> prompt or by hitting Ctrl-D.

Creating Databases and Tables

You can create a new database from the command line by entering the command sqlite3 DatabaseName.db, where DatabaseName.db is the name of the database you wish to create. When creating a table within a database, you need to decide what data type you will use for each column in the table - see Datatypes In SQLite Version 3.

E.g., I needed to track a list of people authorized to request work be done for various projects. The data I need to track consists of the following:

  1. Name - the individual's name; I'll create two columns in a table, one for the peron's first name and another one for the last name, and use a data type of text for both.
  2. UID: Each indvidual has a unique authorized user id assigned to him/her which is based on the person's name, so I'll use a text field for the value and make it the primary key for the table, since there might be two people named John Smith in the table, but they would have unique UIDs.
  3. Phone: I need each person's phone number, which I'll put in the table as a text field, since there won't be any arithmetic done on the numbers in the phone number and I'll include dashes or dots between groups of digits.
  4. Email: I also need the email address for every individual in the table, which will also be a text field.
  5. Note: I also want to include an optional note field, so that I can add any information I may want to add for a person.

You can create a new table with the CREATE TABLE table_name command.

CREATE TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype 
);

SQLite supports the following data types:

If you don't specify a datatype for a column, then SQLite assumes the "type affinity" is BLOB. If the delcared type of a column contains any of the strings "CHAR", "CLOB", or "TEXT", then that column has TEXT affinity. Since VARCHAR contains the string "CHAR", it is assigned the TEXT affinity1.

For each column, if I want to ensure that a value is always present for the column, I can add "NOT NULL" to the column specification, e.g., Last_Name text NOT NULL. In SQL, a NULL value in a column means that a record, i.e. a row in the database, does not have any value stored in that field. By using NOT NULL, I have ensured that SQLite won't allow a record to be created with NULL for that column.

I can also specify that I want a column or columns to be a primary key by adding that indicator to the column specification, e.g., UID text PRIMARY KEY NOT NULL. E.g., I can create a Work Requests (WRS) dtabase with a table containing information on requesters as shown below. I want all of the fields to be mandatory, except for the note field. I can see what tables exist in the database with the SQLite command .tables and I can see the layout for the table I've created with the .schema command.

$ sqlite3 WRS.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Requesters(
   ...> Last_Name text NOT NULL,
   ...> First_Name text NOT NULL,
   ...> UID text PRIMARY KEY NOT NULL,
   ...> Phone text NOT NULL,
   ...> Email text NOT NULL,
   ...> Note text
   ...> );
sqlite> .tables
Requesters
sqlite> .schema
CREATE TABLE Requesters(
Last_Name text NOT NULL,
First_Name text NOT NULL,
UID text PRIMARY KEY NOT NULL,
Phone text NOT NULL,
Email text NOT NULL,
Note text
);

As you enter the parts of a statement you can hit enter when you near the end of a line. SQLite will then display a ...> prompt where you can continue typing the rest of the statement, which you will terminate with a semicolon (;), or you can just continue typing a very long line and what you type will keep wrapping to the next line.

In this case, I also need to track the projects for which a requester is authorized to request work be done. An individual is often authorized to request work be done for multiple projects. Since I don't know beforehand how many projects a person may have to deal with and, since that number can grow over time, I will create another table which contains UIDs and projects for those UIDs.

sqlite> CREATE TABLE UID_Projects(
   ...> UID text PRIMARY KEY NOT NULL,
   ...> Project text NOT NULL
   ...> );
sqlite>

Now, if I issue the .tables command I can see I have two tables in the database. If I issue the .schema command, I will see the layout for both tables displayed. If I want to see just the layout for a particular table, I can specify it with .schema table_name where table_name is the name of a particular table.

sqlite> .tables
UID_Projects  Requesters   
sqlite> .schema UID_Projects
CREATE TABLE UID_Projects(
UID text PRIMARY KEY NOT NULL,
Project text NOT NULL
);
sqlite> .schema Requesters
CREATE TABLE Requesters(
Last_Name text NOT NULL,
First_Name text NOT NULL,
UID text PRIMARY KEY NOT NULL,
Phone text NOT NULL,
Email text NOT NULL,
Note text
);
sqlite> .schema
CREATE TABLE Requesters(
Last_Name text NOT NULL,
First_Name text NOT NULL,
UID text PRIMARY KEY NOT NULL,
Phone text NOT NULL,
Email text NOT NULL,
Note text
);
CREATE TABLE UID_Projects(
UID text PRIMARY KEY NOT NULL,
Project text NOT NULL
);

When you want to exit from the SQLite program, you can hit the Ctrl-D keys simultaneously.

Modifying a Table's Structure

What do you do if, afer you've created a table, you want to add additional columns? You can use the ALTER TABLE command in such instances. The format of a command to add a column is ALTER TABLE table_name ADD COLUMN column_def or you can use ALTER TABLE database_name.table_name ADD COLUMN column_def.

The ADD COLUMN syntax is used to add a new column to an existing table. The new column is always appended to the end of the list of existing columns. The column-def rule defines the characteristics of the new column. The new column may take any of the forms permissible in a CREATE TABLE statement, with the following restrictions:

Note: After ADD COLUMN has been run on a database, that database will not be readable by SQLite version 3.1.3 and earlier, if you transfer the database to another system.

E.g., for the Requesters table I created above, I'd like to add three additional fields that will contain date values: the date when a person was authorized to submit requests, the date the last update was made for the person, e.g., when he/she was given aurhorization to submit requests for another existing project or new project, and a removal date, if the person is no longer authorized to submit any requests, e.g., he/she left for another job.

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values2:

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

I chose TEXT for this field, also. I don't need times, only dates and I don't need to perform any calculations involving the dates.

To add the three new fields, I can enter the ALTER TABLE commands shown below:

$ sqlite3 WRS.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> .schema Requesters
CREATE TABLE Requesters(
Last_Name text NOT NULL,
First_Name text NOT NULL,
UID text PRIMARY KEY NOT NULL,
Phone text NOT NULL,
Email text NOT NULL,
Note text
);
sqlite> ALTER TABLE Requesters ADD COLUMN Added text;
sqlite> ALTER TABLE Requesters ADD COLUMN Last_Modified text;
sqlite> ALTER TABLE Requesters ADD COLUMN Removed text;
sqlite> .schema Requesters
CREATE TABLE Requesters(
Last_Name text NOT NULL,
First_Name text NOT NULL,
UID text PRIMARY KEY NOT NULL,
Phone text NOT NULL,
Email text NOT NULL,
Note text
, Added text, Last_Modified text, Removed text);
sqlite>

New columns are added at the end of the table structure as shown above. If there are records in the table already, the new columns will have a value of NULL assigned to them.

Adding Data to a Table

Once you have created a table, you can add records to it with a INSERT INTO statement in the form below:

INSERT INTO table_name (column1, column2, column3, ... columnN) VALUES (value1, value2, value3, ... valueN);

E.g. for the Requesters table I created, I could insert a record as shown below:

sqlite> INSERT INTO Requesters (Last_Name, First_Name, UID, Phone, Email,
   ...> Last_Modified) VALUES ("Wilson", "David", "dawilso1", "703.555.1234",
   ...> 2016-04-07);
Error: 5 values for 6 columns
sqlite> INSERT INTO Requesters (Last_Name, First_Name, UID, Phone, Email,
   ...> Last_Modified) VALUES ("Wilson", "David", "dawilso1", "703.555.1234",
   ...> "david.a.wilson-1@example.com", 2016-04-07);
sqlite>

When I first entered the statement, I specified 6 columns, but only entered data for 5 columns; I forgot to add the email address as a value, so SQLite displayed the error message "5 values for 6 columns". If you enter the statement correctly, you will be returned to the sqlite prompt. I entered values for all of the fields that can not have a NULL value, but for others where I didn't specify "NOT NULL" for the column definition, I can omit a value and, in the example above, I omitted inserting any data for the "Note", "Added", and "Removed" fields.

Now I need to add projects for the person in the UID_Projects table.

sqlite> INSERT INTO UID_Projects (UID, Project) VALUES (dawilso1, ICE);
Error: no such column: dawilso1
sqlite> INSERT INTO UID_Projects (UID, Project) VALUES ("dawilso1", "ICE");
sqlite>

Why did SQLite display a "no such column" error for the first attempt? I needed to enclose the text values I wanted to insert in the record within double or single quotes. When I enclosed the values in quotes, SQLite accepted the values.

If I want to see all of the records in a table, I can use a SELECT * FROM table_name statement. E.g., though so far I have only one record in the table:

sqlite> SELECT * FROM Requesters;
Wilson|David|dawilso1|703.555.1234|david.a.wilson-1@example.com|||2005|
sqlite>

For fields, where I didn't specify a value, I will see two vertical bars, with no value between them, i.e., || for the "Note" and "Added" values, if the columns aren't at the end of the table. For this table, the last column was Removed. Since I didn't specify a value for that field, there is a vertical bar, also referred to as a "pipe" character, with no value after it at the end of the record.

Modifying Data in a Table

Though I entered 2016-04-07 for the Last_Modified date, I see 2005 displayed in that field. The values don't match, because I didn't include the date value in quotes when I entered it. But I can fix that problem by using an UPDATE statement.

sqlite> UPDATE Requesters SET Last_Modified = '2016-04-07' WHERE UID="dawilso1";
sqlite> SELECT * FROM Requesters;
Wilson|David|dawilso1|703.555.1234|david.a.wilson-1@example.com|||2016-04-07|
sqlite>

In this case since there was only one record, I could have omitted the WHERE clause, but, if there were multiple records in the table, I would want to stipulate which record(s) I wanted to alter using a WHERE condition.

Deleting or Modifying a Column

Now I'll add an additional project for David Wilson in the UID_Projects table.

sqlite> INSERT INTO UID_Projects (UID, Project) VALUES ("dawilso1", "TESS");
Error: UNIQUE constraint failed: UID_Projects.UID
sqlite>

Why did SQLite display the error message "UNIQUE constraint failed"? The error message was displayed because I specified that UID should be a primary key for the table, which means it should be a unique value. If I need multiple records in the table with the same UID, then I should not have made it a primary key. Unfortunately, SQLite does not provide a capability to easily alter a column or even delete a column. Instead, I'll need to copy the data in the table to a new, similar table that doesn't have that constraint.

For the workaround of copying the data to a new table, I can rename a table and copy the contents of a table into another table. So, first I'll rename the exiting UID_Projects table and then I'll create a new, similar table using its prior name, but this time without the UID as a primary key. Then I'll copy the contents of the old table to the new one, though, granted, since I've only got one record in the table, it wouldn't be a substantial effort to just enter that same record in the new table by retyping the data.

sqlite> ALTER TABLE UID_Projects RENAME TO UID_Projects_Old;
sqlite> CREATE TABLE UID_Projects (UID TEXT NOT NULL, Project TEXT NOT NULL);
sqlite> INSERT INTO UID_Projects SELECT * FROM UID_Projects_Old;
sqlite>

I can verify the existing data has been copied correctly, if I wish, and then I can "drop" the old table, i.e., remove the table from the database.

sqlite> SELECT * FROM UID_Projects;
dawilso1|ICE
sqlite> .tables
UID_Projects      UID_Projects_Old  Requesters       
sqlite> DROP TABLE UID_Projects_Old;
sqlite> .tables
UID_Projects  Requesters   
sqlite>

I can then add a new record with the same UID to the table.

sqlite> INSERT INTO UID_Projects VALUES ('dawilso1', 'TESS');
sqlite> SELECT * FROM UID_Projects;
dawilso1|ICE
dawilso1|TESS
sqlite>

If I'm including all of the values with the INSERT statement, I don't need to specify which columns they go into; I just need to put them in the correct order. So, instead of using INSERT INTO table_name (col1, col2, ... colN) VALUES (value1, value2, ... valueN), I can just use a statement in the form INSERT INTO table_name VALUES (value1, value2, ... valueN.

Executing SQL Commands From a File

You can enter SQL statements at the sqlite> prompt or you can execute commands you have stored in a text file using .read filename where filename is the name of the file where you have stored the SQL statements. E.g., I realized that I needed to delete a column from a table, so I renamed the existing table, since SQLite doesn't provide a "delete column" statement. I then wanted to use the existing schema, but with a modification for one of the columns. So I issued the command .schema old_table to see the existing table layout, then copied and pasted the schema information into a text file, then altered the SQL so it would create a new table with the columns that I needed. I was then able to import the table layout I needed to create the new table from the createdb.txt file I had created. I could then copy the records from the old to the new version of the table and then "drop" the old table. You could also give the file the extension .sql rather than .txt, if you wanted to make it clear that the file contained SQL statements.
sqlite> .read createdb.txt

sqlite>

Backing Up a Database

You can see the location for the file containing the database you have open using the .databases command. If you wish to backup that database you can issue the command .backup to back it up to a file3.

.backup DB FILE Backup DB (default "main") to FILE

The backup file will contain the entire database with all its tables and records. E.g.:

$ sqlite3 MSGRS.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /Users/jasmith1/Documents/Work/MSGRS/MSGRS.db             
sqlite> .backup MSGRS_Backup_20160412.db
sqlite> .exit
$ ls -lgh MSGRS*.db
-rw-r--r--  1 ABC\Domain Users    24K Apr 12 16:46 MSGRS.db
-rw-r--r--  1 ABC\Domain Users    24K Apr 12 19:17 MSGRS_Backup_20160412.db
$

Or you can use the .save FILE command specifying a new file name for the output file.

.save FILE Write in-memory database into FILE

E.g., suppose I've opened the MSGRS.db database file, I can save another copy of it to a different file name as shown below:.

$ ls -lgh MSGRS*.db
-rw-r--r--  1 ABC\Domain Users    24K Apr 12 16:46 MSGRS.db
$ sqlite3 MSGRS.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> .databases
seq  name             file                                                      
---  ---------------  ----------------------------------------------------------
0    main             /Users/jasmith1/Documents/Work/MSGRS/MSGRS.db             
sqlite> .save MSGRS_Backup.db
sqlite> .exit
$ ls -lgh MSGRS*.db
-rw-r--r--  1 ABC\Domain Users    24K Apr 12 16:46 MSGRS.db
-rw-r--r--  1 ABC\Domain Users    24K Apr 12 19:07 MSGRS_Backup.db

Alternatively, you can simply copy the .db file to another location or file name at a shell prompt outside of SQLite, since all of the data is stored in the .db file.

References:

  1. Datatypes In SQLite Version 3
    SQLite
  2. SQLite Query Language: Date And Time Functions
    SQLite
  3. Command Line Shell For SQLite
    SQLite

 

Firstrade newegg.com

Justdeals Daily Electronics Deals1x1 px