Connecting Python Script to SQL Server

Question

I want to connect my python script to SQL server:

import pyodbc

conn=pyodbc.connect('Driver=SQL_Server;Server=SQLEXP;user=44;DB=test)

I got the following error:

('28000', '[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user. (18456) (SQLDriverConnect);

and

[28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "test" requested by the login. The login failed. (4060); [28000] [Microsoft][SQL Server Native Client 11.0]Invalid connection string attribute(0);

I have gone through other posts about this on blog but no solution found.

provider cannot be found error in python connecting to SQL Server

pyodbc-data-source-name-not-found-and-no-default-driver-specified

Connecting to Microsoft SQL server using Python

I attempted to add code using editing tags in post but it didn't work. — Parag
Can you use the formatting option {} to format your code as such? Don't you need a password to login to your database? The error messsage could appear if the user has no right to logon to the database. — Florian H
Looks to me like the syntax is still wrong if that's the actual connection string. Still missing a closing single quote and a couple of semi-colons. Try pyodbc.connect('Driver=SQL_Server;Server=SQLEXP;DB=test;user=sa; pwd=pass;') — Steve-o169
Also, you could check out this question which has basically an identical error and seems that the asker was able to find a working string: stackoverflow.com/questions/37392476/… — Steve-o169

Grant Shannon Grant Shannon · Accepted Answer · 2019-08-21T15:14:51

Following on from Steve-o169's comment above (below is a simple implementation):

If using SQL Server Authentication you can do this:

import pyodbc
import pandas as pd

cnxn = pyodbc.connect("Driver={SQL Server};"
                        "Server=yourServerName;"
                        "Database=yourDatebaseName;"
                        "uid=yourUserName;pwd=yourPassword")

query="SELECT TOP(10) * FROM yourTable"

df = pd.read_sql_query(query, cnxn)

For Windows Authentication I used the following:

import pyodbc
import pandas as pd

cnxn = pyodbc.connect("Driver={SQL Server};"
                        "Server=yourServerName;"
                        "Database=yourDatebaseName;")

query="SELECT TOP(10) * FROM yourTable"

df = pd.read_sql_query(query, cnxn)

Connecting Python Script to SQL Server

1 Answers