Connecting SQL Server on Docker to Python

Question

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>

Cabaret

Sql server uses port 1433 by default, try that instead of 7000 (in both your python code and the command to run the docker image) – Bitten

Yes, that did the job. – Cabaret

Answer 1

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.

Psalter

I think you used MYSQL instead of SQL Server. MySQL has a default port of 3306. – Cabaret

Answer 2

The sample code in the question is incomplete and uses variables that are not defined.

A very simple working example is:

# docker compose file
version: "3.9"
services:
    <Some name>:
        image: mcr.microsoft.com/mssql/server:2019-latest # Or whatever version you want
        container_name: <Some name>
        restart: unless-stopped
        ports:
            - "1433:1433"
        environment:
            - ACCEPT_EULA=Y
            - SA_PASSWORD=<Some Password>
            - MSSQL_PID=Developer
            - MSSQL_AGENT_ENABLED=True

import pandas as pd
import pyodbc

cnxn = pyodbc.connect(
       'DRIVER={ODBC Driver 17 for SQL Server}' + 
       ';SERVER=' + 'localhost,1433' + ';UID=' + 'sa' + 
       ';PWD=' + '<Some password' +
       ';database=<some DB name>') # database here is optional if you want to specify it below in the query.

df = pd.read_sql('some query like select * from table', cnxn)

cnxn.commit()
cnxn.close()

print(df)

Calesta

Hi David, how does your python program use the docker compose file? How does it detect that the docker compose file exists? I am not able to see a connection between the two. – Carliecarlile

I don't connect the two. The Docker Compose runs the docker for the database, and they python connects to it with hard coded values. I am not parsing the docker compose to auto inject those values or the status of the database. – Calesta

I understand David. Thank you for explaining that. Also, how did you find out what the name of the Driver was? I have my docker container set up the same way as you have including the same image, but I get a "File not found (0) Can't open lib 'ODBC Driver 17 for SQL Server'" error when running the program. I am however successfully able to access the server on Azure Data Studio, just not on python yet. – Carliecarlile

Within Windows (if you are using that) - you can check which drivers by opening the ODBC Data Source Administrator (64 bit). – Calesta

Answer 3

# docker compose file
version: "3.9"
services:
  SomeName:
    image: mcr.microsoft.com/mssql/server:2019-latest # Or whatever version you want
    container_name: SomeName
    restart: unless-stopped
    ports:
      - "1433:1433"
    environment:
      - ACCEPT_EULA=Y
      - SA_PASSWORD=abcABC123
      - MSSQL_PID=Developer
      - MSSQL_AGENT_ENABLED=True

---

import pandas as pd
import pyodbc

cnxn = pyodbc.connect(
       'DRIVER={ODBC Driver 17 for SQL Server}' + 
       ';SERVER=' + 'localhost,1433' + ';UID=' + 'sa' + 
       ';PWD=' + 'abcABC123' +
       ';database=tempdb') # database here is optional if you want to specify it below in the query.

# df = pd.read_sql('some query like select * from table', cnxn)

# cnxn.commit()
# cnxn.close()

print(cnxn)

If this results is:

<pyodbc.Connection object at 0x7ff75d47c100>

it is working. However, if the result is:

pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

you have not installed the required MicroSoft libraries.

Doddering