Access the host SQL Server with python27 / pyodbc on the container

Overview

I introduced how to access mysql in the following article the other day, Access the host mysql with python3 / mysql-connector-python on the container Introducing the SQL server version.

Premise

Host SQL Server

ODBC Driver 17 for SQL Server Running on port 1443 with SQL Server authentication

procedure

[How to make a Docker container that runs ODBC] Clone the following repositories in the article (https://qiita.com/e_tyubo/items/9c01f92480d90fc8c092). https://github.com/Microsoft/mssql-docker/tree/master/oss-drivers/pyodbc

> git clone https://github.com/microsoft/mssql-docker.git
> cd mssql-docker/oss-drivers/pyodbc

I think it has the following tree.

pyodbc
├── Dockerfile
├── README.md
├── docker-compose.yml *docker-Added to launch with compose
├── entrypoint.sh
└── sample.py *Edit Or you may create another file. I don't have touch...

Dockerfile

# mssql-python-pyodbc
# Python runtime with pyodbc to connect to SQL Server
FROM ubuntu:16.04

# apt-get and system utilities
RUN apt-get update && apt-get install -y \
    curl apt-utils apt-transport-https debconf-utils gcc build-essential g++-5\
    && rm -rf /var/lib/apt/lists/*

# adding custom MS repository
RUN curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
RUN curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list

# install SQL Server drivers
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y msodbcsql unixodbc-dev

# install SQL Server tools
RUN apt-get update && ACCEPT_EULA=Y apt-get install -y mssql-tools
RUN echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
RUN /bin/bash -c "source ~/.bashrc"

# python libraries
RUN apt-get update && apt-get install -y \
    python-pip python-dev python-setuptools \
    --no-install-recommends \
    && rm -rf /var/lib/apt/lists/*

# install necessary locales
RUN apt-get update && apt-get install -y locales \
    && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen
RUN pip install --upgrade pip

# install SQL Server Python SQL Server connector module - pyodbc
RUN pip install pyodbc

# install additional utilities
RUN apt-get update && apt-get install gettext nano vim -y

# add sample code
RUN mkdir /sample
ADD . /sample
WORKDIR /sample

CMD /bin/bash ./entrypoint.sh

docker-compose.yaml

version: '3'
services:
  pyodbc:
    restart: always
    build: .
    container_name: 'pyodbc'
    working_dir: '/sample'
    tty: true
    volumes:
      - .:/sample
    extra_hosts:
      - "(Host COMPUTER NAME):(IP address of the host)"
    ports:
      - 1443:1443

sample.py

import pyodbc


server = '(IP address of the host),(Host SQL Server operating port)'
username = 'sa'
password = 'password'

cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';database=(Database name);UID='+username+';PWD='+password)
cursor = cnxn.cursor()

print ('Using the following SQL Server version:')
tsql = "SELECT @@version;"
with cursor.execute(tsql):
    rows = cursor.fetchall()
    for row in rows:
        print(str(row))

> docker-compose up -d --build
...
> docker container ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                    NAMES
2c51574bfd36        pyodbc_pyodbc       "/bin/sh -c '/bin/ba…"   51 minutes ago      Up 51 minutes       0.0.0.0:1443->1443/tcp   pyodbc
> docker exec -it 2c51574bfd36 /bin/bash
$ python --version
Python 2.7.12
$ python sample.py
Using the following SQL Server version:
(u'Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) \n\tAug 22 2017 17:04:49 \n\tCopyright (C) 2017 Microsoft Corporation\n\tExpress Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18362: ) (Hypervisor)\n', )

result

I was able to access SQL Server and browse the DB safely.

Later talk (additional content on February 28, 2020)

An error occurs when a here document is assigned as a character string

Changes

sample.py

tsql = '''\
    SELECT *
FROM
	~
INNER JOIN
	~
ON
'''

error contents

$ python sample.py 
  File "sample.py", line 17
SyntaxError: Non-ASCII character '\xe7' in file sample.py on line 18, but no encoding declared; see http://python.org/dev/peps/pep-0263/ for details

  File "sample.py", line 54, in <module>
    with cursor.execute(tsql):
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe7 in position 48: ordinal not in range(128)

Tried procedure

The following article did not solve it. http://shu223.hatenablog.com/entry/20111201/1328334689

The following article did not solve it. https://qiita.com/chatrate/items/eb4b05cd1a6652529fd9

Solved procedure

I solved it by referring to the following. https://kaworu.jpn.org/python/Python%E3%81%AEUnicodeDecodeError%E3%81%AE%E5%AF%BE%E5%87%A6%E6%96%B9%E6%B3%95

sample.py

- with cursor.execute(tsql):
+ with cursor.execute(tsql.decode('utf-8')):

Revised discussion at a later date (additional content on February 29, 2020)

Supports variable host IP address

It seems that there is an alias called host.docker.internal from inside the container that points to% COMPUTERNAME% in the host OS.

docker-compose.yml

-    extra_hosts:
-      - "(Host COMPUTER NAME):(IP address of the host)"