import ogr
import os
try:
    os.remove('test.sqlite')
except:
    pass
ds = ogr.GetDriverByName('SQLite').CreateDataSource('test.sqlite')
lyr = ds.CreateLayer('test')
feat = ogr.Feature(lyr.GetLayerDefn())
feat.SetGeometry(ogr.CreateGeometryFromWkt('POINT(0 1)'))
lyr.CreateFeature(feat)
feat = ogr.Feature(lyr.GetLayerDefn())
feat.SetGeometry(ogr.CreateGeometryFromWkt('LINESTRING(2 3)'))
lyr.CreateFeature(feat)
feat = ogr.Feature(lyr.GetLayerDefn())
feat.SetGeometry(ogr.CreateGeometryFromWkt('POLYGON((4 5,6 7))'))
lyr.CreateFeature(feat)
ds = None

ds = ogr.Open('test.sqlite')
print('Test 1')
lyr = ds.ExecuteSQL('select OGR_GEOMETRY from test')
if lyr is None:
    print('this doesnt work but it is expected because OGR_GEOMETRY isnt recognized by sqlite')
else:
    lyr.SetAttributeFilter("OGR_GEOMETRY = 'POLYGON'")
    feat = lyr.GetNextFeature()
    while feat is not None:
        feat.DumpReadable()
        feat = lyr.GetNextFeature()

print('\nTest 2')
lyr = ds.ExecuteSQL('select * from test')
lyr.SetAttributeFilter("OGR_GEOMETRY = 'POLYGON'")
feat = lyr.GetNextFeature()
while feat is not None:
    feat.DumpReadable()
    feat = lyr.GetNextFeature()
print('this works. the where clause isnt propagated to sqlite')

print('\nTest 3')
lyr = ds.GetLayerByName('test')
lyr.SetAttributeFilter("OGR_GEOMETRY = 'POLYGON'")
feat = lyr.GetNextFeature()
while feat is not None:
    feat.DumpReadable()
    feat = lyr.GetNextFeature()
print('this doesnt work. the where clause *IS* propagated to sqlite that doesnt like it')

print('\nTest 4')
lyr = ds.ExecuteSQL('select OGR_GEOMETRY from test', dialect = 'OGRSQL')
lyr.SetAttributeFilter("OGR_GEOMETRY = 'POLYGON'")
feat = lyr.GetNextFeature()
while feat is not None:
    feat.DumpReadable()
    feat = lyr.GetNextFeature()
print('this works too. the where clause isnt propagated to sqlite')

print('\nTest 5')
lyr = ds.ExecuteSQL("select OGR_GEOMETRY from test WHERE OGR_GEOMETRY = 'POLYGON'", dialect = 'OGRSQL')
feat = lyr.GetNextFeature()
while feat is not None:
    feat.DumpReadable()
    feat = lyr.GetNextFeature()
print('this doesnt work. the where clause in that case *IS* propagated to sqlite. Same as test 3')