How to read SQL request result executed with Python OGR?

Question

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
– Midavalo ♦, Commented Mar 19, 2017 at 18:37

Mike T · Accepted Answer · 2017-03-19 21:48:43Z

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 DataSource → Layer → Feature → Field.

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.

answered Mar 19, 2017 at 21:48

Mike T

42.7k10 gold badges132 silver badges194 bronze badges

Add a comment |

Stack Exchange Network

How to read SQL request result executed with Python OGR?

1 Answer 1

Hot Network Questions

How to read SQL request result executed with Python OGR?

1 Answer 1

Related

Hot Network Questions