Geocoding addresses from a SAP database I'm new to python and curious if this is the most efficient way to read addresses from a SAP database, geocode them, read the results, and then write the information back to the database.
Everything below works, I'm just curious if this would make an expert cry beginner bad, or acceptable code for my first try. :-) I'm pretty sure my variable name formatting isn't 100% up to spec.
For reference on 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 = &quot;SELECT ADDRNUMBER, CITY1, REGION, POST_CODE1,STREET FROM CV_COORDS_TO_PROCESS&quot;
cursor.execute(sql_command)
rows = cursor.fetchall()

address = ''
for row in rows:
    #build the address string and save the address id
    address = row[4] +&quot;, &quot; + row[1] +&quot;, &quot; + row[2] +&quot; &quot; + 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 = &quot;upsert zsa_addr_coords values  ('100', %s, %f, %f, %f, '%s')&quot;\
                  %(addr_id, lat, long, accuracy, accuracy_type ) + &quot; where addr_id = '%s'&quot; %addr_id
    try:
        cursor.execute(merge_query);
    except:
        print (ex)
        
    address = ''
cursor.close()
conn.close()

This is example of the results Geocodio returns:
{
  &quot;input&quot;: {
    &quot;address_components&quot;: {
      &quot;number&quot;: &quot;1109&quot;,
      &quot;predirectional&quot;: &quot;N&quot;,
      &quot;street&quot;: &quot;Highland&quot;,
      &quot;suffix&quot;: &quot;St&quot;,
      &quot;formatted_street&quot;: &quot;N Highland St&quot;,
      &quot;city&quot;: &quot;Arlington&quot;,
      &quot;state&quot;: &quot;VA&quot;,
      &quot;zip&quot;: &quot;22201&quot;,
      &quot;country&quot;: &quot;US&quot;
    },
    &quot;formatted_address&quot;: &quot;1109 N Highland St, Arlington, VA 22201&quot;
  },
  &quot;results&quot;: [
    {
      &quot;address_components&quot;: {
        &quot;number&quot;: &quot;1109&quot;,
        &quot;predirectional&quot;: &quot;N&quot;,
        &quot;street&quot;: &quot;Highland&quot;,
        &quot;suffix&quot;: &quot;St&quot;,
        &quot;formatted_street&quot;: &quot;N Highland St&quot;,
        &quot;city&quot;: &quot;Arlington&quot;,
        &quot;county&quot;: &quot;Arlington County&quot;,
        &quot;state&quot;: &quot;VA&quot;,
        &quot;zip&quot;: &quot;22201&quot;,
        &quot;country&quot;: &quot;US&quot;
      },
      &quot;formatted_address&quot;: &quot;1109 N Highland St, Arlington, VA 22201&quot;,
      &quot;location&quot;: {
        &quot;lat&quot;: 38.886665,
        &quot;lng&quot;: -77.094733
      },
      &quot;accuracy&quot;: 1,
      &quot;accuracy_type&quot;: &quot;rooftop&quot;,
      &quot;source&quot;: &quot;Virginia GIS Clearinghouse&quot;
    }
  ]
}
In Python, you don't need to create your variables before using them, so the address = '' before your loop is unnecessary. You also don't need to reset a variable before using it again, so the address = '' at the end of each loop iteration is particularly unnecessary.
You currently get the addresses from the DB and then concatenate parts of that address as the query string for your geolocation. This would be easier if you got the address in the same order you need it later:
sql_command = &quot;SELECT ADDRNUMBER, STREET, CITY1, REGION, POST_CODE1 FROM CV_COORDS_TO_PROCESS&quot;
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], &quot;, &quot;.join(row[1:])
    ...

There's probably a way to do the concatenation already in SQL, but I am not versed enough in that language to suggest how.
If you want to get the first value from an iterable, you can use next:
location = next(iter(client.geocode(address)['results']))

Most DB wrappers support prepared statements, which would mean that you don't have to prepare the full query string, but rather use something like this:
merge_query = &quot;upsert zsa_addr_coords values ('100', ?, ?, ?, ?, ?) where addr_id = ?&quot;
try:
    cursor.execute(merge_query, (addr_id, lat, long, accuracy, accuracy_type, addr_id));
except:
    print (ex)

Indeed, hdbcli also support this: https://blogs.sap.com/2017/07/26/sap-hana-2.0-sps02-new-feature-updated-python-driver/
It should even take care of quoting for you and prevents the most basic SQL
injection attacks.
And since that string now never changes, you can pull it outside of the loop.
You can probably speed up the execution time by disabling the auto-commit mode before your loop and manually committing at the end, which ensures that you don't have to do that every loop iteration:
conn.setautocommit(False)
for row in rows:
    ...
conn.commit()