User Tools

Site Tools



  "SQLite is not designed to replace Oracle. It is designed to replace fopen()."

SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform.

See for a list of appropriate uses for SQLite..



  sudo apt-get install sqlite3


Surf to and find the Windows Binary that best fits your taste


  sqlite3 myDb.sqlite # if myDb.sqlite does not exist, it is created

SQLite supports MOST] of the SQL92 features. A list of unsupported features can be found at


Import values from CSV File

For this example we have a semicolon-separated csv file called emissions.csv and want to create a new database called emissions.sqlite. The csv file also has a header row, which we import and then delete.

create table emissions (stringCol text, doubleCol real, ...); #manually create table according to header row
.separator ";"
.import emissions.csv emissions
delete from emissions where XXXXXXX;   # delete the first row that contains the header
select count(*) from emissions;        # check nr of imported entries

Note: be sure to remove trailing separator, otherwise you and sqlite will think differently about the number of columns to import.

Import values from an other sqlite DB

  attach '20080802_trips.db' as source;
  INSERT INTO trips SELECT fleet_id,trip_id,timestamp,x,y FROM source.trips WHERE trip_id IN (SELECT trip_id FROM source.trips WHERE x > 16.395 AND x < 16.434 AND y>48.269 AND y<48.304);
  detach source;

Write results to a file

You can change this using the “.output” command. Just put the name of an output file as an argument to the .output command and all subsequent query results will be written to that file. Use “.output stdout” to begin writing to standard output again. Example:

  sqlite> .mode list
  sqlite> .separator |
  sqlite> .output test_file_1.txt
  sqlite> select * from tbl1;
  sqlite> .exit
  $ cat test_file_1.txt

In Bashish:

 echo -e '.mode list \n.separator ;\n.header on \n.output muh.txt\n select * from aggregation; \n' | sqlite3 ./my.db

So if you have lots of sqlite-files to export to csv, you might want to do

 for i in `ls *db`; do echo -e ".mode list \n.separator ;\n.header on \n.output $i.csv\n select road_id as RoadId,lane_id as LaneId,inroaddir as Direction,speed as Velocity,length as Length,timestamp as Timestamp,trip_id as TripId,fleet_id as Source from lm; \n" | sqlite3 $i; done

Export a DB

A good way to make an archival copy of a database is this:

  $ echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz

This generates a file named ex1.dump.gz that contains everything you need to reconstruct the database at a later time, or on another machine. To reconstruct the database, just type:

  $ zcat ex1.dump.gz | sqlite3 ex2

The text format is pure SQL so you can also use the .dump command to export an SQLite database into other popular SQL database engines. Like this:

    $ createdb ex2
    $ sqlite3 ex1 .dump | psql ex2

Working with Dates

Although SQLite stores all dates as Strings, it provides some primitive functions to deal with Date-Strings. See

Select hour

  SELECT trip_id, strftime("%H",timestamp) as hour from trips;

Select week day


 SELECT * FROM lm_onroad WHERE  strftime('%w',datetime(timestamp)) in ('6','0')


 SELECT * FROM lm_onroad WHERE  strftime('%w',datetime(timestamp)) in ('1','2','3','4','5')

Subtract Timestamps

To subtract one timestamp from the other, you have to use strftime to convert both timestamps to seconds. For Example:

  SELECT trip_id, start_time,end_time,STRFTIME('%s',end_time) - STRFTIME('%s',start_time) FROM delay; # use this only for dates after 1970!

In-memory databases

In-memory databases must be named :memory: , which tells SQLite to create the database in memory.

The general approach for copying an existing database into memory is:

1. Create a new in-memory database (IMDB) 1. Open the on-disk database (ODDB) and attach it to IMDB 1. Copy table definitions from ODDB to IMDB 1. Copy data from ODDB to IMDB 1. Copy other definitions like indices from ODDB to IMDB 1. Detach ODDB and happily work with your fast IMDB (but beware of the fact that all changes to IMDB are not persistent)

An exhaustive example on how to do this in Java:

  * Open a connection to the database and copy it into RAM. This results
  * in better performance for queries but also means that any changes
  * will not be persistent.
private boolean openConnectionAndCopyDbInMemory(String sqliteFile) {
        File file = new File(sqliteFile);
        System.out.println("Loading " + file.getAbsolutePath() + " into memory");
        try {
                // load the sqlite driver

                conn = DriverManager.getConnection("jdbc:sqlite::memory:");
                stmt = conn.createStatement();
                stmt2 = conn.createStatement();
                rs = rs2 = null;

                // attach local database to in-memory database
                String attachStmt = "ATTACH '" + file.getAbsolutePath() + "' AS src";

                // copy table definition
                String tableQuery = "SELECT sql FROM src.sqlite_master WHERE type='table';";
                rs = stmt.executeQuery(tableQuery);
                while( {

                // copy data
                String tableNameQuery = "SELECT name FROM sqlite_master WHERE type='table'";
                rs = stmt.executeQuery(tableNameQuery);
                while( {
                        String copyDataQuery =
                                "INSERT INTO " + rs.getString(1) + " SELECT * FROM src." + rs.getString(1);

                // copy other definitions (i.e. indices)
                String nonTableQuery = "SELECT sql FROM src.sqlite_master WHERE type!='table';";
                rs = stmt.executeQuery(nonTableQuery);
                while( {

                // detach local db
                String detachStmt = "DETACH src";

                // test if everything went well
                String sqlQuery = "SELECT sql FROM sqlite_master";
                String recordsQuery = "SELECT COUNT(*) FROM emissions";
                rs = stmt.executeQuery(sqlQuery);
                System.out.println("Database in memory has the following definition:");
                rs = stmt.executeQuery(recordsQuery);
                        System.out.println("Database contains " + rs.getInt(1) + " records");

        } catch (Exception e) {
                return false;
        return true;

Explore the Database

SQLite supports two (rather primitive) commands to explore the database:

  .tables   #shows the names of all tables in the DB

Using the Master Table

The database schema in an SQLite database is stored in a special table named “sqlite_master”. You can execute “SELECT” statements against the special sqlite_master table just like any other table in an SQLite database:

  select * from sqlite_master;

This will get you the names of all the tables, and the corresponding CREATE statements



sudo apt-get install python-pysqlite2 spatialite-bin

To prepare a database for spatial data:

gunzip init_spatialite-2.3.sql.gz
spatialite mydatabase.db  < init_spatialite-2.3.sql

Afterwards you'll find additional tables like spatial_ref_sys: contains spatial reference systems]

Now you can for example add a column for GPS coordinates:

spatialite trip.db
>select AddGeometryColumn('trips','the_geom_4326',4326,'Point',2);
>UPDATE trips SET the_geom_4326 = GEOMETRYFROMTEXT('POINT('|x|' '|y |')',4326); # or use MakePoint( x Double precision , y Double precision , [ , SRID Integer] ) : Geometry
>SELECT RecoverGeometryColumn('trips','the_geom_4326',4326,'POINT',2);


Here's the function reference].

Loading the spatialite module:

from pysqlite2 import dbapi2 as sqlite3
# Load the spatialite extension of sqlite
con = sqlite3.connect(db)
con.execute("select load_extension('')") # maybe you'll need a symlink  to       

Performance Issues

Avoid LIKE or GLOB

Never use the LIKE or GLOB statement. A less-elegant AND/OR construct is magnitudes faster. Tests with an in-memory DB and LIKE queries from Java resulted in a runtime difference of 10^3! More insights can also be found on

Use Indices

Placing indices on columns that are used in joins or where statements are a good way to increase query speed.

Using SQLite in programs


sqlite.txt · Last modified: 2012/07/09 18:13 by mantis