Exécution de SQL à l'aide d'une connexion ArcSDE
Parfois, lors de l'utilisation de tables non versionnées, il peut paraître plus simple d'interroger une table dans une base de données à l'aide de SQL (Structured Query Language) plutôt que d'utiliser l'un des outils de géotraitement. L'objet ArcSDESQLExecute prend en charge l'exécution de la plupart des instructions SQL et renvoie à l'utilisateur les résultats de ces instructions. L'objet renvoie une liste de listes lorsque l'instruction renvoie des lignes à partir d'une table ; pour les instructions qui ne renvoient pas de lignes, il renvoie une indication du succès ou de l'échec de l'instruction (True en cas de succès, None en cas d'échec).
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.
|
Propriété | |
|---|---|
|
transactionAutoCommit |
Intervalle de validation automatique. Elle peut être utilisée pour forcer les validations intermédiaires après qu'un nombre spécifié d'entités a été modifié. |
|
Méthodes | |
|---|---|
|
commitTransaction() |
Aucune instruction DML n'est validée avant l'appel de la méthode commitTransaction.  Remarque :Une validation peut également se produire lorsque la connexion à ArcSDE prend fin (consultez la documentation spécifique au SGBD pour savoir comment chaque SGBD gère une déconnexion en cours de transaction). |
|
execute(instruction_sql) |
Envoie l'instruction SQL à la base de données via une connexion ArcSDE. Si la méthode execute est exécutée en dehors d'une transaction, une validation aura automatiquement lieu une fois l'instruction SQL DML (INSERT, UPDATE, DELETE) exécutée. |
|
rollbackTransaction() |
Annule toute opération DML en revenant à la validation antérieure. |
|
startTransaction() |
Pour contrôler le moment où vos modifications sont validées dans la base de données, appelez la méthode startTransaction avant d'appeler execute. Une transaction est alors lancée et aucune instruction DML n'est validée avant l'appel de la méthode commitTransaction. |
La méthode execute envoie l'instruction SQL à la base de données via une connexion ArcSDE. Si la méthode execute est exécutée en dehors d'une transaction, une validation aura automatiquement lieu une fois l'instruction SQL DML (INSERT, UPDATE, DELETE) exécutée.
ArcSDESQLExecute prend en charge le modèle de transaction ArcSDE. Les transactions sont une propriété d'une connexion ArcSDE et lient les opérations afin qu'un ensemble complet de modifications soit enregistré ou rejeté. Par exemple, si un ensemble de parcelles est mis à jour dans un ordre particulier, vous pouvez utiliser une transaction pour définir le début et la fin des modifications afin que toutes les modifications soient réinjectées ensemble. Si un ensemble de modifications ne peut pas être inséré avec succès, l'ensemble de la transaction est rejetée. Toutes les transactions se terminent lorsqu'un utilisateur se déconnecte. ArcSDESQLExecute utilise les fonctions d'API ArcSDE fournies pour démarrer, valider et restaurer les transactions.
Si vous voulez contrôler le moment où vos modifications sont validées dans la base de données, appelez la méthode startTransaction avant d'appeler execute. Une transaction est alors lancée et aucune instruction DML n'est validée avant l'appel de la méthode commitTransaction. Une validation peut également se produire lorsque la connexion à ArcSDE prend fin (consultez la documentation spécifique au SGBD pour savoir comment chaque SGBD gère une déconnexion en cours de transaction). Dans une transaction, il est également possible de restaurer les opérations DML jusqu'à la validation antérieure.
Une propriété d'intervalle de validation automatique, transactionAutoCommit, est disponible. Elle peut être utilisée pour forcer les validations intermédiaires après qu'un nombre spécifié d'entités a été modifié.
Reportez-vous au guide de référence SQL spécifique à votre SGBD pour en savoir plus sur l'écriture d'instructions SQL.
Exemples
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."
# 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."