Querying an Azure SQL Database with Python on Linux using pypyodbc

Querying an Azure SQL Database with Python on Linux using pypyodbc

AJ's photo
AJ
·

7 min read

I've been working with Python and MSSQL off and on for a few years and one thing is certain, they don't tend to play nice. Luckily, pypyodbc tends to make things a little bit easier. Here is a quick how-to on setting up a linux server to get your Flask app to connect to an Azure SQL Server.

For this, you'll need:

  • An Azure account on which you can create a SQL database
  • A linux machine running Python3

First, let's create our database in Azure. Login to the portal and go through the Wizard to create a SQL database. If you need to create the database server as well, go ahead and do that too. For extra simplicity in cost, go with the "Basic" deployment.

image.png

Once created (will take several minutes), find the database name in the Azure Portal and click on it. Click on "Set server firewall". Here, you'll set your home IP or the IP of the server you're going to run your testing on. Conveniently, Azure gives you your own home IP right in the interface.

Click Save when you're finished.

Next, grab the Connection string information for ODBC. This will help us later.

image.png

From the query editor (preview) tab, create a table with some data. Here's an example: 

create table data (
    name varchar(50),
    quote varchar(150)
)

insert into data (name, quote) values ('AJ B', 'I like stuff'), ('Mark Twain','I have never let my schooling interfere with my education.'), ('MLK','I have a dream...'), ('Abe Lincoln', 'Do I not destroy my enemies when I make them my friends?');

Now let's setup our local environment.

For this I'll be using Oracle Linux 8 running on Vagrant We'll also be using Python3. OEL8 includes Python3.

Create a directory for our flask app.

Use pip to install pypyodbc. 

pip install --user pypyodbc

Now, let's setup the file structure. We're going to put our credentials in a credential file. Be sure to EXCLUDE this from your git repo by adding it to the gitignore file.

The credentials file, which I'll call azurecred.py, will contain all of the connection information to connect to your Azure DB. Remember the connection string information we pulled earlier? Use it to fill out this file, except use ODBC Driver 17, instead of 13. 

AZDBSERVER = 'blogserver.database.windows.net'
AZDBNAME = 'TestData'
AZDBUSER = 'bloguser'
AZDBPW = 'pw'
AZDBDRIVER = '{ODBC Driver 17 for SQL Server}'

Now, create a class file called AzureDB.py that will establish our connection information 

import pypyodbc
import azurecred

class AzureDB:
    dsn = 'DRIVER='+azurecred.AZDBDRIVER+';SERVER='+azurecred.AZDBSERVER+';PORT=1433;DATABASE='+azurecred.AZDBNAME+';UID='+azurecred.AZDBUSER+';PWD='+ azurecred.AZDBPW

    def __init__(self):
        self.conn = pypyodbc.connect(self.dsn)
        self.cursor = self.conn.cursor()

    def finalize(self):
      if self.conn:
        self.conn.close()

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.finalize()

    def __enter__(self):
        return self

    def azureGetData(self):
        try:
            self.cursor.execute("SELECT name,quote from data")
            data = self.cursor.fetchall()
            return data
        except pypyodbc.DatabaseError as exception:
            print('Failed to execute query')
            print(exception)
            exit (1)

What's happening in this class fail is the use of the enter and exit functions that allow us to use the "while" function in python, which will handle connections to the DB for us.

Finally, let's create another file that will actually import this class and call the function azureGetData. We'll call this azuredbtest.py. 

from AzureDB import AzureDB

with AzureDB() as a:
    data = a.azureGetData()
    print(data)

Now, run your azuredbtest.py file. 

python3 azuredbtest.py

If you're running on a linux OS like me, you'll notice this error:

pypyodbc.OdbcNoLibrary: 'ODBC Library is not found. Is LD_LIBRARY_PATH set?'

This is where the fun begins. We need to install an ODBC library on our server and put it in our LD_LIBRARY_PATH in order to connect.

Using this article as reference, we can install the driver on our Linux server.

In this case, I'll use the instructions for Redhat 8 

sudo su
curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit

sudo ACCEPT_EULA=Y dnf install -y msodbcsql17
sudo dnf install -y unixODBC-devel

Now, rerun our code. Success! We get a list output of the data in our table! 

[vagrant@oracle8 flask-sql]$ python3 azuredbtest.py
[('AJ B', 'I like stuff'), ('Mark Twain', 'I have never let my schooling interfere with my education.\n'), ('MLK', 'I have a dream...'), ('Abe Lincoln', 'Do I not destroy my enemies when I make them my friends?')]

The objects within this list if a collection of row objects, defined by pypyodbc.

So we can loop through each object and pick out our column names, presenting our data in a more readable format.

In the azuredbtest.py file: 

from AzureDB import AzureDB

with AzureDB() as a:
    data = a.azureGetData()
    for d in data:
        print("%s once said \"%s\"" % (d['name'], d['quote']))

And our output:

AJ B once said "I like stuff"
Mark Twain once said "I have never let my schooling interfere with my education."
MLK once said "I have a dream..."
Abe Lincoln once said "Do I not destroy my enemies when I make them my friends?"

That's all for today! Tomorrow we'll look at using this concept in a Flask app.

View this project on Gitlab

-AJ

PythonSQL ServerAzure