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