How to create a python function to connect sql server?

Question

To summarize the connection process, I’m trying to create a function to connect the data bunch, but I’m not able to make the connection as expected:

def conectar_com_banco(usuario):
    if usuario in 'illuminati':
        username = 'illuminati' 
        password = 'fnord'
    elif usuario in 'fascist_firewall':
        username = 'tor' 
        password = 'onion'
    else:
        print('usuario_nao_encontrado')
    import pyodbc
    cnxn = pyodbc.connect('DRIVER = SQL Server; SERVER = conspiracy; DATABASE = illuminati; UID='+username+';PWD='+password)
    cnxn.cursor()
    return(cursor)
cursor=conectar_com_banco('illu')
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
print(row)

When I run this code, I get the following reply:

Traceback (most recent call last):

  File "<ipython-input-4-d96fceb45081>", line 18, in <module>
cursor=conectar_com_banco('illu')

  File "<ipython-input-4-d96fceb45081>", line 14, in conectar_com_banco
cnxn = pyodbc.connect('DRIVER = SQL Server; SERVER = conspiracy; DATABASE = illuminati; UID='+username+';PWD='+password)

InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Nome da fonte de dados não encontrado e nenhum driver padrão especificado (0) (SQLDriverConnect)')

When the right answer would be the version of my database as occurs in the reference: https://docs.microsoft.com/pt-br/sql/connect/python/pyodbc/step-3-proof-of-concept-connecting-to-sql-using-pyodbc?view=sql-server-2017 , I used as an example to make the connection simple.

Resolution:

The error occurred in:

cnxn = pyodbc.connect('DRIVER = SQL Server; SERVER = conspiracy; DATABASE = illuminati; UID='+username+';PWD='+password)

So I rewrote it as follows:

def conectar_com_banco(usuario):
    if usuario in 'illuminati':
        server = 'conspiracy' 
        database = 'illuminati' 
        username = 'illuminati' 
        password = 'fnord' 
    elif usuario in 'fascist_firewall':
        server = 'conspiracy' 
        database = 'illuminati'
        username = 'tor' 
        password = 'onion' 
    else:
        print('funcao_nao_encontrado')
    import pyodbc
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cur=cnxn.cursor()
    return(cur)
cursor=conectar_com_banco('illu')
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
print(row)

And returned to me the expected: ('Microsoft SQL Server 2005 - 9.00.5000.00 (X64) \n\tDec 10 2010 10:38:40 \n\tCopyright (c) 1988-2005 Microsoft Corporation\n\tEnterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)\n', )

I haven’t figured out the syntax error I must have made.

PS.: Both {ODBC Driver 13 for SQL Server} and {SQL Server} worked.

Answer 1

I did some more tests and I really only managed to simulate the same mistake you did when I installed the ODBC Driver for SQL Serve in the wrong version:

When I install the driver in the latest version (for SQL Server from 2008 to 2017) the same code works normally:

Link to the driver that I used.

With respect to the parameter DRIVER of the connection string, the common is to use DRIVER={ODBC Driver 17 for SQL Server};, because the same 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 parameter DRIVER of the connection string may vary, and the SQL Server that you are using may be the right 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 on 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 was used the 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 may also be being generated by the difference in architectures between the installed driver and Python (Python 32-bit with the 64-bit driver or vice versa), however this is rarest.

OBS: I ran with Python 32 and 64 bits and had no error for this reason.

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

In my case the pyodbc will locate the ODBC Be it my 32 or 64 bit Python interpreter, since I have it installed for both architectures (My Windows is 64 bit).

you can click on ODBC Data Sources right-click properties and try to change the destino amid %windir%\syswow64\odbcad32.exe or %windir%\system32\odbcad32.exe.

However this is more rare and remember to save the original destination.

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