在已有的数据库里添加一列，并写入python的数组数据

总结就是，暂时没有直接添加列的办法，只能先读入python，利用pandas写一个dataframe，加入新的列，再将整备好的dataframe写入数据库。

前提是二者之间的数据结构，长度形状一致。

参考：https://stackoverflow.com/questions/53850316/insert-python-numpy-array-into-postgresql-database

以上的不好用，跟想象中不一样，得到的结果会在列方向上出现很多null值

from osgeo import gdal,ogr

import struct

import os

import numpy as np

path='D:/minxinan/data/NDVI/ndvirepro'

filer=os.listdir(path)

#src_filename = 'D:\idl\919.tif'

plistndvi=[]

for i in filer:

src_filename=path+'/'+i

src_ds=gdal.Open(src_filename)

gt=src_ds.GetGeoTransform()

rb=src_ds.GetRasterBand(1)

shp_filename = 'D:/minxinan/zhandianpoint/121paixuwgs.shp'

ds=ogr.Open(shp_filename)

lyr=ds.GetLayer()

plistoneday=[]

for j in range(16*24):

for feat in lyr:

geom = feat.GetGeometryRef()

mx,my=geom.GetX(), geom.GetY() #coord in map units

#Convert from map to pixel coordinates.

#Only works for geotransforms with no rotation.

px = int((mx - gt[0]) / gt[1]) #x pixel

py = int((my - gt[3]) / gt[5]) #y pixel

structval=rb.ReadRaster(px,py,1,1,buf_type=gdal.GDT_UInt16) #Assumes 16 bit int aka 'short'

intval = struct.unpack('h' , structval) #use the 'short' format code (2 bytes) not int (4 bytes)

plistoneday.append(intval[0])

plistndvi.append(plistoneday)

plistndvi=np.array(plistndvi).reshape(len(plistndvi)*len(lyr))

ndvi2018=plistndvi[:len(lyr)*24*365]

del plistndvi

#ndvi加入数据库

import psycopg2

from io import StringIO

import pandas as pd

conn=psycopg2.connect(database="postgres",user="postgres",password="1234",host="127.0.0.1",port="5432")

cur = conn.cursor()

df_tables = pd.read_sql_query("select * from mxndata3;",con=conn)#

df_tables['ndvi']=ndvi2018

output = StringIO()

df_tables.to_csv(output, sep='\t', index=False, header=False)

output1 = output.getvalue()

cur.execute("CREATE TABLE mxndata4(date2018 timestamp,pointxy varchar,long double precision,lat double precision,pm25 double precision,\

pm10 double precision,so2 double precision,no2 double precision,co double precision,o3 double precision\

,qy double precision,wd double precision,xdsd double precision,fs double precision,fx double precision\

,pointpyname varchar,long1 double precision,lat1 double precision,id1 double precision,\

l1 double precision,l2 double precision,l3 double precision,l4 double precision,l5 double precision,\

l6 double precision,l7 double precision,l8 double precision,rlh double precision\

,x1 double precision,y1 double precision,dem double precision,pop double precision,ndvi double precision);")

cur.copy_from(StringIO(output1), 'mxndata4',columns=('date2018','pointxy','long','lat','pm25','pm10','so2','no2','co','o3','qy','wd'\

,'xdsd','fs','fx','pointpyname','long1','lat1','id1','l1','l2','l3','l4','l5','l6','l7','l8','rlh','x1','y1','dem','pop','ndvi'))

conn.commit()

cur.close()

conn.close()