Loading MS SQL table into QGIS using PyQGIS

Question

I'm hoping to have some pieces of code on hand that link QGIS to a MS SQL database. All the tables in the database lack geometry, but have X Y Z coordinates. The database is password protected but I have the credentials. Lastly, I have loaded data through the normal Data Source Manager.

Is my approach with the URI is correct (which parts are the ConnectionName, DatabaseName, etc.) or is it the QgsVectorLayer loading a layer with no geometry?

from qgis.core import *
uri ="MSSQL:server=ConnectionName;database=DataBaseFromDataBaseList;tables=NameOfTable;trusted_connection=no"
vlayer = QgsVectorLayer(uri, "DisplayName", "ogr")
QgsProject.instance().addMapLayer(vlayer)

• Mapperz: related gis.stackexchange.com/a/232827/276

Answer 1

I figured out two solutions to this. This is for a MS SQL. Both involve knowing the ServerHost, Port, DatabaseName, Username, Password, Schema, Table.

1. loading just the table:

uri = QgsDataSourceUri()
uri.setConnection("Host", "Port", "DatabaseName", "Username", "Password")
connInfo = uri.connectionInfo()
uri.setDataSource("Schema", "Table","")
vlayer = iface.addVectorLayer(uri.uri(False), "NewName", "mssql")

As none of the tables have a geometry column, that is handled by the "" after "Table".

2. loading directly from the server into a geoprocessing to turn it into points

result=processing.runAndLoadResults("native:createpointslayerfromtable",
 { 'INPUT' : 'mssql://dbname=\'DatabaseName\' host=Host port=Port user=\'Username\' password=\'Password\' table="Schema"."Table"', 
 'MFIELD' : '', 
 'OUTPUT' : 'TEMPORARY_OUTPUT', 
 'TARGET_CRS' : QgsCoordinateReferenceSystem('EPSG:26913'), 
 'XFIELD' : 'easting', 
 'YFIELD' : 'northing', 
 'ZFIELD' : 'elevation' })