Executing SQL using an ArcSDE connection
Sometimes when working with tables that are not versioned, it may seem easier to query a table in a database using Structured Query Language (SQL) rather than using one of the geoprocessing tools. The ArcSDESQLExecute object supports the execution of most SQL statements and will return to the user the results of those statements. The object will return 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).
Caution:- ArcSDE and GDB system tables should not be altered using anything other than ArcGIS software. Corruption can occur if these system tables are edited directly using SQL.
- Edits on versioned data performed using SQL should only be done through versioned views.
- For geodatabases implemented in a relational database management system (DBMS) using DBMS data types and table formats, the DBMS's own SQL may be used to work with the information stored in the database.
- Accessing the information in a geodatabase via SQL allows external applications to access the tabular data managed by the geodatabase. These external applications may be nonspatial database applications or custom spatial applications developed in an environment other than ArcObjects. Be aware, though, that SQL access to the geodatabase bypasses geodatabase functionality, such as topology, networks, terrains, or other class or workspace extensions.
- It may be possible to use DBMS features such as triggers and stored procedures to maintain the relationships between tables needed for certain geodatabase functionality. However, executing SQL commands against the database without taking this extra functionality into account—for example, issuing INSERT statements to add records to a business table—will circumvent geodatabase functionality and possibly corrupt the relationships between data in your geodatabase.
- Before attempting to access or modify any ArcSDE or GDB objects, read all ArcSDE and geodatabase documentation about using SQL against ArcSDE or GDB objects in the DBMS.
|
Property | |
|---|---|
|
transactionAutoCommit |
The autocommit interval. This can be used to force intermediate commits after a specified number of features have been modified. |
|
Methods | |
|---|---|
|
commitTransaction() |
No DML statements will be committed until the commitTransaction method is called.  Note:A commit may also occur when the connection to ArcSDE 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() |
Roll back 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. |
The execute method sends the SQL statement to the database via an ArcSDE connection. If execute is run outside a transaction, a commit will automatically take place once the SQL DML (INSERT, UPDATE, DELETE) statement has been executed.
ArcSDESQLExecute supports the ArcSDE Transaction model. Transactions are a property of an ArcSDE connection and bind operations so that an entire set of changes is either recorded or rejected. For example, if a set of parcels is being updated in a particular order, you can use a transaction to define the beginning and end of the changes so that all changes are posted together. If a set of changes can't be successfully inserted, the entire transaction is rejected. All transactions end when a user disconnects. ArcSDESQLExecute uses the provided ArcSDE API functions to start, commit, and roll back transactions.
If you want 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. A commit may also occur when the connection to ArcSDE is terminated (check specific DBMS documentation to see how each DBMS deals with a disconnect while in a transaction). Within a transaction, it is also possible to roll back any DML operations to the previous commit.
An autocommit interval property named transactionAutoCommit is available. This can be used to force intermediate commits after a specified number of features have been modified.
See your specific DBMS SQL Reference guide for help writing SQL statements.
Examples
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)