title: Compatibility between numpy and SQL data types
body: <p>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:</p>
  
  <pre><code>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 &lt; self.shape[1]-1:
          table_cmd += ", "
  table_cmd += ")"
  print table_cmd
  </code></pre>
  
  <p>which creates:</p>
  
  <blockquote>
    <p>CREATE TABLE data ('FLUX_ISOCOR' FLOAT, 'FLUXERR_ISOCOR' FLOAT,
    ..., 'FLAGS' INTEGER, 'FLUX_GROWTH' FLOAT, 'FLUX_GROWTHSTEP' FLOAT, 'FLUX_RADIUS' FLOAT)</p>
  </blockquote>
  
  <p>If I try to insert values into the database</p>
  
  <pre><code>db.executemany("INSERT INTO data VALUES (" + \
                 ','.join(['?' for i in range(self.shape[1])]) + \
                 ')', data)
  </code></pre>
  
  <p>I keep getting the following error message:</p>
  
  <blockquote>
    <p>sqlite3.InterfaceError: Error binding parameter 0 - probably
    unsupported type.</p>
  </blockquote>
  
  <p>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?</p>
  
  <p>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.</p>