python - 将 float64 项目的 Numpy 数组作为二进制插入 PostgreSQL

Question

我正在尝试将 Numpy 数组插入 PostgreSQL。试图这样做

def write_to_db(some_arr, some_txt):
""" insert a new array into the face_ar table """
    sql = """INSERT INTO test_db VALUES(%s,%s);"""
    conn = None
    try:
        params = config()
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute(sql, (some_arr, some_txt))
        conn.commit()
        cur.close()

    except (Exception, psycopg2.DatabaseError) as e:
        print(e)
    finally:
        if conn is not None:
            conn.close()

在此之前我在我的数据库中创建了一个表

create table test_db (encodings double precision[], link text);

最后我得到一个错误:“无法适应类型‘numpy.ndarray’”

我需要编写包含 125 个 float64 项目的 Numpy 数组和每行中的链接之类的小文本。我的项目中将有几百万行。只是读取速度和数据库大小很重要。据我所知，无法直接插入 Numpy 数组，需要将其转换为另一种格式。我的第一个想法是将其转换为二进制数据并将其保存到数据库中，但我不知道该怎么做以及如何以 Numpy 数组格式从数据库中取回它。

Answer 1

感谢 Vasyl Kushnir。这种方法开始很好地读取数据并且速度很快

import psycopg2
from config import config
import msgpack
import msgpack_numpy as m

def write_to_db(encoding, link):
""" insert a new array into the test1_db table """
    sql = """INSERT INTO test1_db VALUES(%s,%s);"""
    conn = None
    dumped_data = msgpack.packb(encoding, default=m.encode)
    try:
        params = config()
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute(sql, (dumped_data, link))
        conn.commit()
        cur.close()

    except (Exception, psycopg2.DatabaseError) as e:
        print(e)
    finally:
        if conn is not None:
            conn.close()

def read_from_db():
""" query data from the test1_db table """
    conn = None
    row = None
    try:
        params = config()
        conn = psycopg2.connect(**params)
        cur = conn.cursor()
        cur.execute("SELECT encodings, link FROM test1_db")
        print("The number of rows: ", cur.rowcount)
        row = cur.fetchone()
        cur.close()
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()
        encoding1, somelink = row
        return msgpack.unpackb(encoding1, object_hook=m.decode), somelink