sqlite3 writes only floating-point numpy arrays not integer ones

Question

I am able to save Numpy arrays with floating point numbers to sqlite3, but not arrays with integers:

import sqlite3
import numpy as np

db = sqlite3.connect('database.db')
database = db.cursor()

database.execute("CREATE TABLE table_name " 
     "(round INT, id INT, PRIMARY   KEY(round, id))")

row_to_write = np.array([1])
dtype = str(row_to_write.dtype)
if dtype.startswith('float'):
    database.execute("ALTER TABLE table_name ADD data FLOAT;")
elif dtype.startswith('int'):
    database.execute("ALTER TABLE table_name ADD data INTEGER;")

insert_str = "INSERT INTO table_name (round, id, data) VALUES (0, 0, ?);"
database.execute(insert_str, row_to_write)

results in:

InterfaceError: Error binding parameter 0 - probably unsupported type.

If I assign an floating point array instead it works row_to_write = np.array([1.1])

what happens with not numpy arrays? "It doesnt work" tells us nothing... — Joran Beasley
– Joran Beasley, Commented Aug 10, 2012 at 23:44

SingleNegationElimination · Accepted Answer · 2012-08-11 02:34:47Z

8

Numpy is using some custom integer data types to efficiently pack data into memory. Since these types aren't familiar to sqlite, you'll have to tell it about how to handle them, beforehand:

>>> for t in (np.int8, np.int16, np.int32, np.int64,
...           np.uint8, np.uint16, np.uint32, np.uint64):
...     sqlite3.register_adapter(t, long)
... 
>>> cur.execute("insert into foo(bar) values(?)", np.array([1]))
<sqlite3.Cursor object at 0x027A7620>

answered Aug 11, 2012 at 2:34

SingleNegationElimination

157k35 gold badges269 silver badges306 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jterrace · Accepted Answer · 2012-08-11 02:49:06Z

0

You can call tolist on the array before using the values:

>>> import numpy as np
>>> x = np.array([1,2,3])
>>> type(x[0])
<type 'numpy.int64'>
>>> y = x.tolist()
>>> y
[1, 2, 3]
>>> type(y[0])
<type 'int'>

answered Aug 11, 2012 at 2:49

jterrace

67.4k23 gold badges164 silver badges208 bronze badges

1 Comment

Davoud Taghawi-Nejad Over a year ago

Yes, that comes at a speed cost and I'd like to understand the problem.

Collectives™ on Stack Overflow

sqlite3 writes only floating-point numpy arrays not integer ones

2 Answers 2

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Related