How to create a function in python to connect the sql server?

I did some more testing and I was only able to simulate the same error as when installing the ODBC Driver for SQL Serve in the wrong version:

WhenIinstallthedriverinthelatestversion(forSQLServer2008to2017),thesamecodeworksnormally:

Link to the driver that I used.

Regarding the DRIVER parameter of the connection string, the common is to use DRIVER={ODBC Driver 17 for SQL Server}; , because it covers several versions of SQL Server:

• {SQL Server} - released with SQL Server 2000
• {SQL Native Client} - released with SQL Server 2005 (also known as version 9.0)
• {SQL Server Native Client 10.0} - released with SQL Server 2008
• {SQL Server Native Client 11.0} - released with SQL Server 2012
• {ODBC Driver 11 for SQL Server} - supports SQL Server 2005 through 2014
• {ODBC Driver 13 for SQL Server} - supports SQL Server 2005 through 2016
• {ODBC Driver 13.1 for SQL Server} - supports SQL Server 2008 through 2016
• {ODBC Driver 17 for SQL Server} - supports SQL Server 2008 through 2017

However, the driver version , as well as the DRIVER f parameter of the connection string may vary, and the SQL Server you are using may be the correct one for your infrastructure.

On Windows Server + SQL Server 2017 I used the following code for testing:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
""""""
# Conector recomendado na documentação oficial.
import pyodbc


def conectar_mssql_docker(usuario, senha):
    con = pyodbc.connect(
        # Driver que será utilizado na conexão
        'DRIVER={ODBC Driver 17 for SQL Server};'
        # IP ou nome do servidor.
        'SERVER=192.168.100.178\SQLEXPRESS;'
        # Porta
        'PORT=1433;'
        # Banco que será utilizado.
        'DATABASE=PythonMSSQL;'
        # Nome de usuário.
        f'UID={usuario};'
        # Senha.
        f'PWD={senha}')

    # Criando o cursor que irá executar os comandos SQL (instruções DML, DDL, etc).
    cur = con.cursor()
    return cur


if __name__ == "__main__":
    usuario = str(input('Usuario: '))
    print(usuario)
    senha = str(input('Senha: '))
    print(senha)

    cursor = conectar_mssql_docker(usuario=usuario, senha=senha)
    cursor.execute("SELECT @@version;")
    row = cursor.fetchone()

    print(row[0])

Already using SQL Server in Docker use the following code:

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
""""""
# Conector recomendado na documentação oficial.
import pyodbc


def conectar_mssql_docker(senha):
    # Conexão Docker imagem Linux.
    con = pyodbc.connect(
        # Driver que será utilizado na conexão
        'DRIVER={ODBC Driver 17 for SQL Server};'
        # IP ou nome do servidor\
        'SERVER=192.168.100.118;'
        # Porta
        'PORT=1433;'
        # Banco que será utilizado (Criar banco).
        'DATABASE=tempdb;'
        # Nome de usuário (Usuário default da imagem).
        'UID=SA;'
        # Senha.
        f'PWD={senha}')

    # Criando o cursor que irá executar os comandos SQL (instruções DML, DDL, etc).
    cur = con.cursor()
    return cur


if __name__ == "__main__":
    senha = str(input('Senha: '))
    print(senha)

    cursor = conectar_mssql_docker(senha=senha)
    cursor.execute("SELECT @@version;")
    row = cursor.fetchone()

    print(row[0])

For the above code we used docker-compose :

version: '3.7'

services:
  db:
    image: microsoft/mssql-server-linux:2017-CU11
    container_name: SQLServer
    restart: on-failure
    ports:
      - '1433:1433'
    volumes:
      - /etc/localtime:/etc/localtime:ro
      - /etc/timezone:/etc/timezone:ro
      # - mssql-volume:/var/opt/mssql
      # - ./data/db:/var/opt/mssql
    environment:
      ACCEPT_EULA: 'Y'
      SA_PASSWORD: 'Python.123456'
      MSSQL_PID: 'Express'
    # devices:
    #   - "/dev/ttyUSB0:/dev/ttyUSB0"

Another possibility

This error can also be caused by the difference in architectures between the installed driver and Python (Python 32-bit with 64-bit driver or vice versa), however this is rarer .

OBS : I ran with Python 32 and 64 bits and I did not have the error for this reason.

To check you can access Painel de Controle\Sistema e Segurança\Ferramentas Administrativas :

In my case the pyodbc will find ODBC be my Python 32 or 64 bit interpreter, since I have it installed for the 2 architectures (My Windows is 64 bits).

You can right click on ODBC Data Sources and click properties and try to change destino between %windir%\syswow64\odbcad32.exe or %windir%\system32\odbcad32.exe .

However, this is rarer and remember to save the original destination .

These were the most common problems I had with MS SQL Server databases.