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

I am trying to use python to insert 2 columns of a numpy array into a postgresql table as two arrays.

我正在尝试使用python将两个numpy数组作为两个数组插入到postgresql表中。

postgresql table is DOS: primary_key energy integer[] dos integer[]

postgresql表为DOS: primary_key energy integer[] DOS integer[]

I have a numpy array that is a 2d array of 2x1D arrays:

我有一个numpy数组，它是2x1D数组的2d数组:

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

I am trying to use the following script for inserting into a database and I keep getting errors with the insert. I can't figure out how to format the array so that it properly formats in the form: INSERT INTO dos VALUES(1,'{1,2,3}','{1,2,3}')"

我正在尝试使用下面的脚本插入到数据库中，并且不断地在insert中获取错误。我不知道如何格式化数组，以便它能正确格式化表单:插入到dos值(1，'{1,2,3}'，'{1,2,3}')中

Script:

脚本:

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

The part I can't figure out is I will get errors like this:

我搞不清楚的是，我会出现这样的错误:

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

I can't figure out where that inner ' ' is coming from in the bracket.

我不知道里面的' '从括号里出来。

5 个解决方案

#1

Too late, but putting this out anyway.

太迟了，但还是把它放出来。

I was trying to insert a numpy array into Redshift today. After trying odo, df.to_sql() and what not, I finally got this to work at a pretty fast speed (~3k rows/minute). I won't talk about the issues I faced with those tools but here's something simple that works:

我今天试着在红移中插入一个numpy数组。在尝试了odo、df.to_sql()等之后，我最终使它以相当快的速度(大约3k行/分钟)工作。我不会谈论这些工具所面临的问题，但这里有一个简单的方法:

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

The 2nd line will need some work based on the dimensions of your array.

第二行需要根据数组的大小做一些工作。

You might want to check these answers too:

你也可以看看这些答案:

Converting from numpy array to tuple
从numpy数组转换到元组
Multiple row inserts in psycopg2
psycopg2中的多行插入

#2

You probably have an array of strings, try changing your command adding astype(float), like:

您可能有一个字符串数组，尝试更改命令添加astype(float)，比如:

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

#3

The quotes come during the numpy.ndarray.tolist() and come because you actually have strings. If you don't want to assume that data is float-typed as @Saullo Castro suggested you could also do a simple str(finArray[0:3,0].tolist()).replace("'","")[1:-1] to get rid of them.

引号出现在numpy.ndarray.tolist()中，因为您实际上有字符串。如果您不想假定数据是float类型的，那么@Saullo Castro建议您也可以做一个简单的str(finArray[0:3,0].tolist()).replace(“'”，“”)[1:-1]来处理它们。

However, more appropriately, if you are treating the data in finArray in any way in your script and assume they are numbers, you should probably make sure they are imported into the array as numbers to start with. You can require the array to have a certain datatype while initiating it by specifying, e.g. finArray = np.array(..., dtype=np.float) and then work backwards towards where it is suitable to enforce the type.

但是，更合适的是，如果您在脚本中以任何方式处理finArray中的数据并假设它们是数字，那么您应该确保它们作为数字导入数组中。您可以要求该数组在初始化时使用指定的数据类型，例如finArray = np.array(…， dtype=np.float)，然后向适合执行类型的方向反向工作。

#4

Psycopg will adapt a Python list to an array so you just have to cast the numpy array to a Python list and pass it to the execute method

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

#5

You need convert the numpy array to a list, example:

您需要将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()

#1