使用python函数输出更新各个postgresql行

Question

我正在进行一个项目，该项目要求postgresql中的一个列由Mapbox地理编码api更新，以便将地址转换为lon，lat坐标。我创建了一个FOR循环，以便从每一行读取地址。然后，我想保存创建到“坐标”列中的唯一lon，lat坐标。

但是，我编写的代码用第一行的lon、lat坐标更新整个“坐标”列，而不是单独迭代和更新每一行的“坐标”列。

我哪里出错了？任何帮助都将不胜感激。

主代码

import psycopg2
import json
from psycopg2.extras import RealDictCursor
import sys
from mapbox import Geocoder
from mapboxgeocode import getCoord
import numpy as np 

con = None

try:

    con = psycopg2.connect(database='database', user='username') 
    cur = con.cursor()
    cur.execute("DROP TABLE IF EXISTS permits")
    cur.execute("""CREATE TABLE permits(issued_date DATE, address
    VARCHAR(200), workdesc VARCHAR(600),permit_type VARCHAR(100),   permit_sub_type
    VARCHAR(100), anc VARCHAR(4), applicant VARCHAR(100),owner_name       
    VARCHAR(200))""")
    cur.execute(""" COPY permits FROM '/path/to/csv/file'
    WITH DELIMITER ',' CSV HEADER """)
    cur.execute("""ALTER TABLE permits ADD COLUMN id SERIAL PRIMARY KEY;
    UPDATE permits set id = DEFAULT;""")
    cur.execute("""ALTER TABLE permits ADD COLUMN coordinates VARCHAR(80);
    UPDATE permits SET coordinates = 4;""")
    cur.execute("""ALTER TABLE permits ADD COLUMN city VARCHAR(80); 
    UPDATE permits SET city = 'Washington,DC'; ALTER TABLE permits ALTER    
    COLUMN city SET NOT NULL;""")

    cur.execute("UPDATE permits SET address = address || ' ' || city;")

    cur.execute("SELECT * FROM permits;")

    for row in cur.fetchall():
        test = row[1]
        help = getCoord(test)
        cur.execute("UPDATE permits SET coordinates = %s;", (help,) )
        print(test)
        con.commit()



except psycopg2.DatabaseError, e:
    print 'Error %s' % e    
    sys.exit(1)


finally:

    if con:
         cur.close()
         con.commit()
         con.close()

地码函数

from mapbox import Geocoder
import numpy as np

def getCoord(address):
    geocoder = Geocoder(access_token='xxxxxxxxxxxxxxxx')
    response = geocoder.forward(address)
    first = response.geojson()['features'][0]
    row = first['geometry']['coordinates']
    return row

Answer 1

您需要在UPDATE语句中添加WHERE条件。如果没有位置，SQL只是认为您想要更新所有的坐标列。适当的WHERE条件会让它具体知道它需要修改列中的哪个单元格。

您可能希望使用主键，因为它是唯一的标识符。也许是一项大意如下的声明：

cur.execute("UPDATE permits SET coordinates = %s WHERE id = %s;", (help, row[index of the id column]) )

我认为您需要的行索引是row8，但是您必须在代码中确认这一点。我希望这能让它发挥作用。