As you all know with the release of PowerShell Open Source and SQL Server on Linux are open doors to cross-platform developments. But is has been going on for awhile as Linux languages like Python, Ruby, Java and PHP has been already available for Windows.
So, all comes down in setting your tools to allows cross-platform development in order to minimize the headaches of tweaking or altering your code between environments.
As PowerShell is the main technology for automation in Windows Datacenters Infrastructure, now becomes clear its importance in other non-Windows Operating Systems. The way I see it, Windows Admin has the advantage with PowerShell as the Linux Admin has the upper hand with Bash/PHP/Python. Here’s where we can collaborate and work with each other.
PowerShell and Python
I’m assumning you have already have installed PowerShell Open Source (powershell_6.0.0-Alpha.xx) as Windows PowerShell already exist.
We need to make sure we install Python in both Windows and Linux environments.
1. Download Python for Windows: https://www.python.org/
2. Select Python version: 2.7 or 3.5 (I’m using Python 3.5)
*Note: My Linux OS is Ubuntu 16.04.1 which already included Python 2.7. So, I have installed Python 3.5.
After the installation, there are a few more steps to do as we’ll be connecting to a SQL Server in any of our environments.
In Ubuntu, to install Python version 3.5 plus needed modules you need to execute the *following ‘bash’ line:
[sourcecode language=”bash”]
$ sudo apt install unixodbc-dev
$ sudo apt install python3
$ sudo apt install python3-pip
$ sudo pip3 install –upgrade pip
$ sudo pip3 install –upgrade setuptools
$ sudo pip3 install –upgrade wheel
$ sudo pip3 install pyodbc
[/sourcecode]
*Note: I’ve notice that sometime doing a “pip3 install –upgrade ..” will fix some issue with modules. But, during any python module installation it will let you know when to run the command.
In Windows, just need to install the module for connecting to SQL Server using Python ODBC
[sourcecode language=”dos”]
c\:> pip3 install pyodbc
[/sourcecode]
Now, you can open Python from any of your Windows Console: DOS, Windows PowerShell, or PowerShell Open Source.
In order to execute *Python 3.5 in Ubuntu, simple type:
[sourcecode language=”bash”]
$ python3.5
[/sourcecode]
*Note: Just an FYI. You can use the “Update-Alternative …” bash command to set the default python to 3.5.
Testing PowerShell and Python
Open any PowerShell Console, on either Windows or Linux, you just type Python at the prompt.
You will immediately see the Python prompt, and just type the following python code:
[sourcecode language=”python”]
import sys;
cur_version = sys.version;
print(cur_version);
exit()
[/sourcecode]
Now, in PowerShell, try typing a python code and save it to a PowerShell variable:
[sourcecode language=”powershell”]
C:\WINDOWS\system32> $v = python -c @"
import sys;
cur_version = sys.version;
print(cur_version);
"@;
$v
[/sourcecode]
As you see, PowerShell and Python interact very well. The same applies in the Linux Ubuntu environment.
Python and SQL Server
For this section we previously installed the python module pyodbc which is needed to connect via ODBC to any SQL Server on the network giving the proper authentication method.
The following sample code can be found this link: https://www.microsoft.com/en-us/sql-server/developer-get-started/python-ubuntu
I did slightly modify the python code to add a new record to an existing database. And, to run it within Powershell:
[sourcecode language=”powershell”]
## – Build Here-String variable with Python code:
$runpythonsql = @"
import pyodbc
server = ‘MTRINIDADLT2\MSSQL2K16A’;
database = ‘sampledb1’;
cnxn = pyodbc.connect(‘DRIVER={ODBC Driver 13 for SQL Server};Trusted_Connection=yes;SERVER=’ + server+’;PORT=1443;DATABASE=’+database);
cursor = cnxn.cursor();
#Insert Query
tsql = ‘INSERT INTO Employees (name, location) VALUES (?,?);’;
if cursor.execute(tsql,’Maximo Trinidad’,’Puerto Rico’):
print(‘Inserting record to Database Sampledb1 was Successful!’);
cnxn.commit()
"@;
## – Execute Python code from within PowerShell
$r = python -c $runpythonsql;
## – Display Result of Inserted data:
$r
[/sourcecode]
As you can see, PowerShell and Python can go hand-in-hand. They can be a perfect match cross-platform.
For more information about PYODBC, click on the link: https://github.com/mkleehammer/pyodbc
