将Python NumPy数组插入PostgreSQL数据库 [英] Insert Python NumPy array into PostgreSQL database
本文介绍了将Python NumPy数组插入PostgreSQL数据库的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何将大量坐标(x,y)插入到postgresSQL表中?我不想使用for
循环.这是一个具有3601x3601像素的栅格.
How do I insert a large array of coordinates (x,y) into a postgresSQL table? I don't want to use a for
loop. It is a raster with 3601x3601 pixels.
import numpy as np
import psycopg2
# Data example:
east = np.linspace(-180.0,180.0,num=10)
north = np.linspace(-90.0,90.0,num=10)
coor = np.vstack([east, north])
conn = psycopg2.connect("dbname='postgres' user='dbuser' host='localhost' password='dbpass'")
cur = conn.cursor()
cur.execute("DROP TABLE IF EXISTS foobar;")
cur.execute("CREATE TABLE foobar (coordinate point);")
# Working for an coordinate example:
cur.execute("INSERT INTO foobar VALUES (('12.56,56.43'));")
# Working for 1st coordinate in coor:
tmp = ','.join(str(e) for e in coor[:,0])
cur.execute('INSERT INTO foobar VALUES (point(' + tmp + '));')
# NOT WORKING!!!
# Insert all points in one go:
# cur.execute('INSERT INTO foobar VALUES (coor);')
conn.commit()
推荐答案
With the function execute_values()
you can insert multiple rows using a single SQL statement. You should prepare the data for the function in this format:
[['(-180.0, -90.0)'],
['(-140.0, -70.0)'],
['(-100.0, -50.0)'],
['(-60.0, -30.0)'],
['(-20.0, -10.0)'],
['(20.0, 10.0)'],
['(60.0, 30.0)'],
['(100.0, 50.0)'],
['(140.0, 70.0)'],
['(180.0, 90.0)']]
代码:
from psycopg2.extras import execute_values
# Data example:
east = np.linspace(-180.0,180.0,num=10)
north = np.linspace(-90.0,90.0,num=10)
# get array of pairs [east, north]
coor = np.dstack([east, north])
# convert to array of tuples (east, north) as strings
values = [[str(tuple(i))] for i in coor[0]]
execute_values(cur, 'INSERT INTO foobar VALUES %s', values)
conn.commit()
另请参见此答案.
这篇关于将Python NumPy数组插入PostgreSQL数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文