How to write a NumPy array to a SQLite database and read it back?

NumPy: I/O Operations Exercise-16 with Solution

Write a NumPy array to a SQLite database and then read it back into a NumPy array.

Sample Solution:

Python Code:

import numpy as np
import sqlite3

# Create a NumPy array with numeric data
data_array = np.array([[1, 2, 3], [4, 5, 6], [7, 8, 9]], dtype=int)

# Define the SQLite database file path
db_file_path = 'test.db'

# Connect to the SQLite database
conn = sqlite3.connect(db_file_path)
cursor = conn.cursor()

# Create a table to store the data with INTEGER data types
cursor.execute('''CREATE TABLE IF NOT EXISTS data_table (col1 INTEGER, col2 INTEGER, col3 INTEGER)''')

# Insert the NumPy array data into the SQLite table
for row in data_array:
    cursor.execute('INSERT INTO data_table (col1, col2, col3) VALUES (?, ?, ?)', tuple(row))

# Commit the changes and close the connection
conn.commit()

# Read the data back from the SQLite table into a NumPy array
cursor.execute('SELECT col1, col2, col3 FROM data_table')
rows = cursor.fetchall()

# Convert the fetched rows from byte strings to integers
cleaned_rows = [[int.from_bytes(value, byteorder='little') if isinstance(value, bytes) else value for value in row] for row in rows]

# Convert the cleaned rows to a NumPy array
loaded_array = np.array(cleaned_rows, dtype=int)

# Close the connection
conn.close()

# Print the original and loaded NumPy arrays
print("Original NumPy Array:")
print(data_array)

print("\nLoaded NumPy Array from SQLite Database:")
print(loaded_array)

Output:

Original NumPy Array:
[[1 2 3]
 [4 5 6]
 [7 8 9]]

Loaded NumPy Array from SQLite Database:
[[1 2 3]
 [4 5 6]
 [7 8 9]]

Explanation:

• Import NumPy and SQLite Libraries: Import the NumPy and SQLite libraries to handle arrays and database operations.
• Create NumPy Array: Define a NumPy array with some example numeric data, ensuring the data type is int.
• Define SQLite Database Path: Specify the path to the SQLite database file.
• Connect to SQLite Database: Establish a connection to the SQLite database and create a cursor object to execute SQL commands.
• Create Table: Create a table named data_table to store the NumPy array data, defining columns with INTEGER data types.
• Insert Data into Table: Iterate through the rows of the NumPy array and insert each row into the SQLite table using INSERT INTO SQL commands.
• Commit and Close Connection: Commit the changes to the database and close the connection.
• Read Data from Table: Execute a SELECT query to retrieve the data from the SQLite table and fetch all rows.
• Convert Byte Strings to Integers: Convert each value from byte strings to integers if necessary.
• Convert Cleaned Rows to NumPy Array: Convert the cleaned rows to a NumPy array ensuring the data type is int.
• Finally, print the output of both the original NumPy array and the loaded array to verify that the data was stored and retrieved correctly.

Python-Numpy Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Read data from a CSV file into a Pandas DataFrame and convert to NumPy array.
Next: Save and load NumPy arrays with MATLAB .mat files using SciPy