def createSDEConnection(self, sdeServer, sdePort): ''' :param sdeServer: the sde host to connect to :type sdeServer: str :param sdePort: the sde port that the application server is listening on :type str: Creates a arc sde connection file in the data directory named TempConnection.sde if the file already exists it will be deleted. ''' connFile = os.path.join(self.dataDir, 'TempConnection' + '.sde') if os.path.exists(connFile): # print u'deleting and recreating the connection file' self.logger.debug("deleting and recreating the connection file") os.remove(connFile) self.logger.debug("Creating the connection file...") arcpy.CreateArcSDEConnectionFile_management( self.dataDir, "TempConnection", sdeServer, sdePort, "", "DATABASE_AUTH", \ self.user, self.passwd, "SAVE_USERNAME", "SDE.DEFAULT", "SAVE_VERSION") self.logger.debug("Connection file successfully created...")
def createVersionStartEdit(fcLST, temploc, sdecon, user, password): global version, workspace, edit, fcLSTglob, userglob fcLSTglob = fcLST userglob = user version = "ARCPY" + time.strftime("%d%m%Y%I%M%S") tempsdecon = "SDECONN" + time.strftime("%d%m%Y%I%M%S") workspace = os.path.join(temploc, tempsdecon + ".sde") arcpy.CreateVersion_management(sdecon, "SDE.DEFAULT", version, "PRIVATE") arcpy.CreateArcSDEConnectionFile_management(temploc, tempsdecon, " ", "sde:oracle11g:somesde.world", "", "", userglob, password, "", user + "." + version) edit = arcpy.da.Editor(workspace) edit.startEditing(False, True) edit.startOperation() for i in fcLSTglob: editfeature = os.path.join(workspace, i) arcpy.MakeFeatureLayer_management(editfeature, i.split(".")[1]) print("versioned editing started") arcpy.AddMessage("versioned editing started") return version, workspace, edit, fcLSTglob, userglob
def CreateConnectionFile(username, password, version="SDE.DEFAULT"): '''Create an SDE connection in memory''' startTime = time.time() msgTxt = "" try: tempFolder = tempfile.gettempdir() fileName = "tempConnection.sde" connectionFile = os.path.join(tempFolder, fileName) serverName = "loicora1" serviceName = "sde:oracle11g:{0}".format(serverName) databaseName = "" authType = "DATABASE_AUTH" saveUserInfo = "SAVE_USERNAME" saveVersionInfo = "SAVE_VERSION" # Remove temporary connection file if it already exists if os.path.isfile(connectionFile): os.remove(connectionFile) # Create temporary connection file in memory if not os.path.isfile(connectionFile): arcpy.CreateArcSDEConnectionFile_management( tempFolder, fileName, serverName, serviceName, databaseName, authType, username, password, saveUserInfo, version, saveVersionInfo) return connectionFile except: # Return any Python specific errors tb = sys.exc_info()[2] tbinfo = traceback.format_tb(tb)[0] pymsg = "PYTHON ERRORS:\n Traceback Info:\n{0}\n Error Info:\n {1}: {2}\n".format( tbinfo, str(sys.exc_type), str(sys.exc_value)) tmRun = time.strftime("%X", time.localtime()) endTime = time.time() prodInfo = "\tScript errored after running for {0} seconds.".format( str(round((endTime - startTime)))) msgTxt += "\n\n\tScript error at: {0}\n{1}\n\n{2}".format( tmRun, prodInfo, pymsg) AddMsgAndPrint(msgTxt, 2)
def connect(database, server="<default server>", username="******", password="******", version="SDE.DEFAULT"): # Check if value entered for option try: #Usage parameters for spatial database connection to upgrade service = "sde:sqlserver:" + server account_authentication = 'DATABASE_AUTH' version = version.upper() database = database.lower() # Check if direct connection if service.find(":") <> -1: #This is direct connect ServiceConnFileName = service.replace(":", "") ServiceConnFileName = ServiceConnFileName.replace(";", "") ServiceConnFileName = ServiceConnFileName.replace("=", "") ServiceConnFileName = ServiceConnFileName.replace("/", "") ServiceConnFileName = ServiceConnFileName.replace("\\", "") else: arcpy.AddMessage("\n+++++++++") arcpy.AddMessage("Exiting!!") arcpy.AddMessage("+++++++++") sys.exit( "\nSyntax for a direct connection in the Service parameter is required for geodatabase upgrade." ) # Local variables Conn_File_NameT = server + "_" + ServiceConnFileName + "_" + 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 = temp + os.sep + Conn_File_NameT + ".sde" if os.path.isfile(Connection_File_Name): return Connection_File_Name # Check for the .sde file and delete it if present arcpy.env.overwriteOutput = True # Variables defined within the script; other variable options commented out at the end of the line saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION print "\nCreating ArcSDE Connection File...\n" # Process: Create ArcSDE Connection File... # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password, version, save_version_info print temp print Conn_File_NameT print server print service print database print account_authentication print username print password print saveUserInfo print version print saveVersionInfo arcpy.CreateArcSDEConnectionFile_management(temp, Conn_File_NameT, server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo) 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") return Connection_File_Name #Check if no value entered for option except SystemExit as e: print e.code return
def create_VMsde_db(vicmap_version, auth_file): logging.info('checking python interpreter is 32 bit') if sys.maxsize > 2**32: raise Exception('Please use Python 32 bit.') logging.info('local variables') vm = gis.VICMAP(vicmap_version) logging.info('creating SDE geodatabase: ' + vm.database_name) arcpy.CreateEnterpriseGeodatabase_management( database_platform="SQL_Server", instance_name="TDB03", database_name=vm.database_name, account_authentication="OPERATING_SYSTEM_AUTH", database_admin="#", database_admin_password="******", sde_schema="DBO_SCHEMA", gdb_admin_name="#", gdb_admin_password="******", tablespace_name="#", authorization_file=auth_file) logging.info('create database connection') conn = arcpy.ArcSDESQLExecute('TDB03', 'sde:sqlserver:TDB03') logging.info('grant permissions to users') sql = ''' USE [{vm}]; CREATE USER [ESTA\ArcGISSOC] FOR LOGIN [ESTA\ArcGISSOC]; ALTER ROLE [db_datareader] ADD MEMBER [ESTA\ArcGISSOC]; CREATE USER [ESTA\ESTAMIS] FOR LOGIN [ESTA\ESTAMIS]; ALTER ROLE [db_datareader] ADD MEMBER [ESTA\ESTAMIS]; '''.format(vm=vm.database_name) logging.info('sql: ' + sql) result = conn.execute(sql) logging.info('result: {result}'.format(result=result)) logging.info('get server property info') logical_name_db = conn.execute( '''select name from sys.master_files where database_id = db_id('{vm}') and type_desc = 'ROWS' ''' .format(vm=vm.database_name)) logical_name_log = conn.execute( '''select name from sys.master_files where database_id = db_id('{vm}') and type_desc = 'LOG' ''' .format(vm=vm.database_name)) size_db = conn.execute( '''select (size*8)/1024 as size from sys.master_files where database_id = db_id('{vm}') and type_desc = 'ROWS' ''' .format(vm=vm.database_name)) if size_db != 15360: logging.info('alter database size: 15gb') sql = ''' COMMIT; ALTER DATABASE [{vm}] MODIFY FILE ( NAME = '{name_db}', SIZE = 30GB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ); BEGIN TRANSACTION; '''.format(vm=vm.database_name, name_db=logical_name_db, name_log=logical_name_log) logging.info('sql: ' + sql) result = conn.execute(sql) logging.info('result: {result}'.format(result=result)) logging.info('alter database recovery') sql = ''' COMMIT; ALTER DATABASE [{vm}] SET RECOVERY SIMPLE WITH NO_WAIT; BEGIN TRANSACTION; '''.format(vm=vm.database_name) logging.info('sql: ' + sql) result = conn.execute(sql) logging.info('result: {result}'.format(result=result)) logging.info('creating SDE connection file: ' + vm.sde) if not os.path.exists(vm.sde): arcpy.CreateArcSDEConnectionFile_management( out_folder_path=os.path.split(vm.sde)[0], out_name=os.path.split(vm.sde)[1], server='TDB03', service='sde:sqlserver:TDB03', database=vm.database_name, account_authentication="OPERATING_SYSTEM_AUTH", version="dbo.DEFAULT", save_version_info="SAVE_VERSION", )
Created on Sep 18, 2013 @author: kyleg ''' import arcpy mxd = arcpy.mapping.MapDocument( r"//gisdata/arcgis/GISdata/MXD/services/DevServices.mxd") connection = r'//gisdata/arcgis/GISdata/MXD/services/sdedev.sde' if arcpy.Exists(connection): arcpy.Delete_management(connection) arcpy.CreateArcSDEConnectionFile_management( r'//gisdata/arcgis/GISdata/MXD/services', "sdedev", "sdedev", "sde:Oracle11g:sdedev", "#", "DATABASE_AUTH", "gis_dev", "gis", "SAVE_USERNAME", "SDE.DEFAULT", "SAVE_VERSION") fromcon = r'Database Connections/SDEPROD_GIS.sde' for lyr in arcpy.mapping.ListLayers(mxd): if lyr.supports("SERVICEPROPERTIES"): servProp = lyr.serviceProperties print "Layer name:" + lyr.name + " at " + servProp.get( 'Connection', 'N/A') print "-----------------------------------------------------" if lyr.serviceProperties["ServiceType"] != "SDE": print lyr.name + " at " + lyr.workspacePath else: print "Service Type: " + servProp.get('ServiceType', 'N/A') print " Service: " + servProp.get('Service', 'N/A')
service = 'sde:sqlserver:' + servername state= 'KK' statedb = (state + '_Hazus.sde') authtype = 'DATABASE_AUTH' username = '******' password = '******' statepath = (folderpath + '\\' + statedb) gdbpath = (folderpath + '\\' + 'Hazus_State.mdb') sygdb = (folderpath + '\\' + 'syHazus.mdb') #os.remove(folderpath + '\\' +'HazusExport.sde') print "Creating SDE connection files" arcpy.CreateArcSDEConnectionFile_management(env.workspace,'syHazus.sde',servername,service,'syHazus', authtype,username,password) arcpy.CreateArcSDEConnectionFile_management(env.workspace,statedb,servername,service,state, authtype,username,password) print "Hazus SQL Server SDE connection created at",statepath sySQL = (folderpath + '\\syHazus.sde') env.workspace = gdbpath featureclasses = arcpy.ListFeatureClasses() tables = arcpy.ListTables() print "Custom State Database Feature Class List:" print featureclasses print "Custom State Database Table List:" print tables
#List all featureclasses in a geodatabase, including any within feature datasets import arcpy, os from arcpy import env arcpy.env.overwriteOutput = True #arcpy.CreateDatabaseConnection_management(out_folder_path=".", # out_name="GIS winauth to gis-server.sde", # server="https://arc03.cc.vt.edu:6443/arcgis/admin/", # database_platform="POSTGRESQL",#Need to check this setting # instance="Oracle", # account_authentication="DATABASE_AUTH",#Not sure if its supported DBMS # username="******", # password="******", # database="gisdb", # version="sde") # # arcpy.CreateArcSDEConnectionFile_management( out_folder_path=r"C:\\Users\\svekhand\\Documents\\ArcGIS\\", out_name="mypostgres.sde", server="ARC03", #servicename= "SampleWorldCities" database="POSTGRESQL", #Need to check this setting account_authentication="DATABASE_AUTH", username="******", password="******", save_username_password="******", version="SDE.DEFAULT")
databaseName = "yourDbName" #db instance authType = "DATABASE_AUTH" #authentication type. db type in our case username = "******" password = "******" folderName = "yourConnectionPath" #connection str path saveUserInfo = "SAVE_USERNAME" versionName = "SDE.DEFAULT" saveVersionInfo = "SAVE_VERSION" for mxd_Name in os.listdir(folder_path): fullpath = os.path.join(folder_path, mxd_Name) if os.path.isfile(fullpath): basename, extension = os.path.splitext(fullpath) if extension.lower() == ".mxd": arcpy.CreateArcSDEConnectionFile_management( folderName, fileName, serverName, serviceName, databaseName, authType, username, password, saveUserInfo, versionName, saveVersionInfo) mxd = arcpy.mapping.MapDocument(fullpath) for df in arcpy.mapping.ListDataFrames(mxd): for lyr in arcpy.mapping.ListLayers(mxd): if lyr.supports("SERVICEPROPERTIES"): servProp = lyr.serviceProperties #Replace old dataset name with new dataset name and replace if (lyr.serviceProperties["ServiceType"] == "SDE" ) and (lyr.serviceProperties["Service"] == "PortName"): dataSet = lyr.dataSource sdeFile = "Database Connections\\" + fileName lyr.replaceDataSource(sdeFile, "SDE_Workspace", lyr.name, "") arcpy.RefreshTOC()
__author__ = 'Roman' import arcpy, os #Remove temporary connection file if it already exists sdeFile = r"C:\Project\Output\TempSDEConnectionFile.sde" if os.path.exists(sdeFile): os.remove(sdeFile) #Create temporary connection file in memory arcpy.CreateArcSDEConnectionFile_management(r"C:\Project\Output", "TempConnection", "myServerName", "5151", "myDatabase", "DATABASE_AUTH", "myUserName", "myPassword", "SAVE_USERNAME", "myUser.DEFAULT", "SAVE_VERSION") #Export a map document to verify that secured layers are present mxd = arcpy.mapping.MapDocument(r"C:\Project\SDEdata.mxd") arcpy.mapping.ExportToPDF(mxd, r"C:\Project\output\SDEdata.pdf") os.remove(sdeFile) del mxd__author__ = 'Administrator'
def __init__(self): #=========================================================================== # SDE Conn #=========================================================================== #I use a folder called Data where I save the sde connection file #self.sdeconnpath = os.path.abspath(os.path.join(os.path.dirname(os.path.abspath(__file__)),"..","..","..","DATA")) #Dont know where you'll save yours self.sdeconnpath = os.path.abspath( os.path.dirname(os.path.abspath(__file__))) self.sp = {} self.sp['server'] = "tetrasql" self.sp['service'] = "sde:sqlserver:tetrasql" self.sp['database'] = "sos" #Databasename self.sp['user'] = "******" self.sp['passw'] = "Sowannebe2" self.sp['name'] = "sos_sa.sde" #Name of connectionfile self.sp['schema'] = "DBO" self.sp['auth'] = "DATABASE_AUTH" self.sp['version'] = "SDE.DEFAULT" self.sp['save'] = "SAVE_USERNAME" self.sp['path'] = self.sdeconnpath self.sp['fullpath'] = os.path.join(self.sp['path'], self.sp['name']) self.sp[ 'dbstart'] = self.sp['database'] + '.' + self.sp['schema'] + '.' #If the file doesn't exist the it created if not os.path.exists(self.sp['fullpath']): arcpy.CreateArcSDEConnectionFile_management( self.sp['path'], self.sp['name'], self.sp['server'], self.sp['service'], self.sp['database'], self.sp['auth'], self.sp['user'], self.sp['passw'], self.sp['save'], self.sp['version']) #=============================================================================== # TABLES # If you for some reason change the tabel name, it's easy to just change the reference here instead of in your code #=============================================================================== self.sp['t_offering'] = "Offering" self.sp['t_procedure'] = "Procedure_" self.sp['t_observation'] = "Observation" self.sp['t_feature'] = "Feature" self.sp['t_property'] = "Property" self.sp['t_proc_prop'] = "Proc_prop" self.sp['t_prop_off'] = "Prop_Off" self.sp['t_foi_off'] = "Foi_Off" #=========================================================================== # Fields # The same goes for field names #=========================================================================== #offering fields self.fields = {} self.fields['f_userID'] = "FK_UserID" self.fields['f_user_uid'] = "UserID" self.fields['f_user_lisys'] = "LogInSystem" self.fields['f_user_lisysid'] = "LogInSystemUserID" self.fields['f_user_nn'] = "NickName" self.fields['f_user_crd'] = "CreateDate" self.fields['f_user_lad'] = "LastActivityDate" #Language table self.fields['f_lang_id'] = "LanguageCode" #Community species table self.fields['f_cs_id'] = "CommunitySpeciesID" self.fields['f_cs_ln'] = "LatinName" self.fields['f_cs_tid'] = "FK_SpeciesTypeID" self.fields['f_cs_att'] = "FK_AttachmentListID" self.fields['f_cs_cid'] = "FK_CommunityID" self.fields['f_cs_lsid'] = "LOV_SpeciesID" #Community table self.fields['f_com_id'] = "CommunityID" self.fields['f_com_type'] = "CommunityType" self.fields['f_com_il'] = "InitialLanguage" self.fields['f_com_sh'] = "ShortName" self.fields['f_com_n'] = "Name" self.fields['f_com_url'] = "WebSiteURL" self.fields['f_com_desc'] = "Description" self.fields['f_com_crd'] = "CreateDate" self.fields['f_com_aac'] = "AccepAnyCommunity" self.fields['f_com_ovaoi'] = "OnlyVerifyAreaOfIntrest" self.fields['f_com_olcs'] = "OnlyListCommSpecies" self.fields['f_com_orum'] = "OnlyRecordUploadedMedia" self.fields['f_com_lad'] = "LastActivityDate" self.fields['f_com_isa'] = "IsActive" self.fields['f_com_ish'] = "IsHidden" self.fields['f_com_attw'] = "FK_AttachmentListID_W" self.fields['f_com_attm'] = "FK_AttachmentListID_M"
else: slashsyntax = "/" if os.environ.get("TMP") == None: temp = "/usr/tmp" else: temp = os.environ.get("TMP") Connection_File_Name = temp + slashsyntax + "connection.sde" # Check for the .sde file and delete it if present if os.path.exists(Connection_File_Name): os.remove(Connection_File_Name) #Variable defined within the script; other variable options commented out at the end of the line saveUserInfo = "SAVE_USERNAME" #DO_NOT_SAVE_USERNAME saveVersionInfo = "SAVE_VERSION" #DO_NOT_SAVE_VERSION print "Creating ArcSDE Connection File..." # Create ArcSDE Connection File # Usage: out_folder_path, out_name, server, service, database, account_authentication, username, password, save_username_password arcpy.CreateArcSDEConnectionFile_management(temp, "connection.sde", server, service, database, account_authentication, username, password, saveUserInfo, version, saveVersionInfo) # Update statistics on system tables arcpy.AnalyzeDatasets_management(Connection_File_Name, "SYSTEM", "", "", "", "") print "Analyze Complete"
def sde_connect(self, database, server="<default server>", username="", password="", version="SDE.DEFAULT"): # Check if value entered for option try: # usage parameters for spatial database connection to upgrade server = server.lower() version = version.upper() database = database.lower() service = "sde:sqlserver:" + server account_authentication = 'OPERATING_SYSTEM_AUTH' # check if direct connection if service.find(":") != -1: # this is direct connect sde_conn_file_name = service.replace(":", "_") sde_conn_file_name = sde_conn_file_name.replace(";", "") sde_conn_file_name = sde_conn_file_name.replace("=", "") sde_conn_file_name = sde_conn_file_name.replace("/", "") sde_conn_file_name = sde_conn_file_name.replace("\\", "") else: arcpy.AddMessage("\n+++++++++") arcpy.AddMessage("Exiting!!") arcpy.AddMessage("+++++++++") sys.exit("\nSyntax for a direct connection in the Service parameter is required for geodatabase upgrade.") # local variables sde_conn_file_name = sde_conn_file_name + "_" + database + "_" + version.split('.')[-1].lower() sde_conn_file_path = self.app_files_path + os.sep + sde_conn_file_name + ".sde" if os.path.isfile(sde_conn_file_path): return sde_conn_file_path # Check for the .sde file and delete it if present arcpy.env.overwriteOutput=True # Variables defined within the script; other variable options commented out at the end of the line save_user_info = "" save_version_info = "" print("Creating ArcSDE Connection File...") print(self.app_files_path) print(sde_conn_file_name) print(server) print(service) print(database) print(account_authentication) print(username) print(password) print(save_user_info) print(version) print(save_version_info) arcpy.CreateArcSDEConnectionFile_management(self.app_files_path, sde_conn_file_name, server, service, database, account_authentication, username, password, save_user_info, version, save_version_info) 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") return sde_conn_file_path # check if no value entered for option except SystemExit as e: print e.code return