topic Re: Can't figure out syntax error for append Call list in Python Questions

Can't figure out syntax error for append Call list

JustinNettleton1 — Thu, 16 May 2024 23:46:28 GMT

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()

Re: Can't figure out syntax error for append Call list

MobiusSnake — Fri, 17 May 2024 00:55:19 GMT

Are you missing some lines? Your call_dict looks like it's missing its last line (or multiple lines), it doesn't have a closing bracket.

Re: Can't figure out syntax error for append Call list

JustinNettleton1 — Fri, 17 May 2024 01:57:01 GMT

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 = [] ##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()

Re: Can't figure out syntax error for append Call list

JakeSkinner — Wed, 22 May 2024 11:09:50 GMT

@JustinNettleton1 do you have ArcGIS Pro? You may be able to simplify this with a truncate/append.

https://community.esri.com/t5/arcgis-online-documents/overwrite-arcgis-online-feature-service-using/ta-p/904457

Re: Can't figure out syntax error for append Call list

JustinNettleton1 — Wed, 22 May 2024 13:48:27 GMT

Thanks, I will give this a try.