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])

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.