SQLite Data Types

In this tutorial, we will understand SQLite Data Types. An SQLite data type specifies the type of data of the database object. For example, the table column has a related data type we specify when creating the table.

SQLite database engine has multiple storage classes to store values. The SQLite data types are as follows:

SQLite Datatype	Description
INTEGER	The INTEGER datatype is used to store the numeric value. The integer is stored in 1,2,3,4,6 or 8 bytes depending on the number.
REAL	The REAL datatype is used to store floating point numbers. The floating-point number with decimal values, for example, like the PI with the value of 3.14159
TEXT	The TEXT value is a text string stored using the UTF-8, UTF- 16BE or UTF-16LE encoding schemes.
BLOB	The value is a BLOB data, i.e., binary data. It is used to store images and files in the database.
NULL	The NULL value.

In this section, let’s understand SQLite and its corresponding Python data types. The following Python data types will be converted to corresponding SQLite datatypes. This table would be helpful when modifying or reading from the SQLite table.

This will help you to store and read data from the SQLite tables.

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

Some missing data types of Python:

BOOLEAN
SQLite does not have a separate boolean data type. Instead, you can use INTEGER with values 0 and 1 to represent false and true, respectively.

DATE and TIME Types
SQLite does not have a dedicated date or time data type, but you can store date and time values as TEXT, REAL, or INTEGER and use appropriate functions to handle them.

SQLite uses dynamic typing, allowing you to store values of any data type in any column. SQLite is flexible with data types, which means you can insert values of any data type into any column. The declared column type is used as a hint but is not strictly enforced. However, it’s still recommended to use appropriate data types.

—