ArcGIS Help 10.2 - ArcSDESQLExecute (arcpy)

摘要

ArcSDESQLExecute 类提供一种通过 ArcSDE 连接执行 SQL 语句的方法。

讨论

警告:

不应使用 ArcGIS 软件以外的任何其他软件更改 ArcSDE 和 GDB 系统表。如果直接使用 SQL 对这些系统表进行编辑，则可能发生损坏。
仅可通过版本化视图使用 SQL 对版本化数据进行编辑。
对于在关系数据库管理系统 (DBMS) 中使用 DBMS 数据类型和表格式实现的地理数据库，则可使用 DBMS 自带的 SQL 来处理该数据库中存储的信息。
通过 SQL 访问地理数据库中的信息允许外部应用程序访问地理数据库管理的表格数据。这些外部应用程序可以是在 ArcObjects 以外的环境中开发的非空间数据库应用程序或自定义空间应用程序。但请注意，通过 SQL 访问地理数据库会忽略地理数据库功能，如拓扑、网络、地形、其他类或工作空间扩展模块。
可使用触发器和存储过程等 DBMS 功能来维护某个地理数据库功能所需的表之间的关系。但是，如果对数据库执行 SQL 命令而不考虑此附加功能（如执行 INSERT 语句以向业务表添加记录），则会避开地理数据库功能并可能对地理数据库中数据之间的关系造成损坏。
尝试访问或修改任何 ArcSDE 或 GDB 对象前，请先阅读有关对 DBMS 中的 ArcSDE 或 GDB 对象使用 SQL 的所有 ArcSDE 和地理数据库文档。

语法

ArcSDESQLExecute ({server}, {instance}, {database}, {user}, {password})

参数	说明	数据类型
server	Name of the server on which the database is installed or a valid connection file.	String
instance	The port number.	String
database	Name of the database.	String
user	The user name.	String
password	The password for the user name.	String

属性

属性	说明	数据类型
transactionAutoCommit (读写)	The autocommit interval. This can be used to force intermediate commits after a specified number of features have been modified.	Integer

方法概述

方法	说明
commitTransaction ()	No DML statements will be committed until the CommitTransaction method is called. 注: A commit may also occur when the connection to ArcSDE it terminated (check specific DBMS documentation to see how each DBMS deals with a disconnect while in a transaction).
execute (sql_statement)	通过 ArcSDE 连接向数据库发送 SQL 语句。如果在事务的外面运行 execute，则执行完 SQL DML（INSERT、UPDATE、DELETE . . .）语句后将自动发生提交。
rollbackTransaction ()	Rollback any DML operations to the previous commit.
startTransaction ()	To control when your changes are committed to the database, call the startTransaction method before calling execute. This starts a transaction and no DML statements will be committed until the commitTransaction method is called.

方法

commitTransaction ()

execute (sql_statement)

参数

说明

数据类型

sql_statement

The SQL statement.

The execute method returns a list of lists in the case where the statement returns rows from a table; for statements that do not return rows, it will return an indication of the success or failure of the statement (True for success; None for failure). Statements that return a single value from a single row will return the value in an appropriate type (string, float, float).

Variant

rollbackTransaction ()

startTransaction ()

代码实例

查询每种犯罪类型的数量，然后返回含有犯罪类型和事件计数的列表。

import arcpy

# Use an sde connection file to create the connection
sde = r'Database Connections\Connection to bedrock.sde'
sde_conn = arcpy.ArcSDESQLExecute(sde)

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)

sde_return = sde_conn.execute(sql)
for i in sde_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 ArcSDE.
    # 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 ArcSDE connection file
    sde_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.
            sde_return = sde_conn.execute(sql)
        except Exception as err:
            print(err)
            sde_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(sde_return, list):
            print("Number of rows returned by query: {0} rows".format(
                len(sde_return)))
            for row in sde_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 sde_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 ArcSDE or GDB 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 ArcSDE.
    # Using the first method, pass a set of strings containing the
    #   connection properties:
    #   <serverName>, <portNumber>, <version>, <userName>, <password>
    sde_conn = arcpy.ArcSDESQLExecute("gpserver3", "5151", "#",
                                      "toolbox", "toolbox")

    # Using the second method pass the path to a valid ArcSDE connection file
    #   arcpy.ArcSDESQLExecute("data\Connection to GPSERVER3.sde")

    for tbl in tables:
        print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")
        for col, val in 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))
                sde_return = sde_conn.execute(sql)
            except Exception as err:
                print(err)
                sde_return = False

            if isinstance(sde_return, list):
                if len(sde_return) > 0:
                    print("Identified {0} rows with incorrect data. Starting "
                          "transaction for update.".format(len(sde_return)))
                    # Start the transaction
                    sde_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))
                        sde_return = sde_conn.execute(sql)
                    except Exception as err:
                        print(err)
                        sde_return = False

                    # If the update completed successfully, commit the
                    # changes.  If not, rollback.
                    if sde_return == True:
                        print("Update statement: \n"
                              "{0} ran successfully.".format(sql))
                        # Commit the changes
                        sde_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))
                            sde_return = sde_conn.execute(sql)
                        except Exception as err:
                            print(err)
                            sde_return = False

                        if isinstance(sde_return, list):
                            print("{0} rows".format(len(sde_return)))
                            for row in sde_return:
                                print(row)
                            print("++++++++++++++++++++++++++++++++++++++++\n")
                        else:
                            if sde_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
                        sde_conn.rollbackTransaction()
                        print("Rolled back any changes.")
                        print("++++++++++++++++++++++++++++++++++++++++\n")
            else:
                print "No records required updating."

    # Disconnect and exit
    del sde_conn
    print("+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++")

except Exception as err:
    print(err)

ArcSDESQLExecute (arcpy)

摘要

讨论

语法

属性

方法概述

方法

代码实例

相关主题