AWS EC2 Linux AMI with pyodbc, psqlODBC, Microsoft ODBC Drivers

Context: Using pyodbc within a small python application to pull tracking data from an Azure SQL Server DB, do some work, and then store the results into AWS RDS PostreSQL DB.

Problem: Default AWS EC2 Linux AMI 2017.09.1 does not come with unixODBC 2.3.x which is required for the mssql odbc linux driver, but instead comes default with unixODBC 2.2.x. Need to get pyodbc library, postgres odbc driver, and mssql odbc driver.

Steps:

Enable EPEL yum repo
- disable yum priority plugin
Install mssql odbc linux driver & unixODBC 2.3x
Install psqlodbc linux driver
Install pyodbc

Enable epel

Resource

sudo yum-config-manager --enable epel epel-source epel-debuginfo #enable epel	 	
sudo yum repolist #verify epel was enabled Disable yum priority plugin

disable (annoying) yum priority plugin

Resource (doesn’t describe how to disable)

sudo nano /etc/yum/pluginconf.d/priorities.conf
...
enabled=0

Install Microsoft ODBC Driver 13.1 for SQL Server and unixODBC2.3x

Resource (quite good)

sudo su
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
sudo ACCEPT_EULA=Y yum install msodbcsql

After unixODBC has been installed as a dependency of Microsoft ODBC Driver you can check the following locations to see what drivers you have, and configure DSN’s, not covered here.

/etc/odbc.ini
/etc/odbcinst.ini

Install postgresql-odbc.x86_64

Resource (don’t get over-zealous running all the commands here), Resource2

sudo yum install postgresql-odbc.x86_64

Install pyodbc

Resource

sudo yum install gcc-c++
sudo yum install python-devel
sudo yum install unixODBC-devel
sudo pip install pyodbc

NOTE!

Be aware of what version of python you’re running. You can check what package versions are available to you in your repos by performing:

yum list | grep {package_name} # for example, put in python-devel

Note for pyodbc connections

Please follow the connection guides in the documentation. On the right hand side you’ll see documentation for connecting to various sources; in this case pay special attention to using explicit decoding/encoding for PostgreSQL.
It might also be worth while to review how an odbc library like pyodbc facilitates these connections here.

General Notes

Make sure security groups are appropriately configured for your MSSQL and PostgreSQL databases.

Bonus: What I listened to to maintain sanity while figuring this all out

AWS EC2 Linux AMI with pyodbc