SQL Server Connection - Python

Question

Good afternoon guys I’m having trouble making a connection with sql, whenever I try to make the connection the following error occurs:

('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]Named Pipes Provider: Could not open a connection to SQL Server [2].  (2) (SQLDriverConnect); [08001] [Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired (0); [08001] [Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (2)')
  File "C:\Users\ytalos\Documents\Ytalo\Projeto1\Conexão.py", line 15, in conectar_com_banco
    conexao = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
  File "C:\Users\ytalos\Documents\Ytalo\Projeto1\Conexão.py", line 18, in <module>
    cursor=conectar_com_banco('DW')

I wanted to authenticate the database with Windows credentials.

Name of the Bank: DW
Server: X

def conectar_com_banco(usuario):
    if usuario in 'DW':
        server = 'X' 
        database = 'dw' 
        username = 'teste' 
        password = 'teste1'
     else: 
        print('funcao_nao_encontrado')
    import pyodbc
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cur=cnxn.cursor()
    return(cur)
cursor=conectar_com_banco('DW')
cursor.execute("SELECT @@version;") 
row = cursor.fetchone() 
print(row)

Answer 1

You can use the connection string this way:

params_conn = 'Driver={{ODBC Driver 17 for SQL Server}};' \
              'Server={servidor}, {porta};' \
              'Database={base};' \
              'UID={usuario};' \
              'PWD={senha};'

To connect, you must create the connection string with the above parameters as follows:

str_conexao = "mssql+pyodbc:///?odbc_connect=%s" % urllib.parse.quote_plus(params_conn.format(servidor='X', porta=123, base='minha-base', usuario='user', senha='senha'))

Notice the use of mssql+pyodbc:///?odbc_connect=%s to compose the connection string. Indicates that I want to use the pyodbc (should install on your machine) along with the ODBC driver (which apparently you already have installed). If not installed, follow the instructions at that link from Microsoft itself teaching how to do.

You also need to do the urllib.parse.quote_plus() to perform the required meeting of the special characters used in the connection string.

That’s it. If you still can’t make the connection, make sure everything is installed correctly (ODBCand pyodbc) and also if the server is accessible or available, or even user and passwords are correct.

To access the database using windows credentials, you can use the parameter Trusted_Connection=yes; and there will be no need to use UID nor PWD.