Design an API with Flask, Flask Restful and Azure SQL Server

I was recently asked by a new client to design and build a back-end database and API to interface with an Android sports statistics tracking app they built this past summer.

The developer of the Android stats app is not a database developer and his app writes the stats data to a flat file. This file is then emailed to people in their organization.

There is definitely a much better way to handle this.

After our initial meeting, I recommended a RESTful API that would allow the Android or iOS stats app to send the player's stats data to a database via a simple POST request.

I have built API's with Django and the DRF, however, for simplicity's sake, I wanted to keep this API super lightweight and have a minimal footprint.

The client already has another database application hosted on Microsoft Azure and they would like to use the existing database to capture the stats data. This means I'll be connecting the Flask API to MS SQL Server.

Hello, pyodbc.

pyodbc

pyodbc is an open source Python module that makes accessing ODBC databases simple. It implements the DB API 2.0 specification and is packed with features. For my project, I need to be able to connect to the Azure SQL database via my Python Flask app. I tested this with an Azure Web/Mobile App specific for Python, however, pyodbc did not compile correctly on this platform and required dependencies from Visual Studio. Nevermind that, I can set this up in Ubuntu in a matter of minutes.

First, I set up an Azure micro instance of Ubuntu 16.04 LTS Server and installed all of the necessary packages. The main packages you need for pyodbc:

$ sudo apt-get install -y unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc python-pyodbc

Once the pyodbc dependencies are installed, let's begin by configuring ODBC on Ubuntu.

First, open /etc/freetds/freetds.conf in a new terminal window and add the following at the end:

# mssql server
[sqlserver]
    host = <host-adddress>
    port = <port-number>
    tds version = 7.0

You can test your database connection using the following command:

tsql -S sqlserver -U <user-name> -P <password>  

You should get a prompt from your terminal:

locale is "en_US.UTF-8"  
locale charset is "UTF-8"  
using default charset "UTF-8"  
1> sp_tables;  

Be advised that at this point, you are only confirming a connection to the Azure cloud database instance. You have not yet specified the database name so the call to the stored procedure sp_tables to list the tables will show master database information.

Next, check your ODBC configuration files using this command:

odbcinst -j  

This command produces the following ouput:

unixODBC 2.3.1  
DRIVERS............: /etc/odbcinst.ini  
SYSTEM DATA SOURCES: /etc/odbc.ini  
FILE DATA SOURCES..: /etc/ODBCDataSources  
USER DATA SOURCES..: /home/craigderington/.odbc.ini  
SQLULEN Size.......: 8  
SQLLEN Size........: 8  
SQLSETPOSIROW Size.: 8  

You can easily see the correct paths to your ODBC data source files.

Next, configure /etc/odbcinst.ini and insert the following:

[FreeTDS]
Description = TDS driver (Sybase/MS SQL)  
# Some installations may differ in the paths
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so  
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so  
CPTimeout =  
CPReuse =  
FileUsage = 1  

Open /etc/odbc.ini and insert the following:

[sqlserverdatasource]
Driver = FreeTDS  
Description = ODBC connection via FreeTDS  
Trace = No  
Servername = sqlserver  
Database = <database-name>  

Finally, check your database connection using the following command:

isql -v sqlserverdatasource <username> <password>  

You should see a database prompt, like so...

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> sp_tables;  

If for any reason you don't get the prompt, you can troubleshoot your database connection with this command:

osql -S <datasource> -U <username> -P <password>  
flask

Flask is a micro-framework for Python based on Werkzeug, Jinja 2 and good intentions. It is by far the easiest way to get your Python powered sites online - FAST!

flask-restful

Flask-RESTful is an extension for Flask that adds support for quickly building REST APIs. It is a lightweight abstraction that works with your existing ORM/libraries. Flask-RESTful encourages best practices with minimal setup. If you are familiar with Flask, Flask-RESTful should be easy to pick up.

OK, so now that we have defined all of our project components, we can begin scaffolding the app.

I'm going to use a very basic project outline as to not give away proprietary information or details about my client's project and software. So, instead of sports stats, I am going to use a task list to create our tutorial API.

Our task table will be comprised of the following fields.

* task_id
* task_name
* task_description
* task_date
* task_due
* task_completed
* task_reminder
* task_userid

That should work fine for our needs.

Now, let's begin constructing our API endpoints.

GET ['/api/version/tasks']  
POST ['/api/version/tasks']  
DELETE ['/api/version/tasks/<int:task_id>']  
GET ['/api/version/tasks/<int:task_id>']  
PATCH ['/api/version/tasks/<int:task_id>']  
PUT ['/api/version/tasks/<int:task_id>']  

OK! This routing scheme looks like we have covered all of our necessary HTTP verbs we will be using.

Now, on to the app...

Let's take a moment to discuss the code in the above Gist.

After our import statements, I set up the Flask app and wrap the app in the flask_restful Api call. I also assign the auth variable with a call to HTTPBasicAuth().

The AzureSQLDatabase class contains our pyodbc connection and sets up our database methods. The init method connects to the database and defines the cursor object. I create 2 other methods in this class query and commit in order to pass these function calls on our database. Finally, the database connection is automatically closed after every call by way of the del method. Important to note, I have not included the config.py file that contains the database connection string. That looks like this:

-- from config.py --

CONN_STRING = 'DRIVER={FreeTDS};SERVER=<database-alias>.database.windows.net;PORT=1433;DATABASE=<database>;UID=<user>;PWD=<pass>;TDS_Version=7.0'  

The decorator function for auth.get_password() ensures that every request to an API resource contains the correct authentication information in the headers. Unauthorized connections to a resource will result in a call to unauthorized and return a JSON message "Unauthorized Access" and a 403 status code.

Next, I have created a dictionary mapping for the task fields our API will utilize. This will be used to marshal the task fields when formatting the response data.

Finally, our TaskListAPI resource initializes with a call to the request parser and defines the function arguments, requirements and data types.

There are only two methods in the TaskListAPI class.

  1. GET - get a list of all tasks, returns json in a list and a 200 status code.
  2. POST - submit a new user task, returns json and a 201 status code.

The TaskAPI resource has the following methods:

  1. GET - retrieve a specific task by ID
  2. PUT - update a task by ID
  3. DELETE - delete a task by ID
Wrapping Up

Flask and flask-restful is a great way to get a minimal and simple API set up and running online with very little overhead.

pyodbc makes connecting to a SQL Server database from Python a breeze and our SQL commands are very straight-forward and easy to implement in our code.

I currently have my client's API installed on the Microsoft Azure Ubuntu micro-instance and the website is being served by Apache and mod_wsgi. We can talk more about setting up Flask on Apache in Linux in a separate article.

Lastly, I recommend Postman for all of your API testing. See the post image header for a screenshot of Postman.

Python legend Miguel Grinberg has a great tutorial on creating RESTful APIs with only Python & Flask and no other modules. Flask can handle the routing, authentication and database access. What a framework, folks.

That's all for now. Please feel free to drop me a line or ask a question. If you have ideas on how to improve the code in the embedded Gist, please let me know.

Craig Derington

Veteran full stack web dev focused on deploying high-performance, responsive, modern web applications using Python, NodeJS, Django, Flask, MongoDB and MySQL.

comments powered by Disqus