Can't figure out syntax error for append Call list

399
4
Jump to solution
05-16-2024 04:46 PM
JustinNettleton1
New Contributor III

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

 

 

Tags (2)
0 Kudos
1 Solution

Accepted Solutions
JakeSkinner
Esri Esteemed Contributor
0 Kudos
4 Replies
MobiusSnake
MVP

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.

0 Kudos
JustinNettleton1
New Contributor III

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

 

 

0 Kudos
JakeSkinner
Esri Esteemed Contributor

@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/...

0 Kudos
JustinNettleton1
New Contributor III

Thanks, I will give this a try. 

0 Kudos