[DOCKER] 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)"

Recommended Posts

Access the host SQL Server with python27 / pyodbc on the container
ODBC access to SQL Server from Linux with Python
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 6: Using the model
[In-Database Python Analysis Tutorial with SQL Server 2017]
Download files on the web with Python
Get the width of the div on the server side with Selenium + PhantomJS + Python
Deploy and use the prediction model created in Python on SQL Server
Install the python module with pip on a server without root privileges
Drawing tips with matplotlib on the server side
Introduction to Python with Atom (on the way)
Local server with python
Yum command to access MySQL with Python 3 on Linux
Make a breakpoint on the c layer with python
Information for controlling the motor with Python on RaspberryPi
Install django on python + anaconda and start the server
Test.py is not reflected on the web server in Python3.
Try to extract Azure SQL Server data table with pyodbc
When you access the web server, L Chika with Arduino
Easy HTTP server with Python
Call the API with python3.
Access Google Drive with Python
Save images on the web to Drive with Python (Colab)
Get the host name of the host PC with Docker on Linux
Put Ubuntu in Raspi, put Docker on it, and control GPIO with python from the container
Life game with Python [I made it] (on the terminal & Tkinter)
How is the progress? Let's get on with the boom ?? in Python
Control the motor with a motor driver using python on Raspberry Pi 3!
Get started with the Python framework Django on Mac OS X
Synchronizing with the server port forwarding ssh on localhost fails [Resolved]
Settings until the Dango project is started on the server with Pycharm
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 1: Download sample data
Host the network library Mirror for Unity on a Linux server
Extract the xz file with python
Install Python3 on Sakura server (FreeBSD)
Get the weather with Python requests 2
Find the Levenshtein Distance with python
Hit the Etherpad-lite API with Python
Install the Python plugin with Netbeans 8.0.2
I liked the tweet with python. ..
Memorize the Python commentary on YouTube.
Notes on using rstrip with python.
Master the type with Python [Python 3.9 compatible]
Get the host name in Python
Getting started with Python 3.8 on Windows
Sakura Use Python on the Internet
Access the Twitter API in Python
Notes on using matplotlib on the server
[Memo] Tweet on twitter with python
[Python] I tried to visualize the night on the Galactic Railroad with WordCloud!
Put Cabocha 0.68 on Windows and try to analyze the dependency with Python
Use python on Raspberry Pi 3 to light the LED with switch control!
[In-Database Python Analysis Tutorial with SQL Server 2017] Step 3: Data Exploration and Visualization
Edit the file of the SSH connection destination server on the server with VS Code
What to do if the server doesn't start with python manage.py runserver
2019 version: Unauthorized access trend analysis (example of general-purpose server on the cloud)
I tried with the top 100 PyPI packages> I tried to graph the packages installed on Python
[Python, ObsPy] I drew a beach ball on the map with Cartopy + ObsPy.
Read the file with python and delete the line breaks [Notes on reading the file]
When you access the web server, L Chika (HTTPS compatible) with Arduino
Compare nighttime and daytime returns on the Nikkei Stock Average with python
Rock-paper-scissors with Python Let's run on a Windows local server for beginners