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, Jan 11 '19 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, Jan 11 '19 at 12:38
>>>Cannot open database "test" requested by the login<<< Either test db does not exists or your login is not mapped to it — sepupic, Jan 11 '19 at 12:47
Is this the actual connection string you used or am I just seeing a typo? You're missing the closing single quote and possibly a semi-colon. I don't really know python, but syntax would be important, also. — Steve-o169, Jan 11 '19 at 13:37
Actually I am using following connection code: pyodbc.connect('Driver=SQL_Server;Server=SQLEXP;user=sa;DB=test, password=pass) @Steveo-169 — Parag, Jan 14 '19 at 06:14
I have already attempted changes suggested by you @Florian H. — Parag, Jan 14 '19 at 06:17
If you are sure that database test exists and online, you should just map your login to it. USE TEST; CREATE USER [44] FOR LOGIN [44] — sepupic, Jan 16 '19 at 07:08
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, Jan 16 '19 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: https://stackoverflow.com/questions/37392476/connect-to-mssql-in-python2-7-11-windows-10-professional — Steve-o169, Jan 16 '19 at 13:50

Grant Shannon · Answer 1 · 2020-01-31T08:06:29.043

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 Answers1