sqlite3

Download the sqlite3.exe file from www.sqlite.org.

SQLite3 : command-line tool for managing SQLite database files

We kept the files inside the folder E:\sqlite3_mgmt\. After opening the window command prompt, we navigate to the location and given the command sqlite3.exe or sqlite3 to run the application.

You can keep the files in any other location.
Command Prompt to run sqlite3

How to close the sqlite3 application?

sqlite> .exit

Listing all the commands by using help

sqlite> .help

Note that all our commands starts with dot (.), however this is not required for SQL statements.

This code will create a database file named my_student.db in the specified path if it does not already exist. If the database file already exists, it will be opened instead.

sqlite> .open E:/my_db/my_student.db
sqlite> .tables

Let us create one Table, here is a multiline SQL terminated by using ; and not starting with dot (.)

sqlite> CREATE TABLE IF NOT EXISTS
			student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      );

List all tables ( dot (.) is used here)

sqlite> .tables

Insert one record to our student table ( Note : dot (.) is not used here) but terminated with ;

sqlite> INSERT INTO `student` 
(`id`, `name`, `class`, `mark`, `gender`) VALUES
(1, 'John Deo', 'Four', 75, 'female');

Display all records of the student table

sqlite> SELECT * FROM student;

Output is here

1|John Deo|Four|75|female

mode 🔝

Get the type of listing by using .mode command.

sqlite> .mode

We can display records in different mode, the values for mode are list, quote, line, column,box, table, markdown

sqlite> .mode column

Output is here

sqlite> SELECT * FROM student;
id  name      class  mark  gender
--  --------  -----  ----  ------
1   John Deo  Four   75    female

sqlite> .mode insert new_table

Output is here

sqlite> .mode insert
sqlite> SELECT * FROM student;
INSERT INTO "table"(id,name,class,mark,gender) VALUES(1,'John Deo','Four',75,'female');

We can use other output modes : "csv", "json", and "tcl"

Let us delete the table

sqlite> DROP table student;

Open Database 🔝

Open database and list tables. This will create the database file my_student.db in the given path if it is not there.

sqlite> .open E:/my_db/my_student.db
sqlite> .tables

Checking the connection to database. This will list all the connection by giving a number.

sqlite> .conn
ACTIVE 0: E:/sqlite3_mgmt/analytics3.db

Save the database 🔝

sqlite> .save my_student.db

In a different location

sqlite> .save E:/my_db/my_student.db

Create the table with 35 records 🔝

Create a new Database my_student2.db and then store 35 records of student table

sqlite> .open E:/my_db/my_student2.db

There are two parts of this command, one is to create the table and other one is to insert the records.

CREATE TABLE IF NOT EXISTS
				student(id integer primary key, 
                      name text, 
                      class text, 
                      mark integer, 
                      gender text 
                      );
					  
INSERT INTO `student` 
(`id`, `name`, `class`, `mark`, `gender`) VALUES
(1, 'John Deo', 'Four', 75, 'female'),
(2, 'Max Ruin', 'Three', 85, 'male'),
(3, 'Arnold', 'Three', 55, 'male'),
(4, 'Krish Star', 'Four', 60, 'female'),
(5, 'John Mike', 'Four', 60, 'female'),
(6, 'Alex John', 'Four', 55, 'male'),
(7, 'My John Rob', 'Five', 78, 'male'),
(8, 'Asruid', 'Five', 85, 'male'),
(9, 'Tes Qry', 'Six', 78, 'male'),
(10, 'Big John', 'Four', 55, 'female'),
(11, 'Ronald', 'Six', 89, 'female'),
(12, 'Recky', 'Six', 94, 'female'),
(13, 'Kty', 'Seven', 88, 'female'),
(14, 'Bigy', 'Seven', 88, 'female'),
(15, 'Tade Row', 'Four', 88, 'male'),
(16, 'Gimmy', 'Four', 88, 'male'),
(17, 'Tumyu', 'Six', 54, 'male'),
(18, 'Honny', 'Five', 75, 'male'),
(19, 'Tinny', 'Nine', 18, 'male'),
(20, 'Jackly', 'Nine', 65, 'female'),
(21, 'Babby John', 'Four', 69, 'female'),
(22, 'Reggid', 'Seven', 55, 'female'),
(23, 'Herod', 'Eight', 79, 'male'),
(24, 'Tiddy Now', 'Seven', 78, 'male'),
(25, 'Giff Tow', 'Seven', 88, 'male'),
(26, 'Crelea', 'Seven', 79, 'male'),
(27, 'Big Nose', 'Three', 81, 'female'),
(28, 'Rojj Base', 'Seven', 86, 'female'),
(29, 'Tess Played', 'Seven', 55, 'male'),
(30, 'Reppy Red', 'Six', 79, 'female'),
(31, 'Marry Toeey', 'Four', 88, 'male'),
(32, 'Binn Rott', 'Seven', 90, 'female'),
(33, 'Kenn Rein', 'Six', 96, 'female'),
(34, 'Gain Toe', 'Seven', 69, 'male'),
(35, 'Rows Noump', 'Six', 88, 'female');

Reading Structure of the table 🔝

There are different ways to get the structure of the table. Here invoice_client is our table name

sqlite>  .schema invoice_client

CREATE TABLE `invoice_client` (
  `client_id` integer primary key,
  `client_name` text NOT NULL,
  `client_add` text ,
  `client_state` text,
  `client_country` text ,
  `client_email` text ,
  `client_phone` text
);

sqlite> PRAGMA table_info('invoice_client');

0|client_id|INTEGER|0||1
1|client_name|TEXT|1||0
2|client_add|TEXT|0||0
3|client_state|TEXT|0||0
4|client_country|TEXT|0||0
5|client_email|TEXT|0||0
6|client_phone|TEXT|0||0

sqlite>  SELECT sql FROM sqlite_master WHERE tbl_name='invoice_client';

CREATE TABLE `invoice_client` (
  `client_id` integer primary key,
  `client_name` text NOT NULL,
  `client_add` text ,
  `client_state` text,
  `client_country` text ,
  `client_email` text ,
  `client_phone` text
)

Export table to CSV,Excel, Json 🔝

.headers on This will keep the column headers , it can be set to off ( default )
.mode csv Output as csv format
.once E:/sqlite3_mgmt/student.csv Output is stored in CSV file at the path given.
.system E:/sqlite3_mgmt/student.csv Same as double click on file or opening the file.

If path is correct then .once will create the student.csv file inside the given directory.
Let us check this

sqlite> .headers on
sqlite> .mode csv
sqlite> .once E:/sqlite3_mgmt/student.csv
sqlite> SELECT * FROM student LIMIT 0,5;
sqlite> .system E:/sqlite3_mgmt/student.csv
sqlite>

This will save and open the student.csv file from the location.
In above code change the file type to Excel

sqlite> .excel
sqlite> SELECT * FROM student LIMIT 0,5;
sqlite>

This will create one temporary file with the data and user can save the file in desired location.
Let us create Json file

sqlite> .mode json
sqlite> .once E:/sqlite3_mgmt/student.json
sqlite> SELECT * FROM student LIMIT 10,5;
sqlite>

To other formats we can export data, list of formats supported is here.

ascii,box,csv,column,html,insert,json,line,
list,markdown,quote,table,tabs,tcl

Import Data : Create table from CSV file 🔝

We will open our sample database. Here use your location ( path )

sqlite>.open E:\sqlite3_mgmt\my_db\my_db.db

List of tables in our my_db.db database

sqlite>.tables

As we already have student table so we can delete the same.

sqlite> DROP TABLE student;

Since we are going to import one CSV file to create the table so change the mode. Now our SQLite is ready to use CSV file.

sqlite> .mode csv

Here we are creating a table name student1 by importing the csv file from the path D:\my_db\student.csv
Download the sample student CSV file from here.

sqlite> .import D:\my_db\student.csv student1

Once our student1 table is created we can check the list of tables by using .tables command.

sqlite> .tables

Use query to get all the records.

sqlite> SELECT * FROM student1;

Get the structure of our student1 table

sqlite> .schema student1

Output

CREATE TABLE IF NOT EXISTS "student1"(
"id" TEXT, "name" TEXT, "class" TEXT, "mark" TEXT,
 "gender" TEXT);

Note that here id and mark columns are TEXT datatype, it should be changed to integer ( INT ) .

We can't use the ALTER TABLE statement to modify a column data type in SQLite. Instead we will rename the table, create a new table, and copy the data into the new table.

sqlite> CREATE TABLE student ( id int,name TEXT,class TEXT,mark INT, gender TEXT);

Now copy all data from student1 table to student table.

sqlite> INSERT INTO student(id,name,class,mark,gender) 
	SELECT id,name,class, mark,gender FROM student1;

Check the data in our student table.

sqlite> SELECT * FROM student;

Delete the student1 table as we have copied the data to our new table.

sqlite> DROP TABLE student1;

Backup and restore database by using dump 🔝

sqlite> .open D:/sqlite3_mgmt/my_student.db
sqlite> .output D:/sqlite3_mgmt/my_dump.sql
sqlite> .dump
sqlite> .exit

Create the database with all tables from the file ( my_dump.sql )

sqlite> .read D:/sqlite3_mgmt/my_dump.sql
sqlite> .save D:/sqlite3_mgmt/my_save.db
sqlite> .exit

Our new database name is my_save.db, from this database let us save one table ( table name is y22_12 ) . Open this database if it is not opened.

sqlite> .output D:/sqlite3_mgmt/y22_12.sql
sqlite> .dump y22_12

Create backup of the table structure only of the table y22_12

sqlite> .output D:/sqlite3_mgmt/y22_12_str.sql
sqlite> .schema y22_12

Create backup of all the tables of the database ( structure only )

sqlite> .open D:/sqlite3_mgmt/my_save.db
sqlite> .output D:/sqlite3_mgmt/my_all.sql
sqlite> .schema

Using environmental variable

In my localhost I use SQLite database to store traffic details. This is the string I use to connect to my database.

.open C:/xampp/htdocs/plus2net/templates/temp1/analytics3.db

I copy this code and use but I keep the sqlite3.exe file in a removable disk so the drive name changes as I connect to different systems. If you are using one system then better to use environmental variable to call this sqlite3.exe file.

sqlite3_changes()

This function is a useful tool for working with SQLite databases. It can be used to verify that queries are executed successfully.

SELECT changes()

More on sqlite3 changes() function
SQLite SQLite3 and PHP

Subscribe to our YouTube Channel here

SQLite3 : command line Interface for managing SQLite database files

mode 🔝

Open Database 🔝

Save the database 🔝

Create the table with 35 records 🔝

Reading Structure of the table 🔝

Export table to CSV,Excel, Json 🔝

Import Data : Create table from CSV file 🔝

Backup and restore database by using dump 🔝

Using environmental variable

sqlite3_changes()

Subscribe