Realization Method of Inserting numpy Array into Database by sqllite in python

sqllite is not related to numpy array Types, we usually need to convert the array to text and then insert it into the database, or use the blob In addition to storing array data with type, we have another method, which allows us to directly use the array To insert and query data, the implementation code is as follows

import sqlite3
import numpy as np
import io

def adapt_array(arr):
    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)


#  When inserting data, the array Convert to text Insert 
sqlite3.register_adapter(np.ndarray, adapt_array)

#  When querying data, the text Convert to array
sqlite3.register_converter("array", convert_array)


# Connect to a database 
con = sqlite3.connect("test.db", detect_types=sqlite3.PARSE_DECLTYPES)
cur = con.cursor()

# Create a table 
cur.execute("create table test (arr array)")

# Insert data 
x = np.arange(12).reshape(2,6)
cur.execute("insert into test (arr) values (?)", (x, ))

# Query data 
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'>

Example code to see Python operation sqlite database and save query numpy type data

# -*- coding: utf-8 -*-
'''
Created on 2019 Year 3 Month 6 Day 

@author: Administrator
'''
import sqlite3
import numpy as np
import io

def adapt_array(arr):

    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)

#  Create a database connection object 
conn = sqlite3.connect('sample_database.db', detect_types=sqlite3.PARSE_DECLTYPES)  #  Connect to SQLite Database 
'''
sqlite3.PARSE_DECLTYPES
 This constant is used in functions connect() In, set the keyword parameter detect_types Up there. Indicates that when returning 1 Row value, whether to analyze the data type definition of this column value. If this parameter is set, the type of the datasheet column is analyzed and an object of this type is returned, not in the form of a string. 

sqlite3.PARSE_COLNAMES 
 This constant is used in functions connect() In, set the keyword parameter detect_types Up there. Indicates that when returning 1 Row value, whether to parse the name of this column value. If this parameter is set, the name of the data table column is analyzed and the name of this type is returned 
'''
#  Parameter :memory: To create 1 Memory database 
# conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)

# 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)

# conn = sqlite3.connect(":memory:", detect_types=sqlite3.PARSE_DECLTYPES)
cursor = conn.cursor()
#  Create a database table 
cursor.execute("create table test (arr array)")
#  Insert 1 Row data 
cursor.execute("insert into test (arr) values (?)", (x,))
#  Submit 
conn.commit()

cursor.execute("select arr from test")
data = cursor.fetchone()[0]

print(data)
'''
[[ 0  1  2  3  4  5]
 [ 6  7  8  9 10 11]]
'''
print(type(data))
'''
<class 'numpy.ndarray'>
'''
cursor.close()  #  Shut down Cursor
conn.close()  #  Close the database 

The above is the python sqllite insert numpy array to the database implementation method details, more information about python numpy array please pay attention to other related articles on this site!