How to Let Python Connect to a SQL Server Database

Question

hello I am trying to connect python to sql server , but only connect on system database like master dtabase , i dont can connect to my databases , someone knows how i can configure my database to connect in python?

import pyodbc as odbc
DRIVER_NAME ='SQL SERVER'
SERVER_NAME='BOOK-FFARDRU9TD'
DATABASE_NAME='Teste' #dosent work with my datavbases
NAME='pierre'
PASS='987652'
connection_string=f"""
    DRIVER={{{DRIVER_NAME}}};
    SERVER={SERVER_NAME};
    DATABASE={DATABASE_NAME};
    Trusted_Connection= yes;
    UID={NAME};
    PWD={PASS};
    """
conn=odbc.connect(connection_string)
print("aaaaaaaaaaa")

and the error is :

pyodbc. ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to open the "Teste" database requested by logon. Logon failed. (4060) (SQLDriverConnect); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to open the "Teste" database prompted by logon. Logon failed. (4060)')

I need help to solve this problem , I think the user that I am trying to use to acess the DB dont have permission , but I dont know how do that.

Answer 1

The error message mentions a database named 'Test', but in the code it is written as 'Teste'.

In a SQL query, you can grant permissions to a database to a specified user with this line:

USE ML_Samples //Database name
GO
EXEC sp_addrolemember 'db_datareader', 'MySQLLogin' //User name

https://learn.microsoft.com/en-us/sql/machine-learning/security/user-permission?view=sql-server-ver16