如何将多维布尔NumPy数组序列化为二进制并实现数据库存读？

Great question! Space-efficient storage and retrieval of boolean arrays (especially n-dimensional ones) is a common need when working with databases, and NumPy gives us some handy tools to pull this off. Let's walk through the entire process step by step.

NumPy's bool dtype uses 1 byte per value by default, which is wasteful for boolean data (we only need 1 bit per value). The np.packbits() function solves this by packing 8 boolean values into a single uint8 byte, cutting your storage footprint to 1/8 of the original.

Here's how to do it:

import numpy as np

# Your original n-dimensional boolean array
arr = np.array([[1, 0, 0, 0, 1, 0], [0, 1, 1, 1, 0, 1], [1, 1, 1, 1, 1, 0]], dtype='bool')

# Flatten the array first (packbits works on 1D arrays)
flat_arr = arr.flatten()

# Pack into uint8 bytes
packed_arr = np.packbits(flat_arr)

# Convert to raw bytes for database storage
packed_bytes = packed_arr.tobytes()

# Save the original array shape (critical for reconstruction later!)
original_shape = arr.shape

A quick note: packbits will pad the end of the array with extra bits to make the total length a multiple of 8. We'll handle this padding when reconstructing the array.

Most databases support a binary data type for storing raw bytes—for example:

• PostgreSQL: bytea
• MySQL: BLOB (or TINYBLOB/MEDIUMBLOB depending on size)
• SQLite: BLOB

You'll also need to store the original array shape (e.g., as a string like "(3, 6)") so you can reshape the data back correctly later. Here's an example with SQLite:

import sqlite3
import ast  # For safe string-to-tuple conversion

# Connect to the database
conn = sqlite3.connect('bool_arrays.db')
cursor = conn.cursor()

# Create a table to store our arrays
cursor.execute('''
CREATE TABLE IF NOT EXISTS boolean_arrays (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    array_data BLOB NOT NULL,
    array_shape TEXT NOT NULL
)
''')

# Insert our packed array and its shape
cursor.execute(
    "INSERT INTO boolean_arrays (array_data, array_shape) VALUES (?, ?)",
    (packed_bytes, str(original_shape))
)

conn.commit()
conn.close()

When you pull the data back from the database, you'll reverse the process: convert the raw bytes back to a uint8 array, unpack the bits, trim any padding, and reshape to the original dimensions.

# Reconnect to the database
conn = sqlite3.connect('bool_arrays.db')
cursor = conn.cursor()

# Retrieve the array data and shape (replace 1 with your target ID)
cursor.execute("SELECT array_data, array_shape FROM boolean_arrays WHERE id = ?", (1,))
row = cursor.fetchone()

# Extract the stored data
retrieved_bytes = row[0]
# Safely convert the shape string back to a tuple (use ast.literal_eval instead of eval for security)
retrieved_shape = ast.literal_eval(row[1])

# Reconstruct the array
# Convert bytes back to uint8 array
packed_retrieved = np.frombuffer(retrieved_bytes, dtype=np.uint8)
# Unpack the bits
unpacked_bits = np.unpackbits(packed_retrieved)
# Trim the padding added by packbits (keep only the original number of elements)
total_elements = np.prod(retrieved_shape)
trimmed_bits = unpacked_bits[:total_elements]
# Reshape and convert back to boolean dtype
reconstructed_arr = trimmed_bits.reshape(retrieved_shape).astype(bool)

# Verify it matches the original!
print("Original array:\n", arr)
print("\nReconstructed array:\n", reconstructed_arr)
print("\nArrays are identical:", np.array_equal(arr, reconstructed_arr))

conn.close()

If you need to query for arrays that match specific boolean patterns (not just exact matches), you can leverage database-specific bitwise operations. For example, in PostgreSQL, you can use operators like & (bitwise AND) to check if certain bits are set in the stored bytea data. Just make sure to pack your query pattern the same way you packed the original arrays before running the query.

内容的提问来源于stack exchange，提问作者Mark Amery