SAP数据库中的地理编码地址

Question

我对python很陌生，我很好奇这是否是从SAP数据库读取地址、对它们进行地理编码、读取结果、然后将信息写回数据库的最有效方法。

下面的每一件事情都很有效，我只是好奇这是否会让一个专业的新手哭得不好，或者我第一次尝试的代码是可以接受的。-)我很确定我的变量名称格式不是100%符合规范。

参考Geocodio，https://www.geocod.io/docs/?python#fields

import platform
import json
import sys
from hdbcli import dbapi
from geocodio import GeocodioClient

client = GeocodioClient('x')

conn = dbapi.connect(address='x', port=x, user='x', password='x')

cursor = conn.cursor()
sql_command = "SELECT ADDRNUMBER, CITY1, REGION, POST_CODE1,STREET FROM CV_COORDS_TO_PROCESS"
cursor.execute(sql_command)
rows = cursor.fetchall()

address = ''
for row in rows:
    #build the address string and save the address id
    address = row[4] +", " + row[1] +", " + row[2] +" " + row[3]
    addr_id = row[0]
    #send the address to geocodio
    location = client.geocode(address)

    #Read the results and break after the first result, as per the geocodio documentation, 
    #in the event of multiple points, it lists them in order of confidence
    for subs in location['results']:
        lat = subs['location']['lat']
        long = subs['location']['lng']
        accuracy = subs['accuracy']
        accuracy_type = subs['accuracy_type']
        break

    ##update / insert into database
    merge_query = "upsert zsa_addr_coords values  ('100', %s, %f, %f, %f, '%s')"\
                  %(addr_id, lat, long, accuracy, accuracy_type ) + " where addr_id = '%s'" %addr_id
    try:
        cursor.execute(merge_query);
    except:
        print (ex)
        
    address = ''
cursor.close()
conn.close()

这是Geocodio返回结果的示例：

{
  "input": {
    "address_components": {
      "number": "1109",
      "predirectional": "N",
      "street": "Highland",
      "suffix": "St",
      "formatted_street": "N Highland St",
      "city": "Arlington",
      "state": "VA",
      "zip": "22201",
      "country": "US"
    },
    "formatted_address": "1109 N Highland St, Arlington, VA 22201"
  },
  "results": [
    {
      "address_components": {
        "number": "1109",
        "predirectional": "N",
        "street": "Highland",
        "suffix": "St",
        "formatted_street": "N Highland St",
        "city": "Arlington",
        "county": "Arlington County",
        "state": "VA",
        "zip": "22201",
        "country": "US"
      },
      "formatted_address": "1109 N Highland St, Arlington, VA 22201",
      "location": {
        "lat": 38.886665,
        "lng": -77.094733
      },
      "accuracy": 1,
      "accuracy_type": "rooftop",
      "source": "Virginia GIS Clearinghouse"
    }
  ]
}

Answer 1

在Python中，不需要在使用变量之前创建变量，因此循环之前的address = ''是不必要的。在再次使用变量之前，您也不需要重新设置一个变量，因此每个循环迭代结束时的address = ''特别没有必要。

您目前从DB中获取地址，然后将该地址的部分连接起来作为地理位置的查询字符串。如果您稍后按所需地址的顺序获得地址，这会更容易：

sql_command = "SELECT ADDRNUMBER, STREET, CITY1, REGION, POST_CODE1 FROM CV_COORDS_TO_PROCESS"
cursor.execute(sql_command)
rows = cursor.fetchall()

for row in rows:
    #build the address string and save the address id
    addr_id, address = row[0], ", ".join(row[1:])
    ...

可能已经有一种方法可以在SQL中进行连接，但我对该语言还不太精通，无法建议如何进行连接。

如果要从可迭代性中获取第一个值，可以使用next：

location = next(iter(client.geocode(address)['results']))

大多数DB包装器支持准备好的语句，这意味着您不必准备完整的查询字符串，而是使用如下所示：

merge_query = "upsert zsa_addr_coords values ('100', ?, ?, ?, ?, ?) where addr_id = ?"
try:
    cursor.execute(merge_query, (addr_id, lat, long, accuracy, accuracy_type, addr_id));
except:
    print (ex)

事实上，hdbcli也支持这一点：https://blogs.sap.com/2017/07/26/sap-hana-2.0-sps02-new-feature-updated-python-driver/

它甚至应该为您处理引用，并防止最基本的SQL注入攻击。

因为这个字符串现在永远不会变，所以你可以把它拉到循环之外。

您可能可以通过在循环之前禁用自动提交模式并在结束时手动提交来加快执行时间，这可以确保不必每次循环迭代都这样做：

conn.setautocommit(False)
for row in rows:
    ...
conn.commit()