How to connect MSSQL in Docker by Python

Posted on |

This short post shares the experience of how to connect to a Microsoft SQL Server database inside a docker image. To be honest it’s not difficult but things always become super fucking annoying when docker and Microsoft come together.

As a bonus, here is a short conversation when we try to get over it:

“About 5% of the team have wasted half a day on it at some point”
“OK…let me try 1 hour and see I’m the 5% or 95% “
“lol the other 95% didnt try”

0.Install Microsoft ODBC Driver

  1. Run docker image first
  2. Run docker exec -it <docker container name> bash and find libsqlvdi.so. It should be at /opt/mssql/lib/ . The first line of this file indicated the driver of this database.
  3. We need pyodbc to build the connection between python to MSSQL. If you didn’t have it, simply run sudo pip install pyodbc in the terminal.
  4. Finally here is the sample code:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    import pyodbc
    import pandas as pd
    con = pyodbc.connect('DRIVER=;SERVER={};PORT:{};DATABASE={};UID={};PWD={}'.format(self.db_config['driver'],
    self.db_config['server'],
    self.db_config['port'],
    self.db_config['database'],
    self.db_config['uid'],
    self.db_config['pwd']))
    con.execute("USE <your_target_database_name>")

    df=pd.read_sql('SELECT * FROM [<your_target_table_name>]', self.con)

The [] in the SQL query is try to read table name with spaces (some people did this, lol). And db_config looks like:

1
2
3
4
5
6
driver: 'ODBC Driver 17 for SQL Server' # change to what you found in libsqlvdi.so
port: 1433 # default MSSQL port. 
server: localhost # default path of MSSQL server
database: DATABASE # change to the name of target database
uid: sa # default user name of MSSQL. Change if necessary
pwd: KlinsmannIsFootballGenius # change to your password

And if you want to view all tables name, simply run: SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'