SQLite Datatypes and its Corresponding Python Types
Last Updated :
21 Apr, 2021
SQLite is a C-language-based library that provides a portable and serverless SQL database engine. It has a file-based architecture; hence it reads and writes to a disk. Since SQLite is a zero-configuration database, no installation or setup is needed before its usage. Starting from Python 2.5.x, SQLite3 comes default with python.
In this article, we will discuss SQLite DataTypes and their corresponding Python Types
Storage Class in SQLite
A storage class can be called a collection of similar DataTypes. SQLite provides the following storage classes:
Storage Class
|
Value Stored
|
NULL
|
NULL
|
INTEGER
|
Signed Integer (1, 2, 3, 4, 5, or 8 bytes depending on magnitude)
|
REAL
|
Floating point value (8 byte IEEE floating-point numbers)
|
TEXT
|
TEXT string (encoded in UTF-8, UTF-16BE or UTF-16LE
|
BLOB (Binary Large Object)
|
Data stored exactly the way it was input, generally in binary format
|
The term Storage Class can be used mutually with DataType.
Corresponding Python Datatypes
The SQLite DataTypes and their corresponding Python types are as follows
Storage Class
|
Python Datatype
|
NULL
|
None
|
INTEGER
|
int
|
REAL
|
float
|
TEXT
|
str
|
BLOB
|
bytes
|
The type() function can be used in python to get the class of an argument. In the program below, the type() function is used to print the classes of every value we store in a database.
Note: The program below uses the GeeksforGeeks logo as logo.png from this page for a demonstration.
Let’s take an example in which we are creating a database with the name ‘gfg’ and then create a table named exam_hall having some columns which are as follows:
- NAME (TEXT),
- PIN (INTEGER),
- OCCUPANCY (REAL),
- LOGO (BLOB).
Then we insert some rows in it and check the data types of the values fetched with the help of SQL queries in Python.
Python3
import sqlite3
cnt = sqlite3.connect( 'gfg.db' )
cnt.execute(
)
fileh = open ( '/content/JSBinCollaborativeJavaScriptDebugging6-300x160.png' , 'rb' )
img = fileh.read()
cnt.execute(
, (img,))
cnt.execute(
, (img,))
cursor = cnt.execute( )
for i in cursor:
print ( str (i[ 0 ]) + " " + str (i[ 1 ]) + " " + str (i[ 2 ]) + " " + str ( len (i[ 3 ])))
print ( str ( type (i[ 0 ])) + " " + str ( type (i[ 1 ])) + " " +
str ( type (i[ 2 ])) + " " + str ( type (i[ 3 ])) + "\n" )
|
Output:
From the output of this program, the following observations can be made:
- ‘centre-a’ that was inserted as TEXT has been interpreted by python as str
- 1125, 1158 that were inserted as INTEGER have been interpreted by python as int
- 98.6, 80.5 that were inserted as REAL have been interpreted by python as float
- NULL was interpreted by python as NoneType
- The logo image which was inserted in binary format as BLOB has been interpreted by python as bytes.
Share your thoughts in the comments
Please Login to comment...