How To: Connect to MS-SQL From Python 2 Inside Linux

Recently I have been working on a machine learning project that requires Python 2.x and we needed to connect to an MS-SQL server to pull and push data.

My initial thinking was to use pyodbc which is a Python based ODBC bridge. It is open-source (on GitHub), easy to install and use.

I am running Ubuntu on Windows which is freely available on the Microsoft Store at https://www.microsoft.com/en-us/p/ubuntu/9nblggh4msv6 . All of the instructions below will work for any Ubuntu 18.1x though, you don’t have to be running it on Windows.

Before you install pyodbc though, you’ll want to make sure you have already installed the Microsoft ODBC driver. Luckily Microsoft has a published article with explicit instructions for installing the driver on Debian, RedHat, SUSE, Ubuntu and even MacOS. You can find the detailed instructions here:
https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017

Here is the exact code I used for my Ubuntu 18.04.2 OS

sudo su 
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install msodbcsql17
sudo ACCEPT_EULA=Y apt-get install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
sudo apt-get install unixodbc-dev

After you’ve installed your Microsoft ODBC drive you can then just run a pip install for ODBC:

pip install pyodbc

Now that the driver has been installed, and pyodbc has been installed you can easily create a new connection from Python into your MS-SQL server like this:

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=[SERVER];'
'Database=[DATATBASE_NAME];'
'Uid=[USERNAME];'
'Pwd=[PASSWORD];', autocommit = True)

You will need to replace:
[SERVER] text with server URL or IP address of your server
[DATABASE_NAME] text with your database name
[USERNAME] text with the username to connect to your database with
[PASSWORD] text with the password to connect to your database with

So you may end up with something like this:

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
'Server=mysqlserver.database.windows.net;'
'Database=ConocoProductionDB;'
'Uid=bsmith;'
'Pwd=TetPp3CMGE4zvR7B;', autocommit = True) 

Visual Studio “Duplicate ‘Content’ items were included” Fix

Recently migrated a project from one machine to another.  Original machine was running Visual Studio 2015 Professional, and the new machine Visual Studio 2017 Professional.  Once I grabbed the source code from our Git repo I could not longer build the project.  When I tried I received this error within Visual Studio:

“Duplicate ‘Content’ items were included. The .NET SDK includes ‘Content’ items from your project directory by default. You can either remove these items from your project file, or set the ‘EnableDefaultContentItems’ property to ‘false’ if you want to explicitly include them in your project file. For more information, see https://aka.ms/sdkimplicititems. The duplicate items were:”

error

There is an easy fix though!  Shorthand is, just “exclude” then “include” your “wwwroot” directory.  This will make the necessary changes to your project.  I included step by step instructions below.

Step 1: Show all files in Visual Studio

Within in Visual Studio, inside your Solution Explorer tab you’ll want to enable “Show All Files”.  This will help you re-include the “wwwroot” directory later on.

Step1

Step 2: Exclude “wwwroot” folder from your project

Step2

Step 3: Include “wwwroot” folder in your project

Step3

That’s it!  You should be able to re-build your project now.

Android – Galaxy S7 Edge – SMS Hyperlink Bug (URL’s with # hash or hashtag)

I’m currently working on an application that sends text messages to some of its users.  In that text message we send a URL for the end user to click.  iOS, Windows Phone, and Android will all show their end users a link for the URL so they can easily click it right from their messaging app!  Great!  Except one thing…

Android does not support hash/hashtag/number sign/hashbang/whatever you call it URL’s!

So if you send a URL like, https://appdomain.com/#/login, the Android user will be sent to https://appdomain.com/ while iOS and Windows Phone will direct their users correctly.

I tried searching around to see if I can find a reason for it, but alas to no avail.

My workaround was to create a page, https://appdomain.com/redirect/ which took in the same query string variables, and just used raw JavaScript to send the user to the correct URL (window.location).  SMS message URL link was tested on iOS, Windows Phone, and Android and all three worked!

*Side note:  I believe the “perfect” long term solution would be to use a “URL Shortening Service”.*

.NET Authentication In WebAPI 2 With Cross Domain Support

Recently I had a project requirement to create a .NET WebAPI 2 project that supported authentication as well as cross domain support (CORS / Cross-Origin Resource Sharing).  This will allow external domains to POST requests to the authentication API, and we will return an OAuth token that can be used in later WebAPI requests.

All .NET code examples below are in C#.

First start a new “ASP.NET Web Application” project.  Select the “WebAPI” template, and make sure “Individual User Accounts” is selected.

Second, use NuGet to install the following packages:

  • Microsoft.AspNet.Cors
  • Microsoft.AspNet.WebApi.Cors
  • Microsoft.Owin.Cors

These packages will enable you to configure CORS for API and Owin calls.

Under “App_Start/Startup.Auth.cs” inside the function “ConfigureAuth” add this line of code to the top of the function (yes, having it be the first line is very important).

public void ConfigureAuth(IAppBuilder app)
{
    // Allow cross domain authentication requests
    app.UseCors(Microsoft.Owin.Cors.CorsOptions.AllowAll);

    // ..... The rest of the code for the function goes below here

Please note, this line will allow requests from ANY external domain. If you want to lock down which domains have rights to post to the authentication API you’ll need more configuration at this point. Typically you will NOT want requests from any domain, and will have a list of accepted domains.

Your login URL will be “/Token”. This can be adjusted in “App_Start/Startup.Auth.cs” where “OAuthOptions” is defined. Specifically the “TokenEndpointPath” is the parameter that controls the route.

When POST’ing to “/Token” you’ll send three parameters.  The “grant_type” is a static string which will be set to “password”.  The “username” and “password” parameters will be the username and password you are attempting to authenticate with.

var loginData = {
    grant_type: 'password',
    username: 'yourusername',
    password: 'yourpassword'
};

If your POST is successful, the response will contain an “access_token”. For all future WebAPI calls you’ll want to set an “Authorization” header to “Bearer ” followed by your access_token you received from your initial login.

Here are some resources that helped me with my project!

Sample .NET C# WebAPI project with local individual accounts.
http://www.asp.net/web-api/overview/security/individual-accounts-in-web-api

Explanation of posting form data in AngularJS
http://www.bennadel.com/blog/2615-posting-form-data-with-http-in-angularjs.htm

AngularJS and MomentJS – Formatting UTC to local time using a custom filter

Frequently in our app, we need to show date/time’s which are stored in our database as UTC time.  From a users perspective however, it is best to view datetime’s in their local timezones.

MomentJS is a great library that is used pretty heavily to display date/time’s in JavaScript in a friendly manner.  Combining MomentJS with AngularJS (and with a small custom filter).

We use “angular-moment” which provides Moment.JS directives for Angular.JS (timeago and more).

https://github.com/urish/angular-moment

We also like to have some conformity for formatting date/times throughout our app. Centralizing the formatting allows use to easily change the style/layout/format of our date/time’s and have that change occur throughout our entire app!

Once you have “angular-moment” running in your AngularJS app, add the custom filter to your AngularJS app.

Our custom filter: (where “angularJSApp” is the name of your AngularJS app)

var angularJSApp = angular.module('angularJSApp', ['angularMoment']);

angularJSApp.filter('UTCToNow', ['moment', function (moment) {
     return function (input, format) {
            if(format)
            {
                return moment.utc(input).local().format('dddd, MMMM Do YYYY, h:mm:ss a');
            }
            else
            {
                return moment.utc(input).local();
            }
        };
    }]
);

How to use the custom filter in our HTML:

{{dateCreated | UTCToNow: true }}
<br />
<span am-time-ago="dateCreated | UTCToNow"></span>

You should now see your date/time in your local timezone

AngularJS UI Bootstrap – Typeahead – Filtering by multiple properties with custom template

I’ve been working on an AngularJS v1.4.7 application that uses “angular-ui-bootstrap” v 1.1 to incorporate Bootstrap components in our AngularJS app.

UI Bootstrap Bootstrap components written in pure AngularJS by the AngularUI Team
https://angular-ui.github.io/bootstrap/

We have been using the “Typeahead” component pretty heavily and I just wanted to make a quick post about how we are using the component, specifically with regards to the custom template, and the ability to filter by multiple items.

https://angular-ui.github.io/bootstrap/#/typeahead

We use the Typeahead component to list objects and allow user selection. Below is a code snippet which shows an AngularJS Bootstrap UI Typeahead component which is filtering on a persons first name, or last name and is using a custom HTML template to display results.

<input type="text" ng-model="cctrl.Person" typeahead-template-url="personTemplate" typeahead-min-length="0" uib-typeahead="person as (person.first_name + ' ' +person.last_name) for person in cctrl.data | filter:{name:$viewValue}" class="form-control" placeholder="Select person" />
<script type="text/ng-template" id="personTemplate">
<a>
     {{match.model.first_name}}&nbsp;{{match.model.first_name}}
     <div class="row small-text">
          <div class="col-sm-4">
               <strong>Address</strong>
               {{match.model.address1}}&nbsp;{{match.model.address2}}
               <br/>
               {{match.model.city}},{{match.model.state}} {{match.model.zipCode}}
          </div>
          <div class="col-sm-4">
               <strong>Phone</strong>
               <br/>
               {{match.model.phone}}
          </div>
          <div class="col-sm-4">
               <strong>Email</strong>
               <br/>
               {{match.model.email}}
          </div>
     </div>
     <hr />
</a>
</script>

The “uib-typeahead” attribute is where you put what fields you would like to filter by.

uib-typeahead="person as (person.first_name + ' ' +person.last_name) for person in cctrl.data | filter:{name:$viewValue}" class="form-control"

If you want to add email and phone filtering to the Typeahead you would just add a space behind your last filter, and then add the new filter you want.

uib-typeahead="person as (person.first_name + ' ' +person.last_name + ' ' + person.email + ' ' + person.phone) for person in cctrl.data | filter:{name:$viewValue}" class="form-control"

The template is just controlled by the “typeahead-template-url” attribute on the Typeahead element and the HTML contained within the “” element. Whatever ID you give your script element will be the value for your “typeahead-template-url” attribute.

typeahead-template-url="personTemplate"
<script type="text/ng-template" id="personTemplate">
</script>

“Just Let Me Code!”

http://beta.slashdot.org/story/204979

LOL!  I have certainly felt like this before.  Knowing what I “love” to do and what I “have” to do.  We always talk about avoiding the “M & M’s”.  Managers and meetings.  Time sinks that can easily corrupt a development plan.  Getting into coding was so easy.  You pick things up, learn, and just hammer away at the keyboard.  Once it becomes a profession, it changes.  Schedules, deadlines, technology you didn’t get to choose.  It becomes more of a dodge and weave scenario vs a creative process.