topic Re: How to get list of views in EGDB in Python Questions

How to get list of views in EGDB

OlivierLefevre — Mon, 21 Jun 2021 13:02:22 GMT

Hello,

I'm trying to create a python script using arcpy.AnalyzeDatasets_management in order to maintain my Enterprise Geodatabase (PostgreSQL).

When listing thef eature classes I also get the views I created in the EGDB and arcpy.AnalyzeDatasets_management provide me with error messages like 'Could not analyze dataset <view name>'.

As I don't know if the tool fails about the entire dataset list or only about those views, I decided to remove those views from the list I provide arcpy.AnalyzeDatasets_management with.

To this end, I use arcpy.ArcSDESQLExecute to execute this query :

SELECT table_name as view_name FROM information_schema.views WHERE table_schema not in ('information_schema', 'pg_catalog', 'sde')

The problem I'm facing is that I get the error message AttributeError: ArcSDESQLExecute: StreamBindOutputColumn ArcSDE Error -65 Invalid pointer argument to function.

And I don't know how to deal with.

Could you please help me ? Why do I get this error ?

Is there another way to list views ? Or is it possible not get them when listing feature classes ?

Here is my code so far :

import arcpy import os # Set the workspace environment workspace = r"path_to\my_egdb_connector.sde" arcpy.env.workspace = workspace # Get a list of all the datasets the user has access to. # First, get all the stand alone Tables, Feature Classes and Rasters. dataList = arcpy.ListTables() + arcpy.ListFeatureClasses() + arcpy.ListRasters() # Get a list of all stand alone Relationship Classes and add them to the master list dataList += [c.name for c in arcpy.Describe(workspace).children if c.datatype == "RelationshipClass"] # Next, for feature datasets get all of the Feature Classes and Relationship Classes and add them to the master list. for dataset in arcpy.ListDatasets("", "Feature"): arcpy.env.workspace = os.path.join(workspace, dataset) FCs = arcpy.ListFeatureClasses() RCs = [c.name for c in arcpy.Describe(arcpy.env.workspace).children if c.datatype == "RelationshipClass"] dataList += FCs + RCs # Reset the workspace arcpy.env.workspace = workspace # Finally, get a list of all views and delete them from the master list # Connect to the GDB egdb_conn = arcpy.ArcSDESQLExecute(workspace) #### HERE COMES THE PROBLEM #### #Execute SQL sql = r"select table_name as view_name from information_schema.views where table_schema not in ('information_schema', 'pg_catalog', 'sde')" egdb_return = egdb_conn.execute(sql) location_in_egdb = arcpy.Describe(workspace).connectionProperties.database + "." + arcpy.Describe(workspace).connectionProperties.user.lower() + "." if isinstance(egdb_return, str): views = [location_in_egdb + egdb_return] elif isinstance(egdb_return, list): views = [location_in_egdb + i[0] for i in egdb_return] dataList = [data for data in dataList if data not in views] # Execute analyze datasets arcpy.AnalyzeDatasets_management(workspace, "NO_SYSTEM", dataList, "ANALYZE_BASE","ANALYZE_DELTA","ANALYZE_ARCHIVE")

Re: How to get list of views in EGDB

JohannesLindner — Mon, 21 Jun 2021 13:23:41 GMT

I don't know why your SQL query fails (it works for me), so I will concentrate on your actual problem.

Including views in the dataset list doesn't give any errors for me.

I guess you already know the help page, because your code looks very similar (as does mine...), but still:
https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/analyze-datasets.htm

Points of notice:

AnalyzeDatasets can only analyze datasets that are owned by the connected database user!
I don't think that function is for relationship classes. At least I got an error when I tried right now.

Try removing the datasets the connected user doesn't own (copied from the help page):

dataList = arcpy.ListTables() + arcpy.ListFeatureClasses() + arcpy.ListRasters() # Next, for feature datasets get all of the datasets and featureclasses # from the list and add them to the master list. for dataset in arcpy.ListDatasets("", "Feature"): arcpy.env.workspace = os.path.join(workspace,dataset) dataList += arcpy.ListFeatureClasses() + arcpy.ListDatasets() # reset the workspace arcpy.env.workspace = workspace # Get the user name for the workspace userName = arcpy.Describe(workspace).connectionProperties.user.lower() # remove any datasets that are not owned by the connected user. userDataList = [ds for ds in dataList if ds.lower().find(".%s." % userName) > -1] # more readable: # userDataList = [ds for ds in dataList if userName in ds.lower()] # Execute analyze datasets # Note: to use the "SYSTEM" option the workspace user must be an administrator. arcpy.AnalyzeDatasets_management(workspace, "NO_SYSTEM", userDataList, "ANALYZE_BASE","ANALYZE_DELTA","ANALYZE_ARCHIVE")

Re: How to get list of views in EGDB

OlivierLefevre — Mon, 21 Jun 2021 14:22:39 GMT

Thanks Johannes for your answer.

All the datasets in the PGSQL schema are owned by the connected user so I skipped the datasets removal.

I agree that arcpy.AnalyzeDatasets_management is not supposed to deal with Relationship Classes. But as it doesn't produce any error when I list RCs in ArcGIS Pro geoprocessing tool, I kept those RCs in arcpy script.

Did you try the SQL query from arcpy.ArcSDESQLExecute or from pgAdmin ? The second worked for me.

Re: How to get list of views in EGDB

JohannesLindner — Mon, 21 Jun 2021 14:36:25 GMT

All the datasets in the PGSQL schema are owned by the connected user so I skipped the datasets removal.

Is this also true for the views? Maybe try it anyway...

I just thought about this: the documentation doesn't say whether it works for views that aren't registered with the database. Have you rgistered your views (I have and don't get errors)?

Did you try the SQL query from arcpy.ArcSDESQLExecute or from pgAdmin ? The second worked for me.

I used ArcSDESQLExecute, but I work with a Microsoft SQL Server, not PostgreSQL.

Re: How to get list of views in EGDB

BlakeTerhune — Mon, 21 Jun 2021 15:23:09 GMT

I'm on Oracle (12c) and have a similar problem. My solution was to simply put my analyze datasets (and the rebuild indexes) functions in their own try/except where I pass on the error from the views. However, if it's as simple as registering the views as @JohannesLindner mentions, I would rather do that.

Re: How to get list of views in EGDB

OlivierLefevre — Tue, 22 Jun 2021 11:09:02 GMT

With the SQL query below, I finally managed to get the views from PGSQL with arcpy.ArcSDESQLExecute :

SELECT table_name::varchar(100) as view_name FROM information_schema.views WHERE table_schema not in ('information_schema', 'pg_catalog', 'sde')