How to connect to Microsoft SQL Server from Python code?

Question

I am trying to run Microsoft SQL Server in Docker and connect to it via Python with pyodbc.

It is the first time I am working with this database and drives me nuts because it killed my system 5 times in a row and I don't understand how to configure a simple connection.

The way I run the database:

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=mySecretPassword1234567890' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2017-latest

I use this code to connect:

import pyodbc

conn_str = (
    r"Driver={ODBC Driver 17 for SQL Server};"
    r"Server=127.0.0.1;"
    r"Database=testdb;"
    r"UID=sa;"
    r"PWD=mySecretPassword1234567890;"
    r"Trusted_Connection=yes;"
)
conn = pyodbc.connect(conn_str)

odbcinst details:

$ odbcinst -j
unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/dmytro/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Debug trace from ODBC driver:

[ODBC][23857][1592579186.233946][__handles.c][460]
        Exit:[SQL_SUCCESS]
            Environment = 0x7fc1a9973e00
[ODBC][23857][1592579186.233981][SQLSetEnvAttr.c][189]
        Entry:
            Environment = 0x7fc1a9973e00
            Attribute = SQL_ATTR_ODBC_VERSION
            Value = 0x3
            StrLen = 4
[ODBC][23857][1592579186.234005][SQLSetEnvAttr.c][381]
        Exit:[SQL_SUCCESS]
[ODBC][23857][1592579186.234024][SQLAllocHandle.c][377]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fc1a9973e00
[ODBC][23857][1592579186.234044][SQLAllocHandle.c][493]
        Exit:[SQL_SUCCESS]
            Output Handle = 0x7fc1a997ae00
[ODBC][23857][1592579186.234321][SQLDriverConnectW.c][290]
        Entry:
            Connection = 0x7fc1a997ae00
            Window Hdl = 0x0
            Str In = [Driver={ODBC Driver 17 for SQL Server};Server=127.0.0.1;Database=testdb;UID=sa;PWD=mySecretPassword1234567890;Trusted_Connectio...][length = 133 (SQL_NTS)]
            Str Out = 0x0
            Str Out Max = 0
            Str Out Ptr = 0x0
            Completion = 0
        UNICODE Using encoding ASCII 'UTF-8' and UNICODE 'UCS-2-INTERNAL'

[ODBC][23857][1592579186.234896][SQLConnect.c][1138]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found
[ODBC][23857][1592579186.234929][SQLDriverConnect.c][748]
        Entry:
            Connection = 0x7fc1a997ae00
            Window Hdl = 0x0
            Str In = [Driver={ODBC Driver 17 for SQL Server};Server=127.0.0.1;Database=testdb;UID=sa;PWD=**************************;Trusted_Connection...][length = 133 (SQL_NTS)]
            Str Out = 0x7ffeef507600
            Str Out Max = 2048
            Str Out Ptr = 0x0
            Completion = 0
[ODBC][23857][1592579186.235105][SQLConnect.c][1138]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found
[ODBC][23857][1592579186.235134][SQLGetDiagRecW.c][535]
        Entry:
            Connection = 0x7fc1a997ae00
            Rec Number = 1
            SQLState = 0x7ffeef5099a4
            Native = 0x7ffeef509194
            Message Text = 0x7ffeef5091a0
            Buffer Length = 1023
            Text Len Ptr = 0x7ffeef50919e
[ODBC][23857][1592579186.235160][SQLGetDiagRecW.c][596]
        Exit:[SQL_SUCCESS]
            SQLState = [01000]
            Native = 0x7ffeef509194 -> 0
            Message Text = [[unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found]
[ODBC][23857][1592579186.235205][SQLFreeHandle.c][290]
        Entry:
            Handle Type = 2
            Input Handle = 0x7fc1a997ae00
[ODBC][23857][1592579186.235224][SQLFreeHandle.c][339]
        Exit:[SQL_SUCCESS]

I read through docs and searched answers in StackOverflow but so far I cannot get it. How can I debug it further?

Important: I cannot switch from the Docker approach of running Microsoft SQL Server and I cannot switch to a different database as well.

it's throwing the following error several times > Can't open lib 'ODBC Driver 17 for SQL Server' : file not found and try to see the podbc drivers available pyodbc.drivers() — abestrad
– abestrad, Commented Jun 19, 2020 at 15:48
pip install pyodbc and check pyodbc.drivers() again. It should show a list like >>> pyodbc.drivers() ['SQL Server', 'Microsoft Access Driver (*.mdb, *.accdb)', 'Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)', 'Microsoft Access dBASE Driver (*.dbf, *.ndx, *.mdx)', 'Microsoft Access Text Driver (*.txt, *.csv)', 'ODBC Driver 13 for SQL Server', 'ODBC Driver 17 for SQL Server', 'SQL Server Native Client 11.0', 'SQL Server Native Client RDA 11.0'] — abestrad
– abestrad, Commented Jun 19, 2020 at 16:08
@abestrad This is exactly the problem, it is installed but I still don't get any drivers. Is it a bug in pyodbc? — Dmytro Chasovskyi
– Dmytro Chasovskyi, Commented Jun 19, 2020 at 16:13
Did you see any of the following messages? > Collecting pyodbc > Downloading https://files.pythonhosted.org/packages/........./pyodbc-4.......whl (61kB) > Installing collected packages: pyodbc Successfully installed pyodbc- That's going to help you to track the problem. It might be an issue with pip and not pyodbc. — abestrad
– abestrad, Commented Jun 19, 2020 at 16:17

Dmytro Chasovskyi · Accepted Answer · 2020-06-22 08:05:09Z

1

It came out that the problem was with drivers. pyodbc.drivers() returned an empty list. In order to communicate between Micorosft SQL Server and Python you need extra ODBC driver layer

Thank you to @abestrad who pointed out this.

answered Jun 22, 2020 at 8:05

Dmytro Chasovskyi

3,7018 gold badges54 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Yarden Shoham · Accepted Answer · 2020-06-19 19:41:59Z

0

Try this:

import pyodbc

conn_str = (
    r"Driver={ODBC Driver 17 for SQL Server};"
    r"Server=host.docker.internal;"
    r"Database=testdb;"
    r"UID=sa;"
    r"PWD=mySecretPassword1234567890;"
    r"Trusted_Connection=yes;"
)
conn = pyodbc.connect(conn_str)

answered Jun 19, 2020 at 19:41

Yarden Shoham

2192 silver badges10 bronze badges

Collectives™ on Stack Overflow

How to connect to Microsoft SQL Server from Python code?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related