Elaboration d'un script pour la création de bases de données et de géodatabases dans PostgreSQL

Vous pouvez copier et modifier l'un des scripts d'exemple fournis. Un des scripts crée à la fois une base de données et une géodatabase dans un cluster de bases de données PostgreSQL. L'autre crée une géodatabase dans une base de données PostgreSQL préconfigurée existante.

Vous devez accomplir certaines tâches supplémentaires avant d'exécuter le script. Ces tâches et le script lui-même sont décrits dans les étapes suivantes :

Etapes :

1. Installez et configurez PostgreSQL sur votre serveur.
2. Modifiez le fichier pg_hba.conf pour permettre la connexion au cluster de bases de données.
3. Créez un répertoire et un tablespace pour stocker votre base de données PostgreSQL.
4. Si vous comptez exécuter le script Python à partir d'un ordinateur différent du serveur sur lequel PostgreSQL est installé, installez et configurez le client PostgreSQL libpq sur l'ordinateur à partir duquel vous allez exécuter le script Python.

   Le fichiers du client PostgreSQL peuvent être téléchargés à partir du portail d'assistance client Esri. Si vous comptez exécuter le script à partir d'un client ArcGIS 32 bits, téléchargez les fichiers du client PostgreSQL 32 bits. Si vous comptez exécuter le script à partir d'ArcGIS for Server, téléchargez la version 64 bits des fichiers du client PostgreSQL. Reportez-vous à la rubrique Configuration d'une connexion à PostgreSQL pour plus d'informations.

5. Copiez un de ces exemples de script dans la fenêtre Python dans ArcGIS for Desktop ou dans un éditeur Python IDE, tel que PythonWin ou WING, sur un ordinateur sur lequel ArcGIS for Desktop (version Standard ou Advanced), ArcGIS for Server Enterprise (Standard ou Advanced) ou ArcGIS Engine avec l'extension Geodatabase Update est installé.

   Cet exemple crée un utilisateur sde et une structure, accorde à l'utilisateur sde les privilèges permettant de créer une géodatabase, spécifie le tablespace dans lequel stocker la base de données, crée une base de données, puis crée la géodatabase.

   """
   Name: create_enterprise_gdb.py
   Description: Provide connection information to a DBMS instance and create an enterprise geodatabase.
   Type  create_enterprise_gdb.py -h or create_enterprise_gdb.py --help for usage
   Author: Esri
   """
   
   # Import system modules
   import arcpy, os, optparse, sys
   
   
   # Define usage and version
   parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for " + arcpy.GetInstallInfo()['Version'] )
   
   #Define help and options
   parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, or POSTGRESQL.")                   
   parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
   parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
   parser.add_option ("--auth", dest="Account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
   parser.add_option ("-U", dest="Dbms_admin", type="string", default="", help="DBMS administrator user")
   parser.add_option ("-P", dest="Dbms_admin_pwd", type="string", default="", help="DBMS administrator password")
   parser.add_option ("--schema", dest="Schema_type", type="choice", choices=['SDE_SCHEMA', 'DBO_SCHEMA'], default="SDE_SCHEMA", help="Schema Type for SQL Server geodatabase, SDE or DBO. Default=SDE_SCHEMA")
   parser.add_option ("-u", dest="Gdb_admin", type="string", default="", help="Geodatabase administrator user name")
   parser.add_option ("-p", dest="Gdb_admin_pwd", type="string", default="", help="Geodatabase administrator password")
   parser.add_option ("-t", dest="Tablespace", type="string", default="", help="Tablespace name")
   parser.add_option ("-l", dest="Authorization_file", type="string", default="", help="Full path and name of authorization file")
   # Check if value entered for option
   try:
   	(options, args) = parser.parse_args()
   
   	
   	#Check if no system arguments (options) entered
   	if len(sys.argv) == 1:
   		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
   		parser.print_help()
   		sys.exit(3)
   
   	#Usage parameters for spatial database connection
   	database_type = options.Database_type.upper()
   	instance = options.Instance
   	database = options.Database.lower()	
   	account_authentication = options.Account_authentication.upper()
   	dbms_admin = options.Dbms_admin
   	dbms_admin_pwd = options.Dbms_admin_pwd
   	schema_type = options.Schema_type.upper()
   	gdb_admin = options.Gdb_admin
   	gdb_admin_pwd = options.Gdb_admin_pwd	
   	tablespace = options.Tablespace
   	license = options.Authorization_file
   	
   	
   	if (database_type == "SQLSERVER"):
   		database_type = "SQL_SERVER"
   	
   	if( database_type ==""):	
   		print " \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified.")
   		parser.print_help()
   		sys.exit(3)		
   		
   	if (license == ""):
   		print " \n%s: error: \n%s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
   		parser.print_help()
   		sys.exit(3)			
   	
   	if(database_type == "SQL_SERVER"):
   		if(schema_type == "SDE_SCHEMA" and gdb_admin.lower() != "sde"):
   			print "\n%s: error: %s\n" % (sys.argv[0], "To create SDE schema on SQL Server, geodatabase administrator must be SDE.")
   			sys.exit(3)
   		if (schema_type == "DBO_SCHEMA" and gdb_admin != ""):
   			print "\nWarning: %s\n" % ("Ignoring geodatabase administrator specified when creating DBO schema...")
   		if( account_authentication == "DATABASE_AUTH" and dbms_admin == ""):
   			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified with database authentication")
   			sys.exit(3)
   		if( account_authentication == "OPERATING_SYSTEM_AUTH" and dbms_admin != ""):
   			print "\nWarning: %s\n" % ("Ignoring DBMS administrator specified when using operating system authentication...")	
   	else:
   		if (schema_type == "DBO_SCHEMA"):
   			print "\nWarning: %s %s, %s\n" % ("Only SDE schema is supported on", database_type, "switching to SDE schema..." )
   			
   		if( gdb_admin.lower() == ""):
   			print "\n%s: error: %s\n" % (sys.argv[0], "Geodatabase administrator must be specified.")
   			sys.exit(3)
   
   		if( gdb_admin.lower() != "sde"):
   			if (database_type == "ORACLE"):
   				print "\nGeodatabase admin user is not SDE, creating user schema geodatabase on Oracle...\n"
   				sys.exit(3)
   			else:
   				print "\n%s: error: %s for %s.\n" % (sys.argv[0], "Geodatabase administrator must be SDE", database_type)
   				sys.exit(3)
   			
   		if( dbms_admin == ""):
   			print "\n%s: error: %s\n" % (sys.argv[0], "DBMS administrator must be specified!")
   			sys.exit(3)
   
   		if (account_authentication == "OPERATING_SYSTEM_AUTH"):
   			print "Warning: %s %s, %s\n" % ("Only database authentication is supported on", database_type, "switching to database authentication..." )
   
   	# Get the current product license
   	product_license=arcpy.ProductInfo()
   	
   	
   	# Checks required license level
   	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
   		print "\n" + product_license + " license found!" + " Creating an enterprise geodatabase requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
   		sys.exit("Re-authorize ArcGIS before creating enterprise geodatabase.")
   	else:
   		print "\n" + product_license + " license available!  Continuing to create..."
   		arcpy.AddMessage("+++++++++")
   	
   	
   	try:
   		print "Creating enterprise geodatabase...\n"
   		arcpy.CreateEnterpriseGeodatabase_management(database_platform=database_type,instance_name=instance, database_name=database, account_authentication=account_authentication, database_admin=dbms_admin, database_admin_password=dbms_admin_pwd, sde_schema=schema_type, gdb_admin_name=gdb_admin, gdb_admin_password=gdb_admin_pwd, tablespace_name=tablespace, authorization_file=license)
   		for i in range(arcpy.GetMessageCount()):
   			arcpy.AddReturnMessage(i)
   		arcpy.AddMessage("+++++++++\n")
   	except:
   		for i in range(arcpy.GetMessageCount()):
   			arcpy.AddReturnMessage(i)
   			
   #Check if no value entered for option	
   except SystemExit as e:
   	if e.code == 2:
   		parser.usage = ""
   		print "\n"
   		parser.print_help()   
   		parser.exit(2)
   

   Si vous préférez créer votre base de données manuellement, par exemple, si vous souhaitez utiliser une base de données dans laquelle PostGIS est installé, copiez ce script d'exemple pour activer les fonctionnalités de géodatabase dans une base de données PostgreSQL préconfigurée existante.

   """
   Name: enable_enterprise_gdb.py
   Description: Provide connection information to an enterprise database
   and enable enterprise geodatabase.
   Type enable_enterprise_gdb.py -h or enable_enterprise_gdb.py --help for usage
   Author: Esri
   """
   
   # Import system modules
   import arcpy, os, optparse, sys
   
   
   # Define usage and version
   parser = optparse.OptionParser(usage = "usage: %prog [Options]", version="%prog 1.0 for " + arcpy.GetInstallInfo()['Version'] )
   
   #Define help and options
   parser.add_option ("--DBMS", dest="Database_type", type="choice", choices=['SQLSERVER', 'ORACLE', 'POSTGRESQL', 'DB2','INFORMIX','DB2ZOS', ''], default="", help="Type of enterprise DBMS:  SQLSERVER, ORACLE, POSTGRESQL, DB2, INFORMIX, or DB2ZOS.")
   parser.add_option ("-i", dest="Instance", type="string", default="", help="DBMS instance name")
   parser.add_option ("--auth", dest="account_authentication", type ="choice", choices=['DATABASE_AUTH', 'OPERATING_SYSTEM_AUTH'], default='DATABASE_AUTH', help="Authentication type options (case-sensitive):  DATABASE_AUTH, OPERATING_SYSTEM_AUTH.  Default=DATABASE_AUTH")
   parser.add_option ("-u", dest="User", type="string", default="", help="Geodatabase administrator user name")
   parser.add_option ("-p", dest="Password", type="string", default="", help="Geodatabase  administrator password")
   parser.add_option ("-D", dest="Database", type="string", default="none", help="Database name:  Not required for Oracle")
   parser.add_option ("-l", dest="Authorization_file", type="string", default="", help="Full path and name of authorization file")
   
   
   # Check if value entered for option
   try:
   	(options, args) = parser.parse_args()
   
   	
   #Check if no system arguments (options) entered
   	if len(sys.argv) == 1:
   		print "%s: error: %s\n" % (sys.argv[0], "No command options given")
   		parser.print_help()
   		sys.exit(3)
   	
   
   	#Usage parameters for spatial database connection
   	database_type = options.Database_type.upper()
   	instance = options.Instance
   	account_authentication = options.account_authentication.upper()
   	username = options.User.lower() 
   	password = options.Password	
   	database = options.Database.lower()
   	license = options.Authorization_file
   
   
   	if( database_type ==""):	
   		print " \n%s: error: \n%s\n" % (sys.argv[0], "DBMS type (--DBMS) must be specified.")
   		parser.print_help()
   		sys.exit(3)		
   		
   	if (license == ""):
   		print " \n%s: error: \n%s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
   		parser.print_help()
   		sys.exit(3)
   
   	if (database_type == "SQLSERVER"):
   		database_type = "SQL_SERVER"
   		
   	# Get the current product license
   	product_license=arcpy.ProductInfo()
   	
   	if (license == ""):
   		print " \n%s: error: %s\n" % (sys.argv[0], "Authorization file (-l) must be specified.")
   		parser.print_help()
   		sys.exit(3)
   	
   	# Checks required license level
   	if product_license.upper() == "ARCVIEW" or product_license.upper() == 'ENGINE':
   		print "\n" + product_license + " license found!" + "  Enabling enterprise geodatabase functionality requires an ArcGIS for Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS for Server license."
   		sys.exit("Re-authorize ArcGIS before enabling an enterprise geodatabase.")
   	else:
   		print "\n" + product_license + " license available!  Continuing to enable..."
   		arcpy.AddMessage("+++++++++")
   	
   	# Local variables
   	instance_temp = instance.replace("\\","_")
   	instance_temp = instance_temp.replace("/","_")
   	instance_temp = instance_temp.replace(":","_")
   	Conn_File_NameT = instance_temp + "_" + database + "_" + username    
   	
   	if os.environ.get("TEMP") == None:
   		temp = "c:\\temp"	
   	else:
   		temp = os.environ.get("TEMP")
   	
   	if os.environ.get("TMP") == None:
   		temp = "/usr/tmp"		
   	else:
   		temp = os.environ.get("TMP")  
   	
   
   	Connection_File_Name = Conn_File_NameT + ".sde"
   	Connection_File_Name_full_path = temp + os.sep + Conn_File_NameT + ".sde"
   	
   	# Check for the .sde file and delete it if present
   	arcpy.env.overwriteOutput=True
   	if os.path.exists(Connection_File_Name_full_path):
   		os.remove(Connection_File_Name_full_path)
   	
   	print "\nCreating Database Connection File...\n"	
   	# Process: Create Database Connection File...
   	# Usage:  out_file_location, out_file_name, DBMS_TYPE, instnace, database, account_authentication, username, password, save_username_password(must be true)
   	arcpy.CreateDatabaseConnection_management(out_folder_path=temp, out_name=Connection_File_Name, database_platform=database_type, instance=instance, database=database, account_authentication=account_authentication, username=username, password=password, save_user_pass="TRUE")
           for i in range(arcpy.GetMessageCount()):
   		if "000565" in arcpy.GetMessage(i):   #Check if database connection was successful
   			arcpy.AddReturnMessage(i)
   			arcpy.AddMessage("\n+++++++++")
   			arcpy.AddMessage("Exiting!!")
   			arcpy.AddMessage("+++++++++\n")
   			sys.exit(3)            
   		else:
   			arcpy.AddReturnMessage(i)
   			arcpy.AddMessage("+++++++++\n")
   	
   	
   	# Process: Enable geodatabase...
   	try:
   		print "Enabling Enterprise Geodatabase...\n"
   		arcpy.EnableEnterpriseGeodatabase_management(input_database=Connection_File_Name_full_path, authorization_file=license)
   		for i in range(arcpy.GetMessageCount()):
   			arcpy.AddReturnMessage(i)
   		arcpy.AddMessage("+++++++++\n")
   	except:
   		for i in range(arcpy.GetMessageCount()):
   			arcpy.AddReturnMessage(i)
   			
   	if os.path.exists(Connection_File_Name_full_path):
   		os.remove(Connection_File_Name_full_path)
   			
   #Check if no value entered for option	
   except SystemExit as e:
   	if e.code == 2:
   		parser.usage = ""
   		print "\n"
   		parser.print_help() 
   		parser.exit(2)
   

6. Modifiez le script pour inclure les informations spécifiques à votre site.
7. Exécutez le script que vous avez copié et modifié.

Un fichier journal correspondant à la création de la géodatabase (GDBCreateGeodatabase<#>.log) est écrit dans le répertoire spécifié pour votre variable TEMP ou TMP sur l'ordinateur sur lequel le script est exécuté.