6.2. SQL Data Types

• Python None -> SQLite3 NULL

• Python int -> SQLite3 INTEGER

• Python float -> SQLite3 REAL

• Python str -> SQLite3 TEXT

• Python bytes -> SQLite3 BLOB

Table 6.2. Data Types: Python vs. SQLite3

Python Type	SQLite Type
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

6.2.1. NULL Type

• The value is a undefined value

6.2.2. 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

6.2.3. REAL Type

• The value is a floating point value

• Stored as an 8-byte IEEE floating point number

Aliases:

• REAL

• DOUBLE

• DOUBLE PRECISION

• FLOAT

6.2.4. 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

6.2.5. BLOB Type

• Binary Large Object

• The value is a blob of data

• Stored exactly as it was input

6.2.6. 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

6.2.7. 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.