Merging layers via Python OGR ExecuteSQL

I have two tables with a common ID field that I would like to merge.

Is it possible to do this through python OGR? I am attempting to use the ExecuteSQL() command but am getting an error that table2 doesn't exist.

Here is my code:

connection = ogr.Open(connString)
connection2 = ogr.Open(connString2)

layer1 = connection.GetLayer()
layer2 = connection2.GetLayer()

newlayer = connection.ExecuteSQL("SELECT table1.field1, table1.field2, table2.field2 FROM table1 INNER JOIN table2 ON table1.field1 = table2.field1",  dialect = "SQLITE")

Is this something that can be done through OGR? Or should I be looking into something else?

Answer

You have two separate datasources and your join does not work across them. There may be other solutions but a simple one is to combine the datasources into one with OGR VRT http://www.gdal.org/drv_vrt.html.

Lets take shapefile a with attributes "id" and "a", and shapefile b with attributes "id" and "b". Wrap them with VRT file ab.vrt

    
        a.shp
        
    
        b.shp

    

Test with ogrinfo

ogrinfo ab.vrt
INFO: Open of `ab.vrt'
      using driver `OGR_VRT' successful.
1: a (Point)
2: b (Point)

Good, we have two layers in one source. Now use the SQL that you provided for ogrinfo

ogrinfo -dialect sqlite -sql "SELECT a.a, b.b FROM a INNER JOIN b ON a.id=b.id" ab.vrt
INFO: Open of `ab.vrt'
      using driver `OGR_VRT' successful.

Layer name: SELECT
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)

a: String (0.0)
b: String (0.0)
OGRFeature(SELECT):0
  a (String) = value from a.a
  b (String) = value from b.b

We have one feature with attributes "a" and "b".

Now just use the SQL part in your Python code and you should get the desired result.