Category: SQL-Server

Getting started with Python

This article is about all you need to setup Python on Windows for a .Net Developer working on Windows using SQL Server.

Install Latest Python for Windows: https://www.python.org/downloads/windows/
-> My choice today: 3.10.3 Windows installer (64-bit)

Installing Python extension for Visual Studio Code which enables Jupiter Notebook
Installing Python for VSCode which brings syntax highlighting
Installing Python Extension Pack which supports intelliSense
Installing ODBC driver
pip install pyodbc

Sample code to run: 

import pyodbc 
conn_str = ("Driver={SQL Server};"
            "Server='tcp:myserver.database.windows.net;"
            "Database=mydb;"
            "UID=sqlUser;"
            "PWD=myPassWord;"
            "Trusted_Connection=no;") 
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
sql_str = ("SET NOCOUNT ON; \n"
           "select * from myTable"
          )
cursor.execute(sql_str)
for row in cursor:
    print(row)

Nice to read more: https://wiki.python.org/moin/BeginnersGuide/Programmers

Next:

  • Installing Jupiter Notebook from https://jupyter.org/install
  • Installing Pandas as part of Anaconda:
    https://pandas.pydata.org/pandas-docs/stable/getting_started/install.html

Finds Close Points by Distance

In this example I am trying to find the distance between the locations in a table and a given point. When I found the distance I will return thee result filtered by a allegible distance.

-- =============================================
-- Author:		Asghar Panahy
-- Create date: 28-Feb-2013
-- Description:	Zoekt objecten binnen bereik van gegeven punt
-- =============================================
ALTER PROCEDURE [dbo].[BereikbareObjecten] 
	-- Add the parameters for the stored procedure here
	@orig_lat REAL , 
	@orig_lng REAL,
	@binnenMeter integer
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	---------------------------------------------------------
	-- Select your SRID wisely. Don't follow me.
	-- select * from sys.spatial_reference_systems 
	-- where spatial_reference_id in (4937, 4258, 4326, 4269)
	DECLARE @SRID as int = 4326;
	---------------------------------------------------------
   
	DECLARE @orig geography;
	SET @orig = geography::Point(@orig_lat, @orig_lng, @SRID);
	
	SELECT  *,CONVERT(INT,  @orig.STDistance( geography::Point([object].[Latitude], [object].[Longitude], @SRID))) As [Distance]
	  INTO #MyTempTable
	  FROM [Object]  
	
	SELECT * FROM #MyTempTable 
         WHERE [Distance] <= @binnenMeter 
         ORDER BY [Distance]
	
END