def createEGDB():
database_platform = "SQL_Server"
instance_name = "localhost"
database_name = "edmar_test"
account_authentication = "DATABASE_AUTH"
database_admin = "sa"
database_admin_password = "******"
sde_schema = "DBO_SCHEMA"
authorization_file = r"C:\tempgis\pgtest\keycodes"
arcpy.CreateEnterpriseGeodatabase_management(
database_platform, instance_name, database_name,
account_authentication, database_admin, database_admin_password,
sde_schema, None, None, None, authorization_file)
def create_enterprise_GDB(self, gdb_name, host, dba, dbapass, sdeuser,
sdepass, keycode):
# gdb_name = country.lower()+'_'+product
#print(gdb_name)
arcpy.CreateEnterpriseGeodatabase_management("PostgreSQL", host,
gdb_name, "DATABASE_AUTH",
dba, dbapass,
"SDE_SCHEMA", sdeuser,
sdepass, "", keycode)
logging.debug("Enterprise Geodatabase {0} is created".format(gdb_name))
arcpy.CreateDatabaseConnection_management(
"Database Connections", "{0}.sde".format(gdb_name), "POSTGRESQL",
host, "DATABASE_AUTH", sdeuser, sdepass, "SAVE_USERNAME", gdb_name,
"#", "POINT_IN_TIME", "#", "5/19/2017 8:43:41 AM")
logger.debug(
"Database connection for geodatabase {0} is created".format(
gdb_name))
return None
def ARCGIS_create_database(gdbpath, time_ymdhms, datatype):
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
authorization_file = os.path.join(SCRIPT_DIR, u"server10.2.ecp")
database_name = datatype + time_ymdhms
arcpy.CreateEnterpriseGeodatabase_management(
database_platform=u"PostgreSQL",
instance_name=u"localhost",
database_name=database_name,
account_authentication=u"DATABASE_AUTH",
database_admin=u"postgres",
database_admin_password=u"Lantucx2018",
sde_schema=u"SDE_SCHEMA",
gdb_admin_name=u"sde",
gdb_admin_password=u"sde",
tablespace_name=u"#",
authorization_file=authorization_file)
connsdepath = SCRIPT_DIR
connsde = database_name + u".sde"
conn = {}
conn[u"out_folder_path"] = connsdepath
conn[u"out_name"] = connsde
conn[u"database_platform"] = u"PostgreSQL"
conn[u"instance"] = u"localhost"
conn[u"account_authentication"] = u"DATABASE_AUTH"
conn[u"database"] = database_name
conn[u"username"] = u"sde"
conn[u"password"] = u"sde"
conn[u"save_user_pass"] = u"SAVE_USERNAME"
arcpy.CreateDatabaseConnection_management(**conn)
arcpy.env.workspace = gdbpath
sdepath = os.path.join(SCRIPT_DIR, connsde)
for ds in arcpy.ListDatasets(feature_type=u'feature') + [u'']:
if ds != u'':
dspath = os.path.join(gdbpath, ds)
sdedspath = os.path.join(sdepath, ds)
arcpy.Copy_management(dspath, sdedspath)
else:
for fc in arcpy.ListFeatureClasses(feature_dataset=ds):
fcpath = os.path.join(gdbpath, ds, fc)
sdedspath = os.path.join(sdepath, ds, fc)
arcpy.Copy_management(fcpath, sdedspath)
print u'创建空间库成功'
return datatype + time_ymdhms + ".sde"
def createSDE(database_type, instance, database, account_authentication,
dbms_admin, dbms_admin_pwd, schema_type, gdb_admin,
gdb_admin_pwd, tablespace, license):
# 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:
arcpy.AddMessage("Creating enterprise geodatabase...")
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("++++++++++++++++++")
except:
for i in range(arcpy.GetMessageCount()):
arcpy.AddReturnMessage(i)
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",
)
authentication = 'DATABASE_AUTH' #type of authentication
databaseAdmin = '<dba>'
databaseAdminPass = '******'
schema = 'SDE_SCHEMA' #see doc, only relevant to SQL Server
gdbAdmin = '<gdb_admin>'
adminPass = '******'
tablespace = ''
authFile = r'<path_to_authorization_file>\<file_name>.ecp'
try:
# Create Enterprise Geodatabase.
print("Creating the enterprise geodatabase")
arcpy.CreateEnterpriseGeodatabase_management(platform, instance, database,
authentication, databaseAdmin,
databaseAdminPass, schema,
gdbAdmin, adminPass,
tablespace, authFile)
# Once the database has been created we will create an admin
# connection so that we can create users in it.
print("Creating connection to geodatabase as the DBA user")
adminConn = arcpy.CreateDatabaseConnection_management(r'<path_to_save_file>', '<file_name>.sde', platform, instance, authentication, \
databaseAdmin, databaseAdminPass,'', database, schema)
# First create a few roles for data viewers and data editors.
print("\tCreating the viewer and editor roles")
arcpy.CreateRole_management(adminConn, 'viewers')
arcpy.CreateRole_management(adminConn, 'editors')
# Next create users and assign them to their proper roles.
import arcpy
arcpy.CreateEnterpriseGeodatabase_management("SQL_SERVER", "tor\ssinstance1", "sp_data", "OPERATING_SYSTEM_AUTH", "", "", "SDE_SCHEMA", "sde", "sde",
"", "//myserver/Program Files/ESRI/License10.1/sysgen/keycodes")
" Creating an enterprise geodatabase requires an ArcGIS Desktop Standard or Advanced, ArcGIS Engine with the Geodatabase Update extension, or ArcGIS 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:
# If running outside of the Python window, uncomment the import
import arcpy
# allow python to overwrite existing data and files
arcpy.env.overwriteOutput = True
# Create the geodatabase
# Full tool documentation: Data Management Toolbox < Geodatabase Administration Toolset
# https://pro.arcgis.com/en/pro-app/tool-reference/data-management/create-enterprise-geodatabase.htm
# different database platforms have different requirements so refer to documentation for
# particulars to connect
arcpy.CreateEnterpriseGeodatabase_management('<your_platform>',
'<your_instance_name>',
'<database_name>', # see doc
'<authentication_type>', # see doc
'<your_dba_user_name>',
'<your_dba_password>',
'<sde_schema>', # see doc, only relevant to SQL Server
'<geodatabase_administrator_name>',
'<geodatabase_administrator_password>',
'', r'<path_to_your_license_file>')
# Create database connection
arcpy.CreateDatabaseConnection_management(r'<full_path_to_connection_file>', '<file_name>.sde', '<platform>', '<your_instance_name>', '<authentication_type>', '<your_dba_user_name>', '<dba_password>', '<save_user_pass>', '<database>', 'schema')
# Create an editor role
arcpy.CreateRole_management(r'<full_path_to_connection_file>\<file_name>.sde', 'editor')
# Create list of users
userList = ['matt', 'tom', 'colin']
# Create users and assign to editor role
#-------------------------------------------------------------------------------
import arcpy
import os
print("Start 2-CreateDatabase")
databasename = "EGT16"
connectionFolderPath = r"D:\EGT16"
connectionFilepath = os.path.join(connectionFolderPath,databasename+".sde")
roleName = None
dbInstance = "ESRIBX0373\SQLEXPRESS"
arcpy.env.overwriteOutput = True
print("Creating database")
#create database
arcpy.CreateEnterpriseGeodatabase_management("SQL_Server", dbInstance, databasename, "OPERATING_SYSTEM_AUTH",
"", "", "SDE_SCHEMA",
"sde", "sde","",r"D:\EGT16\Part 1 - Administration\keycodes")
# Create Connection
print("Creating connection")
arcpy.CreateDatabaseConnection_management(connectionFolderPath,databasename,"SQL_SERVER",
dbInstance,"OPERATING_SYSTEM_AUTH","","","SAVE_USERNAME",databasename)
## Create list of users
print("Creating users")
userList = ['jack', 'linda', 'bill']
## Create users and assign to editor role
for user in userList:
print("Creating user: {}".format(user))
arcpy.CreateDatabaseUser_management(connectionFilepath, "DATABASE_USER", user, "SomePassword01") #, roleName)
def createDataStores(agsServerAccount, password, dataDrive):
success = True
try:
opsServer = OpsServerConfig.getOpsServerRootPath(dataDrive)
environmentData = OpsServerConfig.getEnvDataRootPath(dataDrive)
dbConnFileRootPath = OpsServerConfig.getDBConnFileRootPath(dataDrive)
print
print "--Create Local Data Stores..."
# Create local variables to use for both creating enterprise
# geodatabases and creating connection files.
dbPlatform = "POSTGRESQL"
accountAuthentication = "DATABASE_AUTH"
dbAdmin = "postgres" #For PostgreSQL this is the postgres superuser
gdbAdminName = "sde" #For PostreSQL this must be 'sde'
# 2/26/2013: Modified to dynamically search for keycodes file instead of
# hardcoding to specific version of the software.
#pathList = ["Program Files", "ESRI", "License10.1", "sysgen", "keycodes"]
#authorizationFile = makePath("C", pathList)
pathList = ["Program Files", "ESRI"]
authorizationFile = findFilePath(makePath("C", pathList), "keycodes")
# Create list of database names to create connection file.
dbsToConnect = []
for db in dbsToCreate:
dbsToConnect.append(db)
# ---------------------------------------------------------------------
# Create local environment data folders
# ---------------------------------------------------------------------
if createLocalEnvFolders:
print "\n\t-Creating local environment data folders..."
foldersToCreate = []
foldersToCreate.append(environmentData)
foldersToCreate.append(dbConnFileRootPath)
if not os.path.exists(environmentData):
for folder in foldersToCreate:
print "\t\tCreating folder: " + folder
os.makedirs(folder)
print "\t\tDone."
print
changeOwnership(opsServer, agsServerAccount)
# ---------------------------------------------------------------------
# Create local enterprise databases
# ---------------------------------------------------------------------
if createEnterpriseGDBs:
print "\n\t-Creating local enterprise geodatabases" + \
" (this will take a few minutes)...\n"
for db in dbsToCreate:
print "\t\tCreating geodatabase '" + db + "'..."
arcpy.CreateEnterpriseGeodatabase_management(
dbPlatform, "localhost", db, accountAuthentication,
dbAdmin, password, "", gdbAdminName, password, "",
authorizationFile)
print "\t\tDone.\n"
# ---------------------------------------------------------------------
# Update PostgreSQL connection file to allow remote connections
# to environment databases
# ---------------------------------------------------------------------
if updatePostgreSQLConnectionFile:
connectionFilePath = findFilePath(rootPostgreSQLPath,
"pg_hba.conf")
print "\n\t-Updating PostgreSQL connection file to allow remote" + \
" connections to databases..."
print "\t\tFile: " + connectionFilePath
# Create list of PostgreSQL connection entries
postgreSQLConnEntries = []
postgreSQLConnEntries.append("host all all 0.0.0.0/0 md5") #IPv4
postgreSQLConnEntries.append("host all all ::/0 md5") #IPv6
# Determine if connection entry already exists in file
for postgreSQLConnEntry in postgreSQLConnEntries:
if findInFile(connectionFilePath, postgreSQLConnEntry):
#Entry already exists in file so remove entry from list
postgreSQLConnEntries.remove(postgreSQLConnEntry)
# Add connection entries
if len(postgreSQLConnEntries) > 0:
hbaFile = open(connectionFilePath, 'a')
for postgreSQLConnEntry in postgreSQLConnEntries:
hbaFile.write("{}\n".format(postgreSQLConnEntry))
hbaFile.close()
# Reload config file
print "\n\t-Reloading connection file..."
exeFile = findFilePath(rootPostgreSQLPath, "pg_ctl.exe")
confFolder = os.path.dirname(connectionFilePath)
exeCommand = "”" + exeFile + "” -D “" + confFolder + "” reload"
os.popen(exeCommand)
print "\t\tDone."
# ---------------------------------------------------------------------
# Create SDE connection files to environment geodatabases
# ---------------------------------------------------------------------
if createSDEConnectionFiles:
print "\n\t-Creating SDE connection files..."
for db in dbsToCreate:
outFile = dbsToCreate[db][1] + ".sde"
# Set output folder location
outFolder = dbConnFileRootPath
sdeFilePath = os.path.join(outFolder, outFile)
# If SDE connection file already exists, delete it.
if os.path.exists(sdeFilePath):
print "\t\t* Deleting existing file " + sdeFilePath
os.remove(sdeFilePath)
print "\t\tRe-creating connection file " + sdeFilePath
else:
print "\t\tCreating connection file " + sdeFilePath
arcpy.CreateDatabaseConnection_management(
outFolder, outFile, dbPlatform, servername,
accountAuthentication, gdbAdminName, password,
"SAVE_USERNAME", db.lower(), "#", "TRANSACTIONAL",
"sde.DEFAULT", "#")
print "\t\tDone.\n"
# Change ownership of sde file
changeOwnership(sdeFilePath, agsServerAccount)
except:
success = False
# Get the traceback object
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
# Concatenate information together concerning the error into a message string
pymsg = "PYTHON ERRORS:\nTraceback info:\n" + tbinfo + "\nError Info:\n" + str(
sys.exc_info()[1])
msgs = "ArcPy ERRORS:\n" + arcpy.GetMessages() + "\n"
# Print Python error messages for use in Python / Python Window
print
print "***** ERROR ENCOUNTERED *****"
print pymsg + "\n"
print msgs
finally:
# Return success flag
return success
