1

I'm trying to manage certain operations depending on PostGIS version and schema.

To get PostGIS version and schemas list, I'm used to execute SQL statements, respectively:

SELECT PostGIS_full_version();
SELECT nspname FROM pg_catalog.pg_namespace;

So I'm trying to get the same information using Python GDAL:

# Python 2.7.12 and GDAL 2.1.3
conn_settings = "PG: host={} port={} dbname={} user={} password={}"\
                .format(host, port, db_name, user, password)

conn = ogr.Open(str(conn_settings))

# prepare SQL requests
sql_version = str("SELECT PostGIS_full_version();")
sql_schemas = str("select nspname from pg_catalog.pg_namespace;")

# executing SQL
pg_version = conn.ExecuteSQL(sql_version)
pg_schemas = conn.ExecuteSQL(sql_schemas)

print(pg_version)
>>> <osgeo.ogr.Layer; proxy of <Swig Object of type 'OGRLayerShadow *' at 0x08A54C08> >

print(type(pg_version))
>>> <class 'osgeo.ogr.Layer'>

print(dir(pg_version))
>>> ['AlterFieldDefn', 'Clip', 'CommitTransaction', 'CreateFeature', 'CreateField', 'CreateFields', 'CreateGeomField', 'DeleteFeature', 'DeleteField', 'Dereference', 'Erase', 'FindFieldIndex', 'GetDescription', 'GetExtent', 'GetFIDColumn', 'GetFeature', 'GetFeatureCount', 'GetFeaturesRead', 'GetGeomType', 'GetGeometryColumn', 'GetLayerDefn', 'GetMetadata', 'GetMetadataDomainList', 'GetMetadataItem', 'GetMetadata_Dict', 'GetMetadata_List', 'GetName', 'GetNextFeature', 'GetRefCount', 'GetSpatialFilter', 'GetSpatialRef', 'GetStyleTable', 'Identity', 'Intersection', 'Reference', 'ReorderField', 'ReorderFields', 'ResetReading', 'RollbackTransaction', 'SetAttributeFilter', 'SetDescription', 'SetFeature', 'SetIgnoredFields', 'SetMetadata', 'SetMetadataItem', 'SetNextByIndex', 'SetSpatialFilter', 'SetSpatialFilterRect', 'SetStyleTable', 'StartTransaction', 'SymDifference', 'SyncToDisk', 'TestCapability', 'Union', 'Update', '__bool__', '__class__', '__delattr__', '__dict__', '__doc__', '__format__', '__getattribute__', '__getitem__', '__hash__', '__init__', '__iter__', '__len__', '__module__', '__new__', '__nonzero__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', 'next', 'schema', 'this', 'thisown']

print(pg_version.GetMetadata_Dict())
>>> {}

Is it possible to get SQL result as a string object (or dict...)?

  • Can you edit your question to add an example of how you'd like to see the result, and an example of what you're getting now? – Midavalo Mar 19 '17 at 18:37
2

OGR's ExecuteSQL only returns a Layer handle, as your question demonstrates, so it is really limited for general-purpose SQL work. To use OGR, it's important to understand the data model of DataSourceLayerFeatureField.

For example:

ly = conn.ExecuteSQL(sql_version)
feat = ly.GetNextFeature()  # first and only row
version = feat.GetField(0)  # first and only field index

However, you will find this may not work for general-purpose queries. I normally recommend psycopg2 for most general-purpose queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.