Asst. Prof.
Viral Patel Subject : 303-Database Handling using Python
Unit-2: Database backup and CSV handling
SQLite Dump Command
In SQLite Dump command is used to dump the databases, table’s schema and tables data based
on our requirements.
By using SQLite Dump command we can save the structure and data of the database tables in
SQL format to the disk.
Syntax of SQLite Dump Command
Following is the syntax of SQLite Dump command to dump databases, tables structure and data
based on our requirements.
sqlite> .dump
The above syntax will dump complete database.
Now we will see how to use SQLite dump command to dump database and tables with examples.
SQLite Dump Command Examples
Following are the examples of using SQLite Dump command to dump tables and databases
based on our requirements.
SQLite Dump Database Table
Now we will see how to dump particular table in database for that design table called
“test_dump” in your database and insert some data using following query.
CREATE TABLE test_dump (a INTEGER);
INSERT INTO test_dump(a) values(1);
INSERT INTO test_dump(a) values(2);
INSERT INTO test_dump(a) values(3);
Once we execute above queries we will check the data of “test_dump” table using following
query.
sqlite> select * from test_dump
a
---------
1
2
3
Now we will see how to dump “test_dump” table from database using following command.
Page 1 of 6
Asst. Prof. Viral Patel Subject : 303-Database Handling using Python
sqlite> .dump test_dump
When we execute above command we will get the output like as shown following.
PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
CREATE TABLE test_dump(a INTEGER);
INSERT INTO 'test_dump' VALUES(1);
INSERT INTO 'test_dump' VALUES(2);
INSERT INTO 'test_dump' VALUES(3);
COMMIT;
If you observe here we just dump smaller table but if we want to dump larger database, then
SQLite output will take longer time to generate output on screen. The only solution is we can
redirect output to file.
Redirect Dump Output to File
If we want to dump our database to SQL file then first we need to use “.output” command. After
that we need to use “.dump” command to redirect result to defined file.
Following is the example of illustrating above process to dump database or tables to output file.
.output test_dump.sql
.dump test_dump
.quit
The above statements will create “test_dump.sql” in the location where [Link] is exist like as
shown below.
If you open “test_dump.sql” file that will contain content like shown following.
PRAGMA foreign_keys =OFF;
BEGIN TRANSACTION;
CREATE TABLE test_dump(a INTEGER);
INSERT INTO 'test_dump' VALUES(1);
INSERT INTO 'test_dump' VALUES(2);
INSERT INTO 'test_dump' VALUES(3);
COMMIT;
SQLite Dump Whole Database
Suppose if we want to dump whole database instead of just one table, then we need to
use dump command without specifying any table name as shown following.
sqlite> .dump
The above command will give output like as shown following.
PRAGMA foreign_keys =OFF;
BEGIN TRANSACTION;
Page 2 of 6
Asst. Prof. Viral Patel Subject : 303-Database Handling using Python
CREATE TABLE test_dump(a INTEGER);
INSERT INTO 'test_dump' VALUES(1);
INSERT INTO 'test_dump' VALUES(2);
INSERT INTO 'test_dump' VALUES(3);
COMMIT;
SQLite Dump Only Table Schema
To dump only schema of specific table, SQLite offers “.schema” command and we can redirect
output of this command to external file using “.output” command.
Following is the example of dumping only schema of “products” table.
sqlite> .output [Link]
sqlite> .schema products
sqlite> .quit
Whe we execute above queries “[Link]” is generated at the location where
our [Link] is exist like as shown following.
The content of “[Link]” file will be like as shown following.
CREATE TABLE products
(
Product_id INTEGER PRIMARY KEY,
Product_name VARCHAR NOT NULL,
Qty INTEGER
);
This is how we can use “dump” command in SQLite to dump databases and tables based on our
requirements.
SQLite Import CSV File Data to Table
([Link]
In SQLite we can easily import data from external files like CSV, excel to database tables by using
the “.import” command.
Syntax of SQLite Import Command
Following is the syntax of the SQLite “import” command to import data from external files.
.import filename table-name
In the above syntax, the .import command is used to import data from an external file and insert
it into the specified table. To import data from the CSV file, you must have to execute
command .mode CSV before .import command, and the table must already exist.
Page 3 of 6
Asst. Prof. Viral Patel Subject : 303-Database Handling using Python
Now we will see how to import data from CSV file to database table with examples.
SQLite Import Data from CSV to Table
I have a “test” CSV file at the location where our [Link] is existed like as shown following.
The [Link] file contains data like as shown below.
1,Shweta
2,Vinay
3,Lax
Now to import data from CSV file first create tabled called “test” in the database using the
following query statement.
CREATE TABLE test (id INTEGER PRIMARY KEY, value text);
Once we did with table creation, now we will import [Link] data to test table like as shown
below.
sqlite> .import [Link] test
If you observe the above example we are importing data from [Link] file to test table. Now, we
will check the records of the test table using following statement.
sqlite> SELECT * FROM test;
id value
---------- ----------
1 Shweta
2 Vinay
3 Lax
This is how we can import SQLite data to CSV or excel or other external files based on our
requirements.
SQLite Export Data to CSV File
([Link]
In SQLite, by using “.output” command we can export data from database tables to CSV or excel
external files based on our requirement.
Page 4 of 6
Asst. Prof. Viral Patel Subject : 303-Database Handling using Python
Syntax of SQLite Export Command
Following is the syntax of “.output” command to export data from the database to CSV or excel
file.
.output (filename)
SQLite Export Data to CSV File Example
We will export “emp_master” table data to [Link] file for that write the query like as
shown below. Let’s look at the example of exporting data of emp_master table
to [Link] file. This file does not exist. So it will first create and export data into it.
sqlite> .header on
sqlite> .mode csv
sqlite> .output [Link]
sqlite> SELECT * FROM emp_master;
sqlite> .quit
If you observe above example to export data from SQLite database to CSV or Excel file we
followed few steps those are
1. To insert table column names in CSV or Excel file we used .header on command.
2. To return the data in CSV format we used .mode CSV.
3. To send data to CSV file we used .output command and SELECT statement to export data
from the required table.
Once we execute the above statements [Link] file will create in the folder where
our [Link] file exists with emp_master table data like shown following.
When we open the [Link] file that will contain all the records of emp_master table like as
shown below.
emp_id,first_name,last_name,salary,dept_id
1,Honey,Patel,10100,1
2,Shweta,Jariwala,19300,2
3,Vinay,Jariwala,35100,3
4,Jagruti,Viras,9500,2
Page 5 of 6
Asst. Prof. Viral Patel Subject : 303-Database Handling using Python
5,Shweta,Rana,12000,3
6,Sonal,Menpara,13000,1
7,Yamini,Patel,10000,2
8,Khyati,Shah,50000,3
9,Shwets,Jariwala,19400,2
This is how we can export data from SQLite database to CSV or EXCEL files based on our
requirements.
Other Reference : [Link]
Page 6 of 6