Python Error SQLACHEMY

Question

Is giving an error when I try to connect to SQL SERVER database with SQLALCHEMY

from sqlalchemy import create_engine, engine
import pandas as pd
engine = create_engine('mssql+pyodbc://User:password@server:1433/Bases_testes?drive=SQL+Server+Native+Client+11')
query = "SELECT * FROM dv_rating"
result = pd.read_sql(query,engine)

print(result)

and shows this error

sqlalchemy.exc.Interfaceerror: (pyodbc.Interfaceerror) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (Sqldriverconnect)') (Background on this error at: http://sqlalche.me/e/rvf5)

But I can’t find the error, I have already installed PYODBC and sql Native client and still can’t get the connection

Answer 1

Maybe you should use the SQL Server ODBC driver in place of sql Native client.

To know if you have the driver installed run:

print([x for x in pyodbc.drivers() if x.startswith('ODBC Driver 17 for SQL Server')])

If an empty list is returned install the driver:

• Windows.
• Linux/macOS.

For connection with the MS SQL Server I use:

import pyodbc    
from urllib.parse import quote_plus

from sqlalchemy import create_engine

# String de conexão Windows Server.
parametros = (
    # Driver que será utilizado na conexão
    'DRIVER={ODBC Driver 17 for SQL Server};'
    # IP ou nome do servidor\Versão do SQL.
    'SERVER=192.168.100.178\SQLEXPRESS;'
    # Porta
    'PORT=1433;'
    # Banco que será utilizado.
    'DATABASE=PythonMSSQL;'
    # Nome de usuário.
    'UID=python;'
    # Senha.
    'PWD=123456')

# Convertendo a string para um padrão de URI HTML.
url_db = quote_plus(parametros)

# Conexão.
engine = create_engine('mssql+pyodbc:///?odbc_connect=%s' % url_db)

Remember that additional server settings or even SQL Server settings may be required (login type, firewall, allow TCP connections, port, etc).