How Do I Connect To Sql Server Running Inside Docker On Mac Using Python3?

I want to connect to my SQL Server database running in a Docker container using Python. Currently, I am facing issues with Error: ('01000', '[01000] [unixODBC][Driver Manager]Can

Solution 1:

Microsoft provides a unixODBC compatible driver for SQL Server, available from Homebrew. The full reference is here.

If you're using Homebrew, you can:

1. Install unixodbc

brew install unixodbc

2. Tap into the Microsoft repo

brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release

3. Update and install

brew update
brew install msodbcsql17 mssql-tools

That might properly install the unixodbc driver in your /usr/local/etc/odbcinst.ini file (it should, but it's been 50/50 for me). If it didn't, you can copy the driver config from the msodbcsql17 dir (/usr/local/Cellar/msodbcsql17/17.4.1.1/odbcinst.ini) into your odbcinst.ini

Ultimately, you need a stanza in your /usr/local/etc/odbcinst.ini file that looks like:

[ODBC Driver 17 for SQL Server]Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/usr/local/lib/libmsodbcsql.17.dylib

And your python connection string will look like

connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=localhost;DATABASE=US_NATIONAL_COPY;UID=SA;PWD=<YourStrong!Passw0rd>'

Solution 2:

You can not connect the DB container from your python container using localhost.

Localhost refers to the python container localhost, not the DB container or the Host.

If both are running in the different container then better to use docker-compose so that both containers will be in same network then you can refer the container name in the string.

version:"3"services:web:image:python:3.7-alpine3.9ports:-"8000:80"depends_on:-dbdb:image:"mcr.microsoft.com/mssql/server"environment:SA_PASSWORD:"Your_password123"ACCEPT_EULA:"Y"

Now the connection string will be

connection_string = 'DRIVER={SQL Server};SERVER=db;DATABASE=US_NATIONAL_COPY;UID=SA;PWD=<YourStrong!Passw0rd>'

If you running SQL server outside of container then you can refer the Host SQL server using Host IP inside python container.

connection_string = 'DRIVER={SQL Server};SERVER=YOUR_HOST_IP;DATABASE=US_NATIONAL_COPY;UID=SA;PWD=<YourStrong!Passw0rd>'

YOUR_HOST_IP is not the localhost but the ip of the local network.