ArcSDESQLExecute (arcpy)
Récapitulatif
La classe ArcSDESQLExecute permet d'exécuter des instructions SQL via une connexion ArcSDE.
Discussion
Attention :- Il est déconseillé de modifier les tables système ArcSDE et de géodatabase à l'aide d'autres logiciels qu'ArcGIS. Une corruption peut se produire si ces tables système sont modifiées directement avec SQL.
- Les modifications sur des données versionnées réalisées à l'aide de SQL doivent être effectuées uniquement à l'aide de vues multi-versionnées.
- Pour les géodatabases implémentées dans un système de gestion de base de données (SGBD) relationnel qui utilise des types de données et des formats tabulaires SGBD, le langage SQL propre au SGBD peut être utilisé pour travailler avec les informations stockées dans la base de données.
- L'accès aux informations d'une géodatabase via SQL permet aux applications externes d'accéder aux données tabulaires gérées par la géodatabase. Ces applications externes peuvent être des applications de base de données non-spatiales ou des applications spatiales personnalisées développées dans un environnement autre qu'ArcObjects. Notez toutefois que l'accès SQL à la géodatabase annule des fonctionnalités de géodatabase, telles que la topologie, les réseaux, les MNT, ainsi que d'autres extensions d'espace de travail ou de classe.
- Il peut s'avérer possible d'utiliser des fonctions de SGBD, telles que les déclencheurs et les procédures stockées, afin de conserver les relations entre les tables nécessaires à certaines fonctionnalités de géodatabase. Cependant, le fait d'exécuter des commandes SQL sur la base de données sans tenir compte de cette fonctionnalité supplémentaire (il peut s'agir, par exemple, de l'exécution de commandes INSERT pour ajouter des enregistrements à une table métier) contourne les fonctionnalités de géodatabase et peut éventuellement altérer les relations entre les données de votre géodatabase.
- Avant de tenter d'accéder à des objets ArcSDE ou de géodatabase ou de les modifier, veuillez lire toute la documentation sur ArcSDE et la géodatabase concernant l'utilisation de SQL sur des objets ArcSDE ou de géodatabase dans le SGBD.
Syntaxe
ArcSDESQLExecute ({server}, {instance}, {database}, {user}, {password})
| Paramètre | Explication | Type de données |
server |
Name of the server on which the database is installed. | 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 |
Propriétés
| Propriété | Explication | Type de données |
| transactionAutoCommit (Lecture/écriture) |
The autocommit interval. This can be used to force intermediate commits after a specified number of features have been modified. | Integer |
Vue d'ensemble des méthodes
| Méthode | Explication |
| commitTransaction () |
No DML statements will be committed until the CommitTransaction method is called.  Remarque :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) |
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. |
Méthodes
commitTransaction ()
execute (sql_statement)
| Paramètre | Explication | Type de données |
sql_statement |
The SQL statement. | String |
rollbackTransaction ()
startTransaction ()
Exemple de code
Exécution d'une liste d'instructions SQL
import arcpy
from arcpy import env
import sys
try:
# Make data path relative
#
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>
# sdeConn = arcpy.ArcSDESQLExecute("gpserver3","5151","#","toolbox","toolbox")
# Using the second method pass the path to a valid ArcSDE connection file
#
sdeConn = arcpy.ArcSDESQLExecute("data\Connection to GPSERVER3.sde")
# Get the SQL statements, separated by ; from a text string.
#
SQLStatement = arcpy.GetParameterAsText(0)
SQLStatementList = SQLStatement.split(";")
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
# For each SQL statement passed in, execute it.
#
for sql in SQLStatementList:
print "Execute SQL Statement: " + sql
try:
# Pass the SQL statement to the database.
#
sdeReturn = sdeConn.execute(sql)
except Exception, ErrorDesc:
print ErrorDesc
sdeReturn = 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(sdeReturn, list):
print "Number of rows returned by query: " + str(len(sdeReturn)), "rows"
for row in sdeReturn:
print row
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
else:
# If the return value was not a list, the statement was most likely a DDL statment.
# Check its status.
if sdeReturn == True:
print "SQL statement: " + sql + " ran sucessfully."
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
else:
print "SQL statement: " + sql + " FAILED."
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
except Exception, ErrorDesc:
print Exception, ErrorDesc
except:
print "Problem executing SQL."
Mise à jour conditionnelle à l'aide d'une transaction
# 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 arcpy
from arcpy import env
import sys
try:
# Make data path relative (not relevant unless data is moved here and paths modified)
#
env.workspace = sys.path[0]
#Column name:value that should be in the record.
#
SQLvalues = {"STREET_NAM":"'EUREKA'"}
#Value that is incorrect if found in the above column.
#
badVal = "'EREKA'"
#List of tables to look in for the bad value.
#
tableList = ["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>
#
sdeConn = arcpy.ArcSDESQLExecute("gpserver3","5151","#","toolbox","toolbox")
# Using the second method pass the path to a valid ArcSDE connection file
# sdeConn = arcpy.ArcSDESQLExecute("data\Connection to GPSERVER3.sde")
for tbl in tableList:
print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
for col, val in SQLvalues.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 " + tbl + " for bad data: Column:" + col + " Value: " + badVal
try:
sql = "select OBJECTID," + col + " from " + tbl + " where " + col + " = " + badVal
print "Attempt to execute SQL Statement: " + sql
sdeReturn = sdeConn.execute(sql)
except Exception, ErrorDesc:
print ErrorDesc
sdeReturn = False
if isinstance(sdeReturn, list):
if len(sdeReturn) > 0:
print "Identified " + str(len(sdeReturn)) + " rows with incorrect data. Starting transaction for update."
# Start the transaction
#
sdeConn.startTransaction()
print "Transaction started....."
# Perform the update
#
try:
sql = "update " + tbl + " set " + col + "=" + str(val) + " where " + col + " = " + badVal
print "Changing bad value: " + badVal + " to the good value: " + val + " using update statement:\n " + sql
sdeReturn = sdeConn.execute(sql)
except Exception, ErrorDesc:
print ErrorDesc
sdeReturn = False
# If the update completed sucessfully, commit the changes. If not, rollback.
#
if sdeReturn == True:
print "Update statement: \n" + sql + " ran successfully."
# Commit the changes
#
sdeConn.commitTransaction()
print "Commited Transaction"
# List the changes.
#
try:
print "Displaying updated rows for visual inspection."
sql = "select OBJECTID," + col + " from " + tbl + " where " + col + " = " + val
print "Executing SQL Statement: \n" + sql
sdeReturn = sdeConn.execute(sql)
except Exception, ErrorDesc:
print ErrorDesc
sdeReturn = False
if isinstance(sdeReturn, list):
print len(sdeReturn), "rows"
for row in sdeReturn:
print row
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
else:
if sdeReturn == True:
print "SQL statement: \n" + sql + "\nran successfully."
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
else:
print "SQL statement: \n" + sql + "\nFAILED."
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
else:
print "SQL statement: \n" + sql + "\nFAILED. Rolling back all changes."
# Rollback changes
#
sdeConn.rollbackTransaction()
print "Rolled back any changes."
print "+++++++++++++++++++++++++++++++++++++++++++++\n"
else:
print "No records required updating."
# Disconnect and exit
del sdeConn
print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
except Exception, ErrorDesc:
print Exception, ErrorDesc
except:
print "Problem executing SQL."
Thèmes connexes
9/12/2013