SQLite

CSC 342 - Web Technologies

SQLite Command Line Interface

  • SQLite is a DBMS that stores the database in a single file

  • SQLite has a command line interface CLI to interact with a database file

  • The command to start the CLI on the Unix server is:

    $ sqlite3
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

  • Note that the dollar sign indicates the Unix command line prompt.

SQLite commands

  • .show: displays the current settings for various paramters

  • .quit: quit the CLI program

  • .tables: show the tables in the database

  • .schema: display the table schema

  • .header: display or hide the column headers

  • .mode: select the mode for output

  • .dump: dump the database as SQL text

SQLite Datatypes

  • NULL: the value is NULL

  • INTEGER: the NULL value

  • REAL: the value is a floating point number

  • TEXT: the value is a text string

  • BLOB: the value is a blob of data

Create a New Database File

  • Create a database file named example.db

    $ sqlite3 example.db

  • Note that the dollar sign indicates the Unix command line prompt.

Import Data from a CSV File

  • Import data from a file named data.csv into a table named example_table (this assumes that the table exists)

    sqlite> .mode csv
sqlite> .import data.csv example_table

Execute SQL Statements From a File

  • Execute a SQL statement stored in a file named example.sql

    sqlite> .read example.sql

Dump the Database to a File

  • Dump the SQL statements to recreate the database to a file

    sqlite> .output example.sql
sqlite> .dump
sqlite> .exit

  • Dump a specific table

    sqlite> .output example.sql
sqlite> .dump users
sqlite> .exit

  • Dump the schema

    sqlite> .output example.sql
sqlite> .schema
sqlite> .exit