Connect Python with SQL Server Database

Question

When I am trying to connect python with SQL Server, following error occurred.

"pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect)')"

Following is the my code.

import pyodbc
connection = pyodbc.connect("Driver={SQL Server}; Server=localhost;          
Database=emotionDetection; uid=uname ;pwd=pw;Trusted_Connection=yes")
cursor = connection.cursor()
SQLCommand = ("INSERT INTO emotion"  "(happy, sad, angry) "
          "VALUES (?,?,?)")
Values = ['smile','cry','blame']
cursor.execute(SQLCommand,Values)
connection.commit()
connection.close()

This is my first attempt to connect Python with sql server. I don't have an idea what would be the driver name, server name, username and password.Do you have any idea of what should be my configuration. Please help me.

Answer 1

CONNECTION FROM WINDOWS TO MS SQL SERVER DATABASE:

Here you have an example I use myself to connect to MS SQL database table with a Python script:

import pyodbc
server = 'ip_database_server'
database = 'database_name'
username = 'user_name'
password = 'user_password'
driver = '{SQL Server}' # Driver you need to connect to the database
port = '1433'
cnn = pyodbc.connect('DRIVER='+driver+';PORT=port;SERVER='+server+';PORT=1443;DATABASE='+database+';UID='+username+
                 ';PWD='+password)
cursor = cnn.cursor()

'User' and 'password' and 'table_name' are attibutes defined by the DB administrator, and he should give them to you. The port to connect to is also defined by the admin. If you are trying to connect from a Windows device to the DB, go to ODBC Data Source Administrator from Windows, and check if you have installed the driver:

Where is the ODBC data source administrator in a Windows machine.

The image is in spanish, but you only have to click on 'Drivers' tab, and check if the driver is there as in the image.

CONNECTION FROM LINUX/UNIX TO MS SQL SERVER DATABASE:

If you are working in Linux/Unix, then you shoud install a ODBC manager like 'FreeTDS' and 'unixODBC'. To configure them, you have some examples in the following links:

Example: Connecting to Microsoft SQL Server from Linux/Unix

Example: Installing and Configuring ODBC