Connecting SQL Server on Docker to Python

I am trying to perform a table creation using pyodbc on a SQL Server 2017 database hosted using Docker. I'm also using a network so that I can connect to it later from another Docker image. However, I get the following error

pyodbc.OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')

This is how I went about creating the connection.

To create and run the DB server,

docker run --name mssqldocker -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=<password>' -e 'MSSQL_PID=Express' -p 7000:7000 --network=lambda-local-mssql -v <my_path> -d mcr.microsoft.com/mssql/server:2017-latest-ubuntu

I also tried adding

-h "mssqldocker"

to the command for running the Docker image and then using "mssqldocker" instead of localhost, but to no avail, since mismatched hostnames seem to be the recurring theme when using DBs and Docker together. Also tried adding in \sqlexpress without effect as well. The Python code is as follows

import pyodbc
import sql_clauses
from settings import ENDPOINT, PORT, USERNAME, PASSWORD

cnxn = pyodbc.connect(
       'DRIVER={ODBC Driver 17 for SQL Server}' + 
       ';SERVER=' + ENDPOINT + ';UID=' + USERNAME + 
       ';PWD=' + PASSWORD)

cursor = db.cursor()
cursor.execute(create_database(dbname))
cnxn.commit()
cnxn.close()
print("Database created")

The settings file is as follows

ENDPOINT="localhost"
PORT = 7000
USERNAME="SA"
PASSWORD=<password>

Solution

In your docker run command you specify -p 7000:7000. This translates in "map the host port 7000 (first 7000 - published) to the container port 7000 (the second 7000 - exposed)". If you have MSSQL running on a different port inside your container (which probably you do) then you have to change that second 7000 to the correct port.

Once you do that you should be able to connect to MSSQL from host using "localhost:7000". This applies if your python application runs directly on host.

If your python project also runs in a container, you need to make sure it runs on the same network as the mssql container (--network=lambda-local-mssql) and then you need to connect using "mssqldocker:mssql_exposed_port". In this case localhost and 7000 (the first part of `-p 7000:...) are not valide anymore since you are on a docker managed network.