SQLite Commands

  Total Views : 380
  Comments : 0
  Keywords : Basic commands of sqlite3 command line tool with examples, Useful sqlite commands to execute queryies in sqlite3 command line tool

Here we will learn most commonly used commands of SQLite3 command line program to show tables, database, indexes, schema of tables, etc. with examples. 

SQLite Commands

The SQLite project provides a command line tool called SQLite3 to allow users to interact with SQLite databases to perform insert, update, delete, etc. operations by writing SQLite statements based on our requirements.

 

The SQLite3 command line tool provides some special commands which are called as “dot (.) commands” to define output format for tables, examine databases and for other administrative operations.

 

These SQLite3 dot commands always starts with “dot (.)”. Here we need to remember that we should not use semicolon (;) to define termination of statement.

 

To see all the available commands in SQLite run following command.

 

sqlite.help

The above “.help” command will list all the available commands in SQLite like as shown below.

 

CommandDescription
.auth ON|OFF Show authorizer callbacks
.backup ?DB? FILE Backup DB (default "main") to FILE
.bail on|off Stop after hitting an error. Default OFF
.binary on|off Turn binary output on or off. Default OFF
.changes on|off Show number of rows changed by SQL
.check GLOB Fail if output since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.databases List names and files of attached databases
.dbinfo ?DB? Show status information about the database
.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
.eqp on|off|full Enable or disable automatic EXPLAIN QUERY PLAN
.exit Exit this program
.explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help Show this message
.import FILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of all indexes. If TABLE specified, only show indexes for tables matching LIKE pattern TABLE.
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues. Options: fkey-indexes. Find missing foreign key indexes
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?TABLE?

Set output mode where MODE is one of:

 

  • ascii - Columns/rows delimited by 0x1F and 0x1E
  • 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 strings
  • quote - Escape answers as for SQL
  • 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 ?--new? ?FILE? Close existing database and reopen FILE. The --new starts with an empty file
.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
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN. Add --indent for pretty-printing
.separator COL ?ROW? Change the column separator and optionally the row separator for both the 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? Show stats or 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.
.testcase NAME Begin redirecting output to 'testcase-out.txt'
.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
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set column widths for "column" mode. Negative values right-justify

Now we will see some of the most commonly used commands in SQLite3 to work with SQLite databases.

SQLite Check All Available Databases

In SQLite by using “.databases” command we can list names and files of attached databases.

 

Following is the simple example of getting all the available databases.

 

sqlite.databases

 

seq  name             file

---  ---------------  ------------------------

0    main             d:\sqlite\db\users.db

SQLite Take a Backup of Database File

By using SQLite “.backup” command we can easily take the backup of available database.

 

Following is the example of taking backup of database (default "main") to a file.

 

C:\>cd sqlite

C:\sqlite>sqlite3 users.db

SQLite version 3.13.0 2016-05-18 10:57:30

Enter ".help" for usage hints.

 

sqlite.databases

 

seq  name             file

---  ---------------  ---------------------

0    main             C:\sqlite\users.db

 

sqlite.backup d:/sqlite/backup/user_backup

If you observe above example we are opening SQLite3 tool from command prompt and taking backup of available database.

 

Now, Here you can see back up is taken at d:/sqlite/backup/user_backup.

 

SQLite Take a Backup of Database File

SQLite Clone Database 

In SQLite by using “.clone” command we can easily clone new database from existing database.

 

Following is the example of cloning new database from existing database.

 

C:\>cd sqlite

C:\sqlite>sqlite3 users.db

SQLite version 3.13.0 2016-05-18 10:57:30

Enter ".help" for usage hints.

 

sqlite> .open d:/sqlite/backup/user_backup.db

 

sqlite.tables

 

ADDRESS_MASTER  USER_MASTER

 

sqlite> .clone d:/sqlite/backup/user_backup2.db

 

USER_MASTER... done

ADDRESS_MASTER... done

 

sqlite>

If you observe above example we are cloning new database user_backup2.db from existing database user_backup.db in same folder.

SQLite Exit Tool

In SQLite by using “.exit” command we can easily exit sqlite3 program.

 

sqlite.exit

When we execute above command we will come out of sqlite3 program.

SQLite Import Data from File to Table

By using “.import” command we can easily import data from file to tables based on our requirements.

 

Following is the syntax of .import command in SQLite to import data from file to table.

 

sqlite.import FILE TABLE

Now we will see how to import CSV file data into SQLite table using ".import" command. The ".import" command takes two arguments one is name of the file from which CSV file data need to be read and another one is name of the SQLite table to insert CSV data.

 

It is important to set the "mode" to "csv" before running the ".import" command.

 

Following is the example of importing data from CSV file to database table using “.import” command.

 

sqlite.tables

 

ADDRESS_MASTER  USER_MASTER

 

sqlite.mode csv

 

sqlite.import d:/sqlite/backup/tempuser.csv user_table

 

sqlite.tables

 

ADDRESS_MASTER  USER_MASTER     user_table

 

sqlite.mode column

 

sqliteSELECT FROM user_table;

 

U_TYPE      U_NAME

----------  ----------

1           ADMIN

2           REGISTERED

3           GUEST

In the above example, we are able to see all the available tables using .table command. After that we imported all data of tempuser.csv file to user_table table and again we fire .table command to see the list of tables in database.

SQLite Quit Command

By using SQLite “.quit” command we can exit from current database. Following is the simple example of quitting from current database.

 

sqlite.quit

When we run above command sqlite3 tool will exit from current database session.

SQLite Structure of the Table

By using “.schema” command we can easily check the schema of the tables and indexes which we created on table columns in database.

 

Following is the syntax of SQLite Schema command to check the schema of tables.

 

.schema ?TABLE?

This command is used to see the schema of a table i.e. create statement of the table which we have passed in ?TABLE? parameter and it also shows the indexes which we created on the table columns.

 

Following is the example of executing .schema command to see the database schema information.

 

sqlite>.schema

 

CREATE TABLE USER_MASTER(U_TYPE INTEGER PRIMARY KEY, U_NAME VARCHAR(50));

CREATE TABLE ADDRESS_MASTER(ADDRESS1 VARCHAR(100),CITY VARCHAR(20),PINCODE VARCHAR(6));

CREATE INDEX id_index ON USER_MASTER(U_TYPE);

The above command will return all the available tables schema details. In case if you want to check schema of particular table means we need to write command like as shown following.

 

sqlite.schema user_master

 

CREATE TABLE USER_MASTER(U_TYPE INTEGER PRIMARY KEY, U_NAME VARCHAR(50));

The above command return only schema of “user_master” table.

SQLite Show All the Tables in Database

By using “.tables” command we can easily check available tables in SQLite database.

 

Following is the syntax of SQLite tables command to see all the available tables.

 

.tables ?TABLE?

The above syntax list the names of all available tables in database. In case if we specify table name only matching table will be returned.

 

Following is the example of using “.tables”command to see available tables in database.

 

sqlite.tables

 

ADDRESS_MASTER  USER_MASTER     table1          table2

 

sqlite.tables ADDRESS_MASTER

 

ADDRESS_MASTER

This is how we can use commands in SQLite to get required information.

 
 

 Comments (0)

Be the first to give your valuable feedback

Leave a comment

  • Default Tutlane User