Accessing SQL Server Data in Python

So you want to access Microsoft SQL Server from your Python script(s)?

After weeding out some long-abandoned and/or nonworking solutions, I discovered a very simple Python ODBC driver that works with virtually all SQL Servers since MSSQL 2005 called "pyodbc". 

First, you will need to install this MSSQL ODBC (13.1 or 17 should work) component on your machine in addition to installing the pyodbc driver.

Next, get the pyodbc module for Python by running this from Windows command prompt:

pip install pyodbc

Then open up a python shell using 'py' or 'python' and enter the following after editing configuration values to match your development environment:

 import pyodbc  
 cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=WideWorldImporters;UID=DemoUser;PWD=123Password')  
 cursor = cnxn.cursor()  
 #Sample of a simple SELECT  
 cursor.execute("SELECT TOP (100) Comments, count(*) FROM WideWorldImporters.Sales.Orders GROUP BY Comments")  
 row = cursor.fetchone()   
 while row:   
   print(row[0] + ': ' + str(row[1]))  
   row = cursor.fetchone()  

Running this code will result in the below if you have configured everything correctly (note this example makes use of the Microsoft SQL Server demo WorldWideImporters database):

Reference: https://docs.microsoft.com/en-us/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-2017

.udl for DbConnection Check

This is a useful method to quickly check SQL credentials and/or RDBMS connectivity if working on a Windows OS. Just create a file in any editor (ie. Notepad) and save it with .udl extension which makes it a Microsoft Data Link file type. Then, right-click and inspect file Properties >> "Connection" tab.

Credit to a former colleague of mine (thanks Gene David!) who showed me how to use this simple but very useful trick.

Reference: https://blogs.msdn.microsoft.com/farukcelik/2007/12/31/basics-first-udl-test/

Short Selling

Broker borrows a share, sells the share high, repurchases share at lower price ($) and returns it.

Short selling stock is the practice by which a broker borrows stock with the hope that the price of that stock will fall so that he or she can sell at a high price, (re)purchase at a lower price, and pocket the difference.

Hypothetically, let's say a trader named Joe firmly believed that Apple, Inc. was about to experience a large drop in share price. To short a single share of Apple stock, Joe would do the following:

1). Borrow a share of APPL from his portfolio, a client portfolio, or a fellow broker

2). Sell the share at the highest a price they can find before a drop (say 1 share of AAPL at current $157.76)

3). Wait for the price to fall (say APPL falls to $102.76), then purchase one share at this lower price

4). Subtract the higher price from the lower price (less fees) and return the borrowed share. 

Joe earns a cool $53 bucks from this scheme as he sold at $157.76 and bought back for just $102.76. After fees of $2.00 this is $157.76 - $102.76 -$2.00 == $53.00.

While the idea of selling something short of true value is often associated with the nefarious case of a stock "short" like this, oftentimes it is a necessity. The market always needs people on both the long end (owners/buyers) and the short end (renters/sellers) for it to work properly.

This is why banks who are on the hook with a property that they cannot sell will ultimately agree to a "short sale" (selling the home for below its fair market value) to recoup at least some of their losses.

A combination of consumer preferences and financial factors determine whether to go long or short on any kind of investment or large financial transaction.

Short selling doesn't always work in the sellers favor