SQL request with a shapefile data with PYTHON

You could use feature = layer.GetNextFeature() to get the first feature and then use feature["Lake_name"] to get your desired field.

Seems like using layer[0] does not take the SQL query into account. Using dialect="SQLITE" does (surprisingly) work, however, it may be better not to use it with large datasets.

Note: You may also want to consider using ogr_ds.ReleaseResultSet(layer) and feature.Destroy() when you finished.

Using SQL Query and OGR

from osgeo import ogr

# data downloadable from here: https://www.hydrosheds.org/page/hydrolakes
shapefile = r"data\HydroLAKES_polys_v10_shp\HydroLAKES_polys_v10.shp"

driver = ogr.GetDriverByName("ESRI Shapefile")
data_source = driver.Open(shapefile, 0) 

layer = data_source.GetLayer()
query = f"SELECT * FROM {layer.GetName()} WHERE Lake_name = 'Baikal'"
result = data_source.ExecuteSQL(query)

feature = result.GetNextFeature()
print(feature["Lake_name"]) # prints Baikal

feature.Destroy()
data_source.ReleaseResultSet(result)

Using an Attribute Filter (instead of SQL) and OGR

from osgeo import ogr

# data downloadable from here: https://www.hydrosheds.org/page/hydrolakes
shapefile = r"data\HydroLAKES_polys_v10_shp\HydroLAKES_polys_v10.shp"

driver = ogr.GetDriverByName("ESRI Shapefile")
data_source = driver.Open(shapefile, 0) # read-only

layer = data_source.GetLayer()
layer.SetAttributeFilter("Lake_name = 'Baikal'")

feature = layer.GetNextFeature()
print(feature["Lake_name"]) # prints Baikal

feature.Destroy()

Using GDAL instead of OGR

There is a note in the API documentation that OGR's data_source.ExecuteSQL(query) is deprecated:

Deprecated Use GDALDatasetExecuteSQL() in GDAL 2.0

See also:

• https://gdal.org/python/osgeo.ogr.DataSource-class.html#ExecuteSQL

Following an example using GDAL. It's pretty similar.

from osgeo import gdal

# data downloadable from here: https://www.hydrosheds.org/page/hydrolakes
shapefile = r"data\HydroLAKES_polys_v10_shp\HydroLAKES_polys_v10.shp"

data_source = gdal.OpenEx(shapefile, gdal.OF_VECTOR)

layer = data_source.GetLayer()
query = f"SELECT * FROM {layer.GetName()} WHERE Lake_name = 'Baikal'"
result = data_source.ExecuteSQL(query)

feature = result.GetNextFeature()
print(feature["Lake_name"])

feature.Destroy()
data_source.ReleaseResultSet(result)

Using dialect="SQLITE"

It looks like layer[0]["Lake_name"] works when using dialect="SQLITE" but it is slow since it queries the full table at once. I recommend to use result.GetNextFeature() instead of following example.

# data downloadable from here: https://www.hydrosheds.org/page/hydrolakes
shapefile = r"data\HydroLAKES_polys_v10_shp\HydroLAKES_polys_v10.shp"

driver = ogr.GetDriverByName("ESRI Shapefile")
data_source = driver.Open(shapefile, 0) 

layer = data_source.GetLayer()
query = f"SELECT * FROM {layer.GetName()} WHERE Lake_name = 'Baikal'"
result = data_source.ExecuteSQL(query, dialect="SQLITE")

print(result[0]["Lake_name"]) # prints Baikal

for feature in result:
    feature.Destroy()
data_source.ReleaseResultSet(result)

Using Geopandas instead of OGR

Note: Since the shapefile is huge (1GB+), a solution with Geopandas is not recommended though unless you have endless memory and a power machine. ;) Running the code below on my machine with your shapefile required at its peak 6 GB RAM.

However, it may help some other users which work with a smaller shapefile. That's why I leave it here.

import geopandas as gpd

# data downloadable from here: https://www.hydrosheds.org/page/hydrolakes
shapefile = r"data\HydroLAKES_polys_v10_shp\HydroLAKES_polys_v10.shp"

lakes = gpd.read_file(shapefile)
lakes = lakes[lakes["Lake_name"] == "Baikal"]
print(lakes.head(1))