将Python NumPy数组插入PostgreSQL数据库的最佳方法

Question

我们的团队使用的软件严重依赖于将NumPy数据转储到文件中，这大大降低了代码的速度。如果我们可以将NumPy数组直接存储在PostgreSQL中，我们将获得一个重大的性能提升

在任何数据库或类似于可搜索数据库的结构中存储NumPy数组的其他高性能方法都是受欢迎的，但PostgresSQL将是首选

My question is very similar to one asked previously.但是，我正在寻找一个更健壮、性能更好的答案，我希望存储任意NumPy数组

Answer 1

不确定这是否是您想要的，但假设您对现有postgres DB具有读/写访问权限：

import numpy as np
import psycopg2 as psy
import pickle

db_connect_kwargs = {
    'dbname': '<YOUR_DBNAME>',
    'user': '<YOUR_USRNAME>',
    'password': '<YOUR_PWD>',
    'host': '<HOST>',
    'port': '<PORT>'
}

connection = psy.connect(**db_connect_kwargs)
connection.set_session(autocommit=True)
cursor = connection.cursor()

cursor.execute(
    """
    DROP TABLE IF EXISTS numpy_arrays;
    CREATE TABLE numpy_arrays (
        uuid VARCHAR PRIMARY KEY,
        np_array_bytes BYTEA
    )
    """
)

这种方法的要点是将任何numpy数组（任意形状和数据类型）存储为numpy_arrays表中的一行，其中uuid是一个唯一标识符，以便以后能够检索数组。实际数组将以字节形式保存在np_array_bytes列中

插入数据库：

some_array = np.random.rand(1500,550)
some_array_uuid = 'some_array'

cursor.execute(
    """
    INSERT INTO numpy_arrays(uuid, np_array_bytes)
    VALUES (%s, %s)
    """,
    (some_array_uuid, pickle.dumps(some_array))
)

从数据库查询：

uuid = 'some_array'
cursor.execute(
    """
    SELECT np_array_bytes
    FROM numpy_arrays
    WHERE uuid=%s
    """,
    (uuid,)
)
some_array = pickle.loads(cursor.fetchone()[0])

性能？

If we could store our NumPy arrays directly in PostgreSQL we would get a major performance boost.

我还没有以任何方式对这种方法进行基准测试，所以我无法确认或反驳这一点

磁盘空间？

我的猜测是，这种方法占用的磁盘空间与使用np.save('some_array.npy', some_array)将数组转储到文件中所占用的磁盘空间相同。如果这是一个问题，考虑在插入之前压缩字节。

Answer 2

您可以使用subprocess.run()执行shell命令，以便在管道中使用PostgressqlCOPY将csv文件批量复制到服务器。我更熟悉mssql，它有bcp方法，无法在我的解决方案上完全测试，尽管我认为它是通过终端调用的类似方法。Terminal命令基于使用该方法的第三个链接，尽管该解决方案使用了subprocess.call()，该方法后来已用subprocess.run()更新

https://docs.python.org/3/library/subprocess.html#subprocess.runhttps://ieftimov.com/post/postgresql-copy/

Python psql \copy CSV to remote server

import subprocess


psql_command = "\"\copy table (col1, col2) FROM file_location CSV HEADER QUOTE '\\\"' NULL ''\""
# user, hostname, password, dbname all defined elsewhere above.
command = ["psql",
    "-U", user,
    "-h", hostname,
    "-d", dbname,
    "-w", password,
    "-c", psql_command,
]

subprocess.run(command)