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 et None en cas d'échec). Les instructions qui renvoient une seule valeur à partir d'une ligne unique renverront la valeur dans un type approprié (chaîne, réel simple, réel simple).
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(sql_statement) |
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 ne sera 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 s'achèvent lors de la déconnexion d'un utilisateur. 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 l'exécution. Une transaction est alors lancée et aucune instruction DML ne sera 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 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)