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