描述
ArcSDESQLExecute 类提供一种通过企业级地理数据库连接执行 SQL 语句的方法。
讨论
警告：
- 不应使用 ArcGIS 软件以外的任何其他软件更改企业级地理数据库系统表。如果直接使用 SQL 对这些系统表进行编辑，则可能发生损坏。
- 仅可通过版本化视图使用 SQL 对版本化数据进行编辑。
- 对于在关系数据库管理系统 (DBMS) 中使用 DBMS 数据类型和表格式实现的地理数据库，则可使用 DBMS 自带的 SQL 来处理该数据库中存储的信息。
- 通过 SQL 访问地理数据库中的信息允许外部应用程序访问地理数据库管理的表格数据。这些外部应用程序可以是在 ArcObjects 以外的环境中开发的非空间数据库应用程序或自定义空间应用程序。但请注意，通过 SQL 访问地理数据库会忽略地理数据库功能，如拓扑、网络、地形、其他类或工作空间扩展模块。
- 可使用触发器和存储过程等 DBMS 功能来维护某个地理数据库功能所需的表之间的关系。但是，如果对数据库执行 SQL 命令而不考虑此附加功能（如执行 INSERT 语句以向业务表添加记录），则会避开地理数据库功能并可能对地理数据库中数据之间的关系造成损坏。
- 在尝试访问或修改企业级地理数据库对象前，请阅读 DBMS 中有关对地理数据库对象使用 SQL 的所有企业级地理数据库文档。
语法
ArcSDESQLExecute ({server}, {instance}, {database}, {user}, {password})| 参数 | 说明 | 数据类型 |
server | 安装数据库所在的服务器的名称或有效连接文件的名称。 | String |
instance | 端口号。 | String |
database | 数据库名称。 | String |
user | 用户名。 | String |
password | 用户名对应的密码。 | String |
属性
| 属性 | 说明 | 数据类型 |
| transactionAutoCommit (可读写) | 自动提交间隔。此属性可用于修改完指定数量的要素后强制执行阶段性提交。 | Integer |
方法概述
| 方法 | 说明 |
| commitTransaction () | 只有在调用 commitTransaction 方法后，才能提交 DML 语句。 注：到企业级地理数据库的连接终止时，也可能发生提交（要了解每个 DBMS 对事务中出现断开连接的处理方式，请查看特定的 DBMS 文档）。 |
| execute (sql_statement) | 通过 ArcSDE 连接向数据库发送 SQL 语句。如果在事务的外面运行 execute，则执行完 SQL DML（INSERT、UPDATE、DELETE . . .）语句后将自动发生提交。 |
| rollbackTransaction () | 将所有 DML 操作回滚到上一次提交。 |
| startTransaction () | 要控制向数据库提交更改的时间，请先调用 startTransaction 方法，然后再调用 execute。此方法用于启动事务，并且只有在调用 commitTransaction 方法后才能提交 DML 语句。 |
方法
commitTransaction ()
execute (sql_statement)
| 参数 | 说明 | 数据类型 |
sql_statement | SQL 语句。 对于语句从表返回行的情况，execute 方法将返回一列列表；对于不返回行的语句，该方法将返回语句成功或失败的指示（True 表示成功；None 表示失败）。从单个行返回单个值的语句将以合适的类型（字符串、浮点型、浮点型）返回值。 | Variant |
rollbackTransaction ()
startTransaction ()
代码示例
查询每种犯罪类型的数量，然后返回含有犯罪类型和事件计数的列表。
import arcpy
# Use a connection file to create the connection
egdb = r'Database Connections\Connection to bedrock.sde'
egdb_conn = arcpy.ArcSDESQLExecute(egdb)
table_name = 'vtest.GDB.Crime'
field_name = 'CRIMETYPE'
sql = '''
SELECT {0}, COUNT({0}) AS f_count FROM {1}
GROUP BY {0}
ORDER BY f_count DESC
'''.format(field_name, table_name)
egdb_return = egdb_conn.execute(sql)
for i in egdb_return:
print('{}: {}'.format(*i))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)