Python Error SQLACHEMY

Navigation
0

It is giving an error when I try to connect to the 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)

Show 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: link )

But I can not find the error, I already tried PYODBC and sql native client and I still can not connect.

    
asked by anonymous 28.10.2018 / 16:22

1 answer

2

You may want to use the SQL Server ODBC driver instead of the sql native client .

To find out 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:

For connection to 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)

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

    
28.10.2018 / 19:11
Error reading txt file line by line Replace items in a list in a column of a dataframe