Issue to convert from PostgreSQL input to GPKG using Python GDAL API function gdal.VectorTranslate

Question

When I run the following code, it fails with message

The error is ERROR 1: SELECT from table mytable failed, no such table/featureclass.

from osgeo import gdal
gdal.UseExceptions()
gdal.VectorTranslate(

    '/tmp/out.gpkg', 
    "PG:host=localhost dbname=mydatabase user=username password=mypassword port=5432",

    SQLStatement='SELECT * FROM mytable',
    layerName='tablenameout'
)

The following alternate syntax failed too

gdal.VectorTranslate(  
    '/tmp/out.gpkg', 
    "PG:host=localhost dbname=mydatabase user=username password=mypassword port=5432",  
    options='-sql "SELECT * FROM mytable" -nln layernameout'
)

With ogr2ogr and providing the same input table name and database connexion, the conversion happened correctly.

ogr2ogr -f "GPKG" /tmp/out.gpkg \
"PG:host=localhost user=username dbname=mydatabase password=mypassword port=5432" \
-sql "SELECT * FROM mytable" \
-nln tablenameout

What do I miss in my code syntax to make the Python API gdal.VectorTranslate works to open the database with my query without error?

Answer 1

The issue was about opening implicitly with the "wrong" driver. Seen by setting CPL_DEBUG to ON. When I was trying to establish the connexion, I got

GDAL: GDALOpen(PG:host=localhost user=username dbname=mydatabase password=XXX port=5432, this=0x2972350) succeeds as PostGISRaster.

Forcing the driver to understand my data source was vector (PostgreSQL driver), the table in the SELECT can be then found

from osgeo import gdal
gdal.UseExceptions()
gdal.SetConfigOption('CPL_DEBUG', 'ON')
conn_string = "PG:host=localhost dbname=mydatabase user=username password=mypassword port=5432"
ds = gdal.OpenEx(conn_string, gdal.OF_VECTOR)
You can choose amongst following syntax
Syntax 1
gdal.VectorTranslate(
    '/tmp/out.gpkg',
    ds,
    options='-f GPKG -sql "SELECT * FROM mytable" -nln layernameout'
)
Syntax 2
gdal.VectorTranslate(
    '/tmp/out.gpkg',
    ds,
    SQLStatement='SELECT * FROM mytable',
    layerName='tablenameout',
    format='GPKG'
)
Syntax 3
myoptions = {
    "SQLStatement": "SELECT * FROM mytable",
    "layerName": "tablenameout",
    "format": "GPKG"
}
gdal.VectorTranslate(
    '/tmp/out.gpkg',
    ds,
    **myoptions
)
Syntax 4
gdal.VectorTranslate(
    '/tmp/out.gpkg',
    ds,
    options=gdal.VectorTranslateOptions(
        SQLStatement='SELECT * FROM mytable',
        layerName='tablenameout',
        format='GPKG'
    )
)
ds = None