Python Flask Framework, Docker And SQL Server Connection

by

We are going to look at how to connect a Python Flask application to a SQL Server Docker container.

We will begin by setting up the SQL Server Docker container.

Make sure you have docker installed in your computer and signed in to your account. You can get started on how to set how to set up docker here.

#Try to avoid an @ symbol in your password.

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=<YourStrongPassword>" -p 1433:1433 -v sqlvolume:/var/opt/mssql -d --name mysqlsrv mcr.microsoft.com/mssql/server:2019-latest

The command will set up the SQL server docker container based on 2019 image version. The password provided will be used by the default sa user. I have avoided using the @ symbol because of connection string that will be used in the Python script.

Connect to the Sql Server instance through command line or by using client side management Studio, for Windows I am using Microsoft SQL Server Management Studio. Once connected you can create a database. Note the server name is “localhost, 1433”.

Now we can create a Flask application. With a virtual environment set up, install the pip necessary packages. We are going to use SQLAlchemy to connect to docker container.

These are the content of my requirements.txt. 

click==8.1.3
colorama==0.4.6
Flask==2.2.2
Flask-SQLAlchemy==3.0.2
greenlet==2.0.1
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.1
pyodbc==4.0.34
SQLAlchemy==1.4.43
Werkzeug==2.2.2

Use the following command to install all the packages .

pip install -r requirements.txt
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

app.config["SQLALCHEMY_DATABASE_URI"] = "mssql+pyodbc://sa:<pwd>@localhost:1433/testdb?driver=SQL+Server"
db = SQLAlchemy(app)

class Test(db.Model):

    id = db.Column(db.Integer, primary_key = True)
    code = db.Column(db.String(100))
    name = db.Column(db.String(200))

    def __init__(self, code, name):
        self.code = code
        self.name = name

@app.route('/create-tables')
def home():
    db.create_all()
    return "Done"

With the following script, we pay emphasis on the connection string. Each item on the string can be matched with container we created before.

Now we have achieved to connect to the database and create a test table. My environment is based on python 3.9 and windows. Incase there disparity in environments we might achieve different results.