The answer: it depends!
Is your SQL server accessible via the web, or do you need to be in-network? If the former, you can absolutely use a Notebook. If the latter, you'll need a local Python file, but you can still use a Notebook environment to develop the script.
In either case, you'll want to take a look at pandas, specifically pandas.read_sql(), which can read either a whole table or a specific query into a dataframe. For those not familiar, pandas (and the underlying numpy) is a fantastic tool for analyzing and manipulating tabular data.
Connecting to an SQL database via pandas utilizes sqlalchemy, so that would be useful to look at as well, though the pandas examples show you everything you need to know.
Side note: if you're working in the AGOL Notebook environment, you will be missing some of the submodules sqlalchemy needs to connect to various database types. These can be handled by including a cell which says:
pip install pymssql (or other required submodule)
If you're working locally, you can have this installed already, and skip that step.
Once you've queried out the data and made whatever changes you need, you can then use the ArcGIS Python API to append to / update / overwrite hosted feature services.
from arcgis import GIS
import pandas as pd
from sqlalchemy import create_engine
# DB connection
engine = create_engine('mssql+pymssql://*server_name*/*database_name*?trusted_connection=yes')
# Query string
query = """SELECT t.somecolumn, t.othercolumn
FROM dbo.TableName t
ORDER BY t.somecolumn DESC"""
# Query DB to dataframe
df = pd.ready_sql(query, engine)
## Do some things to the dataframe here
# Connect to your AGOL / Enterprise
gis = GIS('org-url', 'username', 'password')
# Get the target layer; swap the '0' for whatever layer index you need
# Alternate: use .tables[0] if target is non-spatial
target_layer = gis.content.get('layer itemID').layers[0]
# Clear existing records
target_layer.manager.truncate()
# Append new records from dataframe
target_layer.edit_features(adds=df.spatial.to_featureset())
I should say, there are LOTS of ways to manage that end part. Maybe instead of truncate / append, you choose to identify updated rows based on a unique identifier column. Maybe you're just appending new data and you don't need to truncate anything. That part will really depend on your particular situation.
Now, if you're working locally, you can use a Notebook to develop the script, as I mentioned. Once you're sure it works, start to finish, with no manual intervention, copy all the code blocks into a single layer_update.py script, or whatever you want to call it. Then you can schedule that script to run using your computer's task scheduler.
If you're working in an AGOL Notebook, you can schedule it to run from there.
- Josh Carlson
Kendall County GIS
