Python insert numpy array into sqlite3 database

by

You could register a new array data type with sqlite3:

import sqlite3
import numpy as np
import io

def adapt_array(arr):
    """
    http://stackoverflow.com/a/31312102/190597 (SoulNibbler)
    """
    out = io.BytesIO()
    np.save(out, arr)
    out.seek(0)
    return sqlite3.Binary(out.read())

def convert_array(text):
    out = io.BytesIO(text)
    out.seek(0)
    return np.load(out)


# Converts np.array to TEXT when inserting
sqlite3.register_adapter(np.ndarray, adapt_array)

# Converts TEXT to np.array when selecting
sqlite3.register_converter("array", convert_array)

x = np.arange(12).reshape(2,6)

con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test (arr array)")

With this setup, you can simply insert the NumPy array with no change in syntax:

cur.execute("insert into test (arr) values (?)", (x, ))

And retrieve the array directly from sqlite as a NumPy array:

cur.execute("select arr from test")
data = cur.fetchone()[0]

print(data)
# [[ 0  1  2  3  4  5]
#  [ 6  7  8  9 10 11]]
print(type(data))
# <type 'numpy.ndarray'>

More Related Contents:

  1. Cartesian product of x and y array points into single array of 2D points
  2. Indexing one array by another in numpy
  3. Finding local maxima/minima with Numpy in a 1D numpy array
  4. Is there any numpy group by function?
  5. How to write a multidimensional array to a text file?
  6. Matplotlib: save plot to numpy array
  7. Calculate average of every x rows in a table and create new table
  8. How does condensed distance matrix work? (pdist)
  9. What’s an efficient way to find if a point lies in the convex hull of a point cloud?
  10. find length of sequences of identical values in a numpy array (run length encoding)
  11. Is it possible to vectorize recursive calculation of a NumPy array where each element depends on the previous one?
  12. ImportError in importing from sklearn: cannot import name check_build
  13. NumPy array initialization (fill with identical values)
  14. Slicing of a NumPy 2d array, or how do I extract an mxm submatrix from an nxn array (n>m)?
  15. How to use python numpy.savetxt to write strings and float number to an ASCII file?
  16. NumPy: calculate averages with NaNs removed
  17. Linear regression with matplotlib / numpy
  18. Why is SQLAlchemy insert with sqlite 25 times slower than using sqlite3 directly?
  19. TypeError: only integer scalar arrays can be converted to a scalar index with 1D numpy indices array
  20. Generate a random sample of points distributed on the surface of a unit sphere
  21. Block tridiagonal matrix python
  22. Find indices of elements equal to zero in a NumPy array
  23. which is faster for load: pickle or hdf5 in python
  24. Deal with overflow in exp using numpy
  25. How to calculate cumulative normal distribution?
  26. Concatenate sparse matrices in Python using SciPy/Numpy
  27. How to get the values from a NumPy array using multiple indices
  28. How to upgrade sqlite 3.8.2 to >= 3.8.3
  29. numpy arange: how to make “precise” array of floats?
  30. Sort array’s rows by another array in Python

Leave a Comment