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
