8.1. SQL Data Types

8.1.1. Rationale

Table 8.1. Data Types: Python vs SQLite3

Python Type

SQLite Type

None

NULL

int

INTEGER

float

REAL

str

TEXT

bytes

BLOB

8.1.2. NULL Type

  • The value is a undefined value

8.1.3. INTEGER Type

  • The value is a signed integer

  • Stored in 1, 2, 3, 4, 6, or 8 bytes

  • Depending on the magnitude of the value

Aliases:

  • INT

  • INTEGER

  • TINYINT

  • SMALLINT

  • MEDIUMINT

  • BIGINT

  • UNSIGNED BIG INT

  • INT2

  • INT8

8.1.4. REAL Type

  • The value is a floating point value

  • Stored as an 8-byte IEEE floating point number

Aliases:

  • REAL

  • DOUBLE

  • DOUBLE PRECISION

  • FLOAT

8.1.5. TEXT Type

  • The value is a text string

  • Stored using the database encoding (ie. UTF-8)

Aliases:

  • CHARACTER(20)

  • VARCHAR(255)

  • VARYING CHARACTER(255)

  • NCHAR(55)

  • NATIVE CHARACTER(70)

  • NVARCHAR(100)

  • TEXT

  • CLOB

8.1.6. BLOB Type

  • Binary Large Object

  • The value is a blob of data

  • Stored exactly as it was input

8.1.7. NUMERIC Affinity

  • May contain values using all five storage classes

When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal, respectively.

If the TEXT value is a well-formed integer literal that is too large to fit in a 64-bit signed integer, it is converted to REAL.

Aliases:

  • NUMERIC

  • DECIMAL(10,5)

  • BOOLEAN

  • DATE

  • DATETIME

8.1.8. DATETIME Affinity

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in date and time functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values.

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

  • REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the Gregorian calendar.

  • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.