Latest Contributions by JustinNettleton1

Thank you, I will give this a try.  Justin ... View more

Thank you! Your suggestions got it going.     Justin  ... View more

I change to x  and  y and still nothing in the table updated.  Latitude and Longitude are the names of my geometry fields, but not sure that it matters.    Justin  ... View more

Here is how incorporated what you suggested and the error I am getting now.  # Updating units for call in current_units: if call in agol_units: #unit_feature = [f for f in ago_list if f.attributes['id'] == call][0] unit_dict = {"attributes": {"id":call[0], "Unit":call[1], "Status":call[2], "StatusCategory":call[3], "StatusRank":call[4], "OOSReason":call[5], "StatusChange":call[6], "Type":call[7], "Juridiction":call[8].upper(), "Division":call[9], "LastUpdate":call[10]}, "geometry": {"x": call[11], "y": call[12]}} #agoLayer.edit_attributes(updates=[unit_dict]) resp = agoLayer.edit_attributes(updates=[unit_dict]) print (resp) --------------------------------------------------------------------------- NameError Traceback (most recent call last) In [20]: Line 84: print (resp) NameError: name 'resp' is not defined ---------------------------------------------------------------------------   ... View more

Thanks, I will give this a try.  ... View more

Yep, that was that issue. Used someone elses code and accidently removed those lines when editing.  Fixed that and now getting cursor error:  Possibly not connected to the database correctly? --------------------------------------------------------------------------- ProgrammingError Traceback (most recent call last) In [7]: Line 72: ODBC_connect() In [7]: Line 28: records = cursor.fetchall() ProgrammingError: No results. Previous SQL was not a query.   @JustinNettleton1 wrote: I am working on python script that takes data straight from SQL server and update AGO feature service (Hosted table).   I keep getting the below syntax error on line 50 but don't understand why. I have made sure all the fields have the correct spelling and case.  Anyone have any ideas? Thanks,  --------------------------------------------------------------------------- SyntaxError Traceback (most recent call last) File C:\Program Files\ArcGIS\Pro\bin\Python\envs\arcgispro-py3\lib\ast.py, in parse: Line 50: return compile(source, filename, mode, flags, SyntaxError: invalid syntax (<string>, line 72) ---------------------------------------------------------------------------     import pyodbc import os from arcgis.gis import GIS #lists for calls used during the update process agol_calls = [] current_calls = [] call_numbers = [] # SQL Query being passed through the ODBC connection SQL_QUERY = """ select id,Unit,Status,Type,Juridiction,Division,LastUpdate from Reports_Database.dbo.Avail_Units_T order by Unit """ print ('Query Successful') def ODBC_connect(): ##SERVER = 'CADGIS.heartlandcad.net\CADGIS\SQLEXPRESS' # Server FQDN SERVER = 'CADGIS\SQLEXPRESS' # Server FQDN DATABASE = 'Reports_Database' # Database Name ## Connection string to pass in pyodbc connection request. Using Trusted_Connect=yes to have SQL Server Native Client ODBC driver use Windows Authentication of the account running the script ## Using the Windows Authentication of the account running the script allows for the use of a Managed Service Account to run the scheduled python script connectionString = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};Trusted_Connection=yes' conn = pyodbc.connect(connectionString) cursor = conn.cursor() cursor.execute(SQL_QUERY) records = cursor.fetchall() for r in records: record = [r.id,r.Unit,r.Status,r.Type,r.Juridiction,r.Division,r.LastUpdate] current_calls.append(record) call_numbers.append(r.id) print ('SQL Connection Successful') def Portal_push(): user = os.getenv('justin.nettleton') # System Environmental Variable Name for Username password = os.getenv('******') # System Environmental Variable Name for Password portal = os.getenv('https://www.arcgis.com') # System Environmental Variable Name for Portal URL gis = GIS(portal,user,password,use_gen_token=True) agol_item = gis.content.get('38aabc243aaf4f4ab5c8b9d5600ed173') # ArcGIS Online Item ID cadLayer = agol_item.layers[0] cadFSet = cadLayer.query(where = '1=1') cad_list = cadFSet.features # Retrieving the call numbers that are existing in the ArcGIS Online Feature Service for exsting_call in cad_list: agol_calls.append(exsting_call.attributes['id']) for call in agol_calls: if call not in call_numbers: call_feature = [f for f in cad_list if f.attributes['id'] == call][0] call_objid = call_feature.get_value('OBJECTID') cadLayer.edit_features(deletes=str(call_objid)) # Adding Calls for call in current_calls: if call[0] not in agol_calls: call_dict = {"attributes": {"id":call[0], "Unit":call[1], "Status":call[2], "Type":call[3], "Juridiction":call[4].upper(), "Division":call[5], "LastUpdate":call[6], cadLayer.edit_features(adds=[call_dict]) if __name__ == '__main__': ODBC_connect() Portal_push()       import pyodbc import os from arcgis.gis import GIS #lists for calls used during the update process agol_calls = [] current_calls = [] call_numbers = [] ##select id,Unit,Status,Type,Juridiction,Division,LastUpdate # SQL Query being passed through the ODBC connection SQL_QUERY = """ select id,Unit,Status,Type,Juridiction,Division,LastUpdate from Reports_Database.dbo.Avail_Units_T order by id """ print ('Query Successful') def ODBC_connect(): ##SERVER = 'CADGIS.heartlandcad.net\CADGIS\SQLEXPRESS' # Server FQDN SERVER = 'CADGIS\SQLEXPRESS' # Server FQDN DATABASE = 'Reports_Database' # Database Name ## Connection string to pass in pyodbc connection request. Using Trusted_Connect=yes to have SQL Server Native Client ODBC driver use Windows Authentication of the account running the script ## Using the Windows Authentication of the account running the script allows for the use of a Managed Service Account to run the scheduled python script connectionString = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};Trusted_Connection=yes' conn = pyodbc.connect(connectionString) cursor = conn.cursor() records = cursor.fetchall() for r in records: record = [r.id,r.Unit,r.Status,r.Type,r.Juridiction,r.Division,r.LastUpdate] current_calls.append(record) call_numbers.append(r.id) print ('SQL Connection Successful') def Portal_push(): user = os.getenv('justin.nettleton') # System Environmental Variable Name for Username password = os.getenv('******') # System Environmental Variable Name for Password portal = os.getenv('https://www.arcgis.com') # System Environmental Variable Name for Portal URL gis = GIS(portal,user,password,use_gen_token=True) agol_item = gis.content.get('38aabc243aaf4f4ab5c8b9d5600ed173') # ArcGIS Online Item ID cadLayer = agol_item.layers[0] cadFSet = cadLayer.query(where = '1=1') cad_list = cadFSet.features # Retrieving the call numbers that are existing in the ArcGIS Online Feature Service for exsting_call in cad_list: agol_calls.append(exsting_call.attributes['id']) for call in agol_calls: if call not in call_numbers: call_feature = [f for f in cad_list if f.attributes['id'] == call][0] call_objid = call_feature.get_value('OBJECTID') cadLayer.edit_features(deletes=str(call_objid)) # Adding Calls for call in current_calls: if call[0] not in agol_calls: call_dict = {"attributes": {"id":call[0], "Unit":call[1], "Status":call[2], "Type":call[3], "Juridiction":call[4].upper(), "Division":call[5], "LastUpdate":call[6]}} cadLayer.edit_features(adds=[call_dict]) if __name__ == '__main__': ODBC_connect() Portal_push()     ... View more

Thanks for this. This helps. It kind of led me to a knew issue though. I don't want the Closed events to show on the map. I can change the symbol to be blank, but when I click on a feature that is Open, it could have 4 other features under it that are closed and show up in the popup. So is there anyway to just show the date for the Open events on the map and in the popup?     I wish they we could just run arcade expressions in the filter tool.  ... View more

Thank you for these solutions. They have helped the performance.  ... View more