处理尚未版本化的表时，使用结构化查询语言 (SQL) 查询数据库中的表要比使用某些地理处理工具进行查询更加容易。ArcSDESQLExecute 对象支持多数 SQL 语句的执行，并会返回这些语句的结果。对于语句从表返回行的情况，该对象将返回一列列表；对于不返回行的语句，该对象将返回语句成功或失败的指示（True 表示成功；None 表示失败）。从单个行返回单个值的语句将返回对应类型（字符串、浮点型等）的值。
| 属性 | |
|---|---|
transactionAutoCommit | 自动提交间隔。此属性可用于修改完指定数量的要素后强制执行阶段性提交。 |
| 方法 | |
|---|---|
commitTransaction() | 只有在调用 commitTransaction 方法后，才能提交 DML 语句。 |
execute(sql_statement) | 通过企业级地理数据库连接向数据库发送 SQL 语句。如果在事务的外面运行 execute，则执行完 SQL DML（INSERT、UPDATE、DELETE）语句后将自动发生提交。 |
rollbackTransaction() | 将所有 DML 操作回滚到上一次提交。 |
startTransaction() | 要控制向数据库提交更改的时间，请先调用 startTransaction 方法，然后再调用 execute。此方法用于启动事务，并且只有在调用 commitTransaction 方法后才能提交 DML 语句。 |
该 execute 方法通过企业级地理数据库连接向数据库发送 SQL 语句。如果在事务的外面运行 execute，则执行完 SQL DML（INSERT、UPDATE、DELETE）语句后将自动发生提交。
ArcSDESQLExecute 支持地理数据库事务模型。事务是企业级地理数据库连接的一种属性，用于绑定操作，以便记录或拒绝整组更改。例如，如果正在以特定顺序对一组宗地进行更新，则可使用事务来定义更改的开始和结束，以便将所有更改一起提交。如果无法将一组更改成功插入，则会拒绝整个事务。所有事务会在用户断开连接时结束。ArcSDESQLExecute 使用所提供的企业级地理数据库 API 函数来启动、提交及回滚事务。
要控制向数据库提交更改的时间，请先调用 startTransaction 方法，然后再调用 execute。此方法用于启动事务，并且只有在调用 commitTransaction 方法后才能提交 DML 语句。到企业级地理数据库的连接终止时，也可能发生提交（要了解每个 DBMS 对事务中出现断开连接的处理方式，请查看特定的 DBMS 文档）。在事务中，还可将任何 DML 操作回滚到上一次提交。
自动提交间隔属性 transactionAutoCommit 可用。此属性可用于修改完指定数量的要素后强制执行阶段性提交。
有关编写 SQL 语句的帮助，请参阅 DBMS SQL 参考指南。
示例
执行一列 SQL 语句
import sys
import arcpy
try:
# Make data path relative
arcpy.env.workspace = sys.path[0]
# Two ways to create the object, which also creates the
# connection to the enterprise geodatabase.
# Using the first method, pass a set of strings containing
# the connection properties:
# <serverName>, <portNumber>, <version>, <userName>, <password>
# arcpy.ArcSDESQLExecute("gpserver3","5151","#","toolbox","toolbox")
# Using the second method pass the path to a valid connection file
egdb_conn = arcpy.ArcSDESQLExecute(r"data\Connection to GPSERVER3.sde")
# Get the SQL statements, separated by ; from a text string.
sql_statement = arcpy.GetParameterAsText(0)
sql_statement_list = sql_statement.split(";")
print("+++++++++++++++++++++++++++++++++++++++++++++\n")
# For each SQL statement passed in, execute it.
for sql in sql_statement_list:
print("Execute SQL Statement: {0}".format(sql))
try:
# Pass the SQL statement to the database.
egdb_return = egdb_conn.execute(sql)
except Exception as err:
print(err)
egdb_return = False
# If the return value is a list (a list of lists), display
# each list as a row from the table being queried.
if isinstance(egdb_return, list):
print("Number of rows returned by query: {0} rows".format(
len(egdb_return)))
for row in egdb_return:
print(row)
print("+++++++++++++++++++++++++++++++++++++++++++++\n")
else:
# If the return value was not a list, the statement was
# most likely a DDL statement. Check its status.
if egdb_return == True:
print("SQL statement: {0} ran successfully.".format(sql))
else:
print("SQL statement: {0} FAILED.".format(sql))
print("+++++++++++++++++++++++++++++++++++++++++++++\n")
except Exception as err:
print(err)
使用事务进行条件更新
# WARNING - DO NOT USE ON VERSIONED TABLES OR FEATURE CLASSES.
# DO NOT USE ON ANY enterprise geodatabase SYSTEM TABLES.
# DOING SO MAY RESULT IN DATA CORRUPTION.
import sys
import arcpy
try:
# Make data path relative (not relevant unless data is moved
# here and paths modified)
arcpy.env.workspace = sys.path[0]
# Column name:value that should be in the record.
sql_values = {"STREET_NAM": "'EUREKA'"}
# Value that is incorrect if found in the above column.
bad_val = "'EREKA'"
#List of tables to look in for the bad value.
tables = ["streetaddresses_blkA", "streetaddresses_blkB",
"streetaddresses_blkC"]
# Two ways to create the object, which also creates the connection
# to the enterprise geodatabase.
# Using the first method, pass a set of strings containing the
# connection properties:
# <serverName>, <portNumber>, <version>, <userName>, <password>
egdb_conn = arcpy.ArcSDESQLExecute("gpserver3", "5151", "#",
"toolbox", "toolbox")
# Using the second method pass the path to a valid enterprise geodatabase connection file
# arcpy.ArcSDESQLExecute("data\Connection to GPSERVER3.sde")
for tbl in tables:
print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
for col, val in list(sql_values.items()):
print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
# Check for the incorrect value in the column for the
# specific rows. If the table contains the incorrect value,
# correct it using the update SQL statement.
print("Analyzing table {0} for bad data: "
"Column:{1} Value: {2}".format(tbl, col, bad_val))
try:
sql = "select OBJECTID,{0} from {1} where {0} = {2}".format(
col, tbl, bad_val)
print("Attempt to execute SQL Statement: {0}".format(sql))
egdb_return = egdb_conn.execute(sql)
except Exception as err:
print(err)
egdb_return = False
if isinstance(egdb_return, list):
if len(egdb_return) > 0:
print("Identified {0} rows with incorrect data. Starting "
"transaction for update.".format(len(egdb_return)))
# Start the transaction
egdb_conn.startTransaction()
print("Transaction started...")
# Perform the update
try:
sql = "update {0} set {1}={2} where {1} = {3}".format(
tbl, col, val, bad_val)
print("Changing bad value: {0} to the good value: "
"{1} using update statement:\n {2}".format(
bad_val, val, sql))
egdb_return = egdb_conn.execute(sql)
except Exception as err:
print(err)
egdb_return = False
# If the update completed successfully, commit the
# changes. If not, rollback.
if egdb_return == True:
print("Update statement: \n"
"{0} ran successfully.".format(sql))
# Commit the changes
egdb_conn.commitTransaction()
print("Committed Transaction")
# List the changes.
try:
print("Displaying updated rows for "
"visual inspection.")
sql = "select OBJECTID" + \
",{0} from {1} where {0} = {2}".format(
col, tbl, val)
print("Executing SQL Statement: \n{0}".format(sql))
egdb_return = egdb_conn.execute(sql)
except Exception as err:
print(err)
egdb_return = False
if isinstance(egdb_return, list):
print("{0} rows".format(len(egdb_return)))
for row in egdb_return:
print(row)
print("++++++++++++++++++++++++++++++++++++++++\n")
else:
if egdb_return == True:
print("SQL statement: \n{0}\n"
"ran successfully.".format(sql))
else:
print("SQL statement: \n{0}\n"
"FAILED.".format(sql))
print("++++++++++++++++++++++++++++++++++++++++\n")
print("++++++++++++++++++++++++++++++++++++++++\n")
else:
print("SQL statement: \n{0}\nFAILED. "
"Rolling back all changes.".format(sql))
# Rollback changes
egdb_conn.rollbackTransaction()
print("Rolled back any changes.")
print("++++++++++++++++++++++++++++++++++++++++\n")
else:
print "No records required updating."
# Disconnect and exit
del egdb_conn
print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
except Exception as err:
print(err)