SQLite3 : command line Interface for managing SQLite database files

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 (.)
			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.
				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');
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
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;
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;
To other formats we can export data, list of formats supported is here.

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
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
"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.


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


* indicates required
Subscribe to plus2net


    Post your comments , suggestion , error , requirements etc here

    PHP video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer