Parag
Parag

Reputation: 95

Connecting Python Script to SQL Server

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

Upvotes: 3

Views: 5880

Answers (1)

Grant Shannon
Grant Shannon

Reputation: 5075

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)

Upvotes: 1

Related Questions