2

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)
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
Dan Coutts
  • 63
  • 3

1 Answers1

2

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".

  1. 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' })
Dan Coutts
  • 63
  • 3