SQLite CREATE TABLE statement is used to create a new table in any of the given database. Creating a basic table involves naming the table and defining its columns and each column”s data type.

Syntax

Following is the basic syntax of CREATE TABLE statement.

CREATE TABLE database_name.table_name(
   column1 datatype PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype
);

CREATE TABLE is the keyword telling the database system to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement. Optionally, you can specify database_name along with table_name.

Example

Following is an example which creates a COMPANY table with ID as the primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table.

sqlite> CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

Let us create one more table, which we will use in our exercises in subsequent chapters.

sqlite> CREATE TABLE DEPARTMENT(
   ID INT PRIMARY KEY      NOT NULL,
   DEPT           CHAR(50) NOT NULL,
   EMP_ID         INT      NOT NULL
);

You can verify if your table has been created successfully using SQLite command .tables command, which will be used to list down all the tables in an attached database.

sqlite>.tables
COMPANY     DEPARTMENT

Here, you can see the COMPANY table twice because its showing COMPANY table for main database and test.COMPANY table for ”test” alias created for your testDB.db. You can get complete information about a table using the following SQLite .schema command.

sqlite>.schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

SQLite supports five date and time functions as follows −

All the above five date and time functions take a time string as an argument. The time string is followed by zero or more modifiers. The strftime() function also takes a format string as its first argument. Following section will give you detail on different types of time strings and modifiers.

Time Strings

A time string can be in any of the following formats −

You can use the “T” as a literal character separating the date and the time.

Modifiers

The time string can be followed by zero or more modifiers that will alter date and/or time returned by any of the above five functions. Modifiers are applied from the left to right.

Following modifers are available in SQLite −

• NNN days
• NNN hours
• NNN minutes
• NNN.NNNN seconds
• NNN months
• NNN years
• start of month
• start of year
• start of day
• weekday N
• unixepoch
• localtime
• utc

Formatters

SQLite provides a very handy function strftime() to format any date and time. You can use the following substitutions to format your date and time.

Examples

Let”s try various examples now using SQLite prompt. Following command computes the current date.

sqlite> SELECT date(''now'');
2013-05-07

Following command computes the last day of the current month.

sqlite> SELECT date(''now'',''start of month'',''+1 month'',''-1 day'');
2013-05-31

Following command computes the date and time for a given UNIX timestamp 1092941466.

sqlite> SELECT datetime(1092941466, ''unixepoch'');
2004-08-19 18:51:06

Following command computes the date and time for a given UNIX timestamp 1092941466 and compensate for your local timezone.

sqlite> SELECT datetime(1092941466, ''unixepoch'', ''localtime'');
2004-08-19 13:51:06

Following command computes the current UNIX timestamp.

sqlite> SELECT strftime(''%s'',''now'');
1393348134

Following command computes the number of days since the signing of the US Declaration of Independence.

sqlite> SELECT julianday(''now'') - julianday(''1776-07-04'');
86798.7094695023

Following command computes the number of seconds since a particular moment in 2004.

sqlite> SELECT strftime(''%s'',''now'') - strftime(''%s'',''2004-01-01 02:34:56'');
295001572

Following command computes the date of the first Tuesday in October for the current year.

sqlite> SELECT date(''now'',''start of year'',''+9 months'',''weekday 2'');
2013-10-01

Following command computes the time since the UNIX epoch in seconds (like strftime(”%s”,”now”) except includes fractional part).

sqlite> SELECT (julianday(''now'') - 2440587.5)*86400.0;
1367926077.12598

To convert between UTC and local time values when formatting a date, use the utc or localtime modifiers as follows −

sqlite> SELECT time(''12:00'', ''localtime'');
05:00:00

sqlite> SELECT time(''12:00'', ''utc'');
19:00:00

VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

VACUUM command may change the ROWID of entries in tables that do not have an explicit INTEGER PRIMARY KEY. The VACUUM command only works on the main database. It is not possible to VACUUM an attached database file.

VACUUM command will fail if there is an active transaction. VACUUM command is a no-op for in-memory databases. As the VACUUM command rebuilds the database file from scratch, VACUUM can also be used to modify many database-specific configuration parameters.

Manual VACUUM

Following is a simple syntax to issue a VACUUM command for the whole database from command prompt −

$sqlite3 database_name "VACUUM;"

You can run VACUUM from SQLite prompt as well as follows −

sqlite> VACUUM;

You can also run VACUUM on a particular table as follows −

sqlite> VACUUM table_name;

Auto-VACCUM

SQLite Auto-VACUUM does not do the same as VACUUM rather it only moves free pages to the end of the database thereby reducing the database size. By doing so it can significantly fragment the database while VACUUM ensures defragmentation. Hence, Auto-VACUUM just keeps the database small.

You can enable/disable SQLite auto-vacuuming by the following pragmas running at SQLite prompt −

sqlite> PRAGMA auto_vacuum = NONE; -- 0 means disable auto vacuum
sqlite> PRAGMA auto_vacuum = FULL; -- 1 means enable full auto vacuum
sqlite> PRAGMA auto_vacuum = INCREMENTAL; -- 2 means enable incremental vacuum

You can run the following command from the command prompt to check the auto-vacuum setting −

$sqlite3 database_name "PRAGMA auto_vacuum;"

In this chapter, you will learn how to use SQLite in Java programs.

Installation

Before you start using SQLite in our Java programs, you need to make sure that you have SQLite JDBC Driver and Java set up on the machine. You can check Java tutorial for Java installation on your machine. Now, let us check how to set up SQLite JDBC driver.

• Download latest version of sqlite-jdbc-(VERSION).jar from repository.

• Add downloaded jar file sqlite-jdbc-(VERSION).jar in your class path, or you can use it along with -classpath option as explained in the following examples.

Following section assumes you have little knowledge about Java JDBC concepts. If you don”t, then it is suggested to spent half an hour with to become comfortable with the concepts explained below.

Connect to Database

Following Java programs shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

import java.sql.*;

public class SQLiteJDBC {
  public static void main( String args[] ) {
      Connection c = null;

      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Opened database successfully");
   }
}

Now, let”s compile and run the above program to create our database test.db in the current directory. You can change your path as per your requirement. We are assuming the current version of JDBC driver sqlite-jdbc-3.7.2.jar is available in the current path.

$javac SQLiteJDBC.java
$java -classpath ".:sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Open database successfully

If you are going to use Windows machine, then you can compile and run your code as follows −

$javac SQLiteJDBC.java
$java -classpath ".;sqlite-jdbc-3.7.2.jar" SQLiteJDBC
Opened database successfully

Create a Table

Following Java program will be used to create a table in the previously created database.

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;

      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "CREATE TABLE COMPANY " +
                        "(ID INT PRIMARY KEY     NOT NULL," +
                        " NAME           TEXT    NOT NULL, " +
                        " AGE            INT     NOT NULL, " +
                        " ADDRESS        CHAR(50), " +
                        " SALARY         REAL)";
         stmt.executeUpdate(sql);
         stmt.close();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Table created successfully");
   }
}

When the above program is compiled and executed, it will create COMPANY table in your test.db and final listing of the file will be as follows −

-rw-r--r--. 1 root root 3201128 Jan 22 19:04 sqlite-jdbc-3.7.2.jar
-rw-r--r--. 1 root root    1506 May  8 05:43 SQLiteJDBC.class
-rw-r--r--. 1 root root     832 May  8 05:42 SQLiteJDBC.java
-rw-r--r--. 1 root root    3072 May  8 05:43 test.db

INSERT Operation

Following Java program shows how to create records in the COMPANY table created in above example.

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;

      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                        "VALUES (1, ''Paul'', 32, ''California'', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 );";
         stmt.executeUpdate(sql);

         sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) " +
                  "VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 );";
         stmt.executeUpdate(sql);

         stmt.close();
         c.commit();
         c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Records created successfully");
   }
}

When above program is compiled and executed, it will create given records in COMPANY table and will display following two line −

Opened database successfully
Records created successfully

SELECT Operation

Following Java program shows how to fetch and display records from the COMPANY table created in the above example.

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {

   Connection c = null;
   Statement stmt = null;
   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );

      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");

         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
   System.out.println("Operation done successfully");
  }
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

UPDATE Operation

Following Java code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table.

import java.sql.*;

public class SQLiteJDBC {

  public static void main( String args[] ) {

   Connection c = null;
   Statement stmt = null;

   try {
      Class.forName("org.sqlite.JDBC");
      c = DriverManager.getConnection("jdbc:sqlite:test.db");
      c.setAutoCommit(false);
      System.out.println("Opened database successfully");

      stmt = c.createStatement();
      String sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1;";
      stmt.executeUpdate(sql);
      c.commit();

      ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );

      while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");

         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
   } catch ( Exception e ) {
      System.err.println( e.getClass().getName() + ": " + e.getMessage() );
      System.exit(0);
   }
    System.out.println("Operation done successfully");
   }
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

DELETE Operation

Following Java code shows how to use use DELETE statement to delete any record and then fetch and display the remaining records from the our COMPANY table.

import java.sql.*;

public class SQLiteJDBC {

   public static void main( String args[] ) {
      Connection c = null;
      Statement stmt = null;

      try {
         Class.forName("org.sqlite.JDBC");
         c = DriverManager.getConnection("jdbc:sqlite:test.db");
         c.setAutoCommit(false);
         System.out.println("Opened database successfully");

         stmt = c.createStatement();
         String sql = "DELETE from COMPANY where ID=2;";
         stmt.executeUpdate(sql);
         c.commit();

         ResultSet rs = stmt.executeQuery( "SELECT * FROM COMPANY;" );

         while ( rs.next() ) {
         int id = rs.getInt("id");
         String  name = rs.getString("name");
         int age  = rs.getInt("age");
         String  address = rs.getString("address");
         float salary = rs.getFloat("salary");

         System.out.println( "ID = " + id );
         System.out.println( "NAME = " + name );
         System.out.println( "AGE = " + age );
         System.out.println( "ADDRESS = " + address );
         System.out.println( "SALARY = " + salary );
         System.out.println();
      }
      rs.close();
      stmt.close();
      c.close();
      } catch ( Exception e ) {
         System.err.println( e.getClass().getName() + ": " + e.getMessage() );
         System.exit(0);
      }
      System.out.println("Operation done successfully");
   }
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

In this chapter, you will learn how to use SQLite in PHP programs.

Installation

SQLite3 extension is enabled by default as of PHP 5.3.0. It”s possible to disable it by using –without-sqlite3 at compile time.

Windows users must enable php_sqlite3.dll in order to use this extension. This DLL is included with Windows distributions of PHP as of PHP 5.3.0.

For detailed installation instructions, kindly check our PHP tutorial and its official website.

PHP Interface APIs

Following are important PHP routines which can suffice your requirement to work with SQLite database from your PHP program. If you are looking for a more sophisticated application, then you can look into PHP official documentation.

Connect to Database

Following PHP code shows how to connect to an existing database. If database does not exist, then it will be created and finally a database object will be returned.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open(''test.db'');
      }
   }
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfullyn";
   }
?>

Now, let”s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. If the database is successfully created, then it will display the following message −

Open database successfully

Create a Table

Following PHP program will be used to create a table in the previously created database.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open(''test.db'');
      }
   }
   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfullyn";
   }

   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = $db->exec($sql);
   if(!$ret){
      echo $db->lastErrorMsg();
   } else {
      echo "Table created successfullyn";
   }
   $db->close();
?>

When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages −

Opened database successfully
Table created successfully

INSERT Operation

Following PHP program shows how to create records in the COMPANY table created in the above example.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open(''test.db'');
      }
   }

   $db = new MyDB();
   if(!$db){
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfullyn";
   }

   $sql =<<<EOF
      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, ''Paul'', 32, ''California'', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 );

      INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 );
EOF;

   $ret = $db->exec($sql);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo "Records created successfullyn";
   }
   $db->close();
?>

When the above program is executed, it will create the given records in the COMPANY table and will display the following two lines.

Opened database successfully
Records created successfully

SELECT Operation

Following PHP program shows how to fetch and display records from the COMPANY table created in the above example −

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open(''test.db'');
      }
   }

   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfullyn";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      echo "ID = ". $row[''ID''] . "n";
      echo "NAME = ". $row[''NAME''] ."n";
      echo "ADDRESS = ". $row[''ADDRESS''] ."n";
      echo "SALARY = ".$row[''SALARY''] ."nn";
   }
   echo "Operation done successfullyn";
   $db->close();
?>

When the above program is executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

UPDATE Operation

Following PHP code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open(''test.db'');
      }
   }

   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfullyn";
   }
   $sql =<<<EOF
      UPDATE COMPANY set SALARY = 25000.00 where ID=1;
EOF;
   $ret = $db->exec($sql);
   if(!$ret) {
      echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record updated successfullyn";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;

   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      echo "ID = ". $row[''ID''] . "n";
      echo "NAME = ". $row[''NAME''] ."n";
      echo "ADDRESS = ". $row[''ADDRESS''] ."n";
      echo "SALARY = ".$row[''SALARY''] ."nn";
   }
   echo "Operation done successfullyn";
   $db->close();
?>

When the above program is executed, it will produce the following result.

Opened database successfully
1 Record updated successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

DELETE Operation

Following PHP code shows how to use DELETE statement to delete any record and then fetch and display the remaining records from the COMPANY table.

<?php
   class MyDB extends SQLite3 {
      function __construct() {
         $this->open(''test.db'');
      }
   }

   $db = new MyDB();
   if(!$db) {
      echo $db->lastErrorMsg();
   } else {
      echo "Opened database successfullyn";
   }
   $sql =<<<EOF
      DELETE from COMPANY where ID = 2;
EOF;

   $ret = $db->exec($sql);
   if(!$ret){
     echo $db->lastErrorMsg();
   } else {
      echo $db->changes(), " Record deleted successfullyn";
   }

   $sql =<<<EOF
      SELECT * from COMPANY;
EOF;
   $ret = $db->query($sql);
   while($row = $ret->fetchArray(SQLITE3_ASSOC) ) {
      echo "ID = ". $row[''ID''] . "n";
      echo "NAME = ". $row[''NAME''] ."n";
      echo "ADDRESS = ". $row[''ADDRESS''] ."n";
      echo "SALARY = ".$row[''SALARY''] ."nn";
   }
   echo "Operation done successfullyn";
   $db->close();
?>

When the above program is executed, it will produce the following result.

Opened database successfully
1 Record deleted successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000

Operation done successfully

SQLite has many built-in functions to perform processing on string or numeric data. Following is the list of few useful SQLite built-in functions and all are case in-sensitive which means you can use these functions either in lower-case form or in upper-case or in mixed form. For more details, you can check official documentation for SQLite.

Before we start giving examples on the above-mentioned functions, consider COMPANY table with the following records.

ID          NAME        AGE         ADDRESS     SALARY
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0
2           Allen       25          Texas       15000.0
3           Teddy       23          Norway      20000.0
4           Mark        25          Rich-Mond   65000.0
5           David       27          Texas       85000.0
6           Kim         22          South-Hall  45000.0
7           James       24          Houston     10000.0

SQLite COUNT Function

SQLite COUNT aggregate function is used to count the number of rows in a database table. Following is an example −

sqlite> SELECT count(*) FROM COMPANY;

The above SQLite SQL statement will produce the following.

count(*)
----------
7

SQLite MAX Function

SQLite MAX aggregate function allows us to select the highest (maximum) value for a certain column. Following is an example −

sqlite> SELECT max(salary) FROM COMPANY;

The above SQLite SQL statement will produce the following.

max(salary)
-----------
85000.0

SQLite MIN Function

SQLite MIN aggregate function allows us to select the lowest (minimum) value for a certain column. Following is an example −

sqlite> SELECT min(salary) FROM COMPANY;

The above SQLite SQL statement will produce the following.

min(salary)
-----------
10000.0

SQLite AVG Function

SQLite AVG aggregate function selects the average value for a certain table column. Following is an the example −

sqlite> SELECT avg(salary) FROM COMPANY;

The above SQLite SQL statement will produce the following.

avg(salary)
----------------
37142.8571428572

SQLite SUM Function

SQLite SUM aggregate function allows selecting the total for a numeric column. Following is an example −

sqlite> SELECT sum(salary) FROM COMPANY;

The above SQLite SQL statement will produce the following.

sum(salary)
-----------
260000.0

SQLite RANDOM Function

SQLite RANDOM function returns a pseudo-random integer between -9223372036854775808 and +9223372036854775807. Following is an example −

sqlite> SELECT random() AS Random;

The above SQLite SQL statement will produce the following.

Random
-------------------
5876796417670984050

SQLite ABS Function

SQLite ABS function returns the absolute value of the numeric argument. Following is an example −

sqlite> SELECT abs(5), abs(-15), abs(NULL), abs(0), abs("ABC");

The above SQLite SQL statement will produce the following.

abs(5)      abs(-15)    abs(NULL)   abs(0)      abs("ABC")
----------  ----------  ----------  ----------  ----------
5           15                      0           0.0

SQLite UPPER Function

SQLite UPPER function converts a string into upper-case letters. Following is an example −

sqlite> SELECT upper(name) FROM COMPANY;

The above SQLite SQL statement will produce the following.

upper(name)
-----------
PAUL
ALLEN
TEDDY
MARK
DAVID
KIM
JAMES

SQLite LOWER Function

SQLite LOWER function converts a string into lower-case letters. Following is an example −

sqlite> SELECT lower(name) FROM COMPANY;

The above SQLite SQL statement will produce the following.

lower(name)
-----------
paul
allen
teddy
mark
david
kim
james

SQLite LENGTH Function

SQLite LENGTH function returns the length of a string. Following is an example −

sqlite> SELECT name, length(name) FROM COMPANY;

The above SQLite SQL statement will produce the following.

NAME        length(name)
----------  ------------
Paul        4
Allen       5
Teddy       5
Mark        4
David       5
Kim         3
James       5

SQLite sqlite_version Function

SQLite sqlite_version function returns the version of the SQLite library. Following is an example −

sqlite> SELECT sqlite_version() AS ''SQLite Version

The above SQLite SQL statement will produce the following.

SQLite Version
--------------
3.6.20

In this chapter, you will learn how to use SQLite in Python programs.

Installation

SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards.

To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements.

Python sqlite3 module APIs

Following are important sqlite3 module routines, which can suffice your requirement to work with SQLite database from your Python program. If you are looking for a more sophisticated application, then you can look into Python sqlite3 module”s official documentation.

Connect To Database

Following Python code shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect(''test.db'')

print "Opened database successfully";

Here, you can also supply database name as the special name :memory: to create a database in RAM. Now, let”s run the above program to create our database test.db in the current directory. You can change your path as per your requirement. Keep the above code in sqlite.py file and execute it as shown below. If the database is successfully created, then it will display the following message.

$chmod +x sqlite.py
$./sqlite.py
Open database successfully

Create a Table

Following Python program will be used to create a table in the previously created database.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect(''test.db'')
print "Opened database successfully";

conn.execute(''''''CREATE TABLE COMPANY
         (ID INT PRIMARY KEY     NOT NULL,
         NAME           TEXT    NOT NULL,
         AGE            INT     NOT NULL,
         ADDRESS        CHAR(50),
         SALARY         REAL);'''''')
print "Table created successfully";

conn.close()

When the above program is executed, it will create the COMPANY table in your test.db and it will display the following messages −

Opened database successfully
Table created successfully

INSERT Operation

Following Python program shows how to create records in the COMPANY table created in the above example.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect(''test.db'')
print "Opened database successfully";

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (1, ''Paul'', 32, ''California'', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 )");

conn.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
      VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

When the above program is executed, it will create the given records in the COMPANY table and it will display the following two lines −

Opened database successfully
Records created successfully

SELECT Operation

Following Python program shows how to fetch and display records from the COMPANY table created in the above example.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect(''test.db'')
print "Opened database successfully";

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "n"

print "Operation done successfully";
conn.close()

When the above program is executed, it will produce the following result.

Opened database successfully
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

UPDATE Operation

Following Python code shows how to use UPDATE statement to update any record and then fetch and display the updated records from the COMPANY table.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect(''test.db'')
print "Opened database successfully";

conn.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "n"

print "Operation done successfully";
conn.close()

When the above program is executed, it will produce the following result.

Opened database successfully
Total number of rows updated : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 25000.0

ID = 2
NAME = Allen
ADDRESS = Texas
SALARY = 15000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

DELETE Operation

Following Python code shows how to use DELETE statement to delete any record and then fetch and display the remaining records from the COMPANY table.

#!/usr/bin/python

import sqlite3

conn = sqlite3.connect(''test.db'')
print "Opened database successfully";

conn.execute("DELETE from COMPANY where ID = 2;")
conn.commit()
print "Total number of rows deleted :", conn.total_changes

cursor = conn.execute("SELECT id, name, address, salary from COMPANY")
for row in cursor:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "n"

print "Operation done successfully";
conn.close()

When the above program is executed, it will produce the following result.

Opened database successfully
Total number of rows deleted : 1
ID = 1
NAME = Paul
ADDRESS = California
SALARY = 20000.0

ID = 3
NAME = Teddy
ADDRESS = Norway
SALARY = 20000.0

ID = 4
NAME = Mark
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

In this chapter, you will learn how to use SQLite in C/C++ programs.

Installation

Before you start using SQLite in our C/C++ programs, you need to make sure that you have SQLite library set up on the machine. You can check SQLite Installation chapter to understand the installation process.

C/C++ Interface APIs

Following are important C/C++ SQLite interface routines, which can suffice your requirement to work with SQLite database from your C/C++ program. If you are looking for a more sophisticated application, then you can look into SQLite official documentation.

Connect To Database

Following C code segment shows how to connect to an existing database. If the database does not exist, then it will be created and finally a database object will be returned.

#include <stdio.h>
#include <sqlite3.h>

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;

   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can''t open database: %sn", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfullyn");
   }
   sqlite3_close(db);
}

Now, let”s compile and run the above program to create our database test.db in the current directory. You can change your path as per your requirement.

$gcc test.c -l sqlite3
$./a.out
Opened database successfully

If you are going to use C++ source code, then you can compile your code as follows −

$g++ test.c -l sqlite3

Here, we are linking our program with sqlite3 library to provide required functions to C program. This will create a database file test.db in your directory and you will have the following result.

-rwxr-xr-x. 1 root root 7383 May 8 02:06 a.out
-rw-r--r--. 1 root root  323 May 8 02:05 test.c
-rw-r--r--. 1 root root    0 May 8 02:06 test.db

Create a Table

Following C code segment will be used to create a table in the previously created database −

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can''t open database: %sn", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stdout, "Opened database successfullyn");
   }

   /* Create SQL statement */
   sql = "CREATE TABLE COMPANY("
      "ID INT PRIMARY KEY     NOT NULL,"
      "NAME           TEXT    NOT NULL,"
      "AGE            INT     NOT NULL,"
      "ADDRESS        CHAR(50),"
      "SALARY         REAL );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Table created successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will create COMPANY table in your test.db and the final listing of the file will be as follows −

-rwxr-xr-x. 1 root root 9567 May 8 02:31 a.out
-rw-r--r--. 1 root root 1207 May 8 02:31 test.c
-rw-r--r--. 1 root root 3072 May 8 02:31 test.db

INSERT Operation

Following C code segment shows how you can create records in COMPANY table created in the above example −

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *NotUsed, int argc, char **argv, char **azColName) {
   int i;
   for(i = 0; i<argc; i++) {
      printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;

   /* Open database */
   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can''t open database: %sn", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfullyn");
   }

   /* Create SQL statement */
   sql = "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
         "VALUES (1, ''Paul'', 32, ''California'', 20000.00 ); "
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) "
         "VALUES (2, ''Allen'', 25, ''Texas'', 15000.00 ); "
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
         "VALUES (3, ''Teddy'', 23, ''Norway'', 20000.00 );"
         "INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)"
         "VALUES (4, ''Mark'', 25, ''Rich-Mond '', 65000.00 );";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, 0, &zErrMsg);

   if( rc != SQLITE_OK ){
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Records created successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will create the given records in COMPANY table and will display the following two lines −

Opened database successfully
Records created successfully

SELECT Operation

Before proceeding with actual example to fetch records, let us look at some detail about the callback function, which we are using in our examples. This callback provides a way to obtain results from SELECT statements. It has the following declaration −

typedef int (*sqlite3_callback)(
   void*,    /* Data provided in the 4th argument of sqlite3_exec() */
   int,      /* The number of columns in row */
   char**,   /* An array of strings representing fields in the row */
   char**    /* An array of strings representing column names */
);

If the above callback is provided in sqlite_exec() routine as the third argument, SQLite will call this callback function for each record processed in each SELECT statement executed within the SQL argument.

Following C code segment shows how you can fetch and display records from the COMPANY table created in the above example −

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);

   for(i = 0; i<argc; i++){
      printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
   }

   printf("n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can''t open database: %sn", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfullyn");
   }

   /* Create SQL statement */
   sql = "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);

   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

UPDATE Operation

Following C code segment shows how we can use UPDATE statement to update any record and then fetch and display updated records from the COMPANY table.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName){
   int i;
   fprintf(stderr, "%s: ", (const char*)data);

   for(i = 0; i<argc; i++) {
      printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can''t open database: %sn", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfullyn");
   }

   /* Create merged SQL statement */
   sql = "UPDATE COMPANY set SALARY = 25000.00 where ID=1; "
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);

   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 25000.0

Callback function called: ID = 2
NAME = Allen
AGE = 25
ADDRESS = Texas
SALARY = 15000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

DELETE Operation

Following C code segment shows how you can use DELETE statement to delete any record and then fetch and display the remaining records from the COMPANY table.

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>

static int callback(void *data, int argc, char **argv, char **azColName) {
   int i;
   fprintf(stderr, "%s: ", (const char*)data);

   for(i = 0; i<argc; i++) {
      printf("%s = %sn", azColName[i], argv[i] ? argv[i] : "NULL");
   }
   printf("n");
   return 0;
}

int main(int argc, char* argv[]) {
   sqlite3 *db;
   char *zErrMsg = 0;
   int rc;
   char *sql;
   const char* data = "Callback function called";

   /* Open database */
   rc = sqlite3_open("test.db", &db);

   if( rc ) {
      fprintf(stderr, "Can''t open database: %sn", sqlite3_errmsg(db));
      return(0);
   } else {
      fprintf(stderr, "Opened database successfullyn");
   }

   /* Create merged SQL statement */
   sql = "DELETE from COMPANY where ID=2; "
         "SELECT * from COMPANY";

   /* Execute SQL statement */
   rc = sqlite3_exec(db, sql, callback, (void*)data, &zErrMsg);

   if( rc != SQLITE_OK ) {
      fprintf(stderr, "SQL error: %sn", zErrMsg);
      sqlite3_free(zErrMsg);
   } else {
      fprintf(stdout, "Operation done successfullyn");
   }
   sqlite3_close(db);
   return 0;
}

When the above program is compiled and executed, it will produce the following result.

Opened database successfully
Callback function called: ID = 1
NAME = Paul
AGE = 32
ADDRESS = California
SALARY = 20000.0

Callback function called: ID = 3
NAME = Teddy
AGE = 23
ADDRESS = Norway
SALARY = 20000.0

Callback function called: ID = 4
NAME = Mark
AGE = 25
ADDRESS = Rich-Mond
SALARY = 65000.0

Operation done successfully

The following resources contain additional information on SQLite. Please use them to get more in-depth knowledge on this topic.