Flask SQLAlchemy Windows Azure Ubuntu 14.04

intro

i had a project the other day where i wanted to interface flask with windows azure. the task was frustrating to say the least. after i got it working i wanted to document it so that if i ever had this issue in the future that i would have a place to reference it as well as hopefully save someone the pain of going through this headache. i tried to set this up so that the tutorial is build into ‘checkpoints’ so that it would be easier to troubleshoot if you run into any issues.

tools

  • azure account
  • sql server management studio access (not a necessity but will make life easier)
  • ubuntu 14.04 server
  • flask project
  • sqlalchemy
  • odbc
  • freetds
  • patience

step 1: windows azure

so the first thing is to create a mssql box on windows azure. i created a database called ‘flaskdb’ and it was created on a server called ‘dbserver’ and came with a password ‘yourpassword’. after you create the database you will have to configure your new box to allow connections from specific ip’s, this is were i recommend sql server for testing. allow the ip of the computer with sql server to access your azure mssql and then test logging in.

server name: dbserver.database.windows.net
authentication: sql server authentication
login: flaskdb
password: yourpassword

this part is fairly straight forward get going i hope i haven’t lost anyone yet.

step 2: flask setup

i’m not going to go into detail in this section, since i’m terrible enough at writing tutorials, so i will point you to miguel grinberg’s tutorial to explain how i set my flask project up. if you have never done this before you only need to get to the end of part 4 to have all you need to continue with this project. after you can get his tutorial working with sqlite then you can return to this part of the tutorial.

step 3: freetds (get linux to connect)

this section i don’t claim to know everything about what is going on but i got it working with this setup so i’m just sharing what i did. first i install the following packages:

$ sudo apt-get install unixodbc unixodbc-dev freetds-dev freetds-bin tdsodbc

now we want to test that our linux box can connect to the azure box so make sure that you have allowed the ip address of your box and then try executing the following command:

$ tdsver=8.0 tsql -h dbserver.database.windows.net -u flaskdb -d flaskdb -p 1433 -p 'yourpassword'

the password that i was given contained a ‘!’ so i had to put it in quotes so that it would work, just a heads up. now we’ve got connectivity from our linux box, cool.

step 4: pyodbc (get python to connect)

this section comes with the same notice as before, i’m not sure if this is the best way to do it or even what all the options do but i got it working. the next step is to make sure that python can connect to the mssql box. first we need to install pyodbc.

$ pip install pyodbc

we need to configure a bunch of files for python to be able to use pyodbc to connect. first modify /etc/odbc.ini to look like the following:

[sqlserverdatasource]
driver = freetds
description = odbc connection via freetds
trace = no
servername = dbserver
database = flaskdb

i believe ‘sqlserverdatasource’ can be anything you want it be but the two things that are important here are the servername and the database. next we modify /etc/odbcinst.ini to the following:

[freetds]
driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
setup = /usr/lib/x86_64-linux-gnu/odbc/libtdss.so
cptimeout =
cpreuse =
fileusage = 1

and finally add this to the bottom of your /etc/freetds/freetds.conf:

[dbserver]
        host = dbserver.database.windows.net
        port = 1433
        tds version = 8.0

it is important that ‘dbserver’ is set in both host and the brackets. azure was giving me errors when i had the one in the brackets called something different. now that you have all of your files properly set you should be able to create the following file and run it:

import pyodbc

conn = pyodbc.connect('dsn=sqlserverdatasource;database=flaskdb;uid=flaskdb;pwd=yourpassword')
print conn

and if everything worked properly you should get something like this for output:

<pyodbc.connection object at 0x7f1558b56af8>

step 5: sqlalchemy (get orm to connect)

when i went through this myself i wanted to pull my hair out by this step so i hope i’ve made things easier for you. now we have to define the ‘sqlalchemy_database_uri’ constant in your flask config to the following:

sqlalchemy_database_uri = 'mssql+pyodbc://flaskdb:yourpassword@sqlserverdatasource'

‘sqlserverdatasource’ is the name you set in your /etc/odbc.ini file. now to test that everything is working you can execute the db_create.py script from the the flask tutorial. when i first ran this i received and error and it had to do with the ‘unique=true’ in one of my models. i needed to provide a length on the string for the error to go away:

    username = db.column(db.string(50), unique=true)

if you got no errors on the db_create script then you should be able to refer to part 4 of the Flask tutorial and testing your database. Thank the computer gods.