python sqlite3 not support numpy type?

(1) By anonymous on 2020-06-11 08:36:10

import numpy as np
import sqlite3

DB = __file__.replace('.py','.db')

tablename = 'why'

fieldnames = 'ttt'


a = np.array([8.292553,16.242676,16.328308],dtype = np.float32)

v = []

for i in range(3):

    result = [a.mean()] 

    # result = [float(a.mean())] #this is ok,but why? not support numpy?

    result.append(i+1)

    v.append(result)

print(v)

conn = sqlite3.connect(DB)

cur = conn.cursor()

cur.execute('''CREATE TABLE if not exists {table}
            (id         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
            {field}     FLOAT);'''.format(table=tablename,field=fieldnames))

conn.commit()

for i in range(3):

    cur.execute('''INSERT INTO {table}({keys}) VALUES (0.3);'''.format(table=tablename,keys=fieldnames))

conn.commit()

try:

    sql = 'UPDATE {table} SET {field} = ? where id = ?;'

    cur.executemany(sql.format(table=tablename,field=fieldnames),v)

    print(v)

    conn.commit()

    print('SUCCESS:UPDATE [[{table}] {field}]'.format(table=tablename,field=fieldnames))

except:

    print('ERROR:UPDATE [[{table}] 
{field}]'.format(table=tablename,field=fieldnames))

cur.execute('select * from {table} limit 3'.format(table=tablename))

print(cur.fetchall())

conn.close()



out:
[[13.621179, 1], [13.621179, 2], [13.621179, 3]]

[[13.621179, 1], [13.621179, 2], [13.621179, 3]]

SUCCESS:UPDATE [[why] ttt]

[(1, b'Y\xf0YA'), (2, b'Y\xf0YA'), (3, b'Y\xf0YA')]

(2) By Warren Young (wyoung) on 2020-06-11 09:31:45 in reply to 1 [link]

First off, the [set of data types natively supported by SQLite][1] is small. SQLite is not going to natively support every possible data type in every programming language that has a SQLite binding.

The Python SQLite binding could provide a *mapping* between numpy data types and SQLite data types, but that's entirely off-topic here. Take it up with the maintainer of your `sqlite3` module. This appears to be a core Python module, so my outsider's guess would be to post to [the Python ideas mailing list][2]. There may be a more appropriate place to send it, though.

Regardless, SQLite cannot affect what Python and numpy do. It's just a data storage engine. It takes what its callers give it, stores it, and offers ways to retrieve the data later. Data mappings are at a layer above that.


[1]: https://www.sqlite.org/datatype3.html
[2]: https://mail.python.org/mailman3/lists/python-ideas.python.org/