CODEWITHSUNDEEP
pythonsql-serveramazon-web-servicesamazon-elastic-beanstalk
Bjc
Bjc

Reputation: 103

MS SQL Driver with Amazon Linux AMI an Python

I've seen a number of posts of this kind for PHP, but nothing for Python.

I'm trying to deploy a Flask app on AWS Elastic Beanstalk to connect to a MSSQL Database. In development (on windows) I've been using pyodbc and a microsoft sql server drivers.

Based on the (largely outdated) blogs and SO questions, I've been trying to use FreeTDS and unixODBC to connect to my database on the AWS Linux instance but have not been able to get the configurations just right.

I'd prefer to be able to use a native Microsoft driver, but the Microsoft support page doesn't list a specific download for Amazon Linux. Amazon claims that their Linux is similar to Redhat's Fedora. Which version of MS SQL Driver should I download for use with Amazon Linux?

Thanks

UPDATE:

I had figured out how to use FreeTDS, but once I deployed my app it was terribly slow compared to my development server--instead I took the advice in the accepted answer and db requests sped up considerably as well

Upvotes: 0

Views: 2314

Answers (2)

Tom Chapin
Tom Chapin

Reputation: 3566

If you are trying to use FreeTDS on ElasticBeanstalk, it's worth noting that the version on their yum repository is currently 0.91, which is ancient (from 2011).

Once I installed the latest version, all of my problems went away. I don't know if that would solve your performance issues or not, but if not, hopefully it helps someone else who comes along.

Here was the solution I came up with (which downloads and installs freetds directly from the source instead of the outdated yum repo):

  1. Create a file in your project called '/.ebextensions/001_install_freetds.config'
  2. Paste in these contents:

commands:
  000_download_freetds:
    command: "[ ! -e /home/ec2-user/freetds-1.00.86.tar.gz ] && wget -nc ftp://ftp.freetds.org/pub/freetds/stable/freetds-1.00.86.tar.gz -O /home/ec2-user/freetds-1.00.86.tar.gz || true"
  001_extract_freetds:
    command: "[ ! -e /home/ec2-user/freetds-1.00.86 ] && tar -xvf /home/ec2-user/freetds-1.00.86.tar.gz -C /home/ec2-user/ || true"
  002_configure_freetds:
    command: "[ ! -e /usr/local/etc/freetds.conf ] && cd /home/ec2-user/freetds-1.00.86 && sudo ./configure --prefix=/usr/local --with-tdsver=7.4 || true"
  003_build_freetds_and_install:
    command: "[ ! -e /usr/local/etc/freetds.conf ] && ( cd /home/ec2-user/freetds-1.00.86 && sudo make && sudo make install ) || true"

  1. Redeploy your app

Note: You may want to change the various file names to reflect the latest stable version. This is just what is the most recent stable release, as of when I'm writing this post.

Upvotes: 2

John Hanley
John Hanley

Reputation: 81444

Microsoft's recommended driver for Python is pyodbc.

Python SQL Driver - pyodbc

The version of Linux does not matter (for the most part). It is the Python version that you need to be compatible with.

This Stack Overflow article shows how to use SqlAlchemy with Flask:

Connect to MSSQL Database using Flask-SQLAlchemy

Upvotes: 0

Related Questions