def prep_4accounts():
# Build Results Table
item_check = account_destination
arcpy.env.workspace = db_connection
if arcpy.Exists(item_check):
try:
clear_results_SQL = ('''
truncate table {0}
'''.format(account_destination))
arcpy.ArcSDESQLExecute(db_connection).execute(clear_results_SQL)
except Exception as error_check_for_existance:
print ("Status: Failure!")
print(error_check_for_existance.args[0])
else:
create_results_SQL = ('''
CREATE TABLE {0}(
[OBJECTID] [INT] IDENTITY(1,1)
, [address] [varchar](254)
, [streetnum] [varchar](20)
, [city] [varchar](50)
, [zip] [varchar](20)
, [pId] [varchar](20)
, [serviceType] [varchar](10)
, [PGE_status] [varchar](1000)
, [SysChangeDate] [datetime2](7)
)
'''.format(account_destination))
try:
arcpy.ArcSDESQLExecute(db_connection).execute(create_results_SQL)
except Exception as error_check:
print(error_check.args[0])
def createNewProc_Prop(self, procedureObj):
row = None
insert_cs = None
sdeConn = None
try:
# Gets the full table path by calling the sdeProperties function getTPath('Tablereferencename')
insert_cs = arcpy.InsertCursor(self.sp.getTPath('t_proc_prop'))
row = insert_cs.newRow()
row.setValue("PROCEDURE_ID", str(procedureObj.procedure_id))
row.setValue("PROPERTY_ID", str(procedureObj.property_id))
insert_cs.insertRow(row)
# Getting New ID
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) OBJECTID from dbo.Proc_prop where PROCEDURE_ID = '" + str(
procedureObj.procedure_id) + "' and PROPERTY_ID = '" + str(
procedureObj.property_id) + "'"
newid = sdeConn.execute(sql)
return newid
except:
return 0
finally:
if row:
del row
if insert_cs:
del insert_cs
if sdeConn:
del sdeConn
def checkObservation(self, observationObj):
row = None
rows = None
sdeConn = None
try:
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) OBJECTID,NUMERIC_VALUE from dbo.Observation where PROPERTY='" + str(
observationObj.property_ref.property_id
) + "' and OFFERING='" + str(
observationObj.offering_id
) + "' and PROCEDURE_='" + str(
observationObj.procedure_ref.procedure_id
) + "' and FEATURE='" + str(
observationObj.feature_ref.featureID
) + "' and TIME_STAMP='" + observationObj.time_stamp + "' and TIME_STAMP_BEGIN='" + observationObj.time_stamp_begin + "'"
rows = sdeConn.execute(sql)
tempObj = Observation.Observation()
if isinstance(rows, list):
for row in rows:
tempObj.objectID = row[0] if (row[0] != None) else -1
tempObj.numeric_value = row[1] if (row[1] != None) else 0
else:
tempObj.objectID = -1
tempObj.numeric_value = 0
return tempObj
except:
return None
finally:
if row:
del row
if rows:
del rows
if sdeConn:
del sdeConn
def getOffering(self, offeringObj):
row = None
rows = None
sdeConn = None
try:
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) * from dbo.Offering where OFFERING_NAME='" + str(
offeringObj.name) + "'"
rows = sdeConn.execute(sql)
result = None
if isinstance(rows, list):
for row in rows:
result = row[0] if (row[0] != None) else None
return result
except:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(
sys.exc_info()[1])
print pymsg
return -1
finally:
if row:
del row
if rows:
del rows
if sdeConn:
del sdeConn
def checkFoi_Off(self, featureObj):
row = None
rows = None
sdeConn = None
try:
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) * from dbo.Foi_Off where FOI_ID = '" + str(
featureObj.featureID) + "' AND OFFERING_ID ='" + str(
featureObj.offering_id) + "'"
rows = sdeConn.execute(sql)
result = None
if isinstance(rows, list):
for row in rows:
result = row[0] if (row[0] != None) else None
return result
except:
return -1
finally:
if row:
del row
if rows:
del rows
if sdeConn:
del sdeConn
def checkProp_Proc(self, procedureObj):
row = None
rows = None
sdeConn = None
try:
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) * from dbo.Proc_prop where PROPERTY_ID = '" + str(
procedureObj.property_id) + "' AND PROCEDURE_ID='" + str(
procedureObj.procedure_id) + "'"
rows = sdeConn.execute(sql)
result = None
if isinstance(rows, list):
for row in rows:
result = row[0] if (row[0] != None) else None
return result
except:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(
sys.exc_info()[1])
print pymsg
return -1
finally:
if row:
del row
if rows:
del rows
if sdeConn:
del sdeConn
def create_dates_js(self, creds):
self.logger.info('creating dates.json')
start = timeit.default_timer()
sde = self.make_absolute(['connections', creds['sde_connection_path']])
sql = '''SELECT MAX(CAST(crash_date as varchar)) as max_date, MIN(CAST(crash_date as varchar)) as min_date
FROM [DDACTS].[DDACTSadmin].[CRASHLOCATION]'''
try:
c = arcpy.ArcSDESQLExecute(sde)
max_min = c.execute(sql)
max_min = max_min[0]
except Exception as e:
self.logger.info(e)
raise e
finally:
if c:
del c
with open(self.make_absolute(['pickup', 'dates.json']),
'w') as outfile:
template = '{{"minDate": "{}", "maxDate": "{}"}}'.format(
max_min[1].split(' ')[0], max_min[0].split(' ')[0])
outfile.write(template)
self.logger.info('processing time: {}'.format(timeit.default_timer() -
start))
def truncate_tables(self, creds):
sde = self.make_absolute(['connections', creds['sde_connection_path']])
with open(self.make_absolute(['data', 'sql', 'truncate.sql']),
'r') as f:
sql = f.read()
self.logger.info('truncating tabular tables')
try:
c = arcpy.ArcSDESQLExecute(sde)
c.execute(sql)
except Exception as e:
raise e
finally:
if c is not None:
del c
arcpy.env.overwriteOutput = True
arcpy.env.workspace = sde
self.logger.info('truncating spatial tables')
try:
arcpy.TruncateTable_management('CrashLocation')
except arcpy.ExecuteError as e:
self.logger.info(e)
def ARCGIS_add_mapnums_schedule(new_mapindexsde, new_name):
conn = psycopg2.connect(dbname=u"mmanageV0.10",
user=u"postgres",
password=u"Lantucx2018",
host=u"localhost",
port=u"5432")
# Postgres数据库连接
SQL = "SELECT name,mapnums,schedule FROM taskpackages_taskpackage"
cur = conn.cursor()
cur.execute(SQL)
# arcgis数据库连接
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
workspace = os.path.join(SCRIPT_DIR, new_mapindexsde)
sde_conn = arcpy.ArcSDESQLExecute(workspace)
while True:
data = cur.fetchone()
if data:
"""插入数据"""
new_jtb = new_mapindexsde + u".GBmaprange" + new_name
sql = "update {0} set mapnum = '{1}',schedule='{2}' where {3} = '{4}'".format(
new_jtb, data[1], data[2], "taskpackage_name", data[0])
try:
print sql
sde_conn.execute(sql)
except Exception as e:
print str(e).encode('utf8')
else:
conn.close()
break
sde_conn.commitTransaction()
del sde_conn
print "arcgis图号进度添加完成"
def obtain_dbase_connection(db_connection, target_db, target_db_type,
fc_update_owner):
##print ("Entering Obtain Database Connection----")
# Define Global Variable
global conn_string
# Prep variables for SQL Query
target_db_sql = "'" + target_db + "'"
db_type_sql = "'" + target_db_type + "'"
fc_update_owner_sql = "'" + fc_update_owner + "'"
# Pull from Dbase exact connection string needed.
try:
db_connection_stringSQL = '''select * from admingts.SDE_Connections where SourceDB = {0} and SourceDB_Type = {1} and Data_Owner = {2}'''.format(
target_db_sql, db_type_sql, fc_update_owner_sql)
db_connection_stringReturn = arcpy.ArcSDESQLExecute(
db_connection).execute(db_connection_stringSQL)
for row in db_connection_stringReturn:
conn_string = row[4]
print(" Database connection = {0} \n".format(conn_string))
except:
print(" No Connection for: {0}.".format(fc_update_owner))
##print ("----Leaving Obtain Database Connection")
return conn_string
def step_counter(db_connection, current_db, db_type, source_LayerFullName):
# Define Global Variable
global step_count
# Prep variables for SQL Query
print "Entering Step Counter----"
current_db_sql = "'{0}'".format(current_db)
print current_db
db_type_sql = "'{0}'".format(db_type)
print db_type
source_LayerFullName_sql = "'{0}'".format(source_LayerFullName)
print source_LayerFullName
# Check Dbase for publication step count
check_step_count_sql = """SELECT LayerFullName, COUNT({0}) FROM {1} WHERE SourceDB = {2} and SourceDB_Type = {3} and LayerFullName = {4} group by LayerFullName"""\
.format('Process_Step', 'ADMINGTS.Layer_Sync_Control', current_db_sql, db_type_sql, source_LayerFullName_sql)
check_step_count_return = arcpy.ArcSDESQLExecute(db_connection).execute(
check_step_count_sql)
try:
for row in check_step_count_return:
step_count = row[1]
except:
step_count = 0
print "Leaving Step Counter----"
return (step_count)
def delete_locks(fc_owner, fc_name, db_owner):
"""delete locks for the specified table
fc_owner (string): owner of feature class
fc_name (string): name of feature class (e.g. LandOwnership)
db_owner (string): path to connection file with owner creds
"""
if not Path(db_owner).exists():
print(f'{db_owner} does not exist')
return
db_connect = arcpy.ArcSDESQLExecute(db_owner)
sql = dedent(f'''SELECT * FROM sde.SDE_process_information
WHERE SDE_ID IN(SELECT SDE_ID FROM sde.SDE_table_locks
WHERE registration_id = (SELECT registration_id FROM sde.SDE_table_registry
WHERE UPPER(table_name) = UPPER('{fc_name}') AND UPPER(owner) = UPPER('{fc_owner}')));
''')
db_return = db_connect.execute(sql)
if db_return is True:
print('no locks to delete')
return
for user in db_return:
print(f'deleted lock {user[0]}')
arcpy.DisconnectUser(db_owner, user[0])
def createNewOffering(self, offeringObj):
row = None
insert_cs = None
sdeConn = None
try:
# Gets the full table path by calling the sdeProperties function getTPath('Tablereferencename')
insert_cs = arcpy.InsertCursor(self.sp.getTPath('t_offering'))
row = insert_cs.newRow()
row.setValue("OFFERING_NAME", str(offeringObj.name))
row.setValue("PROCEDURE_", str(offeringObj.procedure_id))
row.setValue("SERVICE", str(offeringObj.service_id))
insert_cs.insertRow(row)
# Getting New ID
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) OBJECTID from dbo.Offering where OFFERING_NAME = '" + str(
offeringObj.name) + "' and PROCEDURE_ = '" + str(
offeringObj.procedure_id) + "' and SERVICE ='" + str(
offeringObj.service_id) + "'"
newid = sdeConn.execute(sql)
return newid
except:
return None
finally:
if row:
del row
if insert_cs:
del insert_cs
if sdeConn:
del sdeConn
def createNewFoi_Off(self, featureObj):
row = None
insert_cs = None
sdeConn = None
try:
# Gets the full table path by calling the sdeProperties function getTPath('Tablereferencename')
insert_cs = arcpy.InsertCursor(self.sp.getTPath('t_foi_off'))
row = insert_cs.newRow()
row.setValue("FOI_ID", str(featureObj.featureID))
row.setValue("OFFERING_ID", str(featureObj.offering_id))
insert_cs.insertRow(row)
# Getting New ID
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) OBJECTID from dbo.Foi_Off where FOI_ID = '" + str(
featureObj.featureID) + "' and OFFERING_ID = '" + str(
featureObj.offering_id) + "'"
newid = sdeConn.execute(sql)
return newid
except:
return None
finally:
if row:
del row
if insert_cs:
del insert_cs
if sdeConn:
del sdeConn
def set_current_database(db_connection):
##print ("Entering Set Current Database----")
global current_db
check_db_sql = '''SELECT DB_NAME() AS [Database]'''
check_db_return = arcpy.ArcSDESQLExecute(db_connection).execute(
check_db_sql)
current_db = check_db_return
print("Current Database: " + current_db)
##print ("----Leaving Set Current Database")
return current_db
def db_connection(sde_name):
"""Funkce pro připojení se k databázi dle SDE"""
sde = 'Database Connections\Connection to ' + sde_name + '.sde'
try:
conn = arcpy.ArcSDESQLExecute(sde)
arcpy.AddMessage('\nDatabazove pripojeni ' + sde_name +
' probehlo uspesne')
except:
raise Exception(
'\nDatabazove pripojeni ' + sde_name + ' neexistuje.'
'\nPripojeni je treba vytvorit pomoci "Add Database Connection".')
return (conn)
def createNewObservation(self, observationObj):
row = None
insert_cs = None
sdeConn = None
try:
insert_cs = arcpy.InsertCursor(self.sp.getTPath('t_observation'))
row = insert_cs.newRow()
row.setValue("UNIT_OF_MEASURE",
str(observationObj.unit_of_measure))
row.setValue("TEXT_VALUE", "")
row.setValue("NUMERIC_VALUE", observationObj.numeric_value)
# A SQL server trigger is used to update these fields
#newObservation[0][u'attributes']['TIME_STAMP'] =
#newObservation[0][u'attributes']['TIME_STAMP_BEGIN'] =
row.setValue("TIME_STAMP_TEXT", observationObj.time_stamp)
row.setValue("TIME_STAMP_BEGIN_TEXT",
observationObj.time_stamp_begin)
row.setValue("PROPERTY", observationObj.property_ref.property_id)
row.setValue("PROCEDURE_",
observationObj.procedure_ref.procedure_id)
row.setValue("FEATURE", observationObj.feature_ref.featureID)
row.setValue("OFFERING", observationObj.offering_id)
insert_cs.insertRow(row)
# Getting New obejct ID
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) OBJECTID from dbo.Observation where PROCEDURE_ = '" + str(
observationObj.procedure_ref.procedure_id
) + "' AND FEATURE = '" + str(
observationObj.feature_ref.featureID
) + "' AND OFFERING='" + str(
observationObj.offering_id
) + "' AND PROPERTY='" + str(
observationObj.property_ref.property_id
) + "' and TIME_STAMP_TEXT='" + observationObj.time_stamp + "' and TIME_STAMP_BEGIN_TEXT='" + observationObj.time_stamp_begin + "'"
newid = sdeConn.execute(sql)
return newid
except:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(
sys.exc_info()[1])
print pymsg
return None
finally:
if row:
del row
if insert_cs:
del insert_cs
if sdeConn:
del sdeConn
def check_db():
global source_db
global source_db_type
global db_owner
global conn_string
print('Pulling connections')
try:
# Check DB status
print('Connecting to SQL.')
pull_relevant_dbs_SQL = (
'''select * from admingts.SDE_Connections where Data_Owner = 'DBO' '''
)
pull_relevant_dbs_return = arcpy.ArcSDESQLExecute(
db_connection).execute(pull_relevant_dbs_SQL)
for row in pull_relevant_dbs_return:
source_db = row[1]
source_db_type = row[2]
db_owner = row[3]
conn_string = row[4]
retry = 0
print('Checking database status.')
force_online(retry)
except Exception as error:
hard_fail += 1
print('Trouble connecting to SQL.')
if hard_fail == 1:
mail_body = (
'Unable to check database online status at this time.\nError: {0}'
.format(error))
mail_subject = ('Geodatabase Online Check Failed')
mail_priority = '1'
else:
if hard_fail % 5 == 0:
mail_body = (
'Unable to check database online status at this time. This is attempt #{1}.\nError: {0}'
.format(error, hard_fail))
mail_subject = ('Geodatabase Online Check Failed {0} Times'.
format(hard_fail))
mail_priority = '1'
send_message(mail_body, mail_subject, mail_priority)
time.sleep(900)
check_db()
return
def createNewProcedure(self, procedureObj):
row = None
insert_cs = None
sdeConn = None
array_container = arcpy.Array()
try:
point_object = arcpy.Point()
point_object.X = procedureObj.x
point_object.Y = procedureObj.y
print "" + str(point_object.X) + " " + str(point_object.Y)
array_container.add(point_object) #put first point in container
desc = arcpy.Describe(self.sp.getTPath('t_procedure'))
shapefieldname = desc.ShapeFieldName
del desc
print shapefieldname
insert_cs = arcpy.InsertCursor(self.sp.getTPath('t_procedure'))
row = insert_cs.newRow()
row.setValue("LONG_NAME", str(procedureObj.longname))
row.setValue("UNIQUE_ID", str(procedureObj.unique_id))
row.setValue("INTENDED_APPLICATION", "")
row.setValue("CONTACT", None)
row.setValue(shapefieldname, array_container) # add multipoint
insert_cs.insertRow(row)
# Getting New ID
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) OBJECTID from dbo.Procedure_ where UNIQUE_ID = '" + str(
procedureObj.unique_id) + "' and LONG_NAME = '" + str(
procedureObj.longname) + "'"
newid = sdeConn.execute(sql)
return newid
except:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(
sys.exc_info()[1])
print pymsg
return None
finally:
if row:
del row
if insert_cs:
del insert_cs
if sdeConn:
del sdeConn
if array_container:
del array_container
def city_list():
city_list_SQL = '''
select
distinct(city)
, count(*) as points
from {0} where city <> ''
group by city
order by city asc
'''.format (data_destination)
city_return = arcpy.ArcSDESQLExecute(db_connection).execute(city_list_SQL)
global city_listing
city_listing = []
for city in city_return:
target = city[0]
city_listing.append(target)
def returnData(sql):
try:
GDBconnSQL = arcpy.ArcSDESQLExecute(appConfig.GDBconn)
results = GDBconnSQL.execute(sql)
return results
except:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
pymsg = "PYTHON ERRORS:\nTraceback Info:\n" + tbinfo + "\nError Info:\n " + \
str(sys.exc_type)+ ": " + str(sys.exc_value) + "\n"
print pymsg
arcpy.AddError(pymsg)
raise
pass
def changSDEmapindex(mapnumlist, mapindexsde):
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
workspace = os.path.join(SCRIPT_DIR, mapindexsde)
sde_conn = arcpy.ArcSDESQLExecute(workspace)
tbl = mapindexsde + u".GBmaprange"
col = u"flag"
val = u"1"
for mapnum in mapnumlist.split(u","):
col1 = u"new_jbmapn = '%s'" % mapnum
# sql = "update mapindex20181204150827.sde.GBmaprange set flag="1 where {3} = {4}".format(tbl, col, val, col1,col1_val)
sql = "update {0} set status='1' where {1}".format(tbl, col1)
# sql="select objectid from mapindex20181204150827.sde.GBmaprange"
sde_conn.execute(sql)
sde_conn.commitTransaction()
del sde_conn
return True
def check_FC_count(db_connection, db_table):
##print ('Entering Database Check Feature Class Count----')
# Define global variable to carry through application.
global mx_count
# Poll table for number of feature classes from admingts.view_layer_table_history
db_table_sql = db_table
fc_type_sql = "'" + 'Feature Class' + "'"
check_FC_count_sql = (
'''SELECT count (type) as Feature_Classes FROM {0} where [Type] = {1}'''
.format(db_table_sql, fc_type_sql))
check_FC_count_return = int(
arcpy.ArcSDESQLExecute(db_connection).execute(check_FC_count_sql))
mx_count = check_FC_count_return
print('Processing {0} feature classes.'.format(mx_count))
##print ('----Exiting Database Check Feature Class Count')
return mx_count
def create_points_json(self, creds):
self.logger.info('creating new points.json')
start = timeit.default_timer()
sde = self.make_absolute(['connections', creds['sde_connection_path']])
pattern = re.compile(r'\s+')
points = {'points': []}
sql = 'SELECT [OBJECTID],[Shape].STX as x,[Shape].STY as y FROM [DDACTS].[DDACTSadmin].[CRASHLOCATION]'
try:
c = arcpy.ArcSDESQLExecute(sde)
result = c.execute(sql)
except Exception as e:
self.logger.info(e)
raise e
finally:
if c:
del c
def append_point(crash):
id, x, y = crash
if x is None or y is None:
return
x = int(x)
y = int(y)
points['points'].append([id, x, y])
with open(self.make_absolute(['pickup', 'points.json']),
'w') as outfile:
list(map(append_point, result))
content = re.sub(pattern, '', json.dumps(points))
outfile.write(content)
end = timeit.default_timer()
self.logger.info('processing time: {}'.format(end - start))
def createNewFeature(self, featureObj):
row = None
insert_cs = None
sdeConn = None
try:
point_object = arcpy.Point()
point_object.X = featureObj.x
point_object.Y = featureObj.y
desc = arcpy.Describe(self.sp.getTPath('t_feature'))
shapefieldname = desc.ShapeFieldName
del desc
insert_cs = arcpy.InsertCursor(self.sp.getTPath('t_feature'))
row = insert_cs.newRow()
row.setValue("NAME", str(featureObj.name))
row.setValue("DESCRIPTION", str(featureObj.description))
row.setValue("ID_VALUE", str(featureObj.id_value))
row.setValue("CODE_SPACE", str(featureObj.code_space))
row.setValue("SAMPLED_FEATURE_URL",
str(featureObj.sampled_feature_url))
row.setValue(shapefieldname, point_object) # add point
insert_cs.insertRow(row)
# Getting New ID
sdeConn = arcpy.ArcSDESQLExecute(self.sp.getFullPath())
sql = "select top(1) OBJECTID from dbo.Feature where NAME = '" + str(
featureObj.name) + "'"
newid = sdeConn.execute(sql)
return newid
except:
return None
finally:
if row:
del row
if insert_cs:
del insert_cs
if sdeConn:
del sdeConn
def report_vicmap_count(vm, output_report):
with gis.XLSX(output_report) as report:
counts_ws = report.add_worksheet('VicMap Counts')
counts_ws.set_column(0,0,60.0)
report.append_row(worksheet_name='VicMap Counts',
row_data=['Layer Name', 'Count'],
format_name='header')
conn = arcpy.ArcSDESQLExecute(vm.sde)
for dirpath, dirnames, files in arcpy.da.Walk(workspace=vm.sde,
followlinks=True,
datatype=['Table', 'FeatureClass']):
for f in sorted(files):
sql = 'select count(*) from {table}'.format(table=f)
count = conn.execute(sql)
logging.info('{f}: {c}'.format(f=f, c=count))
report.append_row(worksheet_name='VicMap Counts',
row_data=[f, count])
return output_report
def remove_dupes():
# Removes duplicate accounts from the database. Not sure if each address has a unique account number, etc.
dupe_sql = ('''
WITH cte AS (
SELECT
piD
,ROW_NUMBER() OVER (
PARTITION BY
piD
ORDER BY
piD
) row_num
FROM
{0}
)
DELETE FROM cte
WHERE row_num > 1
''').format(account_destination)
try:
arcpy.ArcSDESQLExecute(db_connection).execute(dupe_sql)
except Exception as error_check:
print(error_check.args[0])
def execute_immediate(sde, sql):
try:
sde_conn = arcpy.ArcSDESQLExecute(sde)
except:
print arcpy.GetMessages()
raise
try:
sde_return = sde_conn.execute(sql)
except:
print "sql fail " + sql
raise
del sde_conn
return sde_return
def execute_sql(statement, database_path, **kwargs):
"""Execute SQL statement via ArcSDE's SQL execution interface.
Only works if database_path resolves to an actual SQL database.
Args:
statement (str): SQL statement to execute.
database_path (str): Path of the database to execute statement in.
**kwargs: Arbitrary keyword arguments. See below.
Keyword Args:
log_level (str): Level to log the function at. Default is 'info'.
Returns:
object: Return value from the SQL statement's execution. Likely return types:
bool: True for successful execution of statement with no return value or
retured rows. False if failure.
list: A List of lists representing returned rows.
object: A single return value.
Raises:
AttributeError: If statement SQL syntax is incorrect.
"""
log = leveled_logger(LOG, kwargs.setdefault('log_level', 'info'))
log("Start: Execute SQL statement.")
conn = arcpy.ArcSDESQLExecute(server=database_path)
try:
result = conn.execute(statement)
except AttributeError:
LOG.exception("Incorrect SQL syntax.")
raise
finally:
# Yeah, what can you do?
del conn
log("End: Execute.")
return result
def _load_sde_compkeys(self):
if hasattr(self._config,"sdeconnection") and hasattr(self._config,"tablecsvlookup"):
sde = arcpy.ArcSDESQLExecute(self._config.sdeconnection)
self.log("Using sde connection: %s" % sde)
try:
for k,v in self._config.tablecsvlookup.items():
self.log("Processing:{0}|{1}".format(k,v))
if not hasattr(self._config,"selectfields"):
raise Exception("Expected configuration missing: selectfields")
sql = "select {0} from {1}".format(self._config.selectfields, v)
self.log("Executing: %s" % sql)
sde_return = sde.execute(sql)
# debug
if isinstance(sde_return,list):
if len(sde_return) > 0:
loaded_comp_keys = [ck[0] for ck in sde_return]
setattr(self,v,loaded_comp_keys)
except Exception as e:
self.errorlog(e.message)
finally:
del sde
else:
raise Exception("Expected configuration missing: sdeconnection, tablecsvlookup")
