[GIS] How to add MS SQL table using python console

MySQLpyqgispythonqgis

I have MS SQL Server 2008 R2 database. How to add MS SQL Table using python console in QGIS?

Best Answer

Each data provider has its URI and its provider key. Once added via User Interface, it's easy to collect them and use them from the console.

Enter this statement in your python console, to collect the URI and key for all map layers:

for layer in QgsMapLayerRegistry.instance().mapLayers().values():
    uri = layer.dataProvider().dataSourceUri()
    providerKey = layer.dataProvider().name()
    print("URI:\n%s\nKey:\n%s\n" % (uri, providerKey))

to create a new map layer based on this information:

Vector Layer

vl = QgsVectorLayer( uri, 'MyLayerName', providerKey )
QgsMapLayerRegistry.instance().addMapLayers( [vl] )

Raster Layer

rl = QgsRasterLayer( uri, 'MyLayerName', providerKey )
QgsMapLayerRegistry.instance().addMapLayers( [rl] )

The key for MS SQL is 'mssql'

The uri follow the QgsDataSourceURI standard, which has an example in the PyQGIS cookbook chapter Loading Layers, Vector Layers. MS SQL works analogue to postgres.

The above example duplicates just one layer, but prints the necessary information to add each of them and gives an idea about the principle.

Related Question