Reputation: 95
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
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