Data Types

SQLite provides several data types, or storage classes, to define the kind of data that can be stored in a table. While SQLite is dynamically typed and doesn’t enforce strict data types, it recognizes five primary affinity types.

Creating a Table with Different Data Types

Here’s an example of creating a table that uses various SQLite data types:

SQLite allows you to store data of any type in any column, regardless of the declared type when the table was created (except for INTEGER PRIMARY KEY columns). It also uses an approach that helps correctly interpret and store data, ensuring everything works smoothly and efficiently.


              1234567891011121314151617181920212223242526272829
            
import sqlite3

conn = sqlite3.connect('data.db')
cursor = conn.cursor()

# Creating the table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
''')

# Inserting different types of data into the `age` column
cursor.execute("INSERT INTO users (name, age) VALUES ('Alice', 25)")  # standard value
cursor.execute("INSERT INTO users (name, age) VALUES ('Bob', 'Thirty')")  # text value in `INTEGER` column
cursor.execute("INSERT INTO users (name, age) VALUES ('Charlie', 30.5)")  # float value in `INTEGER` column

# Retrieving all records from the table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()

# Printing the results
for row in rows:
    print(row)

# Closing the database connection
conn.close()

This example demonstrates how SQLite allows inserting different types of data into the same column, even if the column is declared as INTEGER. For instance, in the age column (declared as INTEGER), the following data is inserted:

An integer (25);
A text value ('Thirty');
A float value (30.5).

SQLite does not restrict the types of data you can insert into a column, thanks to its dynamic typing system. This approach allows storing a variety of data types in a single column without errors.

1. In SQLite, which data type is used to represent the absence of a value?

2. What will happen if you try to insert a text value into an INTEGER column in SQLite?

Everything was clear?

Thanks for your feedback!

Section 3. Chapter 2