如何将numpy数组转换为postgresql列表

Question

我正在尝试用Python把一个numpy数组的两列插入到一个PostgreSQL数据库表中，作为两个数组。

这个PostgreSQL表叫做DOS，结构如下：

• 主键（primary_key）
• 能量（energy）是一个整数数组（integer[]）
• DOS也是一个整数数组（integer[]）

我有一个numpy数组，它是一个2维数组，包含两个1维数组：

finArray = np.array([energy,dos])

我尝试使用以下脚本将数据插入数据库，但每次插入时总是出现错误。我搞不清楚怎么格式化这个数组，以便它能正确显示成这样的形式：INSERT INTO dos VALUES(1,'{1,2,3}','{1,2,3}')

脚本如下：

import psycopg2
import argparse
import sys
import re
import numpy as np
import os

con = None


try:    
    con = psycopg2.connect(database='bla', user='bla')
    cur = con.cursor()
    cur.execute("INSERT INTO dos VALUES(1,'{%s}')", [str(finArray[0:3,0].tolist())[1:-1]])
    con.commit()


except psycopg2.DatabaseError, e:
    if con:
        con.rollback()

    print 'Error %s' % e
    sys.exit(1)

finally:
    if con:
        con.close()

我搞不清楚的是，为什么在括号内会出现这样的单引号（' '）。

Error syntax error at or near "0.31691105000000003"
LINE 1: INSERT INTO dos VALUES(1,'{'0.31691105000000003, -300.0, -19...

Answer 1

你需要把numpy数组转换成列表，举个例子：

import numpy as np
import psycopg2
fecha=12
tipo=1
precau=np.array([20.35,25.34,25.36978])
conn = psycopg2.connect("dbname='DataBase' user='Administrador' host='localhost' password='pass'")
cur = conn.cursor()
#make a list
vec1=[]
for k in precau:
    vec1.append(k)
#make a query
query=cur.mogrify("""UPDATE prediccioncaudal SET fecha=%s, precaudal=%s WHERE idprecau=%s;""", (fecha,vec1,tipo))
#execute a query
cur.execute(query)
#save changes
conn.commit()
#close connection
cur.close()
conn.close()

Answer 2

Psycopg会把Python列表转换成数组，所以你只需要把numpy数组转换成Python列表，然后把它传给execute方法就可以了。

import psycopg2
import numpy as np

energy = [1, 2, 3]
dos = [1, 2, 3]
finArray = np.array([energy,dos])
insert = """
    insert into dos (pk, energy) values (1, %s);
;"""
conn = psycopg2.connect("host=localhost4 port=5432 dbname=cpn")
cursor = conn.cursor()
cursor.execute(insert, (list(finArray[0:3,0]),))
conn.commit()
conn.close()

Answer 3

这些引号是在使用 numpy.ndarray.tolist() 时出现的，原因是你实际上有字符串。如果你不想像 @Saullo Castro 提到的那样假设数据是 float 类型的，你可以简单地用 str(finArray[0:3,0].tolist()).replace("'","")[1:-1] 来去掉这些引号。

不过，更合适的做法是，如果你在脚本中以某种方式处理 finArray 中的数据，并且假设它们是数字，那么你应该确保一开始就把它们作为数字导入到数组中。你可以在创建数组时指定它的类型，比如 finArray = np.array(..., dtype=np.float)，这样就可以确保数据类型是正确的，然后再根据需要进行处理。

Answer 4

你可能有一个字符串数组，试着在你的命令中加上 astype(float)，像这样：

cur.execute("INSERT INTO dos VALUES(1,'{%s}')", [str(finArray[0:3,0].astype(float).tolist())[1:-1]])

Answer 5

虽然有点晚了，但还是想分享一下。

今天我在尝试把一个numpy数组插入到Redshift数据库里。试了很多方法，比如odo和df.to_sql()，最后终于找到一个速度挺快的办法（大约每分钟插入3000行）。我就不说那些工具遇到的问题了，这里有个简单有效的方法：

cursor = conn.cursor()

args_str = b','.join(cursor.mogrify("(%s,%s,...)", x) for x in tuple(map(tuple,np_data)))

cursor.execute("insert into table (a,b,...) VALUES "+args_str.decode("utf-8"))
cursor.commit()
cursor.close()

第二行的代码需要根据你数组的大小做一些调整。

你可能也想看看这些答案：

1. 从numpy数组转换为元组
2. 在psycopg2中插入多行数据