Flask with MS Sql Server or Azure SQL

I have been searching quite a while on how to connect to MS SQL Server or Azure SQL in Flask

Its quite simple if you know how to do it …

You might want to add pyodbc to your requirements or install it with pip

import urllib

conDEBUG = “DRIVER={SQL Server};Database=TestDB;SERVER=localhost\SQLEXPRESS;UID=sa;PWD=mypass”
conDEBUG = urllib.parse.quote_plus(conDEBUG)
conDEBUG = “mssql+pyodbc:///?odbc_connect=%s” % conDEBUG

app.config[‘SQLALCHEMY_DATABASE_URI’] = conDEBUG

and after you done this

 

db = SQLAlchemy(app)

That’s it, you have a working SQL connection

And for Azure SQL just replace the first line with your Azure connection string and do not forget to modify the password

 

 

GUID in Flask

Recently I started on a new webproject and I ran into Flask which I like alot
Since I have azure credits to spend, my plan is to run it on Azure with SQL server

Anyway I wanted to created a Base Class for all my models so that they have some common fields like an autoincrement id and a GUID. The GUID does not really server a purpose, but in case I ever want to migrate the DB it can come in handy

So on my firs try i tried

class KBase(Base):
__abstract__ = True
id = db.Column(‘PK_ID’, db.INTEGER, primary_key=True)
id_GUID = db.Column(‘F_GUID’, mssql.UNIQUEIDENTIFIER, default=db.func.Guid(), unique=True)

which gave me an error

My solution to this is

import uuid

class MyBase(Base):
__abstract__ = True
id = db.Column(‘PK_ID’, db.INTEGER, primary_key=True)
id_GUID = db.Column(‘F_GUID’, mssql.UNIQUEIDENTIFIER, unique=True)

   def __init__(self):
              id_GUID = uuid.uuid4().urn[9:]

uuid.uuid4() gives us a GUID, but you need to convert it into a string

Yes you always have to call MyBase.__init__(self), but this works, and it’s not a real issue for me