SQLite

Overview

SQLite is arguably the most used database in the world. SQLite is a small, fast, self-contained, high-reliability, full-featured, SQL database engine. This page details SQLite essential info.

Links:

• Home
• Data Types
• SQLite ODBC Driver
  • 64bit (sqliteodbc_w64.exe )
  • 32bit (sqliteodbc.exe)
• SQLite Studio
• Python SQLite: 1 | 2

Data Types

Storage classes:

• NULL. The value is a NULL value.
• INTEGER. The value is a signed integer, stored in 0, 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
• REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
• TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
• BLOB. The value is a blob of data, stored exactly as it was input.

• Some say you only really have integer, float, text, and blob.
• Dates can be stored as either a number (unix time is integer, microsoft time is float) or as text.

Capacity of each type:

• INTEGER is always signed 64-bit.
  SQLite optimizes the storage of small integers behind-the-scenes, so TINYINT wouldn't be useful anyway.
• REAL is always 64-bit (double).
• TEXT and BLOB have a maximum size determined by a preprocessor macro, which defaults to 1,000,000,000 bytes.

Data Types vs. Affinity

• INT, INTEGER, SMALLINT, TINYINT = INTEGER
• LONGCHAR, LONGVARCHAR = TEXT
• DEC, DECIMAL, DATETIME, SMALLDATETIME = NUMERIC

SQLite Studio, New Database

 1. From menu select Database (Ctrl-O).
 2. Select Add a database.
 3. Database Window
    File:    Press + to create database file.
             C:\data\hr.db
    Name:    HR
    Options: ⮽ Permanent
    Press [Test connection] Shows ✔ if OK.

    Press [OK] to save.

Code Snippets

Python

 import sqlite3

 # Init
 oConn   = sqlite3.connect('test.db') # Assumes file in current dir.
 oCursor = oConn.cursor()
 sSql    = "SELECT * FROM emp"

 # Processing Loop
 oCursor.execute(sSql)
 aTableData = oCursor.fetchall()
 for row in aTableData:
     print(row)
     oConn.commit()

 # Housekeeping
 oConn.close()

ADO (VBScript, VB6...)

Use the actual driver name SQLite3 ODBC Driver. Then set the path to the data file via Database=.

 sub Connect2DB
   'Init Vars
   dim oConn, oRS
   set oConn=CreateObject("ADODB.Connection")
   oConn.Open "Driver={SQLite3 ODBC Driver};Database=C:\data\hr.db;"
   Set oRS=oConn.Execute("select * from emp")

   'Processing Loop
   Do While Not(oRS.EOF)
      msgbox oRS(1)
      oRS.MoveNext
   Loop

   'Housekeeping
   oRS.close   : set oRS   = nothing
   oConn.close : set oConn = nothing
 end sub