Moving from Pymssql to Pyodbc

I was in the process of updating one of my linux servers with Pymssql when I kept getting an error message about it trying to do the install:

ERROR: Could not find a version that satisfies the requirement pymmssql (from versions: none)
ERROR: No matching distribution found for pymmssql

Turns out pymmsql had been deprecated and was no longer being supported. I use pymmsql to connect to my sql database in order to pull data for ip addresses and other network specific information to build my stores in the Meraki Portal.

For the most part the conversion was fairly easy, I think the biggest issue I had was in getting the drivers installed for MS SQL for pyodbc. This site from MS was helpful in installing the ODBC drivers on Mac and Linux. Once I got the drivers installed I then had some issues with making https calls via my python programs. I had to reinstall python via pyenv reinstall. Once the version was reinstalled then it resolved the issues with the API calls and the requests module. Here is an example of the code I was running and the code that I am now running.

PYMSSQL CODE:

import pymssql as mdb

sql_host = cred.sql_host
sql_username = cred.sql_username
sql_password = cred.sql_password
sql_database = cred.sql_database
store = str(input(“What store are we creating?: “))
sql_connection = mdb.connect(sql_host,sql_username,sql_password,sql_database)
cursor = sql_connection.cursor()
cursor.execute(“select [VID2GW] from tblDSlip where [Store #] = (%s)”, (store))
VLAN2GW = str(cursor.fetchone()[0])

PYODBC CODE:

import pyodbc
store = str(input(“What store are we creating?: “))

if len(store) == 2:
sql_store = ’00’ + store
elif len(store) == 3:
sql_store = ‘0’ + store
else:
sql_store = store

server = cred.sql_host
database = cred.sql_database
username = cred.sql_username
password = cred.sql_password

cnxn = pyodbc.connect(‘DRIVER={ODBC Driver 17 for SQL Server};SERVER=’+server+’;DATABASE=’+database+’;UID=’+username+’;PWD=’+ password)
cursor = cnxn.cursor()
cursor.execute(“select [VID2GW] from tblDSlip where [Store #] = ?”, sql_store)
VLAN2GW = str(cursor.fetchone()[0])

 

Date/Time Conversion in Python – ISO 8601 UTC to Pacific Standard Time

I had an issue where I was getting an ISO 8601 UTC date back from one of my API requests and I was asked to convert it to Pacific time as I was providing the information in a report. Since I had no idea what I was doing I first had to figure out what kind of date I was dealing with so that I could then figure out what I was trying to get to.

Here is what an ISO 8601 date looks like:  2019-12-24T15:04:18Z


Here is what the format of the data will look like after the process has been run: 12-24-2019 7:04:18

#Modules needed
import datetime
import pytz

#set the variable for Pacific timezone
pst = pytz.timezone(‘US/Pacific’)

#create the time variable date_time that will be used throughout, the data is coming from an api request and I am getting back json data.
date_time = (ISO 8601 Date)
#set the format for the date time
date_time = datetime.datetime.strptime(date_time, “%Y-%m-%dT%H:%M:%SZ”)
#tell python the current timezone is UTC
date_time = pytz.timezone(‘UTC’).localize(date_time)
#tell python to change the timezone to Pacific
date_time = date_time.astimezone(pst)
#update the variable with the new time and date in the new format
date_time = date_time.strftime(“%m-%d-%y %H:%M:%S”)


pyenv on Mac OS 10.15 Catalina

I have been running pyenv from homebrew on Mac so I could run Python3 rather than 2.7. However when I upgraded to Catalina I ran into an issue that pyenv wasn’t working anymore. When I went through the GitHub page for pyenv at: pyenv and followed all of the steps and got to this part:

  • Add pyenv init to your shell to enable shims and autocompletion. Please make sure eval "$(pyenv init -)" is placed toward the end of the shell configuration file since it manipulates PATH during the initialization.$ echo -e ‘if command -v pyenv 1>/dev/null 2>&1; then\n eval “$(pyenv init -)”\nfi’ >> ~/.bash_profile
    • Zsh note: Modify your ~/.zshenv file instead of ~/.bash_profile.
    • fish note: Use pyenv init - | source instead of eval (pyenv init -).
    • Ubuntu and Fedora note: Modify your ~/.bashrc file instead of ~/.bash_profile.General warning: There are some systems where the BASH_ENV variable is configured to point to .bashrc. On such systems you should almost certainly put the abovementioned line eval "$(pyenv init -)" into .bash_profile, and not into .bashrc. Otherwise you may observe strange behaviour, such as pyenv getting into an infinite loop. See #264 for details.

.zshenv should actually be .zshrc

First Half of Python for Network Engineers

It’s been non stop for 5 weeks of training, but this week we had a week off so I thought I would post this.

I was able to get my work to fund the Python for Network Engineers course taught by Kirk Byers. 

https://pynet.twb-tech.com/class-pyauto.html

I had taken the free class a couple of times and learned quite a bit. I thought that being able to take the paid course would give me a better understanding of things related to python and how to handle some of the more complex things that I want to do. I really want to be able to take advantage of more automation in our environment and make things work better/easier with fewer chances for errors. I also want to empower my Helpdesk to be able to do more things, we are a very small shop with a large footprint of stores/offices. We have deployed Meraki to almost all of the locations so being able to take advantage of python/rest apis has been a great benefit so far. However I feel there is more that I can do, I just need some more training. Also the more stuff I can give to my Helpdesk the less they have to call me for and I can try and get some more sleep(as though that would happen). 

I have really enjoyed the first half of the class and learned quite a bit so far in just using Netmiko, textfsm, and jinja2. The other part that is nice is the community of people that Kirk has put together so that we can all learn off from each other and exchange ideas and questions. Between using Slack and some group channels there has been a lot of good comments/questions exchanged back and forth. 

As for the class Kirk’s videos have been informative and I have found a lot of useful information in them. His examples have been good and have shown some real life information in working with equipment. Not diving into actual network engineering, but showing some information in relation to real life data/examples. I have also found the exercises he has assigned us to be challenging and quite good. I have picked up some good ideas from them and it has pushed my learning and understanding of python.

In all I am really enjoying it and can’t wait for the next half and to see how my python programming improves.

Meraki Script to pull LTE Card Signal

Script for pulling the make and signal strength of wireless cards

We are trying to continually audit our LTE cards in the Meraki Routers so we wanted to be able to monitor the stores LTE connections and see the signal strength and then determine which if any needed to be swapped out. However that data is only stored at the device level so you have to iterate through the whole Organization then by network and then by device in the network. Meraki has a polling limit for how many times you can poll the cloud per second so I put a 1 second delay in there to keep the program from overwhelming everything and causing issues for itself or for our users monitoring on the website.

The script can be found here:

https://github.com/undrwatr/MERAKI_CARD_SIGNAL

How I handle credentials and shared variables in Python

How to handle common variables between programs

I have been writing a lot of python programs lately for interacting with the Meraki Platform. I was tired of copying and pasting my variables and credentials between programs, plus I wanted the ability to easily upload the programs to GitHub without having to worry about sanitizing the program of my companies or personal data. I did some searching and didn’t find a lot so what I figured I would do is put this information into a python module and then I could call that module from within my programs and then I wouldn’t have to worry about keeping all of my data secure. I decided to call my module cred.py and then I could call it from within the program with just a “import cred”. I used to copy this file into each of the directories where I was working on a program. Then I ran into a problem where I had to change an API key, I then had to go through and find all of the cred.py files I had created and then update the data in them. That proved to be more of a pain than I wanted to deal with so I decided to place it in a central directory for all of my programs. This proved much easier, but then I had to figure out how to call it from within Python without making it a module in the install path.

That is where I came up with this:

import sys

#Import the CRED module from a separate directory
sys.path.insert(0,’../CRED’)
import cred

With this it allows me to keep one central directory to store all of my credentials, but also commonly needed variables. I call it from within the program and can then run my programs easily. Love to hear how others are handling this or if there is a better way for me to do it.

Mac, Python, paramiko, all in a days work

I am trying to learn Python as I think it will be good for my day job. I bought a couple of books, but I am someone that learns by doing. I found some good scripts out on the internet that I wanted to modify and make use of. However I am also a mac user and so I wanted to be able to run these scripts on my Mac so that when I wanted I could run them from where ever I might be. I do on occasion travel to sites and do some extra curricular activities that might require this ability. So the mac has Python pre installed, it’s version 2.7.5, which seemed sufficient for my needs and what I wanted to do. The script I wanted to play with needed the paramiko module. I was able to download it and extract from here:

https://github.com/paramiko/paramiko

That was easy, however to install it said if I had setuptools would be best. So I found this site:

https://pypi.python.org/pypi/setuptools#unix-including-mac-os-x-curl

And was able to find a command to download and install setuptools.
***Make sure you are root, you will have a much better time of it.***

curl https://bitbucket.org/pypa/setuptools/raw/bootstrap/ez_setup.py -o – | python

So that installed correctly, however when I went into python and did an “import paramiko” I was told I needed a crypto module. I then went out and found this:

https://pypi.python.org/pypi/pycrypto

Downloaded it and of course I couldn’t use setuptools for it, it needed to be built and then installed. So that required me to get Xcode 5.1 for the cc compiler and load that on my machine. That was straight forward enough. So after the Xcode install I then ran:

python setup.py build

But I was getting this error:

error: command ‘cc’ failed with exit status 1

Turns out there is an issue with Python and Xcode 5.1. The fix for that is to run the following before doing the build and install:

export CFLAGS=-Qunused-arguments
export CPPFLAGS=-Qunused-arguments

Once that is done you can then go into the pcrypto folder and run:

python setup.py build
python setup.py install

Now you have everything you need to use paramiko to ssh into a cisco device from a mac and run some commands or do whatever it is you want.

I did find one other thing that is needed and that was as part of the connect string for paramiko, I needed to specify “allow_agent=False,look_for_keys=False” as part of the string. If I didn’t then I was getting password errors on the cisco switch I was testing with.

ssh.connect(‘x.x.x.x’, username=’name’, password=’password’, allow_agent=False,look_for_keys=False)

All in all it was a very educational day and I think some hours well spent. I am now going to take my scripts and look to put everything into variables and also specify some lists so I can run it against multiple machines.