SQLite Datatypes and its Corresponding Python Types

In SQLite, each column in a table is associated with a specific data type that defines the kind of data it can store. Here’s a list of commonly used SQLite data types and their corresponding Python types when working with the `sqlite3` module:

1. INTEGER: Signed integer value, which can store whole numbers.

– Python Equivalent: `int`

2. REAL: Floating-point value, which can store decimal numbers.

– Python Equivalent: `float`

3. TEXT: Text string, which can store alphanumeric characters and strings.

– Python Equivalent: `str`

4. BLOB: Binary Large Object, which can store binary data such as images, documents, etc.

– Python Equivalent: `bytes`

When working with SQLite databases in Python using the `sqlite3` module, SQLite automatically maps these data types to their corresponding Python types. However, it’s essential to be aware of the mappings to ensure that the data is stored and retrieved correctly.

Here’s an example demonstrating the mapping of SQLite data types to Python types:

“`python
import sqlite3

# Connect to the SQLite database
connection = sqlite3.connect(‘example.db’)

# Create a cursor object to execute SQL queries
cursor = connection.cursor()

# Create a table with different data types
cursor.execute(”’CREATE TABLE example (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER,
height REAL,
data BLOB
)”’)

# Insert sample data into the table
cursor.execute(“INSERT INTO example (name, age, height, data) VALUES (?, ?, ?, ?)”,
(“John Doe”, 30, 6.1, b”Binary data”))

# Commit the transaction
connection.commit()

# Close the cursor and connection
cursor.close()
connection.close()
“`

In the above example:

– We create a table named `example` with columns `id`, `name`, `age`, `height`, and `data`, each having a different data type.
– We insert a row of sample data into the table, including a text string (`name`), an integer (`age`), a floating-point number (`height`), and binary data (`data`).
– SQLite automatically maps the Python types of the values passed in the `INSERT` statement to the corresponding SQLite data types.