Python将numpy数组插入sqlite3数据库 [英] Python insert numpy array into sqlite3 database
问题描述
我正在尝试在sqlite3数据库中存储约1000个浮点数的numpy数组,但我不断收到错误"InterfaceError:错误绑定参数1-可能不受支持的类型".
I'm trying to store a numpy array of about 1000 floats in a sqlite3 database but I keep getting the error "InterfaceError: Error binding parameter 1 - probably unsupported type".
我的印象是BLOB数据类型可以是任何东西,但绝对不适用于numpy数组.这是我尝试过的:
I was under the impression a BLOB data type could be anything but it definitely doesn't work with a numpy array. Here's what I tried:
import sqlite3 as sql
import numpy as np
con = sql.connect('test.bd',isolation_level=None)
cur = con.cursor()
cur.execute("CREATE TABLE foobar (id INTEGER PRIMARY KEY, array BLOB)")
cur.execute("INSERT INTO foobar VALUES (?,?)", (None,np.arange(0,500,0.5)))
con.commit()
是否存在另一个可用于将numpy数组放入表中的模块?还是可以将numpy数组转换为sqlite可以接受的Python中的另一种形式(例如可以拆分的列表或字符串)?性能不是重中之重.我只希望它能正常工作!
Is there another module I can use to get the numpy array into the table? Or can I convert the numpy array into another form in Python (like a list or string I can split) that sqlite will accept? Performance isn't a priority. I just want it to work!
谢谢!
推荐答案
您可以使用sqlite3
注册新的array
数据类型:
You could register a new array
data type with sqlite3
:
import sqlite3
import numpy as np
import io
def adapt_array(arr):
"""
http://stackoverflow.com/a/31312102/190597 (SoulNibbler)
"""
out = io.BytesIO()
np.save(out, arr)
out.seek(0)
return sqlite3.Binary(out.read())
def convert_array(text):
out = io.BytesIO(text)
out.seek(0)
return np.load(out)
# Converts np.array to TEXT when inserting
sqlite3.register_adapter(np.ndarray, adapt_array)
# Converts TEXT to np.array when selecting
sqlite3.register_converter("array", convert_array)
x = np.arange(12).reshape(2,6)
con = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()
cur.execute("create table test (arr array)")
使用此设置,您可以简单地插入NumPy数组,而无需更改语法:
With this setup, you can simply insert the NumPy array with no change in syntax:
cur.execute("insert into test (arr) values (?)", (x, ))
然后直接从sqlite作为NumPy数组检索数组:
And retrieve the array directly from sqlite as a NumPy array:
cur.execute("select arr from test")
data = cur.fetchone()[0]
print(data)
# [[ 0 1 2 3 4 5]
# [ 6 7 8 9 10 11]]
print(type(data))
# <type 'numpy.ndarray'>
这篇关于Python将numpy数组插入sqlite3数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!