ArcSDESQLExecute

概要

The ArcSDESQLExecute class provides a means of executing SQL statements via an enterprise geodatabase connection.

ディスカッション

注意:
  • ArcGIS ソフトウェア以外のものを使用してエンタープライズ ジオデータベースのシステム テーブルを変更することはできません。SQL を使用してこれらのシステム テーブルを直接編集すると、ジオデータベースを破損する可能性があります。
  • SQL を使用してバージョン対応登録されたデータを編集する場合は、必ずバージョン ビューから行う必要があります。
  • リレーショナル データベース管理システム (DBMS) のデータ タイプとテーブル フォーマットを使用して DBMS に実装されたジオデータベースの場合は、DBMS 独自の SQL を使用して、データベースに格納された情報を操作できます。
  • SQL によってジオデータベースの情報にアクセスすれば、ジオデータベースによって管理されるテーブル データに外部アプリケーションからアクセスできます。この外部アプリケーションは、非空間データベース アプリケーションであっても、ArcObjects 以外の環境で開発されたカスタムの空間アプリケーションであってもかまいません。ただし、ジオデータベースへの SQL アクセス時には、トポロジ、ネットワーク、テレイン、その他のクラスやワークスペースのエクステンションなど、ジオデータベースの機能は適用されないことに注意してください。
  • 一部のジオデータベース機能に必要なテーブル間の関係を維持するために、トリガーやストアド プロシージャなどの DBMS 機能を使用できる場合があります。一方で、これらの機能を考慮せずにデータベースに対して SQL コマンドを実行すると (たとえば、INSERT ステートメントを実行してビジネス テーブルにレコードを追加する)、ジオデータベース機能が適用されず、ジオデータベース内のデータ間の関係が破壊される可能性があります。
  • エンタープライズ ジオデータベースのオブジェクトにアクセスしたり、これらのオブジェクトを変更したりする前に、DBMS 内でジオデータベースのオブジェクトに対して SQL を使用する場合の注意点について、エンタープライズ ジオデータベースの資料をすべてお読みください。

構文

 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 the enterprise geodatabase is terminated (check specific DBMS documentation to see how each DBMS deals with a disconnect while in a transaction).

execute (sql_statement)

Sends the SQL statement to the database via an ArcSDE connection. If execute is run outside of a transaction, a commit will automatically take place once the SQL DML (INSERT, UPDATE, DELETE . . .) statement has been executed.

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 ()

コードのサンプル

Query for the number of each crime type and return a list of lists of the crime type and count of incidents.

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)

関連トピック