ArcSDESQLExecute

摘要

ArcSDESQLExecute 类提供一种通过企业级地理数据库连接执行 SQL 语句的方法。

说明

警告:

• 请勿使用 SQL 更改企业级地理数据库系统表。如果直接使用 SQL 对这些系统表进行编辑，则可能发生损坏。
• 仅可通过版本化视图使用 SQL 对传统版本化数据进行编辑。不要使用 SQL 编辑分支版本化数据。
• 对于在关系数据库管理系统 (DBMS) 中使用 DBMS 数据类型和表格式实现的地理数据库，您可以使用 DBMS 自带的 SQL 来处理该数据库中存储的用户定义表。
• 通过 SQL 访问地理数据库中的信息允许外部应用程序访问地理数据库管理的表格数据。这些外部应用程序可以是在 ArcObjects 以外的环境中开发的非空间数据库应用程序或自定义空间应用程序。但是，请注意，对地理数据库的 SQL 访问会忽略地理数据库功能（如拓扑、网络、地形或其他类或工作空间扩展模块），并忽略用于维护某个地理数据库功能所需的表之间关系的触发器和存储过程。避开地理数据库功能可能会破坏地理数据库中数据之间的关系。
• 在尝试访问或修改企业级地理数据库对象前，请阅读 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))

执行一列 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)