SQLite Forum

python sqlite3 not support numpy type?
Login

python sqlite3 not support numpy type?

(1) By anonymous on 2020-06-11 08:36:10 [link] [source]

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'Yxf0YA'), (2, b'Yxf0YA'), (3, b'Yxf0YA')]

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

First off, the set of data types natively supported by SQLite 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. 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.