Compatibility between numpy and SQL data types

Question

I am trying to write an astropy.fits FITS_rec array (very similar to a numpy recarray) into a SQLite3 database. The idea is to automatically identify the data types of the individual fields in the array and create the db table accordingly. This works fine by looping through the columns:

for key_idx, key in enumerate(self.data.names):
    print type(self.data[key][0]) 
    if type(self.data[key][0]) == numpy.float32:
        table_cmd += "'%s' FLOAT" % key
    if type(self.data[key][0]) == numpy.float64:
        table_cmd += "'%s' DOUBLE" % key
    if type(self.data[key][0]) == numpy.int16:
        table_cmd += "'%s' INTEGER" % key 
    if type(self.data[key][0]) == numpy.int32:
        table_cmd += "'%s' INTEGER" % key 
    if type(self.data[key][0]) == numpy.string_:
        table_cmd += "'%s' TEXT" % key 
    if key_idx < self.shape[1]-1:
        table_cmd += ", "
table_cmd += ")"
print table_cmd

which creates:

CREATE TABLE data ('FLUX_ISOCOR' FLOAT, 'FLUXERR_ISOCOR' FLOAT, ..., 'FLAGS' INTEGER, 'FLUX_GROWTH' FLOAT, 'FLUX_GROWTHSTEP' FLOAT, 'FLUX_RADIUS' FLOAT)

If I try to insert values into the database

db.executemany("INSERT INTO data VALUES (" + \
               ','.join(['?' for i in range(self.shape[1])]) + \
               ')', data)

I keep getting the following error message:

sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

I figured out that this is because the data types I try to insert into the table are numpy data types (numpy.float32, numpy.float64, numpy.int16...), which are probably not compatible with the SQL data types (FLOAT, INT, ...) I use in the table definition. My question to you is what SQL data types I should use for the different numpy data types, e.g., can I insert a numpy.float32 (64?) into a SQL FLOAT (DOUBLE?) table field?

I tried to change all the data into simple floats and then inserting them into the table - this works, but takes a long time since I have to loop over all the data. This makes me think that the problem is really the compatibility between numpy and SQL data types.

Have you tried the odo library? it can transform numpy.recarray to sqllite. odo.pydata.org/en/latest/sql.html — Back2Basics
– Back2Basics, Commented Jan 31, 2016 at 7:03
Thanks, I haven't heard about odo before. I will give it a try, although I would prefer a solution that does not require adding additional modules. — mommermi
– mommermi, Commented Jan 31, 2016 at 17:07
I think I figured it out: on my 64bit system, SQLite3 accepts the 64bit data types, i.e., numpy.float64 and numpy.int64 for FLOAT/REAL and INTEGER, respectively. — mommermi
– mommermi, Commented Jan 31, 2016 at 18:59

gypaetus · Accepted Answer · 2017-03-17 19:34:05Z

2

SQLite supports a limited set of data types. To use numpy Python data types you can use the register_adapter function that converts the type to an SQLite supported representation.

import numpy as np
import sqlite3

sqlite3.register_adapter(np.float64, float)
sqlite3.register_adapter(np.float32, float)
sqlite3.register_adapter(np.int64, int)
sqlite3.register_adapter(np.int32, int)

answered Mar 17, 2017 at 19:34

gypaetus

7,4273 gold badges37 silver badges48 bronze badges

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

Collectives™ on Stack Overflow

Compatibility between numpy and SQL data types

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related