0

I have a problem with storing a numpy array in sqlite database. I have 1 table with Name and Data.

import sqlite3 as sql
from DIP import dip      # function to caclculate numpy array
name = input('Enter your full name\t')
data = dip()
con = sql.connect('Database.db')
c = con.cursor()

c.execute("CREATE TABLE IF NOT EXISTS database(Name text, Vein real )")
con.commit()
c.execute("INSERT INTO database VALUES(?,?)", (name, data))
con.commit()
c.execute("SELECT * FROM database")
df = c.fetchall()
print(data)
print(df)
con.close()

Everything is fine but when Data is being stored instead of this:

[('Name', 0.03908678  0.04326234  0.18298542 ...,  0.15228545  0.09972548   0.03992807)]

I have this:

[('Name', b'\xccX+\xa8.\x03\xa4?\xf7\xda[\x1f ..., x10l\xc7?\xbf\x14\x12\)]

What is problem with this? Thank you.

P.S. I tried the solution from here Python insert numpy array into sqlite3 database but it didn't work. And my numpy array is being calculated from skimage (scikit-image) library with HOG (histogram of oriented gradients). Maybe that's a problem... Also tried to calculate and store it from opencv3 but have the same issue.

Community
  • 1
  • 1
Eloah
  • 15
  • 6
  • 1
    You need to show how the solution(s) in the link did not work. What exactly did you try? What was the problem? – hpaulj Aug 17 '16 at 16:44
  • I tried the answer of unutbu to create and convert my own datatype and then to insert and read the data from database. The result was the same as i described in my question above. It stores the numpy ndarray to the database but when you read it is somehow encoded. – Eloah Aug 17 '16 at 18:37
  • I wonder if it is saving a byte representation of the array's data buffer. Compare this value to `data.tostring()`. – hpaulj Aug 17 '16 at 19:22

2 Answers2

0

On the assumption that it is saving data.tostring() to the database, I tried decoding it with fromstring.

Using your displayed string, and trimming off a few bytes I got:

In [79]: np.fromstring(b'\xccX+\xa8.\x03\xa4?\xf7\xda[\x1f\x10l\xc7?', float)
Out[79]: array([ 0.03908678,  0.18298532])

There's at least one matching number, so this looks promising.

hpaulj
  • 221,503
  • 14
  • 230
  • 353
0

I had similar issue and I have found out that sqlite has problem of storing custom numpy float type (np.float32 in my case).

Change the type of float values to string and it will work fine.

[float(x) for x in data]

Bartosz Baranowski
  • 1
  • 1