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
– Parag, Commented Jan 11, 2019 at 12:31
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
– Florian H, Commented Jan 11, 2019 at 12:38
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
– Steve-o169, Commented Jan 16, 2019 at 13:45
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
– Steve-o169, Commented Jan 16, 2019 at 13:50

Grant Shannon · Accepted Answer · 2020-01-31 08:06:29Z

1

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)

edited Jan 31, 2020 at 8:06

answered Aug 21, 2019 at 15:14

Grant Shannon

5,1432 gold badges51 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connecting Python Script to SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related